ClsMBFCollection

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 ClsMBFCollection
    {
        public DataTable Get_MBFs()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select ID,MBF_Name from MBF_Master where status=0");
            return Db.getTable_(Str.ToString());
        }

        public DataTable Get_Members(int MBF_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select R.Name[Name],R.ID[ID] from Registration R inner join MBF_Members M");
            Str.Append(" on R.ID=M.Member_ID where M.MBF_ID=" + MBF_ID + "");
            return Db.getTable_(Str.ToString());
        }

        public decimal Get_amount(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select Amount_to_pay from MBF_Details where ID="+ID+"");
            DataTable Dt= new DataTable();
            Dt=Db.getTable_(Str.ToString());
            string srt_Amt = Dt.Rows[0][0].ToString();
            return srt_Amt.ToDecimal();
        }

        public DataTable Get_Dates(int MBF_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select convert(varchar,Payment_Date,103)[Date],ID from MBF_Details where MBF_ID=" + MBF_ID + "");
            return Db.getTable_(Str.ToString());
        }

        public int Get_Members_count(int MBF_ID,int User_ID)
        {
            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 = Db.getTable_(Str.ToString());
            return Dt.Rows.Count;
        }


        public decimal Total_Pending(int MBF_ID, int USER_ID, DateTime Dt)
        {
            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();
            return (Total_Amt - Total_Paid);

        }

        public DataTable Get_Payment_details(int MBF_ID, int USER_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBFM.MBF_Name[MBF_NAME],R.NAME[MEMBER],convert(varchar,MBFD.Payment_Date,103)[DATE],");
            Str.Append("MBFP.AMOUNT[AMOUNT] from MBF_Receipt MBFP inner join MBF_Details MBFD on MBFP.INSTALMENT_ID=MBFD.ID ");
            Str.Append("inner join Registration R on MBFP.USER_ID = R.ID inner join MBF_Master MBFM on MBFM.ID=MBFP.MBF_ID ");
            Str.Append("where MBFP.USER_ID="+USER_ID+" and MBFP.MBF_ID="+MBF_ID+"");
            return  Db.getTable_(Str.ToString());

        }


        public void Make_payment()
        {
            //SqlDb Db = new SqlDb();
            //StringBuilder Str = new StringBuilder();
            //Str.Append("Insert into MBF_Payment(MBF_ID,USER_ID,INSTALMENT_ID,AMOUNT)values(");
            //Str.Append("" + MBF_ID + "," + USER_ID + "," + INSTALMENT_ID + "," + Amount + ")");
            //Db.Execute_(Str.ToString());

        }
       

    }
}