using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;


namespace HRM.DA.Fund
{
    #region class MemberBalaneMonthlyDA

    internal class MemberBalaneMonthlyDA
    {
        #region Constructor

        public MemberBalaneMonthlyDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, MemberBalaneMonthly oItem)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO MemberBalaneMonthly(RecordID,MembersID,MonthDate,ProjectID,FundTypeID,EmpCode,MembershipDate,MembershipNo,CMOpeningContributionBalance," +
                " CMClosingContributionBalance,CMActualOpeningInterest,CMActualClosingInterest,CMOpeningProvisionalInterest,CMOpeningActualBalance,CMClosingActualBalance," +
                " CMClosingProvisionalInterest,CMOpeningProvisionalBalance,CMClosingProvisionalBalance,CreatedBy,CreatedDate)" +
                " VALUES(%n,%n,%d,%n,%n,%s,%d,%s,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%D)", oItem.ID, oItem.MembersID,
                oItem.MonthDate, oItem.ID,
                oItem.FundTypeID, oItem.EmpCode, DataReader.GetNullValue(oItem.MembershipDate), oItem.MembershipNo,
                oItem.CMOpeningContributionBalance,
                oItem.CMClosingContributionBalance, oItem.CMActualOpeningInterest, oItem.CMActualClosingInterest,
                oItem.CMOpeningProvisionalInterest, oItem.CMOpeningActualBalance,
                oItem.CMClosingActualBalance, oItem.CMClosingProvisionalInterest, oItem.CMOpeningProvisionalBalance,
                oItem.CMClosingProvisionalBalance, oItem.CreatedBy,
                oItem.CreatedDate);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, MemberBalaneMonthly oItem)
        {
            tc.ExecuteNonQuery(
                "UPDATE MemberBalaneMonthly SET RecordID = %n, MembersID = %n, MonthDate = %d, ProjectID = %n, FundTypeID = %n, EmpCode = %s, MembershipDate =%d, MembershipNo = %s, CMOpeningContributionBalance = %n, CMClosingContributionBalance = %n, CMActualOpeningInterest = %n, CMActualClosingInterest = %n, CMOpeningProvisionalInterest = %n, CMOpeningActualBalance = %n, CMClosingActualBalance = %n, CMClosingProvisionalInterest = %n, CMOpeningProvisionalBalance = %n, CMClosingProvisionalBalance = %n, ModifiedBy = %n, ModifiedDate = %D" +
                " WHERE RecordID=%n", oItem.ID, oItem.MembersID, oItem.MonthDate, oItem.ID, oItem.FundTypeID,
                oItem.EmpCode, DataReader.GetNullValue(oItem.MembershipDate), oItem.MembershipNo,
                oItem.CMOpeningContributionBalance, oItem.CMClosingContributionBalance, oItem.CMActualOpeningInterest,
                oItem.CMActualClosingInterest, oItem.CMOpeningProvisionalInterest, oItem.CMOpeningActualBalance,
                oItem.CMClosingActualBalance, oItem.CMClosingProvisionalInterest, oItem.CMOpeningProvisionalBalance,
                oItem.CMClosingProvisionalBalance, oItem.ModifiedBy, oItem.ModifiedDate, oItem.ID);
        }

        #endregion

        #region int Generation function

        internal static int GetNewID(TransactionContext tc)
        {
            return tc.GenerateID("MemberBalaneMonthly", "RecordID");
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc, int fundtypeid)
        {
            return tc.ExecuteReader("SELECT * FROM MemberBalaneMonthly Where ProjectID=%n", fundtypeid);
        }

        internal static IDataReader Get(TransactionContext tc, DateTime selectedMonth, int fundtypeid)
        {
            return tc.ExecuteReader("SELECT * FROM MemberBalaneMonthly where MonthDate = %d AND ProjectID=%n",
                selectedMonth.Date, fundtypeid);
        }

        internal static IDataReader Get(TransactionContext tc, int RecordID, int fundtypeid)
        {
            return tc.ExecuteReader("SELECT * FROM MemberBalaneMonthly WHERE RecordID=%n AND ProjectID=%n", RecordID,
                fundtypeid);
        }

        internal static IDataReader GetByMemberID(TransactionContext tc, int memberIDinInt, int fundtypeid)
        {
            return tc.ExecuteReader(
                "SELECT * FROM MemberBalaneMonthly WHERE MembersID=%n AND MonthDate = (SELECT Max(MonthDate) FROM MemberBalaneMonthly WHERE MembersID=%n AND ProjectID=%n)",
                memberIDinInt, memberIDinInt, fundtypeid);
        }

        internal static double GetOpeningBalance(TransactionContext tc, DateTime prevMonth, int memberID, int fundID)
        {
            string sql = string.Empty;
            sql = SQLParser.MakeSQL(@"SELECT CMClosingContributionBalance FROM MemberBalaneMonthly 
                WHERE MembersID = %n AND FundTypeID = %n AND MonthDate = (Select Max(MonthDate) From 
                MemberBalaneMonthly WHERE MembersID = %n AND FundTypeID = %n)", memberID, fundID, memberID, fundID);
            object obj = tc.ExecuteScalar(sql);
            if (obj != DBNull.Value)
            {
                return Convert.ToDouble(obj);
            }
            else
                return 0;
        }

        internal static DataTable GetLeftMemberTable(TransactionContext tc, DateTime fromDate, DateTime toDate,
            int fundtypeid)
        {
            string sql = string.Empty;
            // Rashid 
            //sql = SQLParser.MakeSQL("SELECT tblOpening.EmpCode AS EmpIDNo,tblOpening.MembersID, md.Name,md.DOL, md.DOJ,md.MembershipDate, md.Status,"
            //       + "tblOpening.ownOpeningBalance AS OwnOpeningBalance, tblOpening.companyOpeningBalance AS CompanyOpeningBalance, tblOpening.ownopeningInterest AS OwnOpeningInterest,"
            //       + "tblOpening.companyopeningInterest AS CompanyOpeningInterest,(tblClosing.ownClosingBalance - tblOpening.ownOpeningBalance) AS  OwnContribution,"
            //       + "(tblClosing.companyClosingBalance - tblOpening.companyOpeningBalance) AS CompanyContribution, (tblClosing.ownclosingInterest - tblOpening.ownopeningInterest ) AS OwnContributionInterest,"
            //       + "(tblClosing.companyclosingInterest - tblOpening.companyopeningInterest) AS  CompanyContributionInterest FROM MemberDetail AS md "
            //       + "INNER JOIN (SELECT tbl1.MembersID, tbl1.MonthDate AS MonthDate,tbl1.ID,tbl1.EmpCode,tbl1.CMOpeningContributionBalance AS ownOpeningBalance,"
            //       + " tbl2.CMOpeningContributionBalance AS companyOpeningBalance, tbl1.CMActualOpeningInterest AS ownopeningInterest, tbl2.CMActualOpeningInterest AS companyopeningInterest "
            //       + "FROM (SELECT * FROM MemberBalaneMonthly WHERE FundTypeID=1 AND MonthDate = %d) tbl1 INNER JOIN (SELECT * FROM MemberBalaneMonthly WHERE FundTypeID=2 AND MonthDate = %d) tbl2 "
            //       + "ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblOpening ON tblOpening.MembersID = md.MemberID Left outer JOIN (SELECT tbl1.MembersID,	tbl1.MonthDate AS MonthDate,tbl1.CMClosingContributionBalance as ownClosingBalance, "
            //       + "tbl2.CMClosingContributionBalance AS companyClosingBalance, tbl1.CMActualClosingInterest AS ownclosingInterest, tbl2.CMActualClosingInterest AS companyclosingInterest FROM (SELECT * FROM MemberBalaneMonthly t WHERE t.FundTypeID=1 AND t.MonthDate = "
            //       + "(SELECT Max(mbm.MonthDate) FROM MemberBalaneMonthly mbm WHERE mbm.FundTypeID=1 and mbm.MonthDate >= %d AND mbm.MonthDate <=%d AND mbm.MembersID = t.MembersID) ) tbl1 "
            //       + "INNER JOIN (SELECT * FROM MemberBalaneMonthly t WHERE t.FundTypeID=2 AND t.MonthDate = (SELECT Max(mbm.MonthDate) FROM MemberBalaneMonthly mbm WHERE mbm.FundTypeID=2 and mbm.MonthDate >= %d "
            //       + "AND mbm.MonthDate <=%d AND mbm.MembersID = t.MembersID)) tbl2 ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblClosing ON  tblOpening.MembersID = tblClosing.MembersID WHERE md.Status = 2 ", fromDate, fromDate, fromDate, toDate, fromDate, toDate);

            sql = SQLParser.MakeSQL(
                "SELECT tblOpening.EmpCode AS EmpIDNo,tblOpening.MembersID, md.Name,md.DOL, md.DOJ,md.MembershipDate, md.Status,"
                + "tblOpening.ownOpeningBalance AS OwnOpeningBalance, tblOpening.companyOpeningBalance AS CompanyOpeningBalance, tblOpening.ownopeningInterest AS OwnOpeningInterest,"
                + "tblOpening.companyopeningInterest AS CompanyOpeningInterest,(tblClosing.ownClosingBalance - tblOpening.ownOpeningBalance) AS  OwnContribution,"
                + "(tblClosing.companyClosingBalance - tblOpening.companyOpeningBalance) AS CompanyContribution, (tblClosing.ownclosingInterest - tblOpening.ownopeningInterest ) AS OwnContributionInterest,"
                + "(tblClosing.companyclosingInterest - tblOpening.companyopeningInterest) AS  CompanyContributionInterest FROM MemberDetail AS md "
                + "INNER JOIN (SELECT tbl1.MembersID, tbl1.MonthDate AS MonthDate,tbl1.ID,tbl1.EmpCode,tbl1.CMOpeningContributionBalance AS ownOpeningBalance,"
                + " tbl2.CMOpeningContributionBalance AS companyOpeningBalance, tbl1.CMActualOpeningInterest AS ownopeningInterest, tbl2.CMActualOpeningInterest AS companyopeningInterest "
                + "FROM (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 0) WHERE FundTypeID=1 ) tbl1 INNER JOIN (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 0) WHERE FundTypeID=2 ) tbl2 "
                + "ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblOpening ON tblOpening.MembersID = md.MemberID Left outer JOIN (SELECT tbl1.MembersID,	tbl1.MonthDate AS MonthDate,tbl1.CMClosingContributionBalance as ownClosingBalance, "
                + "tbl2.CMClosingContributionBalance AS companyClosingBalance, tbl1.CMActualClosingInterest AS ownclosingInterest, tbl2.CMActualClosingInterest AS companyclosingInterest FROM (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 1) t WHERE t.FundTypeID=1 ) tbl1 "
                + "INNER JOIN (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 1) t WHERE t.FundTypeID=2 ) tbl2 ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblClosing ON  tblOpening.MembersID = tblClosing.MembersID WHERE md.Status = 2 AND md.ID=%n AND FinalSettlementDate   between  %d AND %d order by FinalSettlementDate asc",
                fromDate, toDate, fromDate, toDate, fromDate, toDate, fromDate, toDate, fundtypeid,
                PayrollGlobalFunctions.PayrollFirstDateOfMonth(fromDate), toDate);
            DataSet ds = tc.ExecuteDataSet(sql);
            return ds.Tables[0];
        }

        internal static DataTable GetTable(TransactionContext tc, DateTime fromDate, DateTime toDate, int fundtypeid)
        {
            string sql = string.Empty;

            //Rashid
            //sql = SQLParser.MakeSQL("SELECT tblOpening.EmpCode AS EmpIDNo,tblOpening.MembersID, md.Name,md.DOL, md.DOJ,md.MembershipDate, md.Status,"
            //       + "tblOpening.ownOpeningBalance AS OwnOpeningBalance, tblOpening.companyOpeningBalance AS CompanyOpeningBalance, tblOpening.ownopeningInterest AS OwnOpeningInterest,"
            //       + "tblOpening.companyopeningInterest AS CompanyOpeningInterest,(tblClosing.ownClosingBalance - tblOpening.ownOpeningBalance) AS  OwnContribution,"
            //       + "(tblClosing.companyClosingBalance - tblOpening.companyOpeningBalance) AS CompanyContribution, (tblClosing.ownclosingInterest - tblOpening.ownopeningInterest ) AS OwnContributionInterest,"
            //       + "(tblClosing.companyclosingInterest - tblOpening.companyopeningInterest) AS  CompanyContributionInterest FROM MemberDetail AS md "
            //       + "INNER JOIN (SELECT tbl1.MembersID, tbl1.MonthDate AS MonthDate,tbl1.ID,tbl1.EmpCode,tbl1.CMOpeningContributionBalance AS ownOpeningBalance,"
            //       + " tbl2.CMOpeningContributionBalance AS companyOpeningBalance, tbl1.CMActualOpeningInterest AS ownopeningInterest, tbl2.CMActualOpeningInterest AS companyopeningInterest "
            //       + "FROM (SELECT * FROM MemberBalaneMonthly WHERE FundTypeID=1 AND MonthDate = %d) tbl1 INNER JOIN (SELECT * FROM MemberBalaneMonthly WHERE FundTypeID=2 AND MonthDate = %d) tbl2 "
            //       + "ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblOpening ON tblOpening.MembersID = md.MemberID Left outer JOIN (SELECT tbl1.MembersID,	tbl1.MonthDate AS MonthDate,tbl1.CMClosingContributionBalance as ownClosingBalance, "
            //       + "tbl2.CMClosingContributionBalance AS companyClosingBalance, tbl1.CMActualClosingInterest AS ownclosingInterest, tbl2.CMActualClosingInterest AS companyclosingInterest FROM (SELECT * FROM MemberBalaneMonthly t WHERE t.FundTypeID=1 AND t.MonthDate = "
            //       + "(SELECT Max(mbm.MonthDate) FROM MemberBalaneMonthly mbm WHERE mbm.FundTypeID=1 and mbm.MonthDate >= %d AND mbm.MonthDate <=%d AND mbm.MembersID = t.MembersID) ) tbl1 "
            //       + "INNER JOIN (SELECT * FROM MemberBalaneMonthly t WHERE t.FundTypeID=2 AND t.MonthDate = (SELECT Max(mbm.MonthDate) FROM MemberBalaneMonthly mbm WHERE mbm.FundTypeID=2 and mbm.MonthDate >= %d "
            //       + "AND mbm.MonthDate <=%d AND mbm.MembersID = t.MembersID)) tbl2 ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblClosing ON  tblOpening.MembersID = tblClosing.MembersID WHERE md.Status = 1 OR md.Status = 2 ORDER BY md.Status", fromDate, fromDate, fromDate, toDate, fromDate, toDate);

            sql = SQLParser.MakeSQL(
                "SELECT tblOpening.EmpCode AS EmpIDNo,tblOpening.MembersID, md.Name,md.DOL, md.DOJ,md.MembershipDate, md.Status,"
                + "tblOpening.ownOpeningBalance AS OwnOpeningBalance, tblOpening.companyOpeningBalance AS CompanyOpeningBalance, tblOpening.ownopeningInterest AS OwnOpeningInterest,"
                + "tblOpening.companyopeningInterest AS CompanyOpeningInterest,(tblClosing.ownClosingBalance - tblOpening.ownOpeningBalance) AS  OwnContribution,"
                + "(tblClosing.companyClosingBalance - tblOpening.companyOpeningBalance) AS CompanyContribution, (tblClosing.ownclosingInterest - tblOpening.ownopeningInterest ) AS OwnContributionInterest,"
                + "(tblClosing.companyclosingInterest - tblOpening.companyopeningInterest) AS  CompanyContributionInterest FROM MemberDetail AS md "
                + "INNER JOIN (SELECT tbl1.MembersID, tbl1.MonthDate AS MonthDate,tbl1.ID,tbl1.EmpCode,tbl1.CMOpeningContributionBalance AS ownOpeningBalance,"
                + " tbl2.CMOpeningContributionBalance AS companyOpeningBalance, tbl1.CMActualOpeningInterest AS ownopeningInterest, tbl2.CMActualOpeningInterest AS companyopeningInterest "
                + "FROM (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 0) WHERE FundTypeID=1 ) tbl1 INNER JOIN (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 0) WHERE FundTypeID=2 ) tbl2 "
                + "ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblOpening ON tblOpening.MembersID = md.MemberID Left outer JOIN (SELECT tbl1.MembersID,	tbl1.MonthDate AS MonthDate,tbl1.CMClosingContributionBalance as ownClosingBalance, "
                + "tbl2.CMClosingContributionBalance AS companyClosingBalance, tbl1.CMActualClosingInterest AS ownclosingInterest, tbl2.CMActualClosingInterest AS companyclosingInterest FROM (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 1) t WHERE t.FundTypeID=1 ) tbl1 "
                + "INNER JOIN (SELECT * FROM fn_MemberBalanceMonthly( %d, %d, 1) t WHERE t.FundTypeID=2 ) tbl2 ON tbl1.MembersID=tbl2.MembersID AND tbl1.MonthDate=tbl2.MonthDate) AS tblClosing ON  tblOpening.MembersID = tblClosing.MembersID WHERE md.ID=%n AND ( md.Status = 1 OR md.Status = 2) ORDER BY md.Status",
                fromDate, toDate, fromDate, toDate, fromDate, toDate, fromDate, toDate, fundtypeid);
            DataSet ds = tc.ExecuteDataSet(sql);
            return ds.Tables[0];
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int RecordID)
        {
            tc.ExecuteNonQuery("DELETE FROM [MemberBalaneMonthly] WHERE RecordID=%n", RecordID);
        }

        internal static void DeleteGFSettlementData(TransactionContext tc, int memberID, DateTime monthDate,
            int projectID, decimal closingBalance)
        {
            tc.ExecuteNonQuery(
                "DELETE FROM MemberBalaneMonthly WHERE MembersID=%n and MonthDate=%d and ProjectID=%n and CMClosingActualBalance=%n",
                memberID, monthDate, projectID, closingBalance);
        }

        #endregion
    }

    #endregion
}