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

namespace ajans
{
    class report:connection
    {
        public DataSet SelectAllAmount()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT SUM(amount) as [موجودی] FROM amount";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "amount");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMaxPresent()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP 5 present.dvr_id AS [کد راننده], driver.dvr_name + ' ' + driver.dvr_fname AS [نام راننده], COUNT(present.prs_id) AS [تعداد حضور] FROM present INNER JOIN driver ON present.dvr_id = driver.dvr_id where driver.dvr_delete=0 GROUP BY present.dvr_id, driver.dvr_name, driver.dvr_fname ORDER BY [تعداد حضور] DESC ";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMinPresent()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP 5 present.dvr_id AS [کد راننده], driver.dvr_name + ' ' + driver.dvr_fname AS [نام راننده], COUNT(present.prs_id) AS [تعداد حضور] FROM present INNER JOIN driver ON present.dvr_id = driver.dvr_id where driver.dvr_delete=0 GROUP BY present.dvr_id, driver.dvr_name, driver.dvr_fname ORDER BY [تعداد حضور]";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectLastPresent(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT MAX(present.prs_id) AS [کد حضور], present.dvr_id AS [کد راننده], driver.dvr_name + ' ' + driver.dvr_fname AS [نام راننده], present.prs_date AS [تاریخ حضور], present.prs_time AS [ساعت حضور] FROM present INNER JOIN  driver ON present.dvr_id = driver.dvr_id WHERE (present.prs_time = (SELECT  MAX(prs_time) AS Expr1  FROM  present AS present_2  WHERE  (prs_date = (SELECT   MAX(prs_date) AS Expr1   FROM  present AS present_1 WHERE     (dvr_id = '" + dvrID + "' and dvr_delete=0))) AND (dvr_id = '" + dvrID + "' and dvr_delete=0) AND (dvr_id = '" + dvrID + "' and dvr_delete=0))) GROUP BY present.dvr_id, driver.dvr_name, driver.dvr_fname, present.prs_date, present.prs_time";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMaxRequest()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP (10) request.cus_id AS [کد اشتراک], customer.cus_name AS [نام مشترک], COUNT(request.req_id) AS [تعداد درخواست]FROM request INNER JOIN customer ON request.cus_id = customer.cus_id AND customer.cus_id <> '0' and customer.cus_delete=0 GROUP BY request.cus_id, customer.cus_name ORDER BY [تعداد درخواست] DESC";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMinRequest()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP (10) request.cus_id AS [کد اشتراک], customer.cus_name AS [نام مشترک], COUNT(request.req_id) AS [تعداد درخواست]FROM request INNER JOIN customer ON request.cus_id = customer.cus_id AND customer.cus_id <> '0' and customer.cus_delete=0 GROUP BY request.cus_id, customer.cus_name ORDER BY [تعداد درخواست]";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectCountRequest(string cusID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT request.cus_id AS [کد اشتراک], customer.cus_name AS [نام مشترک], COUNT(request.req_id) AS [تعداد درخواست]FROM request INNER JOIN customer ON request.cus_id = customer.cus_id AND customer.cus_id <> '0' WHERE(customer.cus_id = '" + cusID + "' and customer.cus_delete=0)GROUP BY request.cus_id, customer.cus_name ORDER BY [تعداد درخواست]";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMinService()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP (1) ser_date AS 'تاریخ', COUNT(ser_id) AS 'تعداد سرویس' FROM service GROUP BY ser_date";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMaxService()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP (1) ser_date AS تاریخ, COUNT(ser_id) AS [تعداد سرویس] FROM service GROUP BY ser_date ORDER BY تاریخ DESC";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public DataSet SelectAvrageService(string Date)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT (SELECT COUNT(ser_id) AS Expr1 FROM service WHERE (ser_date LIKE '" + Date + "%')) / 30.0 AS 'میانگین سرویس در ماه'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public DataSet SelectMaxStreet()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP (1) request.req_end_street AS مسیر, COUNT(request.req_end_street) AS 'تعداد' FROM service INNER JOIN request ON service.req_id = request.req_id GROUP BY request.req_end_street ORDER BY 'تعداد' DESC ";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public void DeleteAllInformation()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "DELETE FROM service";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM request";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM present";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM coffer";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM customer WHERE cus_id != 0";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM amount";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM driver";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM car WHERE car_id != 0";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM area WHERE area_id != 0";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM password";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM login";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM count";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM ajans";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "DELETE FROM queue";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();

        }
    }
}
