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

namespace ajans
{
    class area:connection
    {
        protected string area_id;
        protected string area_address;
        protected string area_amount;

        public area()
        {
            area_id = "";
            area_address = "";
            area_amount = "";
        }

        public void SetAreaID(string areaID)
        {
            area_id = areaID;
        }
        public void SetAreaAddress(string areaAddress)
        {
            area_address = areaAddress;
        }
        public void SetAreaAmount(string areaAmount)
        {
            area_amount = areaAmount;
        }
        public void InsertArea()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO area VALUES(N'" + area_id + "',N'" + area_address + "',N'" + area_amount + "',0)";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public DataSet SelectArea()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select area_id as [کد منطقه] , area_address as [نام منطقه] ,area_amount as [مبلغ] from area WHERE area_id !=0 and area_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "area");
            objconn.Close();
            return objds;
        }

        public DataSet SelectArea(string areaID )
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select area_id as [کد منطقه] , area_address as [نام منطقه] ,area_amount as [مبلغ] from area WHERE area_id='" + areaID + "' AND area_id != 0 AND area_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "area");
            objconn.Close();
            return objds;
        }

        public DataSet SelectArea2(string areaAddress)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select area_id as [کد منطقه] , area_address as [نام منطقه] ,area_amount as [مبلغ] from area WHERE area_address LIKE '%" + areaAddress + "%' AND area_id != 0 AND area_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "area");
            objconn.Close();
            return objds;
        }

        public DataSet SelectArea3(string areaAddress)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select area_id as [کد منطقه] , area_address as [نام منطقه] ,area_amount as [مبلغ] from area WHERE area_address LIKE '" + areaAddress + "' AND area_id != 0 AND area_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "area");
            objconn.Close();
            return objds;
        }

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

        public DataSet SelectMinAmount()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select MIN(area_amount) as [هزینه شهری] FROM area WHERE area_id != 0 AND area_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "area");
            objconn.Close();
            return objds;
        }

        public void DeleteArea()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE area SET area_delete=1 WHERE  area_id='" + area_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void DeleteAllArea()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE area SET area_delete=1 WHERE  area_id != '0'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }


        public void UpdateArea()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE area SET area_address='" + area_address + "',area_amount='" + area_amount + "' WHERE area_id='" + area_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE customer SET cus_street='" + area_address + "'FROM customer INNER JOIN area ON customer.area_id=area.area_id WHERE area.area_id='" + area_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

    }
}
