ClsMBFReport

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SNSCT
{
    class ClsMBFReport
    {

        DataTable Dt = new DataTable();
        SqlDb ObjSql = new SqlDb();

        public DataTable Get_Members()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBFID ,MBFName from MBF_Master ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
        public DataTable Dt_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 Get_Rows_Paid(string MBF_name)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select  0 + ROW_NUMBER() OVER(ORDER BY M.MBFName) AS [SL No] ,M.MBFMemberRegNo,M.MBFName,R.Name ");
            Str.Append(" from MBF_Members M inner join Registration R on  R.MemberID=M.MemberID ");
            Str.Append(" where M.MBFName='"+ MBF_name +"' ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

        public DataTable Get_MBFMembers()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select R.Name from Registration R inner join MBF_Members M on  R.MemberID=M.MemberID");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

        public DataTable Installmnt_FullDetails(DateTime frm, DateTime To)
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            string dt_date = ObjSql.getBetweenString(frm, To);
            Str.Append("Select  0 + ROW_NUMBER() OVER(ORDER BY Reg.RegNo) AS [SL No],Reg.RegNo,Reg.Name,M.MBFName,R.ReceiptNo,convert(varchar,R.Date,103) AS Date,R.Narration,R.Amount ");
            Str.Append(" from MBF_Receipt R inner join MBF_Master M on  M.MBFID=R.MbfID  ");
            Str.Append("  inner join Registration Reg on  R.MemberID=Reg.MemberID");
            Str.Append(" where  R.Date BETWEEN " + dt_date);

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

        public DataTable Installmnt_Members()
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBFID ,MBFName from MBF_Master ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
        public DataTable Installmnt_MemberDetails(string Head, 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.RegNo) AS [SL No],Reg.RegNo,Reg.Name,M.MBFName,R.ReceiptNo,convert(varchar,R.Date,103) AS Date,R.Narration,R.Amount");
            Str.Append(" from MBF_Receipt R inner join MBF_Master M on  M.MBFID=R.MbfID  ");
            Str.Append("  inner join Registration Reg on  R.MemberID=Reg.MemberID");
            Str.Append(" where M.MBFName='" + Head + "' and R.Date BETWEEN " + dt_date);
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

       public DataTable  Installmnt_dateDetails(string Name,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.RegNo) AS [SL No],Reg.RegNo,Reg.Name,M.MBFName,R.ReceiptNo,convert(varchar,R.Date,103) AS Date,R.Narration,R.Amount");
            Str.Append(" from MBF_Receipt R inner join MBF_Master M on  M.MBFID=R.MbfID  ");
            Str.Append("  inner join Registration Reg on  R.MemberID=Reg.MemberID");
            Str.Append(" where M.MBFName='" + Name + "' and R.Date BETWEEN " + dt_date);
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
       public DataTable Installmnt_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.RegNo) AS [SL No],Reg.RegNo,Reg.Name,M.MBFName,R.ReceiptNo,convert(varchar,R.Date,103) AS Date,R.Narration,R.Amount");
           Str.Append(" from MBF_Receipt R inner join MBF_Master M on  M.MBFID=R.MbfID  ");
           Str.Append("  inner join Registration Reg on  R.MemberID=Reg.MemberID");
           Str.Append(" where Reg.Name='" + Name + "'");
           Dt = ObjSql.getTable_(Str.ToString());
           return Dt;
       }

       public DataTable Installmnt_MemberDateDetails(string Head,string Name,  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.RegNo) AS [SL No],Reg.RegNo,Reg.Name,M.MBFName,R.ReceiptNo,convert(varchar,R.Date,103) AS Date,R.Narration,R.Amount");
           Str.Append(" from MBF_Receipt R inner join MBF_Master M on  M.MBFID=R.MbfID  ");
           Str.Append("  inner join Registration Reg on  R.MemberID=Reg.MemberID");
           Str.Append(" where M.MBFName='" + Head + "' and Reg.Name='" + Name + "' and R.Date BETWEEN " + dt_date);
           Dt = ObjSql.getTable_(Str.ToString());
           return Dt;
       }
       public DataTable Installmnt_HeadMember(string Head, string Name)
       {
           SqlDb ObjSql = new SqlDb();
           StringBuilder Str = new StringBuilder();
           DataTable Dt = new DataTable();

         
           Str.Append(" select  0 + ROW_NUMBER() OVER(ORDER BY Reg.RegNo) AS [SL No],Reg.RegNo,Reg.Name,M.MBFName,R.ReceiptNo,convert(varchar,R.Date,103) AS Date,R.Narration,R.Amount");
           Str.Append(" from MBF_Receipt R inner join MBF_Master M on  M.MBFID=R.MbfID  ");
           Str.Append("  inner join Registration Reg on  R.MemberID=Reg.MemberID");
           Str.Append(" where M.MBFName='" + Head + "' and Reg.Name='" + Name+ "'");
           Dt = ObjSql.getTable_(Str.ToString());
           return Dt;
       }


        public DataTable Installmnt_Details(string MBF_name)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append("  select  0 + ROW_NUMBER() OVER(ORDER BY Reg.RegNo) AS [SL No],Reg.RegNo,Reg.Name,M.MBFName,R.ReceiptNo,convert(varchar,R.Date,103) AS Date,R.Narration,R.Amount ");
            Str.Append(" from MBF_Receipt R inner join MBF_Master M on  M.MBFID=R.MbfID  ");
            Str.Append("  inner join Registration Reg on  R.MemberID=Reg.MemberID");
            Str.Append(" where M.MBFName='" + MBF_name + "' ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
        public DataTable Payment_Details(string MBFName)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append(" select 0 + ROW_NUMBER() OVER(ORDER BY M.MBFName) AS [SL No],R.RegNo,R.Name,M.MBFName,P.VoucherNo,convert(varchar,P.Date,103) AS  PaymentDate,P.Narration,P.Amount");
            Str.Append(" from MBF_Payment P  inner join MBF_Master M on  M.MBFID=P.MBFID ");
            Str.Append(" inner join Registration R on  R.MemberID=P.MemberID");
            Str.Append(" where M.MBFName='" + MBFName + "' ");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
           
        }
        public DataTable Payment_dateDetails(string Name, 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.MbfID) AS [SL No],R.RegNo,R.Name,M.MBFName,P.VoucherNo,convert(varchar,P.Date,103) AS  PaymentDate,P.Narration,P.Amount");
            Str.Append(" from MBF_Payment P  inner join MBF_Master M on  M.MBFID=P.MBFID ");
            Str.Append(" inner join Registration R on  R.MemberID=P.MemberID");
            Str.Append(" where M.MBFName='" + Name + "' and P.Date BETWEEN " + dt_date);
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }

        public DataTable Payment_HeadMember(string Name,string MemberName)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

             Str.Append(" select  0 + ROW_NUMBER() OVER(ORDER BY P.MbfID) AS [SL No],R.RegNo,R.Name,M.MBFName,P.VoucherNo,convert(varchar,P.Date,103) AS  PaymentDate,P.Narration,P.Amount");
            Str.Append(" from MBF_Payment P  inner join MBF_Master M on  M.MBFID=P.MBFID ");
            Str.Append(" inner join Registration R on  R.MemberID=P.MemberID");
            Str.Append(" where M.MBFName='" + Name + "' and R.Name='" + MemberName + "'");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
          public DataTable Payment_Member(string MemberName)
        {
            SqlDb ObjSql = new SqlDb();
            StringBuilder Str = new StringBuilder();
            DataTable Dt = new DataTable();

            Str.Append(" select  0 + ROW_NUMBER() OVER(ORDER BY P.MbfID) AS [SL No],R.RegNo,R.Name,M.MBFName,P.VoucherNo,convert(varchar,P.Date,103) AS  PaymentDate,P.Narration,P.Amount");
            Str.Append(" from MBF_Payment P  inner join MBF_Master M on  M.MBFID=P.MBFID ");
            Str.Append(" inner join Registration R on  R.MemberID=P.MemberID");
            Str.Append(" where  R.Name='" + MemberName + "'");
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;
        }
          public DataTable Payment_DateMember(string Head, string MemberName, 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.MbfID) AS [SL No],R.RegNo,R.Name,M.MBFName,P.VoucherNo,convert(varchar,P.Date,103) AS  PaymentDate,P.Narration,P.Amount");
            Str.Append(" from MBF_Payment P  inner join MBF_Master M on  M.MBFID=P.MBFID ");
            Str.Append(" inner join Registration R on  R.MemberID=P.MemberID");
            Str.Append(" where  M.MBFName='" + Head + "' and R.Name='" + MemberName + "' and  P.Date BETWEEN " + dt_date);
            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.MbfID) AS [SL No],R.RegNo,R.Name,M.MBFName,P.VoucherNo,convert(varchar,P.Date,103) AS  PaymentDate,P.Narration,P.Amount");
            Str.Append(" from MBF_Payment P  inner join MBF_Master M on  M.MBFID=P.MBFID");
            Str.Append(" inner join Registration R on  R.MemberID=P.MemberID");
            Str.Append(" where P.Date BETWEEN " + dt_date);
            Dt = ObjSql.getTable_(Str.ToString());
            return Dt;

        }
    }

    }