ClsSubscriptionReport

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

namespace SNSCT
{
    class ClsSubscriptionReport
    {
        SqlDb Db = new SqlDb();
        StringBuilder Str = new StringBuilder();
        DataTable Dt = new DataTable();


        public DataTable Get_Members()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MemberName from MonthlyPayment ");
            return Db.getTable_(Str.ToString());
        }
        public DataTable Get_Year()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append("Select Year from MonthlyPayment GROUP BY year HAVING Count(*) >= 1 ");
            Dt = Db.getTable(Str.ToString());
            return Dt;
        }


        public DataTable Fill_MonthDetails(int month,int Year)
            {
                SqlDb Db = new SqlDb();
                StringBuilder Str = new StringBuilder();
                DataTable Dt = new DataTable();

                Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY M.MemberName) AS [SL No] ,Reg.RegNo,M.MemberName As Name, DateName( month , DateAdd( month , M.Month , -1 ) ) AS Month,M.Year,M.Amount  ");
                Str.Append(" from MonthlyPayment M inner join Registration Reg on M.MemberID=Reg.MemberID  ");
                Str.Append("where M.Month='" + month + "' and M.Year='"+Year+"'");
       
            Dt = Db.getTable(Str.ToString());
            return Dt;


        }
        public DataTable Fill_MemberDetails(string Name)
            {
                SqlDb Db = new SqlDb();
                StringBuilder Str = new StringBuilder();
                DataTable Dt = new DataTable();

                Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY M.MemberName) AS [SL No] ,Reg.RegNo,M.MemberName As Name, DateName( month , DateAdd( month , M.Month , -1 ) ) AS Month,M.Year,M.Amount  ");
                Str.Append(" from MonthlyPayment M inner join Registration Reg on M.MemberID=Reg.MemberID  ");
                Str.Append("where  M.MemberName='" + Name + "'");

                Dt = Db.getTable(Str.ToString());
                return Dt;

            }

        public DataTable Fill_Details(int month,int Year,string Name)
            {
                SqlDb Db = new SqlDb();
                StringBuilder Str = new StringBuilder();
                DataTable Dt = new DataTable();

                Str.Append("select  0 + ROW_NUMBER() OVER(ORDER BY M.MemberName) AS [SL No] ,Reg.RegNo,M.MemberName As Name, DateName( month , DateAdd( month , M.Month , -1 ) ) AS Month,M.Year,M.Amount  ");
                Str.Append(" from MonthlyPayment M inner join Registration Reg on M.MemberID=Reg.MemberID  ");
                Str.Append("where M.Month='" + month + "' and M.MemberName='" + Name + "' and M.Year='"+Year+"'");

                Dt = Db.getTable(Str.ToString());
                return Dt;

            }

    }
}