public object getdetail(int id)
{
var transection = new dto_transection();
transection = DatabaseContext.Database.Query <dto_transection>(string.Format("select top(1) * from tb_transection where ID = {0};", id)).FirstOrDefault();
var customer = new tb_customer();
customer = DatabaseContext.Database.Query <tb_customer>(string.Format("select top(1) * from tb_customer where User_ID = {0};", transection.Customer_ID)).FirstOrDefault();
var car = new tb_car();
car = DatabaseContext.Database.Query <tb_car>(
string.Format(@"SELECT top(1) t1.A_ID,Branch_ID,Car_ID,t2.*
From tb_branch_car t1
INNER join tb_car t2 on t1.Car_ID = t2.id
WHERE t1.A_ID = {0};", transection.Car_Branch_ID)).FirstOrDefault();
var optional = new List <tb_optional>();
optional = DatabaseContext.Database.Fetch <tb_optional>(
string.Format(@"select [Item_Type]
,[Item_Price]
,[Sub_Total]
,[Vat_Total]
,[Total]
,t2.*
FROM tb_transection_detail t1
INNER JOIN tb_optional t2 on t1.Item_ID = t2.A_ID
WHERE t1.Transection = {0};", transection.ID)).ToList();
var pickuploc = new tb_branch();
pickuploc = DatabaseContext.Database.Fetch <tb_branch>(
string.Format(@"select top(1) *
FROM tb_branch
WHERE A_ID = {0};", transection.PickUpLoc)).FirstOrDefault();
var dropoffloc = new tb_branch();
dropoffloc = DatabaseContext.Database.Fetch <tb_branch>(
string.Format(@"select top(1) *
FROM tb_branch
WHERE A_ID = {0};", transection.DropOffLoc)).FirstOrDefault();
var price = new tb_car_price();
price = DatabaseContext.Database.Fetch <tb_car_price>(
string.Format(@"select top(1) *
FROM tb_car_price
WHERE price_id = {0};", transection.Price_ID)).FirstOrDefault();
transection.car = car;
transection.customer = customer;
transection.optional = optional;
transection.pickuplocation = pickuploc;
transection.dropofflocation = dropoffloc;
transection.car_price = price;
return(transection);
}
public int create([FromBody] tb_car form)
{
var query = $@"
INSERT INTO [dbo].[tb_car]
([image]
,[brand]
,[name]
,[vgroup]
,[sipp]
,[feature]
,[tranmission]
,[year]
,[enging]
,[door]
,[passenger]
,[luggage]
,[music_system]
,[status])
VALUES
(@image
,@brand
,@name
,@vgroup
,@sipp
,@feature
,@tranmission
,@year
,@enging
,@door
,@passenger
,@luggage
,@music_system
,@status)";
return(DatabaseContext.Database.Execute(query, form));
}
public int edit([FromBody] tb_car form)
{
var query = $@"
UPDATE [dbo].[tb_car]
SET [image] = @image
,[brand] = @brand
,[name] = @name
,[vgroup] = @vgroup
,[sipp] = @sipp
,[feature] = @feature
,[tranmission] = @tranmission
,[year] = @year
,[enging] = @enging
,[door] = @door
,[passenger] = @passenger
,[luggage] = @luggage
,[music_system] = @music_system
,[status] = @status
,[update_Date] = Getdate()
WHERE id = @id
";
return(DatabaseContext.Database.Execute(query, form));
}
[HttpGet] //payment status
public ActionResult Result(RenderModel model, string id)
{
var sendmail = Request["sendmail"];
var testmail = Request["testmail"];
var rto = new Step4Model(CurrentPage);
rto.Transection_ID = Convert.ToInt32(id);
var transection = new dto_transection();
transection = DatabaseContext.Database.Query <dto_transection>(string.Format("select top(1) * from tb_transection where ID = {0};", rto.Transection_ID)).FirstOrDefault();
var customer = new tb_customer();
customer = DatabaseContext.Database.Query <tb_customer>(string.Format("select top(1) * from tb_customer where User_ID = {0};", transection.Customer_ID)).FirstOrDefault();
var car = new tb_car();
car = DatabaseContext.Database.Query <tb_car>(
string.Format(@"SELECT top(1) t1.A_ID,Branch_ID,Car_ID,t2.*
From tb_branch_car t1
INNER join tb_car t2 on t1.Car_ID = t2.id
WHERE t1.A_ID = {0};", transection.Car_Branch_ID)).FirstOrDefault();
var optional = new List <tb_optional>();
optional = DatabaseContext.Database.Fetch <tb_optional>(
string.Format(@"select [Item_Type]
,[Item_Price]
,[Sub_Total]
,[Vat_Total]
,[Total]
,t2.*
FROM tb_transection_detail t1
INNER JOIN tb_optional t2 on t1.Item_ID = t2.A_ID
WHERE t1.Transection = {0};", transection.ID)).ToList();
var pickuploc = new tb_branch();
pickuploc = DatabaseContext.Database.Fetch <tb_branch>(
string.Format(@"select top(1) *
FROM tb_branch
WHERE A_ID = {0};", transection.PickUpLoc)).FirstOrDefault();
var dropoffloc = new tb_branch();
dropoffloc = DatabaseContext.Database.Fetch <tb_branch>(
string.Format(@"select top(1) *
FROM tb_branch
WHERE A_ID = {0};", transection.DropOffLoc)).FirstOrDefault();
var price = new tb_car_price();
price = DatabaseContext.Database.Fetch <tb_car_price>(
string.Format(@"select top(1) *
FROM tb_car_price
WHERE price_id = {0};", transection.Price_ID)).FirstOrDefault();
transection.car = car;
transection.customer = customer;
transection.optional = optional;
transection.pickuplocation = pickuploc;
transection.dropofflocation = dropoffloc;
transection.car_price = price;
var textDetail = model.Content.GetProperty("textContent").Value.ToString();
textDetail = textDetail.Replace("{name}", customer.Name + " " + customer.LName);
textDetail = textDetail.Replace("{email}", customer.Email);
textDetail = textDetail.Replace("{phone}", customer.Phone);
textDetail = textDetail.Replace("{pricecode}", price.price_code);
textDetail = textDetail.Replace("{adddate}", transection.Add_Date.ToString("yyyy-MM-dd hh:mm tt"));
textDetail = textDetail.Replace("{pickuplocation}", pickuploc.Branch_Name);
var status = transection.Status == "A" ? "Comfirm" : "Pending";
textDetail = textDetail.Replace("{status}", status);
var paytype = transection.Payment_Type == "N" ? "Online" : "At counter";
textDetail = textDetail.Replace("{paytype}", paytype);
textDetail = textDetail.Replace("{transectionno}", "#" + transection.Payment_Type + "" + transection.Transection_ID);
textDetail = textDetail.Replace("{pickupdate}", transection.PickUpDatetime?.ToString("yyyy-MM-dd hh:mm tt"));
textDetail = textDetail.Replace("{dropofftime}", transection.DropOffDatetime?.ToString("yyyy-MM-dd hh:mm tt"));
textDetail = textDetail.Replace("{pickuplocation}", pickuploc.Branch_Name);
textDetail = textDetail.Replace("{droplocation}", dropoffloc.Branch_Name);
textDetail = textDetail.Replace("{discount}", transection.Discount_Total?.ToString("N2"));
textDetail = textDetail.Replace("{subtotal}", transection.Sub_Total?.ToString("N2"));
textDetail = textDetail.Replace("{vat}", transection.Vat_Total?.ToString("N2"));
textDetail = textDetail.Replace("{total}", transection.Total_Price?.ToString("N2"));
var totaltext = "";
var carprice = price.price;
var itemcar = string.Format(@"<tr>
<td>{0}</td>
<td>{1}</td>
<td>{2}</td>
<td>{3}</td>
</tr>"
, car.name
, carprice?.ToString("N2")
, transection.Numdate
, (carprice * transection.Numdate)?.ToString("N2"));
var itemoption = "";
foreach (var op in optional)
{
var opprice = op.Price;
itemoption += string.Format(@"<tr>
<td>{0}</td>
<td>{1}</td>
<td>{2}</td>
<td>{3}</td>
</tr>"
, op.Name
, opprice.ToString("N2")
, transection.Numdate
, (opprice * transection.Numdate)?.ToString("N2"));
}
var itemdetail = @"<table style='width:100%' border='0'>
<tr>
<td width='50%'><b>Description</b></td>
<td><b>Rate</b></td>
<td><b>Day(s)</b></td>
<td><b>Total</b></td>
</tr>
" + itemcar + @"
" + itemoption + @"
" + totaltext + @"
</table>";
textDetail = textDetail.Replace("{detail}", itemdetail);
ViewBag.text = textDetail;
if (sendmail == "1")
{
SendEmail(customer.Email, "Your reservation details.", textDetail);
}
if (testmail == "1")
{
return(CurrentTemplate(rto));
}
return(Redirect("/booking/step4/" + rto.Transection_ID));
}