ClsMemberWiseRpt

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 ClsMemberWiseRpt
    {

        public DataTable Get_Members()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select ID,Name from Registration ");
            return Db.getTable_(Str.ToString());
        }

        public DataTable Get_MBFs(int MEMBER_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MM.MBF_Name[Name],MM.ID[ID] from MBF_Master MM inner join MBF_Members MB");
            Str.Append(" on MB.MBF_ID=MM.ID where M.MEMBER_ID=" + MEMBER_ID + "");
            return Db.getTable_(Str.ToString());
        }

        public DataTable Total_Pending(int MBF_ID, int USER_ID)
        {
            DataTable Dt_result = new DataTable();
            Dt_result.Columns.Add("No_OF_M", typeof(int));
            Dt_result.Columns.Add("Total_Amt", typeof(decimal));
            Dt_result.Columns.Add("Total_Paid", typeof(decimal));

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBF_MEMBER_ID from MBF_Members where MBF_ID=" + MBF_ID + " and MEMBER_ID=" + USER_ID + "");
            string MBF_MEMBER_ID = Db.getColumnString(Str.ToString());
            Str.Remove(0, Str.Length);
            Str.Append("Select * from MBF_Members where  MBF_ID=" + MBF_ID + " and MBF_MEMBER_ID='" + MBF_MEMBER_ID + "'");
            DataTable Dt_Temp = new DataTable();
            Dt_Temp = Db.getTable_(Str.ToString());
            int Mem_Count = Dt_Temp.Rows.Count;
            Str.Remove(0, Str.Length);
            Str.Append("Select Sum(Amount_to_Pay) from MBF_Details where  MBF_ID=" + MBF_ID + " and Payment_Date>'" + DateTime.Now + "'");
            decimal Total_Amt = Db.getColumnString(Str.ToString()).ToDecimal();
            Total_Amt = Total_Amt / Mem_Count;
            Str.Remove(0, Str.Length);
            Str.Append("Select Sum(AMOUNT) from MBF_Receipt where  MBF_ID=" + MBF_ID + " and USER_ID=" + USER_ID + "");
            decimal Total_Paid = Db.getColumnString(Str.ToString()).ToDecimal();
           

            DataRow Dr = Dt_result.NewRow();
            Dr[0] = Mem_Count;
            Dr[1] = Total_Amt;
            Dr[2] = Total_Paid;
            Dt_result.Rows.Add(Dr);
            return Dt_result;

        }


        public DataTable Get_Rows_Paid(int MBF_ID, int USER_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select * from MBF_Receipt where  MBF_ID=" + MBF_ID + " and USER_ID=" + USER_ID + "");
            return Db.getTable_(Str.ToString());
        }

     





    }
}