public static void GetApptList(ApptList Appt)
{
string mQuery = string.Format(@"SELECT ID AS NO,PET_ID AS PET_ID,(select name from PET where id = VA.PET_ID) as PET,(SELECT FIRST_NAME || ' ' || LAST_NAME FROM CUSTOMER WHERE ID = (SELECT OWNER_ID FROM PET WHERE PET_ID = PET.ID)) AS CUSTOMER,TO_CHAR(DATE_TIME,'YYYY/MON/DD,HH:MI') as APPOINTMENT
FROM VET_APPOINTMENT VA
WHERE PET_ID IN(select PET.ID from PET join customer on PET.OWNER_ID= customer.ID where
owner_id in (select id from customer where lower(first_name|| last_name) LIKE lower('%{0}%')))", Appt.CusName);
cmdString = new OracleCommand(mQuery, cntString);
try
{
cntString.Open();
OracleDataAdapter da = new OracleDataAdapter(cmdString);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
da.Fill(dt);
ds.Tables.Add(dt);
Appt.ApptTable = ds.Tables[0];
}
finally
{
cntString.Close();
}
}
public static void ChangeAppointment(ApptList Appt)
{
string oQuery = string.Format(@"UPDATE vet_appointment SET DATE_TIME=TO_DATE('{0}','YYYYMONDDHH24:MI') where id='{1}'", Appt.ApptDateTime, Appt.ApptID);
cmdString = new OracleCommand(oQuery, cntString);
try
{
cntString.Open();
cmdString.ExecuteNonQuery();
}
finally
{
cntString.Close();
}
}
public static void CancelAppointment(ApptList Appt)
{
string nQuery = string.Format(@"DELETE FROM vet_appointment where id='{0}'", Appt.ApptID);
cmdString = new OracleCommand(nQuery, cntString);
try
{
cntString.Open();
cmdString.ExecuteNonQuery();
}
finally
{
cntString.Close();
}
}
protected void CancelAppt(object sender, EventArgs e)
{
try
{
Button btn = (Button)(sender);
ApptID = btn.CommandArgument;
// test CommandArgument
ApptList appt = new ApptList(ApptID, PetID, PetName, CusName, ApptDateTime, ApptTable);
ConnectionClass.CancelAppointment(appt);
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert(' Cancelled Successfully! ');</script>");
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert(' Failed! ');</script>");
}
}
public void ApptListView()
{
CusName = cusNameInput.Text;
ApptList appt = new ApptList(ApptID, PetID, PetName, CusName, ApptDateTime, ApptTable);
try
{
ConnectionClass.GetApptList(appt);
ApptListTable.DataSource = appt.ApptTable;
ApptListTable.DataBind();
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert(' Can't find any appointment for the customer! ');</script>");
}
finally
{
}
}
protected void ConfirmChange_Click(object sender, EventArgs e)
{
ApptID = ApptNo.Text;
ApptDateTime = year.SelectedItem.ToString() + month.SelectedItem.ToString() + day.SelectedItem.ToString() + time.SelectedItem.ToString();
ApptList cappt = new ApptList(ApptID, PetID, PetName, CusName, ApptDateTime, ApptTable);
try
{
ConnectionClass.ChangeAppointment(cappt);
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert(' Appointment Changed Successfully! ');</script>");
}
catch
{
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert(' Please check required fields again.');</script>");
}
finally
{
check.Text = year.SelectedItem.ToString() + month.SelectedItem.ToString() + day.SelectedItem.ToString() + time.SelectedItem.ToString();
}
}