com.vertec.daoimpl.ReportDAOImpl.java Source code

Java tutorial

Introduction

Here is the source code for com.vertec.daoimpl.ReportDAOImpl.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.vertec.daoimpl;

import com.vertec.hibe.model.Account;
import com.vertec.hibe.model.BalanceSheetData;
import com.vertec.hibe.model.Bin;
import com.vertec.hibe.model.Branch;
import com.vertec.hibe.model.BranchProductmaster;
import com.vertec.hibe.model.BudgetPlan;
import com.vertec.hibe.model.Company;
import com.vertec.hibe.model.Depreciation;
import com.vertec.hibe.model.DepreciationData;
import com.vertec.hibe.model.DisposeItems;
import com.vertec.hibe.model.Gin;
import com.vertec.hibe.model.Grn;
import com.vertec.hibe.model.Gtn;
import com.vertec.hibe.model.InvoiceItem;
import com.vertec.hibe.model.Payment;
import com.vertec.hibe.model.ReturnByCustomer;
import com.vertec.hibe.model.ReturnBySupplier;
import com.vertec.hibe.model.ReturnToCustomer;
import com.vertec.hibe.model.ReturnToSupplier;
import com.vertec.hibe.model.StockReturn;
import com.vertec.hibe.model.SystemData;
import com.vertec.hibe.model.Transaction;
import com.vertec.util.NewHibernateUtil;
import com.vertec.util.VertecConstants;
import com.vertec.util.test;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

/**
 *
 * @author vertec-r
 */
public class ReportDAOImpl {

    public List<Object[]> invoiceItemToReport(int invoiceId) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT p.product_name,ii.unit_price,ii.quantity,ii.tot_amount,ii.discount,ii.tot_after_dis FROM invoice_item ii inner join product_master pm on ii.product_master_id=pm.product_master_id\n"
                                + "inner join product p on pm.product_id=p.product_id\n"
                                + "where ii.invoice_id=:invoiceId");
                query.setParameter("invoiceId", invoiceId);

                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public Object[] invoiceHeaderToReport(int invoiceId) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT i.invoice_id,i.invoiced_date,i.invoice_total,i.discount,i.tot_after_discount,c.customer_name,c.address as cadd,b.address as badd,b.contact_no,sum(i.tot_after_discount-(i.invoice_total-i.discount)) as tax\n"
                                + "FROM invoice i inner join customer c on i.customer_id=c.customer_id inner join branch b on i.branch_id=b.branch_id\n"
                                + "where i.invoice_id=:invoiceId");
                query.setParameter("invoiceId", invoiceId);
                Object[] invoice = (Object[]) query.uniqueResult();
                return invoice;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> dailyInvoiceReport(String selectDate) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT i.invoice_id,c.customer_name,b.branch_name,i.invoice_total,i.discount,i.tot_after_discount,i.invoiced_date\n"
                                + "FROM invoice i inner join customer c on i.customer_id=c.customer_id inner join branch b on i.branch_id=b.branch_id\n"
                                + "where i.invoiced_date=:selectDate");
                query.setParameter("selectDate", selectDate);

                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> periodicallyInvoiceReport(String[] daeArr) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT i.invoice_id,c.customer_name,b.branch_name,i.invoice_total,i.discount,i.tot_after_discount,i.invoiced_date\n"
                                + "FROM invoice i inner join customer c on i.customer_id=c.customer_id inner join branch b on i.branch_id=b.branch_id\n"
                                + "where i.invoiced_date between :fromDate and :toDate");
                query.setParameter("fromDate", daeArr[0]);
                query.setParameter("toDate", daeArr[1]);

                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> periodicallyOutstanding() {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT sum(balance_amount) as outstanding,c.customer_name FROM outstandig_invoice oi\n"
                                + "inner join invoice i on oi.invoice_id=i.invoice_id\n"
                                + "inner join customer c on i.customer_id=c.customer_id\n"
                                + "group by c.customer_id");

                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> branchWiseProduct(int branchId) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT p.product_name,p.product_code,pm.selling_price,bp.quantity FROM branch_productmaster bp inner join product_master pm on bp.product_master_id=pm.product_master_id\n"
                                + "inner join branch b on bp.branch_id=b.branch_id\n"
                                + "inner join product p on pm.product_id=p.product_id\n"
                                + "where b.branch_id=:branchId");
                query.setParameter("branchId", branchId);
                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> branchWiseProductMaster(int branchId) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery("SELECT p.product_name,p.product_code,bs.quantity\n"
                        + "FROM branch_stock bs inner join branch b on bs.branch_id=b.branch_id\n"
                        + "inner join product p on bs.product_id=p.product_id\n" + "where b.branch_id=:branchId");
                query.setParameter("branchId", branchId);
                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> releaseProductBranchWise(String[] date, int branchId) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT p.product_name,p.product_code,pm.selling_price,sum(ii.quantity) as pQuan\n"
                                + "FROM invoice_item ii inner join invoice i on ii.invoice_id=i.invoice_id\n"
                                + "inner join product_master pm on ii.product_master_id=pm.product_master_id\n"
                                + "inner join product p on pm.product_id=p.product_id\n"
                                + "inner join branch b on i.branch_id=b.branch_id\n"
                                + "where b.branch_id=:branchId and i.invoiced_date between :fromDate and :toDate group by pm.product_master_id");
                query.setParameter("branchId", branchId);
                query.setParameter("fromDate", date[0]);
                query.setParameter("toDate", date[1]);
                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> releaseProductPeriodicalyyVehicle(String[] date) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT p.product_name,p.product_code,pm.selling_price,sum(ii.quantity) as pQuan,v.vehicle_reg_no FROM invoice i\n"
                                + "inner join invoice_item ii on i.invoice_id=ii.invoice_id\n"
                                + "left join vehicle v on i.vehicle_id=v.vehicle_id\n"
                                + "inner join product_master pm on ii.product_master_id=pm.product_master_id\n"
                                + "inner join product p on pm.product_id=p.product_id\n"
                                + "where i.invoice_type='V' and i.invoiced_date between :fromDate and :toDate group by ii.product_master_id,v.vehicle_id");
                query.setParameter("fromDate", date[0]);
                query.setParameter("toDate", date[1]);
                List<Object[]> inList = query.list();
                return inList;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Object[]> releaseProductPeriodicalyySP(String[] date, int user_id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    public int getLastInvoiceId() {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();

        if (session != null) {
            try {

                SQLQuery query = session.createSQLQuery(
                        "SELECT invoice_id FROM invoice\n" + "ORDER BY invoice_id DESC\n" + "LIMIT 1;");
                int inId = (Integer) query.uniqueResult();
                return inId;

            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return 0;
    }

    public List<Transaction> loadAssetCreditSide(Account a, Date from, Date to, Company company) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT t FROM Transaction t WHERE t.credit = :account AND t.companyId=:com AND t.date BETWEEN :from AND :to");
                query.setParameter("account", a);
                query.setParameter("from", from);
                query.setParameter("to", to);
                query.setParameter("com", company);
                List<Transaction> pcList = query.list();
                return pcList;

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Transaction> loadAllOfAccountByPayType(Date fdate, Date todate, Company com) {

        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT t FROM Transaction t WHERE t.companyId=:com  AND t.date  BETWEEN :fdate AND :todate");
                //                query.setParameter("cid",cid);

                query.setParameter("fdate", fdate);
                query.setParameter("todate", todate);
                //                query.setParameter("did",did);
                query.setParameter("com", com);

                List<Transaction> aList = query.list();
                return aList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }

        return null;
    }

    public List<Account> loadAllOfAccountByCash1(Company com) {

        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {

                //                Query query = session.createQuery("SELECT t FROM Transaction t WHERE t.companyId=:com  AND t.date  BETWEEN :fdate AND :todate");
                Query query = session.createQuery(
                        "SELECT a FROM Account a WHERE a.companyId=:com AND a.isValid=:valid AND (a.subtypeId.name='Cash' OR a.subtypeId.name='Bank')");
                //                query.setParameter("cid",cid);

                query.setParameter("valid", true);
                //                query.setParameter("did",did);
                query.setParameter("com", com);

                List<Account> aList = query.list();
                return aList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }

        return null;
    }

    public List<String[]> loadAccountsForProfit(List<Account> account, Date from, Date to) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                List<String[]> starr = new ArrayList<>();
                for (Account a : account) {
                    Query query1 = session
                            .createQuery("SELECT t FROM Transaction t WHERE t.date BETWEEN :from AND :to");
                    query1.setParameter("from", from);
                    query1.setParameter("to", to);
                    List<Transaction> tList = query1.list();

                    for (Transaction transaction : tList) {
                        System.out.println("CHECK >>>" + transaction.getDebit().getSubtypeId().getTypeId().getName()
                                + "   " + transaction.getCredit().getSubtypeId().getTypeId().getName());

                        if (!transaction.getDebit().getSubtypeId().getTypeId().getName().equals("Capital")
                                && !transaction.getCredit().getSubtypeId().getTypeId().getName()
                                        .equals("Capital")) {
                            System.out.println(
                                    "NO >>>" + transaction.getDebit().getSubtypeId().getTypeId().getName());
                            boolean bool = true;
                            for (String[] st : starr) {
                                if (st[0].equals(transaction.getId() + "")) {
                                    bool = false;
                                }
                            }
                            if (bool) {
                                String[] arr = { transaction.getId() + "", transaction.getCredit().getName(),
                                        transaction.getDebit().getName(), transaction.getPrice() + "" };
                                starr.add(arr);
                            }
                        }
                    }
                }
                List<String[]> arr = new ArrayList<>();

                for (String[] st : starr) {
                    boolean cbool = false;
                    boolean dbool = false;
                    boolean is_change = false;

                    Query query1 = session.createQuery("SELECT t FROM Transaction t WHERE t.id=:id");
                    query1.setParameter("id", Integer.parseInt(st[0]));
                    Transaction t1 = (Transaction) query1.uniqueResult();

                    System.out
                            .println(">>>>>" + t1.getDescription() + "----" + t1.getDebit().getSubtypeId().getName()
                                    + "----" + t1.getCredit().getSubtypeId().getName());

                    if ((t1.getDebit().getSubtypeId().getName().equals("Cash")
                            | t1.getDebit().getSubtypeId().getName().equals("Bank"))
                            & (t1.getCredit().getSubtypeId().getName().equals("Cash")
                                    | t1.getCredit().getSubtypeId().getName().equals("Bank"))) {
                        dbool = true;
                        cbool = true;
                    } else if ((!t1.getDebit().getSubtypeId().getName().equals("Cash")
                            | !t1.getDebit().getSubtypeId().getName().equals("Bank"))
                            & (t1.getCredit().getSubtypeId().getName().equals("Cash")
                                    | t1.getCredit().getSubtypeId().getName().equals("Bank"))) {
                        dbool = false;
                        cbool = true;
                        if (t1.getDebit().getSubtypeId().getName().equals("Creditors")
                                | t1.getDebit().getSubtypeId().getName().equals("Debtors")) {
                            cbool = false;
                            System.out.println("Ignored<<<<<<<<<<<<<<<<<<<<<<<");
                        }

                    } else if ((t1.getDebit().getSubtypeId().getName().equals("Cash")
                            | t1.getDebit().getSubtypeId().getName().equals("Bank"))
                            & (!t1.getCredit().getSubtypeId().getName().equals("Cash")
                                    | !t1.getCredit().getSubtypeId().getName().equals("Bank"))) {
                        dbool = true;
                        cbool = false;

                        if (t1.getCredit().getSubtypeId().getName().equals("Creditors")
                                | t1.getCredit().getSubtypeId().getName().equals("Debtors")) {
                            dbool = false;
                            System.out.println("Ignored<<<<<<<<<<<<<<<<<<<<<<<");
                        }
                    } else if ((!t1.getDebit().getSubtypeId().getName().equals("Cash")
                            | !t1.getDebit().getSubtypeId().getName().equals("Bank"))
                            & (!t1.getCredit().getSubtypeId().getName().equals("Cash")
                                    | !t1.getCredit().getSubtypeId().getName().equals("Bank"))) {
                        dbool = false;
                        cbool = false;
                    }

                    if (cbool & dbool) {
                    } else if (!cbool & !dbool) {
                    } else {
                        double amount = 0.0;
                        String accountname = "";

                        if (dbool & dbool) {
                            amount = t1.getPrice();
                            accountname = t1.getCredit().getName() + "  (" + t1.getCredit().getSubtypeId().getName()
                                    + ")";
                        } else {
                            amount = (-1) * t1.getPrice();
                            accountname = t1.getDebit().getName() + "  (" + t1.getDebit().getSubtypeId().getName()
                                    + ")";
                        }
                        String[] sarr = { st[0], accountname, amount + "" };
                        arr.add(sarr);
                    }
                }
                List<String[]> finalarr = new ArrayList<>();
                for (String[] starray : arr) {
                    boolean finalbool = true;
                    for (String[] arr2 : finalarr) {
                        if (starray[1].equals(arr2[0])) {
                            finalbool = false;
                            arr2[1] = (Double.parseDouble(starray[2]) + Double.parseDouble(arr2[1])) + "";
                        }
                    }
                    if (finalbool) {
                        String[] newarr = { starray[1], starray[2] };
                        finalarr.add(newarr);
                    }
                }
                for (String[] starray : finalarr) {
                    System.out.println(starray[0] + "_____________________" + starray[1]);
                }
                return finalarr;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Transaction> loadAssetDebitSide(Account a, Date from, Date to, Company company) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT t FROM Transaction t WHERE t.debit = :account AND t.companyId=:com AND t.date BETWEEN :from AND :to");
                query.setParameter("account", a);
                query.setParameter("from", from);
                query.setParameter("to", to);
                query.setParameter("com", company);
                List<Transaction> pcList = query.list();
                return pcList;

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public Account getAccountById(int id) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT a FROM Account a WHERE a.id=:id");
                query.setParameter("id", id);
                Account pcList = (Account) query.uniqueResult();
                return pcList;

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public Double getAccountOpenBalance(int id) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT a.balance FROM Account a WHERE a.id=:id");
                query.setParameter("id", id);
                Double pcList = (Double) query.uniqueResult();
                return pcList;

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public Double loadBalanceBackward(Account a, Date date, Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Double debit = 0.0;
                Double credit = 0.0;
                Query query = session.createQuery(
                        "SELECT SUM(t.price) FROM Transaction t WHERE t.debit = :account AND t.companyId=:com AND t.date < :date");
                query.setParameter("account", a);
                query.setParameter("date", date);
                query.setParameter("com", com);
                Object ob = query.uniqueResult();
                if (ob == null) {
                } else {
                    debit = (Double) ob;
                }
                Query query1 = session.createQuery(
                        "SELECT SUM(t.price) FROM Transaction t WHERE t.credit = :account AND t.companyId=:com AND t.date < :date");
                query1.setParameter("account", a);
                query1.setParameter("date", date);
                query1.setParameter("com", com);
                //                credit =(Double) query1.uniqueResult();
                Object ob2 = query1.uniqueResult();
                if (ob2 == null) {
                } else {
                    credit = (Double) ob2;
                }
                System.out.println("<><><><><><><><><><><><><><><><><><><><><><><><><><>");
                System.out.println("DEBIT :" + debit);
                System.out.println("CREDIT :" + credit);
                System.out.println("<><><><><><><><><><><><><><><><><><><><><><><><><><>");
                return debit - credit;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<String[]> loadTrialbalance(Date from, Date to, Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT a FROM Account a ");
                List<Account> account = (List<Account>) query.list();
                List<String[]> starr = new ArrayList<>();
                for (Account a : account) {

                    Double debit = 0.0;
                    Double credit = 0.0;

                    Query query1 = session.createQuery(
                            "SELECT SUM(t.price) FROM Transaction t WHERE t.debit=:acc AND t.companyId=:com AND t.date BETWEEN :from AND :to");
                    query1.setParameter("from", from);
                    query1.setParameter("to", to);
                    query1.setParameter("acc", a);
                    query1.setParameter("com", com);
                    Object ob1 = query1.uniqueResult();
                    if (ob1 == null) {
                    } else {
                        debit = (Double) ob1;
                    }

                    Query query2 = session.createQuery(
                            "SELECT SUM(t.price) FROM Transaction t WHERE t.credit=:acc AND t.companyId=:com AND t.date BETWEEN :from AND :to");
                    query2.setParameter("from", from);
                    query2.setParameter("to", to);
                    query2.setParameter("acc", a);
                    query2.setParameter("com", com);
                    Object ob2 = query2.uniqueResult();
                    if (ob2 == null) {
                    } else {
                        credit = (Double) ob2;
                    }

                    String[] arr = { a.getName(), (debit - credit) + "" };
                    starr.add(arr);

                }
                return starr;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Account> getAccountsByCompany(Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT a FROM Account a WHERE a.companyId=:com");
                query.setParameter("com", com);
                List<Account> pcList = (List<Account>) query.list();
                return pcList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Account> getAccountsByCompany(Company com, String acc) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = null;
                if (acc.equals("")) {
                    query = session.createQuery("SELECT a FROM Account a WHERE a.companyId=:com");
                } else {
                    query = session.createQuery("SELECT a FROM Account a WHERE a.companyId=:com AND a.id=:id");
                    query.setParameter("id", Integer.parseInt(acc));
                }

                //                
                //                Query query = session.createQuery("SELECT a FROM Account a WHERE a.companyId=:com");
                query.setParameter("com", com);
                List<Account> pcList = (List<Account>) query.list();
                return pcList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<BalanceSheetData> getBalanceSheetData(Date from, Date to, Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT a FROM Account a");
                List<Account> account = (List<Account>) query.list();
                List<BalanceSheetData> starr = new ArrayList<>();
                for (Account a : account) {

                    Double debit = 0.0;
                    Double credit = 0.0;

                    Query query1 = session.createQuery(
                            "SELECT SUM(t.price) FROM Transaction t WHERE t.debit=:acc AND t.companyId=:com AND t.date BETWEEN :from AND :to");
                    query1.setParameter("from", from);
                    query1.setParameter("to", to);
                    query1.setParameter("acc", a);
                    query1.setParameter("com", com);
                    Object ob1 = query1.uniqueResult();
                    if (ob1 == null) {
                    } else {
                        debit = (Double) ob1;
                    }

                    Query query2 = session.createQuery(
                            "SELECT SUM(t.price) FROM Transaction t WHERE t.credit=:acc AND t.companyId=:com AND t.date BETWEEN :from AND :to");
                    query2.setParameter("from", from);
                    query2.setParameter("to", to);
                    query2.setParameter("acc", a);
                    query2.setParameter("com", com);
                    Object ob2 = query2.uniqueResult();
                    if (ob2 == null) {
                    } else {
                        credit = (Double) ob2;
                    }
                    BalanceSheetData bsd = new BalanceSheetData();
                    bsd.setA(a);
                    bsd.setSt(null);
                    bsd.setAmount((debit - credit));
                    starr.add(bsd);
                }
                return starr;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<DepreciationData> getDepreciationData(Date from, Date to, Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT d FROM Depreciation d");
                List<Depreciation> depreciation = (List<Depreciation>) query.list();
                List<DepreciationData> starr = new ArrayList<>();
                for (Depreciation dep : depreciation) {
                    Query query1 = session.createQuery(
                            "SELECT SUM(t.price) FROM Transaction t WHERE t.depreciationId=:dep AND t.debit.subtypeId.name='Depreciation' AND t.companyId=:com AND t.date BETWEEN :from AND :to");
                    query1.setParameter("from", from);
                    query1.setParameter("to", to);
                    query1.setParameter("dep", dep);
                    query1.setParameter("com", com);
                    Double ob1 = (Double) query1.uniqueResult();

                    Query query2 = session.createQuery(
                            "SELECT SUM(t.price) FROM Transaction t WHERE t.depreciationId=:dep AND t.debit.subtypeId.name='Depreciation' AND t.companyId=:com AND t.date < :from ");
                    query2.setParameter("from", from);
                    query2.setParameter("dep", dep);
                    query2.setParameter("com", com);
                    Double ob2 = (Double) query2.uniqueResult();

                    DepreciationData depre = new DepreciationData();
                    if (ob1 == null) {
                    } else {
                        depre.setAmount(ob1);
                    }
                    if (ob2 == null) {
                    } else {
                        depre.setBeforedep(ob2);
                    }
                    depre.setAccount(dep.getDebit());
                    depre.setDepreciation(dep);
                    starr.add(depre);
                }
                return starr;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public void CalculateDepreciation(Date from, Date to, Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction tr = session.beginTransaction();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT d FROM Depreciation d WHERE d.isClosed=:ic AND d.credit.companyId=:com");
                query.setParameter("com", com);
                query.setParameter("ic", false);
                List<Depreciation> pcList = (List<Depreciation>) query.list();

                for (Depreciation dep : pcList) {
                    Query dq = session.createQuery(
                            "DELETE FROM Transaction t WHERE t.credit=:credit AND t.debit=:debit AND t.depreciationId=:did");
                    dq.setParameter("credit", dep.getCredit());
                    dq.setParameter("debit", dep.getDebit());
                    dq.setParameter("did", dep);
                    dq.executeUpdate();
                    session.flush();
                    tr.commit();

                    int terms = dep.getTerm();
                    double amount = dep.getAmount();
                    double rate = dep.getRate();
                    Date ldate = dep.getDate();
                    Date CurrentDate = to;

                    int i = 0;
                    while (ldate.before(CurrentDate)) {
                        i++;
                        Calendar c = Calendar.getInstance();
                        c.setTime(ldate);
                        c.add(Calendar.YEAR, 1);
                        Date dd = c.getTime();
                        long diff = CurrentDate.getTime() - dd.getTime();

                        if (diff > 365) {
                            double depamount = amount * (rate / 100);
                            Transaction trans = new Transaction();
                            trans.setCompanyId(com);
                            trans.setCredit(dep.getCredit());
                            trans.setDebit(dep.getDebit());
                            trans.setDate(dd);
                            trans.setDepreciationId(dep);
                            trans.setDescription("Calculated Depreciation Automatically...");
                            trans.setDiscount(0.0);
                            trans.setPaidAmount(depamount);
                            trans.setPrice(depamount);
                            new SaveDAOImpl().saveObject(trans);
                            amount -= depamount;
                        }
                        long diff2 = CurrentDate.getTime() - ldate.getTime();

                        if (diff < 0 && diff2 > 0) {
                            int diffInDays = getDifferenceDays(ldate, CurrentDate);
                            double depamount = amount * (rate / 100) * (Double.parseDouble(diffInDays + "") / 365);
                            DecimalFormat df = new DecimalFormat("#.##");
                            depamount = Double.valueOf(df.format(depamount));
                            Transaction trans = new Transaction();
                            trans.setCompanyId(com);
                            trans.setCredit(dep.getCredit());
                            trans.setDebit(dep.getDebit());
                            trans.setDate(CurrentDate);
                            trans.setDepreciationId(dep);
                            trans.setDescription("Calculated Depreciation Automatically...");
                            trans.setDiscount(0.0);
                            trans.setPaidAmount(depamount);
                            trans.setPrice(depamount);
                            new SaveDAOImpl().saveObject(trans);
                            amount -= depamount;
                        }
                        if (i == terms) {
                            Query uq = session.createQuery("UPDATE Depreciation d SET d.isClosed=:ic WHERE d=:did");
                            uq.setParameter("did", dep);
                            uq.setParameter("ic", true);
                            uq.executeUpdate();
                            session.flush();
                            tr.commit();
                            break;
                        }
                        ldate = dd;
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
    }

    public int getDifferenceDays(Date d1, Date d2) {
        int daysdiff = 0;
        long diff = d2.getTime() - d1.getTime();
        long diffDays = diff / (24 * 60 * 60 * 1000) + 1;
        daysdiff = (int) diffDays;
        return daysdiff;
    }

    public List<Grn> GetGRNDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT grn FROM Grn grn WHERE grn.productProductId=:pid AND grn.pPrice=:pprice AND grn.sPrice=:sprice");
                query.setParameter("pid", bpm.getProductMasterId().getProductId());
                query.setParameter("pprice", bpm.getProductMasterId().getPurchasedPrice());
                query.setParameter("sprice", bpm.getProductMasterId().getSellingPrice());
                List<Grn> inList = (List<Grn>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<InvoiceItem> GetInvoiceDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT i FROM InvoiceItem i WHERE i.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<InvoiceItem> inList = (List<InvoiceItem>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Gtn> GetGtnDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT g FROM Gtn g WHERE g.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<Gtn> inList = (List<Gtn>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Gin> GetGinDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT g FROM Gin g WHERE g.bpmId=:bpmid");
                query.setParameter("bpmid", bpm);
                List<Gin> inList = (List<Gin>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<ReturnByCustomer> GetCustomerReturnDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT r FROM ReturnByCustomer r WHERE r.invoiceItemId.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<ReturnByCustomer> inList = (List<ReturnByCustomer>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<ReturnToCustomer> GetReturnToCustomerDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT r FROM ReturnToCustomer r WHERE r.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<ReturnToCustomer> inList = (List<ReturnToCustomer>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<DisposeItems> GetDisposeDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT d FROM DisposeItems d WHERE d.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<DisposeItems> inList = (List<DisposeItems>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<StockReturn> GetStockReturnDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT s FROM StockReturn s WHERE s.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<StockReturn> inList = (List<StockReturn>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<ReturnToSupplier> GetReturnToSupplierDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT r FROM ReturnToSupplier r WHERE r.returnStockId.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<ReturnToSupplier> inList = (List<ReturnToSupplier>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<ReturnBySupplier> GetReturnBySupplierDATA(BranchProductmaster bpm) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT r FROM ReturnBySupplier r WHERE r.productMasterId=:pmid");
                query.setParameter("pmid", bpm.getProductMasterId());
                List<ReturnBySupplier> inList = (List<ReturnBySupplier>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Payment> getCreditCardPayment(Date fd, Date td) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT p FROM Payment p WHERE p.paymentTypeId.ptId=:type AND p.paymentDate BETWEEN :fdate AND :tdate");
                //                Query query1 = session.createQuery("SELECT p FROM InvoicePayment p WHERE p.paymentId.paymentId:type AND p.invoiceId.companyId=:");
                query.setParameter("type", 3);
                query.setParameter("fdate", fd);
                query.setParameter("tdate", td);
                List<Payment> inList = (List<Payment>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<BudgetPlan> getBudgetPlan(String acc, String year, Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT b FROM BudgetPlan b WHERE b.accountId.id=:acc AND b.year=:year AND b.companyId=:com");
                query.setParameter("acc", Integer.parseInt(acc));
                query.setParameter("year", year);
                query.setParameter("com", com);
                List<BudgetPlan> inList = (List<BudgetPlan>) query.list();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public double getBudgetOfYear(Company com, int accid, String year) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery(
                        "SELECT SUM(s.value)FROM BudgetPlan s WHERE s.accountId.id=:accId AND s.companyId=:com AND s.year=:year");
                //                Query query = session.createQuery("SELECT SUM(s.value),s.year FROM BudgetPlan s WHERE s.accountId.id=:accId AND s.companyId=:com GROUP BY s.year");
                query.setParameter("com", com);
                query.setParameter("accId", accid);
                query.setParameter("year", year);
                double value = (double) query.uniqueResult();
                System.out.println("/////////" + value);
                return value;

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }

        return 0.0;
    }

    public List<Object[]> getBudgetOfYear1(Company com, int accid) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                //                Query query = session.createQuery("SELECT SUM(s.value)FROM BudgetPlan s WHERE s.accountId.id=:accId AND s.companyId=:com AND s.year=:year");
                Query query = session.createQuery(
                        "SELECT SUM(s.value),s.year FROM BudgetPlan s WHERE s.accountId.id=:accId AND s.companyId=:com GROUP BY s.year");
                query.setParameter("com", com);
                query.setParameter("accId", accid);
                //                query.setParameter("year", year);

                //                double value =(double) query.uniqueResult();
                List<Object[]> list = (List<Object[]>) query.list();

                return list;

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }

        return null;
    }

    public SystemData getCreditCardRate() {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if (session != null) {
            try {
                Query query = session.createQuery("SELECT s FROM SystemData s WHERE s.id =1");
                //                query.setParameter("sid",id);

                SystemData inList = (SystemData) query.uniqueResult();
                return inList;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public String updateBudgetPlan(BudgetPlan bp, Company com) {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        org.hibernate.Transaction transaction = session.beginTransaction();
        if (session != null) {
            try {

                Query query = session.createQuery(
                        "UPDATE BudgetPlan as b set b.value=:val WHERE b.companyId=:com AND b.month=:month AND b.year=:year");

                query.setParameter("com", com);
                query.setParameter("month", bp.getMonth());
                query.setParameter("year", bp.getYear());
                query.setParameter("val", bp.getValue());
                query.executeUpdate();
                transaction.commit();
                return VertecConstants.SUCCESS;

            } catch (Exception e) {
                e.printStackTrace();
                return VertecConstants.ERROR;
            } finally {
                if (session != null && session.isOpen()) {
                    session.close();
                }
            }
        }
        return null;
    }

    public List<Bin> GetBin(BranchProductmaster bpm, Branch branch) {

        List<Grn> grnData = GetGRNDATA(bpm);
        List<InvoiceItem> invoiceData = GetInvoiceDATA(bpm);
        List<Gtn> gtnData = GetGtnDATA(bpm);
        List<Gin> ginData = GetGinDATA(bpm);

        List<ReturnByCustomer> customerReturnData = GetCustomerReturnDATA(bpm);
        List<ReturnToCustomer> ReturntocustomerData = GetReturnToCustomerDATA(bpm);
        List<DisposeItems> DisposeData = GetDisposeDATA(bpm);
        List<StockReturn> StockReturnData = GetStockReturnDATA(bpm);
        List<ReturnToSupplier> ReturnToSupplierData = GetReturnToSupplierDATA(bpm);
        List<ReturnBySupplier> ReturnbySupplierData = GetReturnBySupplierDATA(bpm);

        List<Bin> binList = new ArrayList<>();

        for (ReturnBySupplier c : ReturnbySupplierData) {
            Bin b = new Bin();
            b.setDate(c.getSupplierReturnId().getDate());
            b.setQty(c.getQty());
            b.setDes("Returned By Supplier");
            binList.add(b);

        }
        for (ReturnToSupplier c : ReturnToSupplierData) {
            Bin b = new Bin();
            b.setDate(c.getSupplierReturnId().getDate());
            b.setQty(c.getQty() * (-1));
            b.setDes("Return to supplier");
            binList.add(b);

        }
        for (DisposeItems c : DisposeData) {
            Bin b = new Bin();
            b.setDate(c.getDate());
            b.setQty(c.getQty() * (-1));
            b.setDes("Disposed");
            binList.add(b);

        }
        for (ReturnToCustomer c : ReturntocustomerData) {
            Bin b = new Bin();
            b.setDate(c.getCustomerReturnId().getDate());
            b.setQty(c.getQty() * (-1));
            b.setDes("Returned to customer");
            binList.add(b);

        }
        for (ReturnByCustomer c : customerReturnData) {
            Bin b = new Bin();
            b.setDate(c.getCustomerReturnId().getDate());
            b.setQty(c.getQty());
            b.setDes("Customer Returned");
            binList.add(b);

        }

        for (Gin gin : ginData) {

            Bin b = new Bin();

            b.setDate(gin.getGinInfoId().getDate());
            b.setQty(gin.getQty() * (-1));
            b.setDes("GIN");
            binList.add(b);

        }
        for (Grn grn : grnData) {
            Bin b = new Bin();
            b.setDate(grn.getGrnInfoGrnInfoId().getDate());
            b.setQty(grn.getQty());
            b.setDes("GRN");
            binList.add(b);
        }
        for (InvoiceItem invoice : invoiceData) {
            Bin b = new Bin();
            b.setDate(invoice.getInvoiceId().getInvoicedDate());
            b.setQty(invoice.getQuantity() * (-1));
            b.setDes("Invoice");
            binList.add(b);
        }

        for (Gtn gtn : gtnData) {
            Bin b = new Bin();
            b.setDate(gtn.getGtninfoId().getDate());
            if (gtn.getGtninfoId().getToBranch().equals(branch)) {
                b.setQty(gtn.getQty());
            } else if (gtn.getGtninfoId().getFromBranch().equals(branch)) {
                b.setQty(gtn.getQty() * (-1));
            }
            b.setDes("GTN");
            binList.add(b);
        }
        System.gc();
        //Return data should be added
        List<Bin> binList2 = new ArrayList<>();
        Collections.sort(binList, new CompDate(false));
        System.out.println("BY Date asc");
        for (Bin p : binList) {
            binList2.add(p);
        }
        return binList2;
    }
}

class CompDate implements Comparator<Bin> {

    private int mod = 1;

    public CompDate(boolean desc) {
        if (desc) {
            mod = -1;
        }
    }

    public int compare(Bin arg0, Bin arg1) {
        return mod * arg0.getDate().compareTo(arg1.getDate());
    }

}