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

namespace ajans
{
    class service: request
    {
        protected string ser_id;
        protected string prs_id;
        protected string amount;
        protected string ser_date;
        protected string ser_time;
        protected string ser_value;

        public service()
        {
            ser_id = "";
            prs_id = "";
            amount = "";
            ser_date = "";
            ser_time = "";
            ser_value = "";
        }

        public void SetSerID(string serID)
        {
            ser_id = serID;
        }
        public void SetPrsID(string prsID)
        {
            prs_id = prsID;
        }
        public void SetAmount(string Amount)
        {
            amount = Amount;
        }
        public void SetDate(string Date)
        {
            ser_date = Date;
        }
        public void SetTime(string Time)
        {
            ser_time = Time;
        }
        public void SetValue(string Value)
        {
            ser_value = Value;
        }

        public void InsertService()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO service VALUES(N'" + ser_id + "',N'" + req_id + "',N'" + prs_id + "',N'" + amount + "',N'" + ser_date + "',N'" + ser_time + "',N'" + ser_value + "')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE driver SET dvr_amount = dvr_amount + '" + amount + "' WHERE(driver.dvr_id = (SELECT driver.dvr_id FROM driver INNER JOIN  present ON driver.dvr_id = present.dvr_id WHERE(present.prs_id = '" + prs_id + "')))";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE present SET prs_status= '1' WHERE prs_id='" + prs_id + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE customer SET cus_amount = cus_amount + '" + amount + "' WHERE(customer.cus_id = (SELECT customer.cus_id FROM customer INNER JOIN  request ON customer.cus_id = request.cus_id WHERE(customer.cus_id <> '0') AND (request.req_id = '" + req_id + "')))";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE request SET req_ser_status=req_ser_status - " + 1 + "WHERE req_id='" + req_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 SelectService()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select ser_id as [کد سرویس] , req_id as [کد درخواست] ,prs_id as [کد حضور], ser_amount as [مبلغ] from service";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }


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


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

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

        public DataSet SelectPresentService()
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT COUNT(service.prs_id) AS [وضعیت] FROM service INNER JOIN present ON service.prs_id = present.prs_id WHERE (present.prs_status > '0')";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

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

        public DataSet SelectDriverAmount(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT driver.dvr_id AS [کد راننده], driver.dvr_name + ' ' + driver.dvr_fname AS [نام راننده], service.ser_date AS [تاریخ سرویس], service.ser_time AS [ساعت سرویس], service.ser_id AS [کد سرویس], service.ser_amount AS [مبلغ] FROM service INNER JOIN present ON service.prs_id = present.prs_id INNER JOIN driver ON present.dvr_id = driver.dvr_id WHERE (driver.dvr_id = '" + dvrID + "')";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverAmount2(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select dvr_amount as [حساب راننده] from driver where (driver.dvr_id = '" + dvrID + "')";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "driver");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverAmount(string dvrID, string Date)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT driver.dvr_id as [کد راننده], COUNT(service.ser_id) AS [تعداد سرویس],  SUM(service.ser_amount) AS [مجموع کارکرد] FROM service INNER JOIN present ON service.prs_id = present.prs_id INNER JOIN driver ON present.dvr_id = driver.dvr_id WHERE (driver.dvr_id = '" + dvrID + "') AND (service.ser_date LIKE '" + Date + "%') GROUP BY driver.dvr_id";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverAmount3(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT driver.dvr_id AS [کد راننده], driver.dvr_name + ' ' + driver.dvr_fname AS [نام راننده], service.ser_date AS [تاریخ سرویس], service.ser_time AS [ساعت سرویس], service.ser_id AS [کد سرویس], service.ser_amount AS [مبلغ] FROM service INNER JOIN present ON service.prs_id = present.prs_id INNER JOIN driver ON present.dvr_id = driver.dvr_id WHERE (driver.dvr_id = '" + dvrID + "') AND (service.ser_value='0')";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverAmount(string dvrID, string Date1,string Date2)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT driver.dvr_id as [کد راننده], COUNT(service.ser_id) AS [تعداد سرویس],  SUM(service.ser_amount) AS [مجموع کارکرد] FROM service INNER JOIN present ON service.prs_id = present.prs_id INNER JOIN driver ON present.dvr_id = driver.dvr_id WHERE (driver.dvr_id = '" + dvrID + "') AND (service.ser_date BETWEEN '" + Date1 + "' AND '" + Date2 + "') GROUP BY driver.dvr_id";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }

        public DataSet SelectDriverAmount4(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT TOP 10 driver.dvr_id AS [کد راننده], driver.dvr_name + ' ' + driver.dvr_fname AS [نام راننده], service.ser_date AS [تاریخ سرویس], service.ser_time AS [ساعت سرویس], service.ser_id AS [کد سرویس], service.ser_amount AS [مبلغ] FROM service INNER JOIN present ON service.prs_id = present.prs_id INNER JOIN driver ON present.dvr_id = driver.dvr_id WHERE (driver.dvr_id = '" + dvrID + "') AND (service.ser_value='0')";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();
            return objds;
        }


        public DataSet SelectServiceReport2(string serID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "select * from service where ser_id='" + serID + "'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "service");
            objconn.Close();

            objda.SelectCommand.CommandText = "select * from request inner join service on request.req_id=service.req_id where ser_id='" + serID + "'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "request");
            objconn.Close();

            objda.SelectCommand.CommandText = "select * from present inner join service on present.prs_id=service.prs_id  where ser_id='" + serID + "'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "present");
            objconn.Close();
            return objds;
        }

        public DataTable SelectServiceReport(string serID)
        {
            DataTable dt = new DataTable();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            string sql = @"select * from service
                         inner join present on service.prs_id=present.prs_id
                         inner join request on service.req_id=request.req_id
                         where ser_id='" + serID + "'";
            objda.SelectCommand.CommandText = sql;
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(dt);
            objconn.Close();
            return dt;
        }

        public void UpdateServiceValue(string serID)
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE service SET ser_value='1' WHERE ser_id='" + serID + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void InsertAmount(string dvrID,string Date,string Time,string Amount)
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO amount VALUES(N'" + dvrID + "',N'" + Date + "',N'" + Time + "',N'" + Amount + "')";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
            objcomm.CommandText = "UPDATE coffer SET stock= stock + '" + Amount + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }

        public void InserCoffer()
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "INSERT INTO coffer VALUES(0)";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }
    }
}
