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

namespace ajans
{
    class present
    {
        protected string prs_id;
        protected string dvr_id;
        protected string prs_date;
        protected string prs_time;
        protected string prs_status;

        protected SqlConnection objconn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ajans.mdf;Integrated Security=True;User Instance=True");
        protected SqlCommand objcomm = new SqlCommand();
        protected SqlDataAdapter objda = new SqlDataAdapter();
        protected DataSet objds = new DataSet();

        public present()
        {
            prs_id = "";
            dvr_id = "";
            prs_date = "";
            prs_time = "";
            prs_status = "";
        }

        public void SetPrsID(string prsID)
        {
            prs_id = prsID;
        }
        public void SetDvrID(string dvrID)
        {
            dvr_id = dvrID;
        }
        public void SetPrsDate(string prsDate)
        {
            prs_date = prsDate;
        }
        public void SetPrsTime(string prsTime)
        {
            prs_time = prsTime;
        }
        public void SetPrsStatus(string prsStatus)
        {
            prs_status = prsStatus;
        }

        public void InsertPresent()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO present VALUES(N'" + prs_id + "',N'" + dvr_id + "',N'" + prs_date + "',N'" + prs_time + "',N'" + prs_status + "')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void ServiceCarCount()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE present SET prs_status='0' WHERE prs_id ='" + prs_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE count SET count= count - " + 1 + " WHERE count >= 0 ";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public DataSet SelectPresent()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select prs_id as [کد حضور] , dvr_id as [کد راننده] ,prs_date as [تاریخ], prs_time as [ساعت] from present where prs_status='0' ";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDvrPresent(string prsID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select prs_id as [کد حضور] , dvr_id as [کد راننده] ,prs_date as [تاریخ], prs_time as [ساعت] from present WHERE prs_id='" + prsID + "'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDvrPresent2(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select prs_id as [کد حضور] , dvr_id as [کد راننده] ,prs_date as [تاریخ], prs_time as [ساعت] from present WHERE (prs_status = '0') AND dvr_id='" + dvrID + "'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectPresent2(string PrsID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT present.dvr_id as [کد راننده],dvr_name+' '+dvr_fname as [نام راننده],dvr_car_class as [کلاس ماشین] FROM present INNER JOIN driver ON present.dvr_id=driver.dvr_id WHERE prs_id='" + PrsID + "' AND prs_status='0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectPresentDriver()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select prs_id as [کد حضور] , present.dvr_id as [کد راننده] ,dvr_name + ' ' + dvr_fname as [نام راننده], dvr_car_class as [کلاس ماشین] FROM present INNER JOIN driver ON present.dvr_id=driver.dvr_id WHERE prs_status='0' ORDER BY prs_id ";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverPresent(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(present.dvr_id) AS 'وضعیت' FROM present INNER JOIN driver ON present.dvr_id = driver.dvr_id WHERE (present.prs_status = '0') AND (present.dvr_id = '" + dvrID + "')";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverPresent()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(present.dvr_id) AS 'وضعیت' FROM present INNER JOIN driver ON present.dvr_id = driver.dvr_id WHERE (present.prs_status = '0') AND (present.dvr_id != '0')";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectTopCar()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(prs_id) as [کد حضور] FROM present  INNER JOIN  driver ON present.dvr_id = driver.dvr_id WHERE prs_status='0' AND dvr_car_class=N'مدل بالا'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMiddleCar()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(prs_id) as [کد حضور] FROM present  INNER JOIN  driver ON present.dvr_id = driver.dvr_id WHERE prs_status='0' AND dvr_car_class=N'مدل متوسط'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectButtonCar()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(prs_id) as [کد حضور] FROM present  INNER JOIN  driver ON present.dvr_id = driver.dvr_id WHERE prs_status='0' AND dvr_car_class=N'مدل پایین'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectPresent(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT prs_id as [کد حضور], prs_status as [وضعیت سرویس] FROM present WHERE (prs_id =(SELECT MAX(prs_id) FROM present WHERE (dvr_id ='" + dvrID + "')))";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectPresentCount()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(prs_id) as [کد حضور] FROM present WHERE prs_status='0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }


        public DataSet SelectCarCount()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select count as [تعداد] from count ";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "count");
            objconn.Close();
            return objds;
        }

        public DataSet SelectPrsID()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select MIN(prs_id) as [کد حضور] from present INNER JOIN driver ON present.dvr_id=driver.dvr_id WHERE prs_status ='0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }


        public DataSet SelectPrsID(string reqCarClass)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select MIN(prs_id) as [کد حضور] from present INNER JOIN driver ON present.dvr_id=driver.dvr_id WHERE prs_status ='0' AND dvr_car_class=N'" + reqCarClass + "'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }


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

        public void DeletePresent()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "DELETE FROM present WHERE  prs_id='" + prs_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void DeleteAllPresent()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "DELETE FROM present WHERE  prs_status ='0'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void UpdateCountPresent()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE count SET count= count - '" + 1 + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void UpdateCountPresent2()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE count SET count= count + '" + 1 + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        } 

        public void UpdateCountNull()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE count SET count='0'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

    }
}
