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


namespace ajans
{
    class request:customer
    {
        protected string req_id;
        protected string req_date;
        protected string req_time;
        protected string req_car_class;
        protected string req_car_count;
        protected string req_street;
        protected string req_address;
        protected string req_cus_name;
        protected string req_end_street;
        protected string req_end_address;
        protected string req_end_name;
        protected string req_amount;
        protected string req_ser_status;

        public request()
        {
            req_id = "";
            req_date = "";
            req_time = "";
            req_car_class = "";
            req_car_count = "";
            req_street = "";
            req_address = "";
            req_cus_name = "";
            req_end_street = "";
            req_end_address = "";
            req_end_name = "";
            req_amount = "";
            req_ser_status = "";
        }

        public void SetReqID(string reqID)
        {
            req_id = reqID;
        }
        public void SetReqDate(string reqDate)
        {
            req_date = reqDate;
        }
        public void SetReqTime(string reqTime)
        {
            req_time = reqTime;
        }
        public void SetReqCarClass(string reqCarClass)
        {
            req_car_class = reqCarClass;
        }
        public void SetReqCarCount(string reqCarCount)
        {
            req_car_count = reqCarCount;
        }
        public void SetReqStreet(string reqStreet)
        {
            req_street = reqStreet;
        }
        public void SetReqAddress(string reqAddress)
        {
            req_address = reqAddress;
        }
        public void SetReqCusName(string reqCusName)
        {
            req_cus_name = reqCusName;
        }
        public void SetReqEndStreet(string reqEndStreet)
        {
            req_end_street = reqEndStreet;
        }
        public void SetReqEndAddress(string reqEndAddress)
        {
            req_end_address= reqEndAddress;
        }
        public void SetReqEndName(string reqEndName)
        {
            req_end_name = reqEndName;
        }
        public void SetReqAmount(string reqAmount)
        {
            req_amount = reqAmount;
        }
        public void SetReqSerStatus(string reqSerStatus)
        {
            req_ser_status = reqSerStatus;
        }

        public void InsertRequest()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO request VALUES(N'" + req_id + "',N'" + cus_id + "',N'" + req_date + "',N'" + req_time + "',N'" + req_car_count + "',N'" + req_car_class + "',N'" + req_street + "',N'" + req_address + "',N'" + req_cus_name + "',N'" + req_end_street + "',N'" + req_end_address + "',N'" + req_end_name + "',N'" + req_amount + "',N'" + req_ser_status + "')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }
        public DataSet SelectRequest()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id AS [کد درخواست],req_cus_name AS [نام مشتری], req_date as [تاریخ] ,req_time AS [ساعت], req_ser_status AS [تعداد ماشین], req_car_class AS [کلاس ماشین],req_street + '-' + req_address AS [آدرس مبدا],req_end_street + '-' + req_end_address + '-' + req_end_name AS [آدرس مقصد] FROM request WHERE req_ser_status > '0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }



        public DataSet SelectRequest(string ReqID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id AS [کد درخواست],req_cus_name AS [نام مشتری], req_date as [تاریخ] ,req_time AS [ساعت], req_ser_status AS [تعداد ماشین], req_car_class AS [کلاس ماشین],req_street + '-' + req_address AS [آدرس مبدا],req_end_street + '-' + req_end_address + '-' + req_end_name AS [آدرس مقصد],req_amount FROM request WHERE req_id='" + ReqID + "' AND req_ser_status > '0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectRequest2(string ReqName)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id AS [کد درخواست],req_cus_name AS [نام مشتری], req_date as [تاریخ] ,req_time AS [ساعت], req_ser_status AS [تعداد ماشین], req_car_class AS [کلاس ماشین],req_street + '-' + req_address AS [آدرس مبدا],req_end_street + '-' + req_end_address + '-' + req_end_name AS [آدرس مقصد] FROM request WHERE req_cus_name LIKE '%" + ReqName + "%' AND req_ser_status > '0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectRequestCHK1()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id AS [کد درخواست],req_cus_name AS [نام مشتری], req_date as [تاریخ] ,req_time AS [ساعت], req_ser_status AS [تعداد ماشین], req_car_class AS [کلاس ماشین],req_street + '-' + req_address AS [آدرس مبدا],req_end_street + '-' + req_end_address + '-' + req_end_name AS [آدرس مقصد] FROM request inner join customer on request.cus_id=customer.cus_id WHERE request.cus_id > '0' AND req_ser_status > '0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectRequestCHK2()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id AS [کد درخواست],req_cus_name AS [نام مشتری], req_date as [تاریخ] ,req_time AS [ساعت], req_ser_status AS [تعداد ماشین], req_car_class AS [کلاس ماشین],req_street + '-' + req_address AS [آدرس مبدا],req_end_street + '-' + req_end_address + '-' + req_end_name AS [آدرس مقصد] FROM request WHERE cus_id = '0' AND req_street=N'حضوری' AND req_ser_status > '0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectRequestCHK3()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id AS [کد درخواست],req_cus_name AS [نام مشتری], req_date as [تاریخ] ,req_time AS [ساعت], req_ser_status AS [تعداد ماشین], req_car_class AS [کلاس ماشین],req_street + '-' + req_address AS [آدرس مبدا],req_end_street + '-' + req_end_address + '-' + req_end_name AS [آدرس مقصد] FROM request WHERE request.cus_id = '0' AND req_cus_name !=N'حضوری' AND req_ser_status > '0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectRequestCount()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select count(req_id) as [کد درخواست] from request where req_ser_status > '0'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

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


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

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

        public void DeleteRequest()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "DELETE FROM request WHERE  req_id='" + req_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void DeleteAllRequest()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "DELETE FROM request WHERE  req_id !='0'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public DataSet SelectReqID()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT MIN(req_id) AS [req_id],req_amount,req_car_class  FROM request WHERE(req_time =(SELECT MIN(req_time) FROM  request WHERE  (req_date = (SELECT MIN(req_date) FROM  request WHERE (req_ser_status > '0')))AND(req_ser_status > '0')))GROUP BY req_amount,req_car_class";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectQueue()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id,req_date+','+req_time as req_datetime FROM request WHERE(req_time =(SELECT MIN(req_time) FROM  request WHERE  (req_date = (SELECT MIN(req_date) FROM  request WHERE (req_ser_status > '0')))AND(req_ser_status > '0')))";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectQueue2()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT req_id as [کد درخواست],req_cus_name as [نام],req_date as [تاریخ], req_time AS [ساعت] FROM request WHERE req_ser_status > 0 ORDER BY req_date, req_time";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public void UpdateServiceRequest()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE request SET req_ser_status= req_ser_status - " + 1 + "WHERE req_id='" + req_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }


        public void InsertQueue(string que_req_id,string que_date,string que_time,string que_alarm)
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO queue VALUES(N'" + que_req_id + "',N'" + que_date + "',N'" + que_time + "',N'" + que_alarm + "')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public DataSet SelectQueue3()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT que_req FROM queue";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "queue");
            objconn.Close();
            return objds;
        }
    }
}

