ClsMonthyPayment

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

        public int MemberID { set; get; }
        public string MemberName { set; get; }
        public int Month { set; get; }
        public int Year { set; get; }
        public decimal Amount { set; get; }

        public DataTable Top_MonthlyPaid(int MemID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select max(convert(int,rtrim(year)+REPLICATE('0',2-len(Rtrim(month)))+");
            Str.Append(" cast(Month as varchar(2)))) from MonthlyPayment where MemberID=" + MemID + "");
            return Db.getTable_(Str.ToString());
        }


        public DataTable Fill_Grid(int MemID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select top 24 ");
            Str.Append("case when month=1 then 'Jan/'+rtrim(year) ");
            Str.Append("when month=2 then 'Feb/'+rtrim(year) when month =3 then 'Mar/'+rtrim(year) ");
            Str.Append("when month =4 then 'Apr/'+rtrim(year) when month =5 then 'May/'+rtrim(year) ");
            Str.Append("when month =6 then 'Jun/'+rtrim(year) when month =7 then 'Jul/'+rtrim(year) ");
            Str.Append("when month =8 then 'Aug/'+rtrim(year) when month =9 then 'Sep/'+rtrim(year) ");
            Str.Append("when month =10 then 'Oct/'+rtrim(year) when month =11 then 'Nov/'+rtrim(year) ");
            Str.Append("when month =12 then 'Dec/'+rtrim(year) end [Month], ");
            Str.Append("year,case when PaymentType=0 then 'Monthly' when PaymentType=1 then 'Yearly' end[Payment Type], ");
            Str.Append("Amount from MonthlyPayment where MemberID=" + MemID + " order by (convert(int,rtrim(year)+REPLICATE('0',2-len(Rtrim(month)))+");
            Str.Append("cast(Month as varchar(2)))) desc ");
            return Db.getTable_(Str.ToString());
           
        }


        public void insert_monthly(int mon, int yer)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append(" DECLARE @Receipt_Id INT ");
            Str.Append(" SELECT @Receipt_Id=IDENT_CURRENT('Receipt') ");
            Str.Append("Insert into MonthlyPayment(ReceiptID,MemberID,MemberName,Month,Year,Amount,PaymentType,Status) values");
            Str.Append("(@Receipt_Id," + MemberID + ",'" + MemberName + "'," + mon + "," + yer + "," + Amount + ",0,0)");
            Db.Execute_(Str.ToString());
        }

        public void update_lasttwo(int MemID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("update MonthlyPayment set Amount=0 where MonthlyPaymentID in (Select top 2 MonthlyPaymentID from ");
            Str.Append(" MonthlyPayment where MemberID=" + MemID + "  order by (convert(int,rtrim(year)+REPLICATE('0',2-len(Rtrim(month)))+");
            Str.Append("cast(Month as varchar(2)))) desc )");
            Db.Execute_(Str.ToString());
        }

        public void Insert_Initial(int Mon,int yer)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
         
            for (int i = Mon; i < 13; i++)
            {

                Str.Remove(0, Str.Length);
                Str.Append(" DECLARE @Receipt_Id INT ");
                Str.Append(" SELECT @Receipt_Id=IDENT_CURRENT('Receipt') ");
                Str.Append("Insert into MonthlyPayment(ReceiptID,MemberID,MemberName,Month,Year,Amount,PaymentType,Status) values");
                Str.Append("(@Receipt_Id," + MemberID + ",'" + MemberName + "'," + i + "," + yer + "," + Amount + ",1,0)");
                Db.Execute_(Str.ToString());
            }
            yer=yer+1;
            Insert_Final(Mon, yer);

        }

        public void DeleteMonthlyuSubscription(int Receipt_ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Delete from MonthlyPayment where ReceiptID=" + Receipt_ID + "");
            Db.Execute_(Str.ToString());
        }

        private void Insert_Final(int Mon, int yer)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            for (int i = 1; i < Mon; i++)
            {
                Str.Remove(0, Str.Length);
                Str.Append(" DECLARE @Receipt_Id INT ");
                Str.Append(" SELECT @Receipt_Id=IDENT_CURRENT('Receipt') ");
                Str.Append("Insert into MonthlyPayment(ReceiptID,MemberID,MemberName,Month,Year,Amount,PaymentType,Status) values");
                Str.Append("(@Receipt_Id," + MemberID + ",'" + MemberName + "'," + i + "," + yer + "," + Amount + ",1,0)");
                Db.Execute_(Str.ToString());
            }

            update_lasttwo(MemberID);
        }

    }
}