using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MSWP
{
    class DBAccess
    {
        SqlConnection conn;

        public DBAccess()
        {
            conn = ConnectionManager.GetConnection();
        }

        public bool setSubsInfo(int MemID, int year, int amount, string date)
        {
            bool status = false;



            conn.Open();


            SqlCommand newCmd = conn.CreateCommand();

            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;

            newCmd.CommandText = "Insert into MemberSubscription values( " + MemID + "," + year + "," + amount + ",'" + date + "')";
            newCmd.ExecuteNonQuery();

            status = true;

            conn.Close();

            return status;

        }

        public DataSet FillperInfoS1(int memID, string memName, string busName)
        {
            conn.Open();


            SqlCommand newCmd2 = conn.CreateCommand();
            newCmd2.Connection = conn;
            newCmd2.CommandType = CommandType.Text;
            newCmd2.CommandText = "select MemberID,NIC, FName, MName, LName, Address, Gender, SpouseName, Telephone_Home, Mobile, Marital_Status, DOB, DOM" +
            "from dbo.personalinfo  where MemberID=" + memID + " OR LName LIKE'%" + memName + "%' OR Bname=' " + busName + "'";

            SqlDataAdapter da = new SqlDataAdapter(newCmd2);
            DataSet ds = new DataSet();
            da.Fill(ds, "personalinfo");
            conn.Close();

            return ds;

        }

        public DataSet FillBusInfoS1(int memID, string memName, string busName)
        {
            conn.Open();


            SqlCommand newCmd3 = conn.CreateCommand();
            newCmd3.Connection = conn;
            newCmd3.CommandType = CommandType.Text;
            newCmd3.CommandText = "select MemberID,BusRegno,Bname,ownership,Postal_no,Street_1,Street_2,Street_3,City_town, floor,bus_category, incometaxNo,VATno, ETF, EPF, StartDate,NoofEmp" +
            "from dbo.businessinfo  where MemberID=" + memID + " OR LName LIKE'%" + memName + "%' OR Bname=' " + busName + "'";
            SqlDataAdapter da = new SqlDataAdapter(newCmd3);
            DataSet ds = new DataSet();
            da.Fill(ds, "businessinfo");
            conn.Close();

            return ds;

        }


        public DataSet FillBusInfoS2(string busCat, string st1, string st2, string st3, string city, string dateB, string monthB, string yearB, string yearM, string monthM, string dateM)
        {
            conn.Open();


            SqlCommand newCmd4 = conn.CreateCommand();
            newCmd4.Connection = conn;
            newCmd4.CommandType = CommandType.Text;
            newCmd4.CommandText = "select MemberID,BusRegno,Bname,ownership,Postal_no,Street_1,Street_2,Street_3,City_town, floor,bus_category, incometaxNo,VATno, ETF, EPF, StartDate,NoofEmp" +
            "from dbo.businessinfo  where bus_category='" + busCat + "' or street1_1='" + st1 + "' or street_2='" + st2 + "' or street_3='" + st3 + "' or city_town='" + city +
            "' or DOB='" + dateB + monthB + yearB + "' or DOM='" + dateM + monthM + yearM + "'";
            SqlDataAdapter da = new SqlDataAdapter(newCmd4);
            DataSet ds = new DataSet();
            da.Fill(ds, "businessinfo");
            conn.Close();

            return ds;

        }

        public DataSet FillperInfoS2(string busCat, string st1, string st2, string st3, string city, string dateB, string monthB, string yearB, string yearM, string monthM, string dateM)
        {
            conn.Open();


            SqlCommand newCmd5 = conn.CreateCommand();
            newCmd5.Connection = conn;
            newCmd5.CommandType = CommandType.Text;
            newCmd5.CommandText = "select MemberID,NIC, FName, MName, LName, Address, Gender, SpouseName, Telephone_Home, Mobile, Marital_Status, DOB, DOM" +
            "from dbo.personalinfo where bus_category='" + busCat + "' or street1_1='" + st1 + "' or street_2='" + st2 + "' or street_3='" + st3 + "' or city_town='" + city +
            "' or DOB='" + dateB + monthB + yearB + "' or DOM='" + dateM + monthM + yearM + "'";
            SqlDataAdapter da = new SqlDataAdapter(newCmd5);
            DataSet ds = new DataSet();
            da.Fill(ds, "personalinfo");
            conn.Close();

            return ds;

        }



    }
}