public ActionResult Update(tblHotelDetail E)//this action method is used to update the data in hoteldetail table
{
string m = Dbclass.GethotelData(E);
ViewBag.msg = m;
return(View("HotelUpdate"));
}
public ActionResult bookingupdate(int id) //booking update can be done here
{
tblBooking t = Dbclass.getbookingdetails(id); //which booking id we want to change that we are retrieving from database and populating in view
Session["opp"] = t; //here by using session variable we are storing the object of that bookingid which we want to edit
return(View(t));
}
public ActionResult booking1(tblBooking b1) //values of booking are bought through b1 object
{
if (ModelState.IsValid)
{
tblLogin z = (tblLogin)Session["customerid"];
string n = z.UserId;
if (n == b1.Customer_ID)
{
string s = Dbclass.Hotelbooking(b1); //validations and billamount is returned through this s
if (s.StartsWith("N") || s.StartsWith("a") || s.StartsWith("H")) //if validations are not satisfied it goes to booking view and generate which validation is missing
{
ViewBag.msg = s;
return(View("booking"));
}
else
{
ViewBag.msg = s;
Session["bill"] = s;
Session["var"] = b1; //here we are storing object of tblbooking by using sessions so that we can use this value after payment
slist = Dbclass.getstate(); //this slist consists values of bankdetails we will use it to populate dropdown values in payment view
ViewBag.bankid = slist;
return(View("payment"));
}
}
else
{
ViewBag.msg = "Please Enter Your CustomerId";
return(View("booking"));
}
}
return(View("booking"));
}
public ActionResult passQuery(DbContact contact)
{
//generate the query to check the user name or passwod
Dbclass instance = new Dbclass();
instance.Addcontact(contact.txtName, "ijok", "@as.com", "asas");
return(View("Cofirmation"));
}
public ActionResult Updatebook(tblBooking E)
{
tblBooking s = (tblBooking)Session["opp"]; // retrieving that object by session variable so we can retrieve booking id by this
string m = Dbclass.GetbookData(E, s); //here updation is done
ViewBag.msg = m;
return(View("bookingupdate"));
}
public ActionResult SearchHotel1()
{
if (Request.Form["txtSearchHName"] == null && Request.Form["txtSearchCity"] != null)
{
//string city = Request.Form["rdbCity"];
string TS = Request.Form["txtSearchCity"];
if (TS.Length == 0)
{
ViewBag.msg = "City Name not entered";
}
else
{
HD = Dbclass.GetCity(TS);
if (HD.Count == 0)
{
ViewBag.List = null;
ViewBag.msg = "no search found ";
}
else
{
ViewBag.List = HD;
}
//HD = Dbclass.GetHotelName(TS);
}
return(View("SearchHotelNew"));
}
else if (Request.Form["txtSearchHName"] != null && Request.Form["txtSearchCity"] == null)
{
//string HName = Request.Form["rdbHotelName"];
string TS = Request.Form["txtSearchHName"];
if (TS.Length == 0)
{
ViewBag.msg = "hotel Name not entered";
}
else
{
HD = Dbclass.GetHotelName(TS);
if (HD.Count == 0)
{
ViewBag.List = null;
ViewBag.msg = "no search found ";
}
else
{
ViewBag.List = HD;
}
}
return(View("SearchHotelNew"));
}
else if (Request.Form["txtSearchHName"] == null && Request.Form["txtSearchCity"] == null)
{
ViewBag.msg = "Invalid Credentials";
}
return(View("SearchHotelNew"));
}
public ActionResult Insert()//this action method displays the view of insert action method
{
CountryList = Dbclass.getCountry();
ViewBag.L = CountryList;
tblHotelDetail E = new tblHotelDetail();
return(View(E));
}
public ActionResult Insert(tblHotelDetail E)// this action method is used to insert the data into hoteldetail table
{
if (ModelState.IsValid)
{
CountryList = Dbclass.getCountry();
ViewBag.L = CountryList;
ViewBag.msg = Dbclass.Inserthotel(E);
return(View("Insert"));
}
CountryList = Dbclass.getCountry();
ViewBag.L = CountryList;
return(View());
}
public ActionResult verifyLogin(DbLogin Login_Block)
{
//generate the query to check the user name or passwod
Dbclass cls = new Dbclass();
if (cls.CheckLogin(Login_Block.Usertxt, Login_Block.Passwordtxt) == 1)
{
return(View("AdminZone"));
}
else
{
return(View("Invalid"));
}
}
public ActionResult CustomerLogin(Customer C1)//this action method redirects us to customer registration page where we can give details and register as a member
{
if (ModelState.IsValid)
{
C1.Customer_ID = Dbclass.getCustomerId(C1.CustomerName);
C1.CustomerType = Dbclass.getCustomerType(C1.DateOfBirth);
ViewBag.msg = Dbclass.UserRegistration(C1);
return(View());
}
else
{
return(View("CustomerLogin"));
}
}
public ActionResult GetCity(string Country)
{
if (ModelState.IsValid)
{
// CountryList = Dbclass.getCountry();
ViewBag.L = CountryList;
CityList = Dbclass.getCity(Country);
if (CityList != null)
{
ViewBag.city = new SelectList(CityList, "CITY_NAME", "CITY_NAME");
return(PartialView("DisplayCities"));
}
}
return(View("Insert"));
}
public ActionResult gettingbookingdetails() //By this we will get all the booking details that we have inserted so far
{
tblLogin c = (tblLogin)Session["customerid"];
string s = c.UserId;
P = Dbclass.gettingallbooking(s); //here all booking values are brought through this gettingallbooking method
if (P.Count == 0)
{
ViewBag.List = null;
ViewBag.msg = "No Booking are there ";
}
else
{
ViewBag.List = P;
}
return(View());
//return View();
}
public ActionResult Login(tblLogin t)//this method checks the credentials of users and validates them
{
if (ModelState.IsValid)
{
string user = Request.Form["UserId"];
string password = Request.Form["Password"];
tblLogin t1 = Dbclass.loginn(user, password);
if (t1 != null)
{
Session["user"] = t1.UserId; //sessionvariables are used to remove that particular session when userlogouts
Session["UserType"] = t1.UserType;
if (t1.UserType == "Admin")
{
return(RedirectToAction("Login1"));
}
else if (t1.UserType == "Customer")
{
Session["customerid"] = t1;
Session["id"] = t1.UserId;
return(RedirectToAction("SearchHotelNew"));
}
else
{
return(RedirectToAction("Login"));
}
}
else
{
ViewBag.msg = "Invalid credentials";
}
return(View("Login"));
}
return(View("Login"));
}
public ActionResult payment1(tblPayment p)
{
if (ModelState.IsValid) //if validations are satisfied then it goes into model
{
tblBooking z = (tblBooking)Session["var"]; //here we are retrieving tblbooking object values that are stored in session variable
bool s = Dbclass.getpayment(p);
if (s == true) //if payment is done successfully then it goes to insertion of booking
{
string x = Dbclass.getbooking(z); //for inserting into booking
ViewBag.g = x;
}
return(View());
}
else
{
ViewBag.msg = Session["bill"].ToString();
slist = Dbclass.getstate();
ViewBag.bankid = slist;
return(View("payment")); //it will show where the validation is missing
}
}
static void Main(string[] args)
{
/*
* 1. 更變訂單狀態 私廚確認_開放客戶評價 -> 客戶確認_完成評價
* 2. 變更 [私廚] 的評級 : 由 [訂單] 平均算出
* 3. 依[訂單][數量] * [v評價VM][價格] 加入至私廚的 [會員][點數]
*/
Dbclass db = new Dbclass(args[0]);
var fOID_list = new List <int>();
var fPID_list = new List <int>();
var fCID_list = new List <int>();
var sql_order = @"
SELECT o.fOID [fOID], o.fPID [fPID], c.fCID [fCID]
FROM
(SELECT fOID , fPID
FROM [t訂單] o
WHERE o.f狀態=2 AND
DATEADD(day,5,
(SELECT TOP 1 value FROM STRING_SPLIT(o.f預定日期,' '))
) < GETDATE()
) AS o
JOIN [t販售項目] p ON o.fPID=p.fPID
JOIN [t私廚] c ON p.fCID=c.fCID
";
db.SQLReader(sql_order, new SqlParameter[] { }, (reader) =>
{
while (reader.Read())
{
fOID_list.Add((int)reader["fOID"]);
fPID_list.Add((int)reader["fPID"]);
fCID_list.Add((int)reader["fCID"]);
}
});
for (int i = 0; i < fOID_list.Count(); i++)
{
// 1. 更變訂單狀態 私廚確認_開放客戶評價 -> 客戶確認_完成評價
string sql_up_order = @"
UPDATE o
SET o.f評價日期 = @f評價日期,
o.f狀態=3,
o.f評級=5,
o.f評價內容=N'5天未評價，系統自動評價'
FROM [t訂單] o
WHERE o.fOID=@fOID
";
db.SQLExecute(sql_up_order, new SqlParameter[] {
new SqlParameter("@fOID", fOID_list[i]),
new SqlParameter("@f評價日期", DateTime.Now.ToString("g"))
});
// 2. 變更 [私廚] 的評級 : 由 [訂單] 平均算出
string sql_up_avg = @"
UPDATE c
SET c.f私廚評級 = chef_Avg.平均評級,
c.f私廚評級筆數 = chef_Avg.總筆數
FROM
(SELECT
CAST(ROUND(AVG(CAST(o.f評級 AS decimal)),0) AS int) [平均評級],
COUNT(*) [總筆數], c.fCID [fCID]
FROM [t私廚] c
JOIN [t販售項目] p ON c.fCID = p.fCID
JOIN [t訂單] o ON o.fPID = p.fPID
WHERE c.fCID = @fCID and o.f狀態 = 3
GROUP BY c.fCID) AS chef_Avg
JOIN [t私廚] c ON chef_Avg.fCID=c.fCID
WHERE c.fCID = @fCID
";
db.SQLExecute(sql_up_avg, new SqlParameter[] { new SqlParameter("@fCID", fCID_list[i]) });
// 3. 依[訂單][數量] * [v評價VM][價格] 加入至私廚的 [會員][點數]
string sql_up_point = @"
UPDATE u
SET u.f點數= u.f點數 - CAST(ROUND(0.9*o.f數量*p.f價格, 0) AS int)
FROM
(SELECT *
FROM [t訂單] o
WHERE o.fPID=@fPID
) AS o
JOIN [t販售項目] p ON o.fPID=p.fPID
JOIN [t私廚] c ON p.fCID=c.fCID
JOIN [t會員] u ON c.fUID=u.fUID
";
db.SQLExecute(sql_up_point, new SqlParameter[] { new SqlParameter("@fPID", fPID_list[i]) });
}
}
public ActionResult Login1()//this action method displays the view to the admin
{
l = Dbclass.gethotels1();
return(View(l));
}
public ActionResult HotelUpdate(string id)//this action method is used to extract the data from hoteldetail table
{
tblHotelDetail t = Dbclass.gethotels(id);
return(View(t));
}
public ActionResult Delete(string id)//this action method is used to delete the data from hoteldetail table
{
ViewBag.msg = Dbclass.Delhotel(id);
return(View());
}
public ActionResult Deletebooking(int id) //here we will do booking values deleted based on some validations
{
ViewBag.msg = Dbclass.Delbooking(id);
return(View());
}
