public async Task <Order> GetOrderAsync(int id)
{
using (var connection = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <dynamic>(
@"select o.Id as ordernumber,o.OrderDate as date, o.Description as description,
o.Address_City as city, o.Address_Country as country, o.Address_State as state, o.Address_Street as street, o.Address_ZipCode as zipcode,
os.Name as status,
oi.ProductName as productname, oi.Units as units, oi.UnitPrice as unitprice, oi.PictureUrl as pictureurl
FROM `orders` o
LEFT JOIN `orderItems` oi ON o.Id = oi.orderid
LEFT JOIN `orderstatus` os on o.OrderStatusId = os.Id
WHERE o.Id=@id"
, new { id }
);
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(MapOrderItems(result));
}
}
public async Task <IEnumerable <CardType> > GetCardTypesAsync()
{
using (var connection = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
return(await connection.QueryAsync <CardType>("SELECT * FROM `cardtypes`"));
}
}
public async Task <IEnumerable <OrderSummary> > GetOrdersAsync()
{
using (var connection = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
return(await connection.QueryAsync <OrderSummary>(@"SELECT o.Id as ordernumber,o.OrderDate as date,os.Name as status,SUM(oi.units*oi.unitprice) as total
FROM `orders` o
LEFT JOIN `orderItems` oi ON o.Id = oi.orderid
LEFT JOIN `orderstatus` os on o.OrderStatusId = os.Id
GROUP BY o.Id, o.OrderDate, os.Name
ORDER BY o.Id"));
}
}
//--------------------------------------------------------------
// Load Data by calling related Stored Procedure
public void UomLoadData(IConfiguration configuration)
{
System.Console.WriteLine("--------------------------------UomLoadData:1--------------------------------");
// Link to resolve the error: 'The type 'MySqlConnection ' exists in both 'MySql.Data' ... and 'MySqlConnector'...
// - https://stackoverflow.com/questions/48683241/the-type-mysqlconnection-exists-in-both-mysql-data-issue
// Link used to calll a MySql Stored Procedure:
// - https://dev.mysql.com/doc/connector-net/en/connector-net-programming-stored-using.html
// Call Stored Procedure
const string storedProcedureName = "DSP_INS_Data";
MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection conn = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection();
// Get connection string from appsettings.json
conn.ConnectionString = configuration.GetSection("DBInfo").GetSection("ConnectionString").Value;
MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlCommand cmd = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlCommand();
try{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = storedProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pTruncateData", 1);
cmd.Parameters["@pTruncateData"].Direction = ParameterDirection.Input;
cmd.ExecuteNonQuery();
}
catch (MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlException ex)
{
System.Console.WriteLine($"Error: {ex.Number}, Message: {ex.Message}");
}
finally
{
conn.Close();
}
}
public async Task <IActionResult> GetFlightProfileGroup(DateTime strtDate, DateTime endDate, string arrival)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
List <FlightGroup> result = new List <FlightGroup>();
string[] ArrivalAirports = arrival.Split(',');
foreach (string airport in ArrivalAirports)
{
var flightProfiles = new List <FlightGroupDetail>();
string constring = _iconfiguraton.GetSection("ConnectionStrings").GetSection("ATSEFEntities").Value;
string commandInput = "SELECT count(*),ARRIVAL,RUNWAY_HEADING,AIRCRAFT,SECOND_ENTRY_SECTOR,min(SECOND_TRAVEL_TIME),max(SECOND_TRAVEL_TIME),AVG(SECOND_TRAVEL_TIME),39 as P15, 40 as P20,48 as P80 ,50 as P85 FROM ATSEF_DB.FLIGHT_PROFILE where ARRIVAL = '" + airport + "' and ARRIVAL_TIME between '" + strtDate.ToString("yyyy-MM-dd",
CultureInfo.InvariantCulture) + "' and '" + endDate.ToString("yyyy-MM-dd",
CultureInfo.InvariantCulture) + "' group by AIRCRAFT,RUNWAY_HEADING,SECOND_ENTRY_SECTOR;";
//string commandInput = "SELECT count(*),ARRIVAL,RUNWAY_HEADING,AIRCRAFT,SECOND_ENTRY_SECTOR ,min(SECOND_TRAVEL_TIME),max(SECOND_TRAVEL_TIME),AVG(SECOND_TRAVEL_TIME), 40 as P20,48 as P80 FROM ATSEF_DB.FLIGHT_PROFILE where ARRIVAL='VTBS' and ARRIVAL_TIME between '2018-01-01' and '2018-01-27' group by AIRCRAFT,RUNWAY_HEADING,SECOND_ENTRY_SECTOR ;";
try
{
using (DbConnection connection = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection(constring))
{
connection.Open();
using (DbCommand command = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlCommand(commandInput, (MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection)connection))
{
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var groupDetail = new FlightGroupDetail()
{
Amount = (long)reader["count(*)"],
Arrival = reader["ARRIVAL"] == DBNull.Value?"": (string)reader["ARRIVAL"],
RunwayHeading = (reader["RUNWAY_HEADING"] == DBNull.Value? "": (string)(reader["RUNWAY_HEADING"])),
Aircraft = (reader["AIRCRAFT"] == DBNull.Value? "":(string)(reader["AIRCRAFT"])),
SecondEntrySector = (int)reader["SECOND_ENTRY_SECTOR"],
Min = (long)reader["min(SECOND_TRAVEL_TIME)"],
Max = (long)reader["max(SECOND_TRAVEL_TIME)"],
Avg = (decimal)reader["AVG(SECOND_TRAVEL_TIME)"],
P15 = (long)reader["P15"],
P20 = (long)reader["P20"],
P80 = (long)reader["P80"],
P85 = (long)reader["P85"]
};
flightProfiles.Add(groupDetail);
}
}
}
//DbCommand command = new MySql.Data.MySqlClient.MySqlCommand(commandInput, (MySql.Data.MySqlClient.MySqlConnection)connection);
}
}
catch (Exception e)
{
throw e;
}
//var flightProfiles = await _context.FlightProfile.Where(m => m.ArrivalTime >= strtDate && m.ArrivalTime <= endDate && m.Arrival == airport)
// .GroupBy(a => new { a.Aircraft, a.RunwayHeading, a.SecondEntrySector })
// .Select(group => new FlightGroupDetail
// {
// Aircraft = group.Key.Aircraft,
// RunwayHeading = group.Key.RunwayHeading,
// Amount = group.Count(),
// Arrival = airport,
// SecondEntrySector = group.Key.SecondEntrySector,
// }).ToListAsync();
if (flightProfiles == null)
{
return(NotFound());
}
FlightGroup flightGroup = new FlightGroup
{
Arrival = airport,
StartTime = strtDate,
EndingTime = endDate,
ListFlightGroups = flightProfiles
};
result.Add(flightGroup);
}
return(Ok(result));
}
