ClsMBFInstalment

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

        public DateTime Date { set; get; }
        public int ReceiptNo { set; get; }
        public int MbfID { set; get; }
        public int MemberID { set; get; }
        public int InstalmentID { set; get; }
        public decimal  Amount { set; get; }
        public string  Narration { set; get; }

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


        public int Get_Next_ID()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select Max(ReceiptNo) from MBF_Receipt ");
            DataTable Dt = new DataTable();
            Dt = Db.getTable_(Str.ToString());
            if (Dt.Rows.Count > 0)
            {
                return Dt.Rows[0][0].ToInt() + 1;
            }
            else
            {
                return 1;
            }

        }


        public DataTable Get_Details(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select RegNo,Mobile,HouseName+ CHAR(13) + CHAR(10)+HouseNo+CHAR(13) +CHAR(10)+ ");
            Str.Append("street+CHAR(13) + CHAR(10)+City+CHAR(13) + CHAR(10)+District+ ");
            Str.Append("CHAR(13) + CHAR(10)+State[Address],Image from Registration where MemberID=" + ID + "");
            return Db.getTable_(Str.ToString());
        }

        public DataTable Member_MBFs(int memberID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select distinct(M.MBFID)[ID],max(M.MBFName)[Name] from MBF_Master M inner join ");
            Str.Append("MBF_Members D on D.MBFID=M.MBFID where D.MemberID=" + memberID + " group by M.MBFID,M.MBFName");
            return Db.getTable_(Str.ToString());
        }


        public DataTable Get_MBF_Details(int MBFID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBFDetailsID[ID],convert(varchar,PaymentDate,106)[Date] ");
            Str.Append("from MBF_Details where MBFID=" + MBFID + "");
            return Db.getTable_(Str.ToString());
        }

        public DataTable Get_Payment_details(int MBF_ID, int USER_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBFP.ReceiptNo[Receipt No], MBFM.MBFName[MBF Name],R.NAME[Member],convert(varchar,MBFD.PaymentDate,106)[Instalment Date],");
            Str.Append("MBFP.AMOUNT[Amount],convert(varchar,MBFP.Date,106)[Payment Date] from MBF_Receipt MBFP inner join MBF_Details MBFD on MBFP.INSTALMENTID=MBFD.MBFDetailsID ");
            Str.Append("inner join Registration R on MBFP.MemberID = R.MemberID inner join MBF_Master MBFM on MBFM.MBFID=MBFP.MBFID ");
            Str.Append("where MBFP.MemberID=" + USER_ID + " and MBFP.MBFID=" + MBF_ID + " and MBFP.Status=0");
            return Db.getTable_(Str.ToString());

        }

        public DataTable Get_Todays_Payment_details()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            string Str_Dt = Db.getBetweenString(DateTime.Now, DateTime.Now);
            Str.Append("Select MBFP.ReceiptID[ReceiptID],MBFP.ReceiptNo[Receipt No], MBFM.MBFName[MBF Name],R.NAME[Member],convert(varchar,MBFD.PaymentDate,106)[Instalment Date],");
            Str.Append("MBFP.AMOUNT[Amount],convert(varchar,MBFP.Date,106)[Payment Date],MBFP.Narration[Narration] from MBF_Receipt MBFP inner join MBF_Details MBFD on MBFP.INSTALMENTID=MBFD.MBFDetailsID ");
            Str.Append("inner join Registration R on MBFP.MemberID = R.MemberID inner join MBF_Master MBFM on MBFM.MBFID=MBFP.MBFID ");
            Str.Append("where MBFP.Date between "+Str_Dt);
            return Db.getTable_(Str.ToString());
        }


        public DataTable Get_Payment_Row(int Receipt_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBFP.ReceiptNo[Receipt No], MBFM.MBFName[MBF Name],R.NAME[Member],convert(varchar,MBFD.PaymentDate,106)[Instalment Date],");
            Str.Append("MBFP.AMOUNT[Amount],convert(varchar,MBFP.Date,106)[Payment Date],MBFP.Narration[Narration] from MBF_Receipt MBFP inner join MBF_Details MBFD on MBFP.INSTALMENTID=MBFD.MBFDetailsID ");
            Str.Append("inner join Registration R on MBFP.MemberID = R.MemberID inner join MBF_Master MBFM on MBFM.MBFID=MBFP.MBFID ");
            Str.Append("where MBFP.ReceiptNo=" + Receipt_ID + " and MBFP.Status=0");
            return Db.getTable_(Str.ToString());

        }


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

        public int Get_Members_count(int MBF_ID, int User_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MBFMemberRegNo from MBF_Members where MBFID=" + MBF_ID + " and MemberID=" + User_ID + "");
            string MBF_MEMBER_ID = Db.getColumnString(Str.ToString());
            Str.Remove(0, Str.Length);
            Str.Append("Select * from MBF_Members where  MBFID=" + MBF_ID + " and MBFMemberRegNo='" + MBF_MEMBER_ID + "'");
            DataTable Dt = Db.getTable_(Str.ToString());
            return Dt.Rows.Count;
        }

        public void Make_payment()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Insert into MBF_Receipt(ReceiptNo,Date,MbfID,MemberID,InstalmentID,Amount,Narration,Status)values(");
            Str.Append(""+ReceiptNo+",'"+Date+"'," + MbfID + "," + MemberID + "," + InstalmentID + "," + Amount + ",'"+Narration+"',0)");
            Db.Execute_(Str.ToString());

        }


        public void Update_payment(int Receipt_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Update  MBF_Receipt set ");
            Str.Append("ReceiptNo=" + ReceiptNo + ",");
            Str.Append("Date='" + Date + "',");
            Str.Append("MbfID="+MbfID+",");
            Str.Append("MemberID=" + MemberID + ",");
            Str.Append("InstalmentID=" + InstalmentID + ",");
            Str.Append("Amount=" + Amount + ",");
            Str.Append("Narration='" + Narration + "' where ReceiptID=" + Receipt_ID + "");
            Db.Execute_(Str.ToString());
        }

        public void Delete_payment(int Receipt_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Update  MBF_Receipt set ");
            Str.Append("Status=1");
            Str.Append(" where ReceiptNo=" + Receipt_ID + "");
            Db.Execute_(Str.ToString());

        }


    }
}