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

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

        public DataSet SelectpersonAmount(string dvrID)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT amount.dvr_id AS [کد راننده], driver.dvr_name + ' ' + driver.dvr_fname AS [نام راننده], amount.date AS [تاریخ پرداخت], amount.time AS [ساعت پرداخت], SUM(amount.amount) AS واریزی FROM amount INNER JOIN driver ON amount.dvr_id = driver.dvr_id WHERE (amount.dvr_id = '" + dvrID + "') GROUP BY amount.dvr_id, driver.dvr_name, driver.dvr_fname, amount.date, amount.time";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "amount");
            objconn.Close();
            return objds;
        }

        public DataSet SelectAmount(string Date)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT SUM(amount) AS [مجموع واریزی] FROM amount WHERE date LIKE '" + Date + "%'";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "amount");
            objconn.Close();
            return objds;
        }

        public DataSet SelectAmount(string date1,string date2)
        {
            objds.Clear();
            objda.SelectCommand = new SqlCommand();
            objda.SelectCommand.Connection = objconn;
            objda.SelectCommand.CommandText = "SELECT SUM(amount) AS [مجموع واریزی] FROM amount WHERE (date < '" + date1 + "') AND ('" + date2 + "' < date)";
            objda.SelectCommand.CommandType = CommandType.Text;
            objconn.Open();
            objda.Fill(objds, "amount");
            objconn.Close();
            return objds;
        }

        public void UpdateCofferStock(string Amount)
        {
            objcomm.Connection = objconn;
            objcomm.CommandText = "UPDATE coffer SET stock= stock - '" + Amount + "'";
            objconn.Open();
            objcomm.ExecuteNonQuery();
            objconn.Close();
        }
    }
}
