ClsAccountReceiptReport

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

namespace SNSCT
{
    class ClsAccountReceiptReport
    {
         public DataTable Dt_Head()
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append("Select AccountHead from Account_Head where Type=1");
            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  Dt_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 Reg.Name) AS [SL No] ,Reg.RegNo ,Reg.Name AS Name,R.ReceiptNo,AH.AccountHead,convert(varchar,R.ReceiptDate,103) AS ReceiptDate,R.Narration,R.Amount ");
            Str.Append(" from Receipt R inner join Registration Reg on R.MemberID=Reg.MemberID ");
            Str.Append(" inner join  Account_Head AH on AH.AccountHeadID=R.AccountHeadID ");
            Str.Append(" where R.AccountHeadID=" + ID + " and R.Status = 0 and R.ReceiptDate BETWEEN " + dt_date  );
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
        public DataTable Dt_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 Reg.Name) AS [SL No] ,Reg.RegNo ,Reg.Name AS Name,R.ReceiptNo,AH.AccountHead,convert(varchar,R.ReceiptDate,103) AS ReceiptDate,R.Narration,R.Amount ");
            Str.Append(" from Receipt R inner join Registration Reg on R.MemberID=Reg.MemberID ");
            Str.Append(" inner join  Account_Head AH on AH.AccountHeadID=R.AccountHeadID ");
            Str.Append(" where R.AccountHeadID=" + ID + " and Reg.Name='"+Name+"' and R.Status = 0 and R.ReceiptDate BETWEEN " + dt_date);
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;

        }
        public DataTable Receipt_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 Reg.Name) AS [SL No] ,Reg.RegNo ,Reg.Name AS Name,R.ReceiptNo,AH.AccountHead,convert(varchar,R.ReceiptDate,103) AS ReceiptDate,R.Narration,R.Amount ");
            Str.Append(" from Receipt R inner join Registration Reg on R.MemberID=Reg.MemberID ");
            Str.Append(" inner join  Account_Head AH on AH.AccountHeadID=R.AccountHeadID ");
            Str.Append(" where R.AccountHeadID=" + ID + " and Reg.Name='" + Name + "' and R.Status = 0 ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

        public DataTable Receipt_MemberOnly(string Name)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append(" select  0 + ROW_NUMBER() OVER(ORDER BY Reg.Name) AS [SL No] ,Reg.RegNo ,Reg.Name AS Name,R.ReceiptNo,AH.AccountHead,convert(varchar,R.ReceiptDate,103) AS ReceiptDate,R.Narration,R.Amount ");
            Str.Append(" from Receipt R inner join Registration Reg on R.MemberID=Reg.MemberID ");
            Str.Append(" inner join  Account_Head AH on AH.AccountHeadID=R.AccountHeadID ");
            Str.Append(" where Reg.Name='" + Name + "' and R.Status = 0 ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

        public DataTable Dt_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 Reg.Name) AS [SL No] ,Reg.RegNo,Reg.Name AS Name ,R.ReceiptNo,AH.AccountHead,convert(varchar,R.ReceiptDate,103) AS ReceiptDate,R.Narration,R.Amount ");
            Str.Append(" from Receipt R inner join Registration Reg on R.MemberID=Reg.MemberID ");
            Str.Append(" inner join  Account_Head AH on AH.AccountHeadID=R.AccountHeadID ");
            Str.Append(" where  R.Status = 0 and R.ReceiptDate BETWEEN " + dt_date);
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

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

            Str.Append(" select 0 + ROW_NUMBER() OVER(ORDER BY Reg.Name) AS [SL No] , Reg.RegNo,Reg.Name as Name,R.ReceiptNo,AH.AccountHead,convert(varchar,R.ReceiptDate,103) AS ReceiptDate,R.Narration,R.Amount ");
            Str.Append(" from Receipt R inner join Registration Reg on R.MemberID=Reg.MemberID ");
            Str.Append(" inner join  Account_Head AH on AH.AccountHeadID=R.AccountHeadID ");
            Str.Append(" where R.AccountHeadID='" + Id + "' and R.Status = 0");
            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;
            }
        }


     
    }
}