I have three different tables:
1)Employee leaves
2)Breakfast order
3)Breakfast Cancel
Employee leave has columns: "Si_no, EmpCode, EmpName, Designation, Leave_Type, LFrom, LTo, Numbersof_leave, Reason, Status, comments"
Breakfast order has: "Emp_ID, Order_For_Date, Order_Day, Order_From, Dish, Price, Quantity, Company_Limit, Extra_Chgs, Total_Cost, Ordered_Date, Emp_Name"
Now,
when user submit leaves (half day(LFrom) / Full day(LFrom) / Casual leave(LFrom and LTo))
His/her breakfast order (Order_For_Date) should get cancelled for the leave applied dates(LFrom to LTo) and the data for the matched dates should get removed from Breakfast Order table and inserted into Breakfast cancel table.
Plz, help me...
What I have tried:
public void deletebreakfast()
{
DataTable Breakfast = new DataTable();
var date = DateTime.Now;
var nextSundayy = date.AddDays(7 - (int)date.DayOfWeek);
string today = DateTime.Now.ToString("yyyy-MM-dd");
string nextsunday = nextSundayy.ToString("yyyy-MM-dd");
cmd = new SqlCommand("select * from tb_Breakfast_data where Order_For_Date between '" + today.Trim() + "' and '" + nextsunday.Trim() + "' and Emp_ID = '" + Session["EmpCode"].ToString() + "'", Connectivity.cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(Breakfast);
if (dd2.SelectedItem.Text == "Half Day Leave" || dd2.SelectedItem.Text == "Full Day Leave")
{
foreach (DataRow dr in Breakfast.Rows)
{
if()
try
{
string Fdate = "", Todate = "", Empcode = "";
try
{
Fdate = dr["LFrom"].ToString();
}
catch { }
try
{
Todate = dr["LTo"].ToString();
}
catch { }
try
{
Empcode = dr["EmpCode"].ToString();
}
catch { }
string Query1 = "";
string Query2 = "";
if (Todate.Trim() == "")
{
Query1 = "select * from tb_Breakfast_data where Order_For_Date = '" + Fdate.Trim() + "' and Emp_ID='" + Empcode.Trim() + "'";
Query2 = "
}
else
{
DateTime startDate ;
DateTime endDate ;
string date1 = "";
{
for (DateTime dt = startDate; dt <= endDate; dt = dt.AddDays(1.0))
{
date1 = Convert.ToString(date);
allDateb.Add(date1);
}
return allDateb;