using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace RezerveBeliteHavaPeimaCS
{
    public partial class frmRezerveSale : Form
    {
        public frmRezerveSale()
        {
            InitializeComponent();
        }

        private void grdFill()
        {
            FirstClass db = new FirstClass();
            dataGridView1.DataSource = db.dbOut(@"SELECT     RezerveTickets.RezerveID AS [کد بلیط], RezerveTickets.RezerveDate AS [تاریخ رزرو یا فروش], StaffsTable.NameSname AS فروشنده, 
                                                  Custumers.NameSname AS خریدار, ServicesDef.ServiceDescript AS مقصد, RezerveTickets.QuanTity AS تعداد, 
                                                    RezerveTickets.BianehPardakhti AS بیعانه, RezerveTickets.Mandeh AS مانده, RezerveTickets.Notes AS ملاحظات
                                                      FROM         RezerveTickets INNER JOIN  ServicesDef ON RezerveTickets.ServiceID = ServicesDef.ServiceID INNER JOIN
                                                                      Custumers ON RezerveTickets.RezerverID = Custumers.CustomerID INNER JOIN
                                                                      StaffsTable ON RezerveTickets.StaffID = StaffsTable.StaffID ORDER BY RezerveTickets.RezerveID DESC ");
            dropFill();
            textBox1.Text = db.dtShamsi();
        }

        private void dropFill()
        {
            FirstClass db1 = new FirstClass();
            FirstClass db2 = new FirstClass();
            FirstClass db3 = new FirstClass();

            comboBox1.DataSource = db1.dbOut("SELECT     TOP 100 PERCENT StaffID, NameSname FROM       StaffsTable ORDER BY StaffID");
            comboBox2.DataSource = db2.dbOut("SELECT     TOP 100 PERCENT CustomerID, NameSname FROM     Custumers ORDER BY CustomerID DESC");
            comboBox3.DataSource = db3.dbOut("SELECT     TOP 100 PERCENT ServiceID, ServiceDescript + N'  ' + ServiceDate AS Expr1, ExpireBit FROM   ServicesDef  WHERE     (ExpireBit = 0) ORDER BY ServiceID DESC");

            comboBox1.ValueMember = "StaffID";
            comboBox1.DisplayMember = "NameSname";

            comboBox2.ValueMember = "CustomerID";
            comboBox2.DisplayMember = "NameSname";

            comboBox3.ValueMember = "ServiceID";
            comboBox3.DisplayMember = "Expr1";


            FirstClass db4 = new FirstClass();
            DataTable dt4 = new DataTable();

            dt4 = db4.dbOut("SELECT ServiceID, Quantity FROM ServicesDef WHERE (ExpireBit = 0) AND (ServiceID = '" + comboBox3.SelectedValue + "')");
            label9.Text = "قابل رزرو / فروش = " + dt4.Rows[0][1].ToString();
        }

        protected void dbFill()
        {
            if (textBox1.Text == "" || textBox2.Text == "")
            {
                MessageBox.Show("ورود مقادیر فیلدهای تاریخ و تعداد اجباری است");
            }
            else
            {
                FirstClass db8 = new FirstClass();
                DataTable dt8 = new DataTable();
                db8.cmd.Parameters.Add("@ServiceID", SqlDbType.Int).Value = comboBox3.SelectedValue;

                dt8 = db8.dbOut(@"SELECT     DERIVEDTBL.ServiceID AS dd, DERIVEDTBL.Expr1 AS ForookhteShode, DERIVEDTBL_1.dd3, DERIVEDTBL_1.dd4 AS KolleTarif, 
                      DERIVEDTBL_1.dd4 - DERIVEDTBL.Expr1 AS Mandeh
                        FROM         (SELECT     ServiceID, SUM(QuanTity) AS Expr1
                        FROM         RezerveTickets
                        GROUP BY ServiceID
                        HAVING      (ServiceID = @ServiceID)) DERIVEDTBL CROSS JOIN
                          (SELECT     ServiceID AS dd3, Quantity AS dd4
                             FROM         (SELECT     ServiceID, Quantity
                                                     FROM         ServicesDef
                                                     WHERE     (ExpireBit = 0) AND (ServiceID = @ServiceID)) DERIVEDTBL) DERIVEDTBL_1");
                if (dt8.Rows.Count > 0)
                {
                    if (int.Parse(dt8.Rows[0][4].ToString()) < int.Parse(textBox2.Text.Trim()))
                    {
                        MessageBox.Show("میزان رزرو / فروش بیشتر از صندلی آزاد است");
                    }
                    else
                    {
                        txtNullAdd();
                        FirstClass db = new FirstClass();
                        db.cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = comboBox1.SelectedValue;
                        db.cmd.Parameters.Add("@RezerverID", SqlDbType.Int).Value = comboBox2.SelectedValue;
                        db.cmd.Parameters.Add("@ServiceID", SqlDbType.Int).Value = comboBox3.SelectedValue;
                        db.cmd.Parameters.Add("@RezerveDate", SqlDbType.NVarChar, 10).Value = textBox1.Text.Trim();
                        db.cmd.Parameters.Add("@QuanTity", SqlDbType.Int).Value = int.Parse(textBox2.Text.Trim());
                        db.cmd.Parameters.Add("@BianehPardakhti", SqlDbType.Float).Value = float.Parse(textBox3.Text.Trim());
                        db.cmd.Parameters.Add("@Mandeh", SqlDbType.Float).Value = float.Parse(textBox4.Text.Trim());
                        db.cmd.Parameters.Add("@Notes", SqlDbType.NVarChar, 500).Value = textBox5.Text.Trim();

                        db.exeCommand("sp_RezerveTickets_Insert");

                        exprPlanes(int.Parse(comboBox3.SelectedValue.ToString()), int.Parse(textBox2.Text.Trim()), int.Parse(dt8.Rows[0][4].ToString()));

                        txtClear();
                        grdFill();
                        MessageBox.Show("رکورد جدید درج شد");
                    }
                }
                else
                {
                    FirstClass db9 = new FirstClass();
                    DataTable dt9 = new DataTable();
                    db9.cmd.Parameters.Add("@ServiceID", SqlDbType.Int).Value = comboBox3.SelectedValue;
                    dt9 = db9.dbOut("SELECT ServiceID, Quantity FROM ServicesDef WHERE (ExpireBit = 0) AND (ServiceID = '" + comboBox3.SelectedValue + "')");

                    if (int.Parse(dt9.Rows[0][1].ToString()) < int.Parse(textBox2.Text.Trim()))
                    {
                        MessageBox.Show("میزان رزرو / فروش بیشتر از صندلی آزاد است");
                    }
                    else
                    {
                        txtNullAdd();
                        FirstClass db11 = new FirstClass();
                        db11.cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = comboBox1.SelectedValue;
                        db11.cmd.Parameters.Add("@RezerverID", SqlDbType.Int).Value = comboBox2.SelectedValue;
                        db11.cmd.Parameters.Add("@ServiceID", SqlDbType.Int).Value = comboBox3.SelectedValue;
                        db11.cmd.Parameters.Add("@RezerveDate", SqlDbType.NVarChar, 10).Value = textBox1.Text.Trim();
                        db11.cmd.Parameters.Add("@QuanTity", SqlDbType.Int).Value = int.Parse(textBox2.Text.Trim());
                        db11.cmd.Parameters.Add("@BianehPardakhti", SqlDbType.Float).Value = float.Parse(textBox3.Text.Trim());
                        db11.cmd.Parameters.Add("@Mandeh", SqlDbType.Float).Value = float.Parse(textBox4.Text.Trim());
                        db11.cmd.Parameters.Add("@Notes", SqlDbType.NVarChar, 500).Value = textBox5.Text.Trim();

                        db11.exeCommand("sp_RezerveTickets_Insert");

                        exprPlanes(int.Parse(comboBox3.SelectedValue.ToString()), int.Parse(textBox2.Text.Trim()), int.Parse(dt9.Rows[0][1].ToString()));

                        txtClear();
                        grdFill();
                        MessageBox.Show("رکورد جدید درج شد");
                    }
                }
            }
        }

        protected void exprPlanes(int SeviceID11, int QuanTity11 , int sUmOfTotal11)
        {
            if (QuanTity11 == sUmOfTotal11 )
            {
                FirstClass db = new FirstClass();
                db.cmd.Parameters.Add("@ServiceID", SqlDbType.Int).Value = SeviceID11;
                db.cmd.Parameters.Add("@ExpireBit", SqlDbType.Bit).Value = 1;

                db.exeCommand("sp_ServicesDef_Update");
                MessageBox.Show("ظرفیت تکمیل شد");
            }
        }

        protected void dbdel()
        {
            if (textBox6.Text != "")
            {
                FirstClass db = new FirstClass();
                db.cmd.Parameters.Add("@RezerveID", SqlDbType.Int).Value = int.Parse(textBox6.Text.Trim());
                db.exeCommand("sp_RezerveTickets_DeleteRow");
                grdFill();
                txtClear();
                MessageBox.Show("رکورد مورد نظر حذف شد");
            }
            else
            {
                MessageBox.Show("کد رزرو/فروش بلیط وارد نشده است");
            }

        }

        protected void txtClear()
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox6.Text = "";
        }

        protected void txtNullAdd()
        {
            if (textBox3.Text == "")
            {
                textBox3.Text = "0";
            }
            if (textBox4.Text == "")
            {
                textBox4.Text = "0";
            }
            if (textBox5.Text == "")
            {
                textBox5.Text = "Null";
            }
        }

        private void comboBox3_DropDownClosed(object sender, EventArgs e)
        {
            try
            {
                FirstClass db = new FirstClass();
                db.cmd.Parameters.Add("@ServiceID", SqlDbType.Int).Value = comboBox3.SelectedValue;
                DataTable dt = new DataTable();
                dt = db.dbOut(@"SELECT     DERIVEDTBL.ServiceID AS dd, DERIVEDTBL.Expr1 AS ForookhteShode, DERIVEDTBL_1.dd3, DERIVEDTBL_1.dd4 AS KolleTarif, 
                      DERIVEDTBL_1.dd4 - DERIVEDTBL.Expr1 AS Mandeh
                        FROM         (SELECT     ServiceID, SUM(QuanTity) AS Expr1
                        FROM         RezerveTickets
                        GROUP BY ServiceID
                        HAVING      (ServiceID = @ServiceID)) DERIVEDTBL CROSS JOIN
                          (SELECT     ServiceID AS dd3, Quantity AS dd4
                             FROM         (SELECT     ServiceID, Quantity
                                                     FROM         ServicesDef
                                                     WHERE     (ExpireBit = 0) AND (ServiceID = @ServiceID)) DERIVEDTBL) DERIVEDTBL_1");

                label9.Text = "قابل رزرو / فروش = " + dt.Rows[0][4].ToString();
            }
            catch
            {
                FirstClass db1 = new FirstClass();
                DataTable dt1 = new DataTable();
                db1.cmd.Parameters.Add("@ServiceID", SqlDbType.Int).Value = comboBox3.SelectedValue;
                dt1 = db1.dbOut("SELECT ServiceID, Quantity FROM ServicesDef WHERE (ExpireBit = 0) AND (ServiceID = @ServiceID)");
                label9.Text = "قابل رزرو / فروش = " + dt1.Rows[0][1].ToString();
            }
        }

        private void frmRezerveSale_Load(object sender, EventArgs e)
        {
            grdFill();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dbFill();
        }


        private void button2_Click(object sender, EventArgs e)
        {
            dbdel();
        }

    }
}