ClsAccountPaymentReport

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SNSCT
{
 
    class ClsAccountPaymentReport
    {
        SqlDb ObjSql = new SqlDb();
        StringBuilder Str = new StringBuilder();
        DataTable Dt = new DataTable();

        public DataTable Dt_Head()
        {
            Str.Append("Select AccountHead from Account_Head where Type=0");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

        public DataTable Payment_Details(int Id)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY P.MemberID) AS [SL No] ,R.RegNo,P.MemberName AS Name,P.VoucherNo,A.AccountHead,convert(varchar,P.PaymentDate,103) AS PaymentDate,P.Narration,P.Amount ");
            Str.Append(" from Payment P  inner join Registration R on P.AccountHeadID='" + Id + "' and R.MemberID=P.MemberID  and P.Status = 0 ");
            Str.Append(" inner join Account_Head A   on A.AccountHeadID=P.AccountHeadID ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
        public DataTable MemberName()
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable dt = new DataTable();

            Str.Append("select Name from Registration");
            dt = ObjSql.getTable_(Str.ToString());
            return dt;
        }

        public DataTable Payment_dateDetails(int Id, DateTime frm, DateTime To)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            string dt_date = ObjSql.getBetweenString(frm, To);

            Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY P.MemberID) AS [SL No] ,R.RegNo,P.MemberName AS Name, P.VoucherNo,A.AccountHead,convert(varchar,P.PaymentDate,103) AS PaymentDate,P.Narration,P.Amount ");
            Str.Append(" from Payment P inner join Registration R on  P.AccountHeadID='" + Id + "' and R.MemberID=P.MemberID  and P.Status = 0 ");
            Str.Append(" inner join Account_Head A   on A.AccountHeadID=P.AccountHeadID ");
             Str.Append(" where P.PaymentDate BETWEEN " + dt_date);
         
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
        public DataTable Payment_MemberDateDetails(string Name, int Id, DateTime frm, DateTime To)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            string dt_date = ObjSql.getBetweenString(frm, To);
            Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY P.MemberID) AS [SL No] ,R.RegNo,P.MemberName AS Name, P.VoucherNo,A.AccountHead,convert(varchar,P.PaymentDate,103) AS PaymentDate,P.Narration,P.Amount ");
            Str.Append(" from Payment P inner join Registration R on  R.MemberID=P.MemberID   ");
            Str.Append(" inner join Account_Head A   on A.AccountHeadID=P.AccountHeadID ");
            Str.Append(" where P.AccountHeadID='" + Id + "' and  P.Status = 0 and P.MemberName='"+Name+"' and P.PaymentDate BETWEEN " + dt_date);

            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;

        }


        public DataTable Payment_MemberDetails(string Name,int Id)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY P.MemberID) AS [SL No] ,R.RegNo,P.MemberName AS Name, P.VoucherNo,A.AccountHead,convert(varchar,P.PaymentDate,103) AS PaymentDate,P.Narration,P.Amount ");
            Str.Append(" from Payment P inner join Registration R on  R.MemberID=P.MemberID   ");
            Str.Append(" inner join Account_Head A   on A.AccountHeadID=P.AccountHeadID ");
            Str.Append(" where  P.AccountHeadID='" + Id + "' and R.Name='"+Name+"' and P.Status = 0");

            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
           
        }
        public DataTable Payment_MemberOnly(string Name)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY P.MemberID) AS [SL No] ,R.RegNo,P.MemberName AS Name, P.VoucherNo,A.AccountHead,convert(varchar,P.PaymentDate,103) AS PaymentDate,P.Narration,P.Amount ");
            Str.Append(" from Payment P inner join Registration R on  R.MemberID=P.MemberID   ");
            Str.Append(" inner join Account_Head A   on A.AccountHeadID=P.AccountHeadID ");
            Str.Append(" where   R.Name='" + Name + "' and P.Status = 0");

            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;

        }


        public DataTable Payment_FullDetails(DateTime frm, DateTime To)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();


            string dt_date = ObjSql.getBetweenString(frm, To);
            Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY P.MemberID) AS [SL No] ,R.RegNo,P.MemberName AS Name,P.VoucherNo,A.AccountHead,convert(varchar,P.PaymentDate,103) AS PaymentDate,P.Narration,P.Amount ");
            Str.Append(" from Payment P inner join Registration R on  P.Status = 0 and R.MemberID=P.MemberID  ");
            Str.Append(" inner join Account_Head A   on A.AccountHeadID=P.AccountHeadID ");
            Str.Append(" where  P.PaymentDate BETWEEN " + dt_date);

            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

        public int Dt_HeadName(string HeadName)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append("Select AccountHeadID from Account_Head where AccountHead='" + HeadName + "' ");
            Dt = ObjSql.getTable_(Str.ToString());
            if (Dt.Rows.Count > 0)
            {
                return Dt.Rows[0][0].ToInt();
            }
            else
            {
                return 0;
            }
        }

    }
}