ClsReceipt

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 ClsReceipt
    {
        public int Voucher_No { set; get; }
        public int Receipt_No { set; get; }
        public int Member_ID { set; get; }
        public int Member_Type { set; get; }
        public string Member_Name { set; get; }
        public DateTime Date { set; get; }
        public int Acc_Head { set; get; }
        public decimal  Amount { set; get; }
        public string Narration { set; get; }
     

        public DataTable Get_ReceiptHeads()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select AccountHeadID,AccountHead from Account_Head where Type=1");
            return Db.getTable_(Str.ToString());
        }
        public decimal Get_Amount(int ID,int Type)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select AMOUNT from Account_Head where Type=" + Type + " and AccountHeadID=" + ID + "");
            DataTable Dt = new DataTable();
            Dt=Db.getTable_(Str.ToString());
            if (Dt.Rows.Count > 0)
            {
                return Dt.Rows[0][0].ToDecimal();
            }
            else
            {
                return 0;
            }
           
        }

        public DataTable Get_PaymentHeads()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select AccountHeadID,AccountHead from Account_Head where Type=0");
            return Db.getTable_(Str.ToString());
        }

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

        public DataTable Get_by_Name_Acc_Members()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select UserID,Name from AccountMembers Order by Name");
            return Db.getTable_(Str.ToString());
        }

        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 Get_Details_AccountMambers(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] from AccountMembers where UserID=" + ID + "");
            return Db.getTable_(Str.ToString());
        }


        public int get_ID(string REG_NO)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MemberID from Registration where RegNo='" + REG_NO + "'");
            return Db.GetValue(Str.ToString()).ToInt() ;


        }

        public DataTable Get_Pervious_Rec(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select top 10 R.ReceiptNo,R.ReceiptDate,A.AccountHead,R.Amount,R.Narration ");
            Str.Append("from Receipt R inner join Account_Head A on R.AccountHeadID=A.AccountHeadID ");
            Str.Append("Where R.MemberID=" + ID + " and R.Status=0 order by R.ReceiptDate");
            return Db.getTable_(Str.ToString());
        }

        public DataTable Get_Row(int Rec_NO)
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select  RE.Name[Name],R.ReceiptNo[Receipt No],convert(varchar,R.ReceiptDate,106)[Date],A.AccountHead[Account Head],R.Amount,R.Narration ");
            Str.Append("from Receipt R inner join Account_Head A on R.AccountHeadID=A.AccountHeadID ");
            Str.Append("inner join Registration RE on R.MemberID=RE.MemberID Where R.ReceiptNo=" + Rec_NO + " and R.Status=0 ");
            return Db.getTable_(Str.ToString());

        }

        public DataTable Get_Row_Pay(int Voucher_No)
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select  RE.Name[Name],P.VoucherNo[Voucher No],convert(varchar,P.PaymentDate,106)[Date],A.AccountHead[Account Head],P.Amount,P.Narration ");
            Str.Append("from Payment P inner join Account_Head A on P.AccountHeadID=A.AccountHeadID ");
            Str.Append("inner join Registration RE on P.MemberID=RE.MemberID Where P.VoucherNo=" + Voucher_No + " and P.Status=0 ");
            return Db.getTable_(Str.ToString());

        }
        public DataTable Get_Row_Pay_acc(int Voucher_No)
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select  RE.Name[Name],P.VoucherNo[Voucher No],convert(varchar,P.PaymentDate,106)[Date],A.AccountHead[Account Head],P.Amount,P.Narration ");
            Str.Append("from Payment P inner join Account_Head A on P.AccountHeadID=A.AccountHeadID ");
            Str.Append("inner join AccountMembers RE on P.MemberID=RE.UserID Where P.VoucherNo=" + Voucher_No + " and P.Status=0 ");
            return Db.getTable_(Str.ToString());

        }
        public DataTable Get_Row_Pay_othr(int Voucher_No)
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select  P.MemberName[Name],P.VoucherNo[Voucher No],convert(varchar,P.PaymentDate,106)[Date],A.AccountHead[Account Head],P.Amount,P.Narration ");
            Str.Append("from Payment P inner join Account_Head A on P.AccountHeadID=A.AccountHeadID ");
            Str.Append("Where P.VoucherNo=" + Voucher_No + " and P.Status=0 ");
            return Db.getTable_(Str.ToString());

        }

        public int Get_Paytype(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select MemberType from Payment where VoucherNo="+ID+"");
            DataTable dt = new DataTable();
            dt = Db.getTable_(Str.ToString());
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToInt();
            }
            else
            {
                return 4;
            }
        }


        public DataTable Get_Pervious_Pay(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select top 10 P.VoucherNo,P.PaymentDate,A.AccountHead,P.Amount,P.Narration ");
            Str.Append("from Payment P inner join Account_Head A on P.AccountHeadID=A.AccountHeadID ");
            Str.Append("Where P.MemberID=" + ID + " and MemberType="+ClsConst.Reg_Member+" and P.Status=0 order by P.PaymentDate");
            return Db.getTable_(Str.ToString());
        }

        public DataTable Get_Pervious_Pay_NonMembers(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select top 10 P.VoucherNo,P.PaymentDate,A.AccountHead,P.Amount,P.Narration ");
            Str.Append("from Payment P inner join Account_Head A on P.AccountHeadID=A.AccountHeadID ");
            Str.Append("Where P.MemberID=" + ID + " and MemberType=" + ClsConst.Acc_Member + " and P.Status=0 order by P.PaymentDate");
            return Db.getTable_(Str.ToString());
        }

        public int Get_Next_ID()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select Max(ReceiptNo) from 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 int Get_Next_ID_Payment()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select Max(VoucherNo) from Payment ");
            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_Pervious_Pay(int ID)
        //{
        //    SqlDb Db = new SqlDb();
        //    StringBuilder Str = new StringBuilder();
        //    Str.Append("Select P.ID,P.Payment_No,P.Date,A.ACCOUNT_HEAD,P.Amount,P.Narration ");
        //    Str.Append("from Payment P inner join Account_Head A on P.Payment_Head=A.ID ");
        //    Str.Append("Where P.Member_ID=" + ID + " and P.Status=0");
        //    return Db.getTable_(Str.ToString());
        //}

        public void insert_Receipt()
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Insert into Receipt(ReceiptNo,MemberID,ReceiptDate,AccountHeadID,Amount,Narration,Status,UserID) values");
            Str.Append("("+Receipt_No+"," + Member_ID + ",'"+Date+"',"+Acc_Head+","+Amount+",'"+Narration+"',0,"+ClsConst.User_ID+")");
            Db.Execute_(Str.ToString());
        }
        public void insert_Payment()
        {

            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Insert into Payment(VoucherNo,MemberID,MemberType,MemberName,PaymentDate,AccountHeadID,Amount,Narration,Status,UserID) values");
            Str.Append("(" + Voucher_No + "," + Member_ID + "," + Member_Type + ",'" + Member_Name + "','" + Date + "'," + Acc_Head + "," + Amount + ",'" + Narration + "',0," + ClsConst.User_ID + ")");
            Db.Execute_(Str.ToString());
        }


        public DataTable Get_todays_Receipts()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            string Str_Dt = Db.getBetweenString(DateTime.Now, DateTime.Now);
            Str.Append("Select R.ReceiptID,R.ReceiptNo,REG.Name,R.ReceiptDate[Date],A.AccountHead[Account Head],R.Amount,R.Narration ");
            Str.Append("from Receipt R inner join Registration REG on R.MemberID=REG.MemberID inner join ");
            Str.Append("Account_Head A on R.AccountHeadID=A.AccountHeadID where R.Status=0 and R.ReceiptDate between " + Str_Dt);
            return Db.getTable_(Str.ToString());
        }
        public DataTable Get_todays_Payments_old()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            string Str_Dt = Db.getBetweenString(DateTime.Now, DateTime.Now);
            Str.Append("Select P.VoucherNo,REG.Name,P.PaymentDate[Date],A.AccountHead[Account Head],P.Amount,P.Narration ");
            Str.Append("from Payment P inner join Registration REG on P.MemberID=REG.MemberID inner join ");
            Str.Append("Account_Head A on P.AccountHeadID=A.AccountHeadID where P.Status=0 and P.PaymentDate between " + Str_Dt);
            return Db.getTable_(Str.ToString());
        }
       
         public DataTable Get_todays_Payments()
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            string Str_Dt = Db.getBetweenString(DateTime.Now, DateTime.Now);
            Str.Append("Select P.PaymentID,P.VoucherNo[VoucherNo],T.Name[Name],T.ID[ID],P.MemberType[MemberType],P.PaymentDate[PaymentDate],A.AccountHead[AccountHead],P.Amount,P.Narration from Payment P ");
            Str.Append("inner join (Select UserID[ID],Name,2[Type] from AccountMembers union Select MemberID[ID],Name,1[Type] from Registration) T ");
            Str.Append("on P.MemberID=T.ID and P.MemberType=T.Type inner join Account_Head A on P.AccountHeadID=A.AccountHeadID where P.Status=0 and P.PaymentDate between " +Str_Dt);
            Str.Append(" union ");
            Str.Append("Select P.PaymentID,P.VoucherNo,P.MemberName[Name],0[ID],P.MemberType,P.PaymentDate,A.AccountHead,P.Amount,P.Narration from Payment P ");
            Str.Append("inner join  Account_Head A on P.AccountHeadID=A.AccountHeadID where P.MemberType=0 and  P.Status=0 and P.PaymentDate between " + Str_Dt);
            return Db.getTable_(Str.ToString());
        }


         public int Receipt_no_present()
         {
             SqlDb Db = new SqlDb();
             StringBuilder Str = new StringBuilder();
             Str.Append("Select * from Receipt where  ReceiptNo=" + Receipt_No + "");
             DataTable Dt_temp = new DataTable();
             Dt_temp = Db.getTable_(Str.ToString());
             if (Dt_temp.Rows.Count > 0)
             {
                 return 0;
             }
             else
             {
                 return 1;
             }
         }

         public int Payment_no_present()
         {
             SqlDb Db = new SqlDb();
             StringBuilder Str = new StringBuilder();
             Str.Append("Select * from Payment where  VoucherNo=" + Voucher_No + "");
             DataTable Dt_temp = new DataTable();
             Dt_temp = Db.getTable_(Str.ToString());
             if (Dt_temp.Rows.Count > 0)
             {
                 return 0;
             }
             else
             {
                 return 1;
             }
         }

        public int Edit_Receipt(int ID)
        {


            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("Select * from Receipt where ReceiptID=" + ID + " and ReceiptNo<>" + Receipt_No + "");
            DataTable Dt_temp = new DataTable();
            Dt_temp = Db.getTable_(Str.ToString());
            if (Dt_temp.Rows.Count > 0)
            {
                return 2;
            }
            else
            {
                Str.Remove(0, Str.Length);
                Str.Append("update Receipt set ");
                Str.Append("ReceiptNo='" + Receipt_No + "',");
                Str.Append("MemberID=" + Member_ID + ",");
                Str.Append("ReceiptDate='" + Date + "', ");
                Str.Append("AccountHeadID=" + Acc_Head + ",");
                Str.Append("Amount=" + Amount + ",");
                Str.Append("UserID=" + ClsConst.User_ID + ",");
                Str.Append("Narration='" + Narration + "' where ReceiptID=" + ID + " ");
                Db.Execute_(Str.ToString());
                return 1;
            }
        }

        public int  Edit_Payement(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();

            Str.Append("Select * from Payment where PaymentID=" + ID + " and VoucherNo<>" + Voucher_No + "");
            DataTable Dt_temp = new DataTable();
            Dt_temp = Db.getTable_(Str.ToString());
            if (Dt_temp.Rows.Count > 0)
            {
                return 2;
            }
            else
            {
                Str.Remove(0, Str.Length);
                Str.Append("update Payment set ");
                Str.Append("VoucherNo='" + Voucher_No + "',");
                Str.Append("MemberID=" + Member_ID + ",");
                Str.Append("MemberType=" + Member_Type + ",");
                Str.Append("MemberName='" + Member_Name + "',");
                Str.Append("PaymentDate='" + Date + "', ");
                Str.Append("AccountHeadID=" + Acc_Head + ",");
                Str.Append("Amount=" + Amount + ",");
                Str.Append("UserID=" + ClsConst.User_ID + ",");
                Str.Append("Narration='" + Narration + "' where PaymentID=" + ID + " ");
                Db.Execute_(Str.ToString());
                return 1;
            }
        }


        public void Delete_Receipt(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("update Receipt set status=1 where ReceiptID=" + ID + " ");
            Db.Execute_(Str.ToString());
        }
        public void Delete_Payment(int ID)
        {
            SqlDb Db = new SqlDb();
            StringBuilder Str = new StringBuilder();
            Str.Append("update payment set status=1 where VoucherNo=" + ID + " ");
            Db.Execute_(Str.ToString());
        }

    }
}