如何检查项目在特定日期范围内是否可用

Question

我正在使用SQL DB构建一个c#库存应用程序。使用这个应用程序，你可以选择一个项目预订，如果它是可用的，它将被预订。听起来很简单啊!我也是这么想的。问题来了。这个应用程序需要检查两个条件。

1)如果项目在特定日期范围内可用(例如:8月18日至8月23日)

2)是否有所需数量(如:2或3)。

我有一个保存项目名称和初始数量的数据库。另一个是持有当前的预订。它有以下数据:

ID   Cottage    quantity   From Date    To Date     Item name
 2   Woodcastle    2       2016-08-18   2016-08-24   Kayaks

现在，如果我选择1皮艇(初始数量3)预订2016-08-19至2016-08-23。我如何用SQL做到这一点?

这是我到目前为止所做的，但没有运气

selecteditem = items_listbox.SelectedItem.ToString();
       from_date = from_datepicker.Value.ToString();
       to_date = to_datepicker.Value.ToString();
        quantity = quantity_need.Text;
        from_date = Convert.ToDateTime(from_date).ToString("yyyy-MM-dd");
        to_date = Convert.ToDateTime(to_date).ToString("yyyy-MM-dd");
        int init_i = 0;
        int taken_i = 0;
        int dropped_i = 0;
        string select_init = "SELECT initial_quantity from inventory_items Where item_name LIKE '" + selecteditem + "'; "+ "SELECT COALESCE(SUM(Quantity), 0) from inventory_bookings Where item_name LIKE '" + selecteditem + "' AND '" + from_date + "' between date_from and date_to; "+ "SELECT COALESCE(SUM(Quantity), 0) from inventory_bookings Where item_name LIKE '" + selecteditem + "' AND '" + to_date + "' between date_from and date_to";
        conn = new MySqlConnection( connectionstring);
        MySqlCommand init = new MySqlCommand(select_init, conn);
        try
        {
            conn.Open();
            reader = init.ExecuteReader();
            while (reader.Read())
            {
                init_i = reader.GetInt32(0);
            }
            reader.NextResult();
            while (reader.Read())
            {
               taken_i = reader.GetInt32(0);
            }
            reader.NextResult();
            while (reader.Read())
            {
                dropped_i = reader.GetInt32(0);
            }
            int quantity_avail= init_i - taken_i - dropped_i;
            if (quantity_avail >= Convert.ToInt32(quantity_need.Text))
            {
                checkresult_lbl.Text= "Currently There are "+ quantity_avail + " "+ selecteditem +"/s available in your Inventory, Please Proceed to book";
                Booking_btn.Enabled = true;
            }
           else
            {
                checkresult_lbl.Text = "Currently There are not enough available Items in your Inventory, Please change date or Quantity";
            }
         }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            conn.Close();
        }

Answer 1

我可以用这个逻辑来解决这个问题。

Statement 1: "SELECT initial_quantity from inventory_items Where item_name LIKE '" + selecteditem + "'; "
Statement 2: "SELECT COALESCE(SUM(Quantity), 0) from inventory_bookings Where item_name LIKE '" + selecteditem + "' AND '" + from_date + "'<= date_to AND date_from < '"+to_date+"'"

语句#1选择项目的初始值数量。(假设是3).

语句#2选择在选定的天数(假设为2天)中有多少项未售出

然后检查(3-2)是否大于或等于所需数量，然后预订