using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.CoreV35.DataAccess.SQL;

namespace Payroll.Service
{
    class PremiumProcessDA
    {
        internal static IDataReader Get(TransactionContext tc, ID id)
        {
            return tc.ExecuteReader("SELECT * FROM PremiumProcess WHERE PremiumProcessID=%n", id.Integer);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM PremiumProcess");
        }

        internal static void Insert(TransactionContext tc, PremiumProcess oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO PremiumProcess (PremiumProcessID, Month, Factor, PremierRate)" +
            " VALUES(%n, %d, %n, %n)", oItem.ID.Integer, oItem.Month, oItem.Factor, oItem.PremierRate);
        }

        internal static void Update(TransactionContext tc, PremiumProcess oItem)
        {
            tc.ExecuteNonQuery("UPDATE PremiumProcess SET Month=%d, Factor=%n, PremierRate=%n" +
            " WHERE PremiumProcessID=%n", oItem.Month, oItem.Factor, oItem.PremierRate, oItem.ID.Integer);
        }

        internal static void Delete(TransactionContext tc, ID id)
        {
            tc.ExecuteNonQuery("DELETE FROM PremiumProcessDetail WHERE PremiumProcessID=%n", id.Integer);
            tc.ExecuteNonQuery("DELETE FROM PremiumProcess WHERE PremiumProcessID=%n", id.Integer);
        }

        internal static DataSet GetGroupInsurance(TransactionContext tc, DateTime dtCur)
        {
            DateTime dtCurFrom = GlobalFunctions.FirstDateOfMonth(dtCur);
            DateTime dtCurTo = GlobalFunctions.LastDateOfMonth(dtCur);
            DateTime dtPrevFrom = GlobalFunctions.FirstDateOfYear(dtCur);
            DateTime dtPrevTo = GlobalFunctions.LastDateOfMonth(dtCur.AddMonths(-1));
            string strSQLQuery = SQLParser.MakeSQL(@"SELECT emp.EMPLOYEENO EmployeeID,emp.NAME Name,grd.DESCRIPTION Grade,
                                                        pos.Level1 Division,pos.Level2 Department,pos.RC, 
                                                        Cast(CONVERT(DECIMAL(36,0),curtab.Basic) as nvarchar) 'Actual Basic',
                                                        Cast(CONVERT(DECIMAL(36,0),curtab.Gross) as nvarchar) Gross,
                                                        Cast(CONVERT(DECIMAL(36,0),curtab.AssuredAmount) as nvarchar) 'Sum Assured Amount',
                                                        Cast(CONVERT(DECIMAL(36,0),curtab.AssuredAmount/1000) as nvarchar) 'Per 1000',
                                                        Cast(CONVERT(DECIMAL(36,0),curtab.PremiumAmount) as nvarchar) 'Premium Amount',
                                                        Cast(CONVERT(DECIMAL(36,0),curtab.PremiumAmount-prevtab.PremiumAmount) as nvarchar) 'Adjusted Premium Amount',
                                                        Cast(CONVERT(DECIMAL(36,2),curtab.PremierRate) as nvarchar) 'Rate BDT.' FROM
                                                        (SELECT ppd.EmployeeID, ppd.Basic,ppd.Gross,ppd.AssuredAmount,ppd.PremiumAmount,pp.PremierRate
                                                        FROM PremiumProcess pp,PremiumProcessDetail ppd
                                                        WHERE pp.PremiumProcessID=ppd.PremiumProcessID
                                                        AND pp.Month BETWEEN %d AND %d)curtab
                                                        LEFT OUTER JOIN Employee emp ON emp.EMPLOYEEID=curtab.EmployeeID
                                                        full OUTER join 
                                                        (SELECT ppd.EmployeeID, sum(ppd.PremiumAmount)PremiumAmount
                                                        FROM PremiumProcess pp,PremiumProcessDetail ppd
                                                        WHERE pp.PremiumProcessID=ppd.PremiumProcessID
                                                        AND pp.Month BETWEEN %d AND %d
                                                        GROUP BY ppd.EmployeeID)prevtab ON prevtab.employeeid=curtab.employeeid
 
                                                        LEFT OUTER join  Grades grd ON grd.GRADEID=emp.GRADEID
                                                        LEFT OUTER join  EmpCurrentPosition pos ON pos.employeeid=emp.EMPLOYEEID

                                                    ", dtCurFrom, dtCurTo, dtPrevFrom, dtPrevTo);
            return tc.ExecuteDataSet(strSQLQuery);
        }
        internal static IDataReader GetByMonth(TransactionContext tc, DateTime month)
        {
            return tc.ExecuteReader("SELECT * FROM PremiumProcess where Month=%d",month);
        }
    }
}