public IHttpActionResult AssignPassengers([FromBody] ListDetails details)
{
List <DateTime> dates = details.flights.Select(a => a.date).ToList();
List <string> nums = details.flights.Select(a => a.num).ToList();
Expression <Func <Client, bool> > whereSelectedFlights = a => dates.Contains(a.date_arr) && nums.Contains(a.num_arr);
if (details.direction == "out")
{
whereSelectedFlights = a => dates.Contains(a.date_dep) && nums.Contains(a.num_dep);
}
var soldActivies = from a in db.SoldActivities
join b in db.Sales on a.sale_fk equals b.ID
join c in db.Clients.Where(whereSelectedFlights) on new { b.PNR, b.agency_fk } equals new { c.PNR, c.agency_fk }
where a.activity_fk == details.activity_fk && a.canceled == false
select a;
foreach (SoldActivity se in soldActivies)
{
se.event_fk = details.event_fk;
}
db.SaveChanges();
EventsController eventCtrl = new EventsController();
return(Ok(eventCtrl.getEvents(details.date, details.activity_fk)));
}
public IHttpActionResult Post([FromBody] Hotel hotel)
{
try
{
hotel.ID = db.Hotels.OrderByDescending(a => a.ID).FirstOrDefault().ID + 1;
db.Hotels.Add(hotel);
db.SaveChanges();
return(Ok(hotel));
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
return(Content(HttpStatusCode.BadRequest, rootEx.Message));
}
}
public IHttpActionResult Post([FromBody] Flight flight)
{
try
{
flight.date_update = DateTime.Now;
db.Flights.Add(flight);
db.SaveChanges();
return(Ok(flight));
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
return(Content(HttpStatusCode.BadRequest, rootEx.Message));
}
}
public IHttpActionResult Post([FromBody] Guide guide)
{
try
{
guide.ID = db.Guides.OrderByDescending(a => a.ID).FirstOrDefault().ID + 1;
db.Guides.Add(guide);
db.SaveChanges();
return(Ok(guide));
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
return(Content(HttpStatusCode.InternalServerError, rootEx.Message));
}
}
public IHttpActionResult PostSale([FromBody] Sale sale)
{
if (sale.persons == 0)
{
throw new Exception("persons cannot be zero");
}
try
{
var existing_sale = (from a in db.Sales
where a.product_fk == sale.product_fk && a.PNR == sale.PNR
orderby a.date_update descending
select a).FirstOrDefault();
//It is possible to add more than one sale to each reservation (client) Unless it's 'other tour'
var product = db.Products.SingleOrDefault(a => a.ID == sale.product_fk);
if (existing_sale != null && existing_sale.canceled == false && (!(product.category == "tour" && product.subcat == "other")))
{
return(Content(HttpStatusCode.Conflict, "Sale already exists"));
}
else
{
sale.canceled = false;
sale.date_update = DateTime.Now;
sale.date_sale = DateTime.Today;
db.Sales.Add(sale);
AddSaleEvents(sale);
}
db.SaveChanges();
return(Ok("{}"));
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
return(Content(HttpStatusCode.BadRequest, rootEx.Message));
}
}
public IHttpActionResult CancelClient(int agency_fk, string PNR)
{
try
{
var client_to_cancel = db.Clients.SingleOrDefault(a => a.PNR == PNR && a.agency_fk == agency_fk && a.canceled == false);
client_to_cancel.canceled = true;
client_to_cancel.date_update = DateTime.Now;
IQueryable <Sale> sales_to_cancel = db.Sales.Where(x => x.PNR == PNR && x.agency_fk == agency_fk);
foreach (Sale sale in sales_to_cancel)
{
sale.canceled = true;
sale.date_update = DateTime.Now;
sale.comments = "***Reservation canceled*** " + sale.comments;
}
db.SaveChanges();
return(Ok("{}"));
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
return(Content(HttpStatusCode.BadRequest, rootEx.Message));
}
}
public string ExtractDataTabletFromCSVFileToDb(string csv_file_path)
{
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { ";" });
csvReader.HasFieldsEnclosedInQuotes = true;
using (HolaShalomDBEntities db = new HolaShalomDBEntities())
{
db.Database.ExecuteSqlCommand("DELETE FROM [Upload_temp]");
csvReader.ReadFields();
int count = 0;
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
Upload_temp rowUpload = new Upload_temp();
rowUpload.PNR = fieldData[0];
rowUpload.names = fieldData[1];
rowUpload.phone = fieldData[2];
rowUpload.date_arr = Convert.ToDateTime(fieldData[3]);
rowUpload.num_arr = fieldData[4];
rowUpload.PAX = int.Parse(fieldData[5]);
rowUpload.date_dep = Convert.ToDateTime(fieldData[6]);
rowUpload.num_dep = fieldData[7];
rowUpload.hotel_name = fieldData[8];
rowUpload.comments = fieldData[9];
db.Upload_temp.Add(rowUpload);
count += db.SaveChanges();
}
if (count > 50)
{
throw new Exception("CSV file limited to 50 records");
}
}
}
return(string.Empty);
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
string error = "There was a problem with the CSV file (internal error message: " + rootEx.Message + ")";
Logger.Write(error);
return(error);
}
}
//[ResponseType(typeof(List<SoldActivities>))]
public IHttpActionResult PutInEvent(DateTime date, int activity_fk, int event_fk)
{
try
{
var saleActivityForEvent = from a in db.SoldActivities
join b in db.Events on a.event_fk equals b.ID
where a.activity_fk == activity_fk && b.date == date
select a;
foreach (SoldActivity sale_activity in saleActivityForEvent)
{
sale_activity.event_fk = event_fk;
}
db.SaveChanges();
List <EventDTO> events = getEvents(date, activity_fk);
return(Ok(events));
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
return(Content(HttpStatusCode.InternalServerError, rootEx.Message));
}
}
public Result updateTables(string fieldsStr, int rowIndex, string agency_fk, string hotel_fk)
{
Result result = new Result();
Client client_new = new Client();
try
{
using (HolaShalomDBEntities db = new HolaShalomDBEntities())
{
client_new = getClientObj(fieldsStr);
if ((client_new.num_dep != "" && client_new.date_dep == null) || (client_new.num_dep == "" && client_new.date_dep != null))
{
result.status = "danger";
result.message = "It is impossible have a flight number without a flight date or vice versa. Please correct the csv file and try again.";
return(result);
}
client_new.hotel_fk = int.Parse(hotel_fk);
client_new.agency_fk = int.Parse(agency_fk);
client_new.date_update = DateTime.Now;
db.Clients.Add(client_new);
// Create sale object
Sale sale_new = new Sale();
sale_new.PNR = client_new.PNR;
sale_new.agency_fk = client_new.agency_fk;
sale_new.date_sale = DateTime.Today;
sale_new.date_update = DateTime.Now;
sale_new.canceled = false;
sale_new.persons = client_new.PAX;
sale_new.sale_type = "External";
sale_new.product_fk = 1;
sale_new.remained_pay = 0;
db.Sales.Add(sale_new);
var activities = from a in db.Activities
join b in db.Rel_product_activity on a.ID equals b.activity_fk
where b.product_fk == sale_new.product_fk
select a;
foreach (var activity in activities)
{
SoldActivity saleEvent = new SoldActivity();
saleEvent.Sale = sale_new;
saleEvent.PNR = sale_new.PNR;
saleEvent.agency_fk = sale_new.agency_fk;
saleEvent.date_update = sale_new.date_update;
saleEvent.canceled = sale_new.canceled;
saleEvent.activity_fk = activity.ID;
saleEvent.event_fk = 0;
db.SoldActivities.Add(saleEvent);
}
db.SaveChanges();
result.status = "success";
result.message = String.Format("[Reservation <b>'[{0}] {1}'</b> was successfully inserted into the database.", client_new.PNR, client_new.names);
}
}
catch (DbUpdateException ex)
{
SqlException ex_sql = ex.GetBaseException() as SqlException;
if (ex_sql != null)
{
switch (ex_sql.Number)
{
case 547:
result.status = "warning";
result.message = ex_sql.Message; // "One of the flights does not exist in the system. Please enter the flights and try again.";
return(result);
case 2627:
result.status = "info";
result.message = String.Format("Reservation <b>[{0}] {1}</b> already exists in the system. No need to insert it again.", client_new.PNR, client_new.names);
return(result);
default:
result.status = "danger";
result.message = "SQL Exception: " + ex_sql.Message;
return(result);
}
}
else
{
result.status = "danger";
result.message = "SQL Exception: " + ex_sql.Message;
}
}
catch (Exception ex)
{
result.status = "danger";
result.message = "General Exception: " + ex.Message;
}
return(result);
}
public IHttpActionResult PostBasicReservation([FromBody] ReservationDTO reservation)
{
if (reservation.PAX == 0)
{
throw new Exception("PAX cannot be zero");
}
Client client = new Client();
try
{
//Create client object
client.PNR = reservation.PNR;
client.agency_fk = reservation.agency_fk;
client.comments = reservation.comments;
client.date_arr = reservation.date_arr;
client.date_dep = reservation.date_dep;
client.hotel_fk = reservation.hotel_fk;
client.names = reservation.names;
client.num_arr = reservation.num_arr;
client.num_dep = reservation.num_dep;
client.PAX = reservation.PAX;
client.phone = reservation.phone;
client.date_update = DateTime.Now;
client.canceled = false;
db.Clients.Add(client);
//Create sale object:
//all agencies except for Hola Shalom (100) are External. External clients already paid to the agency so remained_pay will always be 0
string _sale_type = "Internal";
if (client.agency_fk != 100)
{
_sale_type = "External";
reservation.remained_pay = 0;
}
Sale sale = new Sale()
{
PNR = reservation.PNR,
agency_fk = reservation.agency_fk,
product_fk = reservation.product_fk,
remained_pay = reservation.remained_pay,
persons = reservation.PAX,
sale_type = _sale_type,
date_sale = DateTime.Today,
date_update = DateTime.Now,
canceled = false
};
db.Sales.Add(sale);
var activities = from a in db.Products
join b in db.Rel_product_activity on a.ID equals b.product_fk
join c in db.Activities on b.activity_fk equals c.ID
where a.ID == reservation.product_fk
select c;
foreach (var activity in activities)
{
SoldActivity sale_activity = new SoldActivity()
{
PNR = reservation.PNR,
agency_fk = reservation.agency_fk,
activity_fk = activity.ID,
Sale = sale,
event_fk = 0,
date_update = DateTime.Now,
canceled = false,
};
db.SoldActivities.Add(sale_activity);
}
;
db.SaveChanges();
return(Ok(client));
}
catch (Exception ex)
{
Exception rootEx = ex.GetBaseException();
SqlException sqleX = rootEx as SqlException;
if (sqleX != null)
{
if (sqleX.Number == 2627)
{
string message = string.Format("The PNR <b>{0} (agency ID:{1})</b> already exists in the system.", client.PNR, client.agency_fk);
return(Content(HttpStatusCode.Conflict, message));
}
}
return(Content(HttpStatusCode.InternalServerError, rootEx.Message));
}
}