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

namespace ajans
{
    class driver:car
    {
        protected SqlConnection objconnc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ajans.mdf;Integrated Security=True;User Instance=True");
        protected SqlCommand objcommc = new SqlCommand();
        protected SqlDataAdapter objdac = new SqlDataAdapter();
        protected DataSet objdsc = new DataSet();


        protected string dvr_id;
        protected string dvr_name;
        protected string dvr_fname;
        protected string dvr_birthday;
        protected string dvr_marry;
        protected string dvr_address;
        protected string dvr_phone;
        protected string dvr_mobile;
        protected string dvr_car;
        protected string dvr_car_class;
        protected string dvr_recruitdate;
        protected string dvr_status;
        protected string dvr_amount;
        protected string dvr_delete;

        public driver()
        {
            dvr_id = "";
            dvr_name = "";
            dvr_fname = "";
            dvr_birthday = "";
            dvr_marry = "";
            dvr_address = "";
            dvr_phone = "";
            dvr_mobile = "";
            dvr_car = "";
            dvr_car_class = "";
            car_id = "";
            dvr_recruitdate = "";
            dvr_status = "";
            dvr_amount = "";
            dvr_delete = "";
        }

        public void SetDvrID(string dvrID)
        {
            dvr_id = dvrID;
        }
        public void SetDvrName(string dvrName)
        {
            dvr_name = dvrName;
        }
        public void SetDvrFname(string dvrFname)
        {
            dvr_fname = dvrFname;
        }
        public void SetDvrBir(string dvrBir)
        {
            dvr_birthday = dvrBir;
        }
        public void SetDvrMarry(string dvrMarry)
        {
            dvr_marry = dvrMarry;
        }
        public void SetDvrAddress(string dvrAddress)
        {
            dvr_address = dvrAddress;
        }
        public void SetDvrPhone(string dvrPhone)
        {
            dvr_phone = dvrPhone;
        }
        public void SetDvrMobile(string dvrMobile)
        {
            dvr_mobile = dvrMobile;
        }
        public void SetDvrCar(string dvrCar)
        {
            dvr_car = dvrCar;
        }
        public void SetDvrCarClass(string dvrCarClass)
        {
            dvr_car_class = dvrCarClass;
        }
        public void SetDvrRec(string dvrRec)
        {
            dvr_recruitdate = dvrRec;
        }
        public void SetDvrStatus(string dvrStatus)
        {
            dvr_status = dvrStatus;
        }
        public void SetDvrAmount(string dvrAmount)
        {
            dvr_amount = dvrAmount;
        }
        public void SetDvrDelete(string dvrDelete)
        {
            dvr_delete = dvrDelete;
        }

        public void InsertDriver()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO driver VALUES(N'" + dvr_id + "',N'" + dvr_name + "',N'" + dvr_fname + "',N'" + dvr_birthday + "',N'" + dvr_marry + "',N'" + dvr_address + "',N'" + dvr_phone + "',N'" + dvr_mobile + "',N'" + dvr_car + "',N'" + car_id + "',N'" + dvr_recruitdate + "',N'" + dvr_status + "',N'" + dvr_car_class + "','" + dvr_amount + "','" + dvr_delete + "')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }


        public DataSet SelectDriver()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT dvr_id as [کد راننده] , dvr_name + ' ' + dvr_fname as [نام و نام خانوادگی], dvr_birthday as [تاریخ تولد],dvr_address as [آدرس],dvr_phone as [تلفن],dvr_mobile as [موبایل],dvr_car as [نام ماشین],dvr_car_class as [کلاس ماشین],car_id as [کد ماشین],dvr_recruitdate as [تاریخ استخدام] FROM driver where dvr_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriver3(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT dvr_id as [کد راننده] , dvr_name as [نام], dvr_fname as [نام خانوادگی], dvr_birthday as [سال تولد], dvr_marry as [تاهل],dvr_address as [آدرس],dvr_phone as [تلفن ثابت],dvr_mobile as [موبایل],dvr_car as [نام ماشین],car_id as [کد ماشین],dvr_recruitdate as [تاریخ استخدام],dvr_car_class as [کلاس ماشین] FROM driver WHERE dvr_id='" + dvrID + "' and dvr_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCountDriver(string dvrID)
        {
            objdsc.Clear();
            objdac.SelectCommand = new SqlCommand();
            objdac.SelectCommand.Connection = objconnc;
            objdac.SelectCommand.CommandText = "SELECT COUNT(dvr_id) AS 'count' FROM driver WHERE (dvr_id = '" + dvrID + "') and dvr_delete=0";
            objdac.SelectCommand.CommandType = CommandType.Text;
            objconnc.Open();
            objdac.Fill(objdsc, "driver");
            objconnc.Close();
            return objdsc;
        }

        public DataSet SelectDriver(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT dvr_id as [کد راننده] , dvr_name + ' ' + dvr_fname as [نام و نام خانوادگی], dvr_birthday as [تاریخ تولد],dvr_address as [آدرس],dvr_phone as [تلفن],dvr_mobile as [موبایل],dvr_car as [نام ماشین],dvr_car_class as [کلاس ماشین],car_id as [کد ماشین],dvr_recruitdate as [تاریخ استخدام] FROM driver WHERE dvr_id='" + dvrID + "' and dvr_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverID(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT dvr_id as [کد راننده] , dvr_name + ' ' + dvr_fname as [نام و نام خانوادگی], dvr_car as [نام ماشین],dvr_car_class as [کلاس ماشین],car_id as [کد ماشین],dvr_status as [وضعیت] FROM driver WHERE dvr_id='" + dvrID + "' and dvr_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriver(string dvrName, string dvrFname)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT dvr_id as [کد راننده] , dvr_name + ' ' + dvr_fname as [نام و نام خانوادگی], dvr_birthday as [تاریخ تولد],dvr_address as [آدرس],dvr_phone as [تلفن],dvr_mobile as [موبایل],dvr_car as [نام ماشین],dvr_car_class as [کلاس ماشین],car_id as [کد ماشین],dvr_recruitdate as [تاریخ استخدام] FROM driver WHERE (dvr_name + ' ' + dvr_fname LIKE '%" + dvrName + "%'+' '+'%" + dvrFname + "%') and dvr_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriver2(string dvrCar)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT dvr_id as [کد راننده] , dvr_name + ' ' + dvr_fname as [نام و نام خانوادگی], dvr_birthday as [تاریخ تولد], dvr_address as [آدرس],dvr_phone as [تلفن],dvr_mobile as [موبایل],dvr_car as [نام ماشین],dvr_car_class as [کلاس ماشین],car_id as [کد ماشین],dvr_recruitdate as [تاریخ استخدام] FROM driver WHERE dvr_car LIKE '%" + dvrCar + "%' and dvr_delete=0";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCarDriver(string carID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(car.car_id) AS 'وضعیت' FROM driver INNER JOIN  car ON driver.car_id = car.car_id WHERE (driver.car_id = '" + carID + "') and (driver.dvr_delete=0) and (car.car_delete=0)";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCarDriver()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(car.car_id) AS 'وضعیت' FROM driver INNER JOIN  car ON driver.car_id = car.car_id WHERE (driver.car_id != '0') and (driver.dvr_delete=0) and (car.car_delete=0)";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

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


        public DataTable SelectDriverReport(string dvrID)
        {
            DataTable dt = new DataTable();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            string sql = "select * from driver where dvr_id='" + dvrID + "' and dvr_delete=0";
            objda.SelectCommand.CommandText = sql;
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(dt);
            objconn.Close();
            return dt;
        }


        public void UpdateDriverStatus()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE driver SET dvr_status='" + "0" + "' WHERE dvr_id='" + dvr_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void DeleteDriver()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "update driver set dvr_delete=1 WHERE  dvr_id='" + dvr_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void DeleteAllDriver()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "update driver set dvr_delete=1 WHERE  dvr_id !='0'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void UpdateDriver()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE driver SET dvr_car=N'" + dvr_car + "',dvr_car_class=N'" + dvr_car_class + "',car_id='" + car_id + "' WHERE dvr_id='" + dvr_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }
        public void UpdateDriver2()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE driver SET dvr_name=N'" + dvr_name + "',dvr_fname=N'" + dvr_fname + "',dvr_phone='" + dvr_phone + "',dvr_mobile='" + dvr_mobile + "',dvr_address=N'" + dvr_address + "' WHERE dvr_id='" + dvr_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }
    }
}
