private static bool IsFlightAvailable(BAPISCODAT item, string classType)
{
bool isAvailable = true;
#region check availability
if (!_flightConnection.GetDetail(
item.FLIGHTCONN,
item.FLIGHTDATE,
"",
item.AGENCYNUM))
{
return(false);
}
if (_flightConnection._bapiAvailability.Length > 0)
{
for (int counter = 0; counter < _flightConnection._bapiAvailability.Length; counter++)
{
switch (classType)
{
case "Y":
if (_flightConnection._bapiAvailability[counter].ECONOFREE == 0)
{
isAvailable = false;
}
break;
case "C":
if (_flightConnection._bapiAvailability[counter].BUSINFREE == 0)
{
isAvailable = false;
}
break;
case "F":
if (_flightConnection._bapiAvailability[counter].FIRSTFREE == 0)
{
isAvailable = false;
}
break;
}
}
}
#endregion
return(isAvailable);
}
public void AddListItemMethod(BAPISCODAT item)
{
DateTime flightDate = FlightDate(item.FLIGHTDATE);
if (flightDate.AddMonths(3) < _eventDate)
{
return;
}
else if (flightDate > _eventDate)
{
return;
}
else if (flightDate < DateTime.Now)
{
return;
}
else if (flightDate < _prevEventDate)
{
return;
}
//check availability first
SAPFlightConnection flightConn = new SAPFlightConnection(Config.SAPUserName, Config.SAPPassword);
if (!flightConn.GetDetail(
item.FLIGHTCONN,
item.FLIGHTDATE,
"",
item.AGENCYNUM))
{
return;
}
if (flightConn._bapiAvailability.Length > 0)
{
for (int counter = 0; counter < flightConn._bapiAvailability.Length; counter++)
{
switch (_packageType)
{
case "Y":
if (flightConn._bapiAvailability[counter].ECONOFREE == 0)
{
return;
}
break;
case "C":
if (flightConn._bapiAvailability[counter].BUSINFREE == 0)
{
return;
}
break;
case "F":
if (flightConn._bapiAvailability[counter].FIRSTFREE == 0)
{
return;
}
break;
}
}
}
else
{
return;
}
ListViewItem itemCreate = listViewFlight.Items.Add(item.FLIGHTDATE);
itemCreate.SubItems.Add(item.DEPTIME);
itemCreate.SubItems.Add(item.AIRPORTFR);
itemCreate.SubItems.Add(item.ARRDATE);
itemCreate.SubItems.Add(item.AIRPORTTO);
itemCreate.SubItems.Add(item.ARRTIME);
itemCreate.SubItems.Add(item.CITYFROM);
itemCreate.SubItems.Add(item.CITYTO);
FlightConnectionList itemList = new FlightConnectionList();
itemList.AgencyNumber = item.AGENCYNUM;
itemList.AirportFrom = item.AIRPORTFR;
itemList.AirportTo = item.AIRPORTTO;
itemList.ArrivalDate = item.ARRDATE;
itemList.ArrivalTime = item.ARRTIME;
itemList.CityFrom = item.CITYFROM;
itemList.CityTo = item.CITYTO;
itemList.DepartureTime = item.DEPTIME;
itemList.FlightConnectionNumber = item.FLIGHTCONN;
itemList.FlightDate = item.FLIGHTDATE;
itemList.FlightTime = item.FLIGHTTIME;
itemList.NumberHops = item.NUMHOPS;
_connectionList.Add(itemList);
labelNumRecords.Text = string.Format("{0} records", listViewFlight.Items.Count);
}
private void buttonTicket_Click(object sender, RibbonControlEventArgs e)
{
PseudoProgressForm progress = new PseudoProgressForm();
progress.ProgressLabel = "Querying SAP...";
BackgroundWorker backgroundWorker = new BackgroundWorker();
TicketSales ticketDataset = new TicketSales();
backgroundWorker.DoWork +=
delegate(object workSender, DoWorkEventArgs eventArg)
{
TicketSales.TicketSaleDataTable ticketSaleDataTable =
ticketDataset.TicketSale;
SAPEventAttendeeAgencyMapReadWrite attendeeAgency =
new SAPEventAttendeeAgencyMapReadWrite(Config._dbConnectionName);
SAPRefTravelAgencyReadWrite travelAgency =
new SAPRefTravelAgencyReadWrite(Config._dbConnectionName);
//get all agencies where client booked flights to
Dictionary <string, string> agencyList = new Dictionary <string, string>();
using (SAPDataReaderEventAttendeeAgencyMap reader =
attendeeAgency.ReaderSelectAll())
{
if (reader.DataReader.HasRows)
{
while (reader.DataReader.Read())
{
if (!agencyList.ContainsKey(reader.AgencyNumber))
{
string agencyName = string.Empty;
using (SAPDataReaderRefTravelAgency travelReader =
travelAgency.ReaderSelectByAgencyNumber(reader.AgencyNumber))
{
travelReader.DataReader.Read();
agencyName = travelReader.AgencyName;
}
agencyList.Add(
reader.AgencyNumber,
agencyName);
}
}
}
}
SAPFlightTrip flightTripHelper =
new SAPFlightTrip(Config.SAPUserName, Config.SAPPassword);
SAPFlightConnection flightConnectionHelper =
new SAPFlightConnection(Config.SAPUserName, Config.SAPPassword);
foreach (KeyValuePair <string, string> agency in agencyList)
{
TicketSales.TicketSaleRow ticketSaleRow =
ticketSaleDataTable.NewTicketSaleRow();
ticketSaleRow.AgencyName = agency.Value;
ticketSaleRow.TicketsSold = 0;
ticketSaleRow.TotalTicketPrice = 0;
flightTripHelper.GetList(string.Empty, agency.Key);
foreach (SAPServices.SAP_FLIGHTTRIPLIST.BAPISTRDAT flightTrip in flightTripHelper._bapiFlightTripList)
{
flightConnectionHelper.GetDetail(
flightTrip.FLCONN1,
flightTrip.FLDATE1,
string.Empty,
flightTrip.AGENCYNUM);
ticketSaleRow.TicketsSold += 1;
ticketSaleRow.TotalTicketPrice += flightConnectionHelper._bapiPrice.PRICE_BUS1;
}
ticketSaleDataTable.AddTicketSaleRow(ticketSaleRow);
}
};
backgroundWorker.RunWorkerCompleted +=
delegate(object workSender, RunWorkerCompletedEventArgs eventArg)
{
progress.Close();
progress.Dispose();
ExcelHelper.LoadExcelSheet(
"TicketSalesDistribution",
Resources.TicketSalesDistribution,
ticketDataset);
};
backgroundWorker.RunWorkerAsync();
progress.ShowDialog();
}
public static PackageInfo GetPackageInfo(string agencyNumber)
{
PackageInfo packageInfoDataset = new PackageInfo();
PackageInfo.PackageDataTable packageInfoPackageDataTable =
packageInfoDataset.Package;
PackageInfo.EventDataTable packageInfoEventDataTable =
packageInfoDataset.Event;
PackageInfo.FlightDataTable packageInfoFlightDataTable =
packageInfoDataset.Flight;
SAPPackageReadWrite package =
new SAPPackageReadWrite(Config._dbConnectionName);
SAPEventReadWrite events =
new SAPEventReadWrite(Config._dbConnectionName);
SAPVenueReadWrite venue =
new SAPVenueReadWrite(Config._dbConnectionName);
SAPPackageEventMapReadWrite packageEventMap =
new SAPPackageEventMapReadWrite(Config._dbConnectionName);
SAPFlightConnection flightConnectionHelper = new SAPFlightConnection(
Config.SAPUserName, Config.SAPPassword);
SAPDataSetPackage packageDataset = package.SelectAll();
foreach (SAPDataSetPackage.PackageRow packageRow in packageDataset.Package.Rows)
{
PackageInfo.PackageRow packageInfoPackageRow =
packageInfoPackageDataTable.AddPackageRow(
packageRow.PackageID,
packageRow.PackageName,
packageRow.PackageDescription);
SAPDataSetPackageEventMap packageEventMapDataset =
packageEventMap.SelectByPackageID(packageRow.PackageID);
foreach (SAPDataSetPackageEventMap.PackageEventMapRow packageEventMapRow in packageEventMapDataset.PackageEventMap.Rows)
{
SAPDataSetEvent eventDataset = events.SelectByEventID(packageEventMapRow.EventID);
foreach (SAPDataSetEvent.EventRow eventRow in eventDataset.Event.Rows)
{
string venueCity = string.Empty;
using (SAPDataReaderVenue venueReader = venue.ReaderSelectByVenueID(eventRow.VenueID))
{
if (venueReader.DataReader.HasRows)
{
venueReader.DataReader.Read();
venueCity = venueReader.VenueCity.ToUpper();
}
}
PackageInfo.EventRow packageInfoEventRow =
packageInfoEventDataTable.AddEventRow(
eventRow.EventID,
packageInfoPackageRow,
eventRow.EventName,
eventRow.EventDescription,
eventRow.EventDate.ToString(),
string.Format("{0}, {1}", eventRow.VenueName, venueCity),
0,
0,
0);
if (venueCity != string.Empty)
{
flightConnectionHelper.GetList(string.Empty, agencyNumber, string.Empty, venueCity);
foreach (SAPServices.SAP_FLIGHTCONNLIST.BAPISCODAT flight in flightConnectionHelper._bapiConnectionList)
{
//flight dates should be between today and before the event date
if (Convert.ToDateTime(flight.FLIGHTDATE) > DateTime.Today &&
Convert.ToDateTime(flight.FLIGHTDATE) < eventRow.EventDate)
{
flightConnectionHelper.GetDetail(flight.FLIGHTCONN, flight.FLIGHTDATE, string.Empty,
agencyNumber);
PackageInfo.FlightRow packageInfoFlightRow =
packageInfoFlightDataTable.AddFlightRow(
packageInfoEventRow,
flightConnectionHelper._bapiHopList[0].AIRLINE,
flight.FLIGHTDATE,
flight.DEPTIME,
flight.AIRPORTFR,
flight.CITYFROM,
flight.AIRPORTTO,
flight.CITYTO,
flight.ARRDATE,
flight.ARRTIME,
flightConnectionHelper._bapiAvailability[0].FIRSTFREE,
flightConnectionHelper._bapiAvailability[0].BUSINFREE,
flightConnectionHelper._bapiAvailability[0].ECONOFREE);
packageInfoEventRow.GoldAvailability += flightConnectionHelper._bapiAvailability[0].FIRSTFREE;
packageInfoEventRow.SilverAvailability += flightConnectionHelper._bapiAvailability[0].BUSINFREE;
packageInfoEventRow.BronzeAvailability += flightConnectionHelper._bapiAvailability[0].ECONOFREE;
}
}
}
}
}
}
return(packageInfoDataset);
}
public static Sales GetSalesData(int month, int year)
{
Sales salesDataset = new Sales();
Sales.PackageDataTable salesPackageDataTable = salesDataset.Package;
Sales.PackageSaleDataTable salesPackageSaleDataTable = salesDataset.PackageSale;
Sales.EventDataTable salesEventDataTable = salesDataset.Event;
Sales.EventSaleDataTable salesEventSaleDataTable = salesDataset.EventSale;
Sales.FlightDataTable salesFlightDataTable = salesDataset.Flight;
SAPPackageReadWrite package =
new SAPPackageReadWrite(Config._dbConnectionName);
SAPEventAttendeeReadWrite attendee =
new SAPEventAttendeeReadWrite(Config._dbConnectionName);
SAPEventAttendeeAgencyMapReadWrite eventAttendeeAgencyMap =
new SAPEventAttendeeAgencyMapReadWrite(Config._dbConnectionName);
SAPPackageEventMapReadWrite packageEventMap =
new SAPPackageEventMapReadWrite(Config._dbConnectionName);
SAPEventReadWrite events =
new SAPEventReadWrite(Config._dbConnectionName);
SAPFlightTrip flightTripHelper =
new SAPFlightTrip(Config.SAPUserName, Config.SAPPassword);
SAPFlightConnection flightConnectionHelper =
new SAPFlightConnection(Config.SAPUserName, Config.SAPPassword);
//fetch all packages
SAPDataSetPackage packageDataset = package.SelectAll();
//iterate through the packages
foreach (SAPDataSetPackage.PackageRow packageRow in packageDataset.Package.Rows)
{
Sales.PackageRow salesPackageRow =
salesPackageDataTable.AddPackageRow(packageRow.PackageID, packageRow.PackageName);
SAPDataSetEventAttendee attendeeDataset =
attendee.SelectByPackageID(packageRow.PackageID);
SAPDataSetPackageEventMap packageEventMapDataset =
packageEventMap.SelectByPackageID(packageRow.PackageID);
foreach (SAPDataSetEventAttendee.EventAttendeeRow eventAttendeeRow in attendeeDataset.EventAttendee.Rows)
{
if ((eventAttendeeRow.Created.Month == month || month == 0) &&
(eventAttendeeRow.Created.Year == year || year == 0))
{
//build temp package table
Sales.PackageSaleRow salesPackageSaleRow =
salesPackageSaleDataTable.AddPackageSaleRow(
salesPackageRow,
eventAttendeeRow.EventAttendeeID,
eventAttendeeRow.Created,
eventAttendeeRow.DateOfBirth,
eventAttendeeRow.CustomerNumber,
0,
0);
SAPDataSetEventAttendeeAgencyMap eventAttendeeAgencyMapDataset =
eventAttendeeAgencyMap.SelectByEventAttendeeID(eventAttendeeRow.EventAttendeeID);
for (int i = 0; i < packageEventMapDataset.PackageEventMap.Rows.Count; i++)
{
SAPDataSetPackageEventMap.PackageEventMapRow packageEventMapRow =
packageEventMapDataset.PackageEventMap.Rows[i] as
SAPDataSetPackageEventMap.PackageEventMapRow;
SAPDataSetEvent eventDataset =
events.SelectByEventID(packageEventMapRow.EventID);
SAPDataSetEvent.EventRow eventRow = eventDataset.Event[0];
//assumes same record position as to event, need to revise!!!
//SAPDataSetEventAttendeeAgencyMap.EventAttendeeAgencyMapRow eventAttendeeAgencyMapRow =
// eventAttendeeAgencyMapDataset.EventAttendeeAgencyMap.Rows[i] as
// SAPDataSetEventAttendeeAgencyMap.EventAttendeeAgencyMapRow;
DataRow[] eventAttendeeAgencyMapDataRow =
eventAttendeeAgencyMapDataset.EventAttendeeAgencyMap.Select("EventID = " +
packageEventMapRow.EventID);
SAPDataSetEventAttendeeAgencyMap.EventAttendeeAgencyMapRow eventAttendeeAgencyMapRow =
eventAttendeeAgencyMapDataRow[0] as
SAPDataSetEventAttendeeAgencyMap.EventAttendeeAgencyMapRow;
Sales.EventRow salesEventRow = salesEventDataTable.FindByEventID(eventRow.EventID);
if (salesEventRow == null)
{
salesEventRow = salesEventDataTable.AddEventRow(
eventRow.EventID,
eventRow.EventName,
eventRow.GoldPackagePrice,
eventRow.SilverPackagePrice,
eventRow.BronzePackagePrice,
eventRow.GoldPackageTrueCost,
eventRow.SilverPackageTrueCost,
eventRow.BronzePackageTrueCost);
}
salesEventSaleDataTable.AddEventSaleRow(
salesEventRow,
packageRow.PackageID,
eventAttendeeRow.EventAttendeeID,
eventAttendeeAgencyMapRow.AgencyNumber,
eventAttendeeAgencyMapRow.TripNumber,
string.Empty,
0,
0);
}
}
}
}
foreach (Sales.PackageSaleRow salesPackageSalesRow in salesPackageSaleDataTable.Rows)
{
foreach (DataRow row in salesPackageSalesRow.GetChildRows("PackageSale_EventSale"))
{
Sales.EventSaleRow salesEventSaleRow = row as Sales.EventSaleRow;
flightTripHelper.GetList(
salesPackageSalesRow.CustomerNumber.Trim(),
salesEventSaleRow.AgencyNumber);
foreach (SAPServices.SAP_FLIGHTTRIPLIST.BAPISTRDAT trip in flightTripHelper._bapiFlightTripList)
{
if (salesEventSaleRow.TripNumber == trip.TRIPNUMBER)
{
flightConnectionHelper.GetDetail(
trip.FLCONN1,
trip.FLDATE1,
string.Empty,
salesEventSaleRow.AgencyNumber);
Sales.EventRow salesEventRow =
salesEventDataTable.FindByEventID(salesEventSaleRow.EventID);
decimal ticketPrice = 0;
switch (trip.CLASS)
{
case "F":
salesEventSaleRow.Cost = salesEventRow.GoldPackageCost;
salesEventSaleRow.Price = salesEventRow.GoldPackagePrice;
salesEventSaleRow.PackageType = "Gold";
ticketPrice =
trip.NUMADULT == 1
? flightConnectionHelper._bapiPrice.PRICE_FST1
:
trip.NUMCHILD == 1
? flightConnectionHelper._bapiPrice.PRICE_FST2
:
trip.NUMINFANT == 1
? flightConnectionHelper._bapiPrice.PRICE_FST3
: 0;
break;
case "C":
salesEventSaleRow.Cost = salesEventRow.SilverPackageCost;
salesEventSaleRow.Price = salesEventRow.SilverPackagePrice;
salesEventSaleRow.PackageType = "Silver";
ticketPrice =
trip.NUMADULT == 1
? flightConnectionHelper._bapiPrice.PRICE_BUS1
:
trip.NUMCHILD == 1
? flightConnectionHelper._bapiPrice.PRICE_BUS2
:
trip.NUMINFANT == 1
? flightConnectionHelper._bapiPrice.PRICE_BUS3
: 0;
break;
case "Y":
salesEventSaleRow.Cost = salesEventRow.BronzePackageCost;
salesEventSaleRow.Price = salesEventRow.BronzePackagePrice;
salesEventSaleRow.PackageType = "Bronze";
ticketPrice =
trip.NUMADULT == 1
? flightConnectionHelper._bapiPrice.PRICE_ECO1
:
trip.NUMCHILD == 1
? flightConnectionHelper._bapiPrice.PRICE_ECO2
:
trip.NUMINFANT == 1
? flightConnectionHelper._bapiPrice.PRICE_ECO3
: 0;
break;
}
salesPackageSalesRow.Cost += salesEventSaleRow.Cost;
salesPackageSalesRow.Price += salesEventSaleRow.Price;
salesFlightDataTable.AddFlightRow(
salesEventSaleRow,
trip.FLCONN1,
trip.FLDATE1,
trip.CLASS,
ticketPrice,
trip.NUMADULT,
trip.NUMCHILD,
trip.NUMINFANT);
}
}
}
}
return(salesDataset);
}