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

namespace ajans
{
    class customer:area
    {
        protected string cus_id;
        protected string cus_name;
        protected string cus_phone;
        protected string cus_street;
        protected string cus_address;
        protected string cus_plate;
        protected string cus_amount;
        protected string cus_delete;

        public customer()
        {
            cus_id = "";
            cus_name = "";
            cus_phone = "";
            cus_street = "";
            cus_address = "";
            cus_plate = "";
            cus_amount="";
            cus_delete = "";
        }

        public void SetCusID(string cusID)
        {
            cus_id = cusID;
        }
        public void SetCusName(string cusName)
        {
            cus_name = cusName;
        }
        public void SetCusStreet(string cusStreet)
        {
            cus_street = cusStreet;
        }
        public void SetCusPhone(string cusPhone)
        {
            cus_phone = cusPhone;
        }
        public void SetCusAddress(string cusAddress)
        {
            cus_address = cusAddress;
        }
        public void SetCusAmount(string cusAmount)
        {
            cus_amount = cusAmount;
        }
        public void SetCusDelete(string cusDelete)
        {
            cus_delete = cusDelete;
        }

        public void InsertCustomer()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO customer VALUES(N'" + cus_id + "',N'" + cus_name + "',N'" + cus_phone + "',N'" + cus_street + "',N'" + cus_address + "',N'" + area_id + "',N'" + cus_amount + "',N'" + cus_delete + "')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public DataSet SelectCustomer()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select cus_id as [کد مشترک] , cus_name as [نام] ,cus_street as [نام منطقه],cus_address as [آدرس دقیق], cus_phone as [تلفن] from customer INNER JOIN area ON area.area_id = customer.area_id WHERE cus_id !=0 AND cus_delete=0 ";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCustomer(string cusID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select cus_id as [کد مشترک] , cus_name as [نام] ,cus_street as [منطقه],cus_address as [آدرس دقیق], cus_phone as [تلفن] from customer WHERE cus_id='" + cusID + "' AND cus_id != 0 AND cus_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCustomerArea(string areaID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(area.area_id) AS 'وضعیت' FROM customer INNER JOIN  area ON customer.area_id = area.area_id WHERE (customer.area_id = '" + areaID + "' AND cus_delete=0)";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCustomerArea()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(area.area_id) AS 'وضعیت' FROM customer INNER JOIN  area ON customer.area_id = area.area_id WHERE (customer.area_id != '0' AND customer.cus_delete=0 AND area.area_delete=0 )";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCusAreaAmount(string cusID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT area.area_amount as [هزینه مبدا] FROM customer INNER JOIN area ON customer.area_id = area.area_id WHERE (customer.cus_id ='" + cusID + "' AND customer.cus_delete=0)";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCustomer2(string cusName)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select cus_id as [کد مشترک] , cus_name as [نام مشترک] ,cus_street as [نام منطقه],cus_address as [آدرس دقیق], cus_phone as [تلفن] from customer WHERE cus_name LIKE N'%" + cusName + "%' AND cus_id != 0 AND cus_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCustomer3(string cusTel)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select cus_id as [کد مشترک] , cus_name as [نام مشترک] ,cus_street as [نام منطقه],cus_address as [آدرس دقیق], cus_phone as [تلفن] from customer WHERE cus_phone LIKE '%" + cusTel + "%' AND cus_id != 0 AND cus_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCustomer(string cusName, string cusTel,string areaID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select cus_id as [کد مشترک] , cus_name as [نام مشترک] ,cus_street as [نام منطقه],cus_address as [آدرس دقیق], cus_phone as [تلفن] from customer WHERE (cus_name='" + cusName + "')AND (cus_phone='" + cusTel + "')AND (area_id='" + areaID + "') AND(cus_delete=0)";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public DataSet MaxCusID()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT MAX(cus_id) as [MAX] FROM customer";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "customer");
            objconn.Close();
            return objds;
        }

        public void DeleteCustomer()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE customer SET cus_delete=1 WHERE  cus_id='" + cus_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void DeleteAllCustomer()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE customer SET cus_delete=1 WHERE  cus_id !='0'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void UpdateCustomer()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE customer SET cus_name='" + cus_name + "',cus_phone='" + cus_phone + "',cus_street='" + cus_street + "',cus_address='" + cus_address + "',area_id='" + area_id + "' WHERE cus_id='" + cus_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE request SET req_street='" + cus_street + "', req_address='" + cus_address+ "',req_cus_name='" + cus_name + "' FROM request INNER JOIN customer ON request.cus_id=customer.cus_id WHERE (request.cus_id ='" + cus_id + "') AND (request.req_ser_status > '0')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void RequestService()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE customer SET req_count= req_count + " + 1 + "WHERE cus_id='" + cus_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }


    }
}
