IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh)
{
var cb = new SqlCommandBuilder();
var commandText = string.Format(@"select
i.name,
i.index_id,
i.type,
i.is_unique
from {0}.sys.schemas s (nolock)
join {0}.sys.objects o (nolock)
on s.schema_id = o.schema_id
join {0}.sys.indexes i (nolock)
on o.object_id = i.object_id
where o.object_id = @object_id
order by i.name",
cb.QuoteIdentifier(_databaseNode.Name));
var parameters = new SqlParameterCollectionBuilder();
parameters.Add("object_id", _id);
var request = new ExecuteReaderRequest(commandText, parameters.ToReadOnlyCollection());
var connectionString = _databaseNode.Databases.Server.ConnectionString;
var executor = new SqlCommandExecutor(connectionString);
return(executor.ExecuteReader(request, 128, dataRecord =>
{
var name = dataRecord.GetStringOrDefault(0);
var indexId = dataRecord.GetInt32(1);
var type = dataRecord.GetByte(2);
var isUnique = dataRecord.GetBoolean(3);
return new IndexNode(_databaseNode, _id, indexId, name, type, isUnique);
}));
}
private void Button_Today_DB_Click(object sender, RoutedEventArgs e)
{
using (var p = new ExcelPackage())
using (var sqlcmd = new SqlCommandExecutor("EXEC pr_GetDactyloscopyReport"))
using (var sqlReader = sqlcmd.ExecuteReader())
{
var ws = p.Workbook.Worksheets.Add("BANK");
int count = sqlReader.FieldCount;
for (var i = 1; sqlReader.Read(); i++)
{
for (int j = 1; j <= count; j++)
{
ws.Cells[i, j].Value = sqlReader.GetValue(j - 1);
ws.Cells[i, j].Style.Font.SetFromFont(new Font("Times New Roman", 12));
}
}
var dlg = new VistaSaveFileDialog()
{
Filter = "Open XML Spreadsheet |*.xlsx",
FileName = "Отчет для дактилоскопии",
DefaultExt = ".xlsx",
OverwritePrompt = true,
CreatePrompt = false,
};
if (dlg.ShowDialog() == true)
{
using (var fs = File.Create(dlg.FileName))
p.SaveAs(fs);
}
}
}
/// <summary>
/// Get the top 10 most visited attractions
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void TopTenAttractions_Click(object sender, RoutedEventArgs args)
{
uxReportList.Items.Clear();
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
List <string> topTenAttractions = (List <string>)executor.ExecuteReader(new AgencyTopTenAttractionsDelegate());
//Set label for columns
uxReportListLabel.Content = $"{Check.Format("Attraction",20,false)}{Check.Format("Number of Customers",30,false)}" +
$"{Check.Format("City, Country",20,false)}{Check.Format("Ticket Price",20,false)}";
if (topTenAttractions.Count > 0)
{
//Add each row and format into columns
foreach (string row in topTenAttractions)
{
string[] splitRow = row.Split('-');
TextBlock t = new TextBlock();
t.Text = $"{Check.Format(splitRow[0],6,false)} {Check.Format(splitRow[1],40,true)}" +
$"{Check.Format(splitRow[2],16,true)}{Check.Format(splitRow[3],32,true)}" +
$"{Check.Format(splitRow[4],20,true)}";
uxReportList.Items.Add(t);
}
}
}
/// <summary>
/// Autofills restaurant information with restaurant ID when user clicks "Autofill" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void Autofill_Click(object sender, RoutedEventArgs args)
{
string message = "";
if (Check.ValidPositiveInt("Restaurant ID", uxRestaurantID.Text, out message))
{
int restaurantID = int.Parse(uxRestaurantID.Text);
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
//Lookup restaurant
Restaurant restaurant = executor.ExecuteReader(new RestaurantsGetRestaurantDelegate(restaurantID));
//If restaurant exists, autofill info
if (restaurant == null)
{
MessageBox.Show("Restaurant does not already exist");
}
else
{
City city = executor.ExecuteReader(new LocationGetCityByCityIdDelegate(restaurant.CityID));
uxRestaurantName.Text = restaurant.Name;
uxCity.Text = city.CityName;
uxRegion.Text = city.Region;
uxCountry.Text = city.Country;
}
}
else
{
MessageBox.Show(message);
}
}
/// <summary>
/// Autofills some of the information given an attractionID when the user clicks "Autofill" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void Autofill_Click(object sender, RoutedEventArgs args)
{
string message = "";
if (Check.ValidPositiveInt("Attraction ID", uxAttractionID.Text, out message))
{
int attractionID = int.Parse(uxAttractionID.Text);
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
//If attraction exists, autofill
if (executor.ExecuteReader(new GetAttractionDataDelegate(attractionID)) == null)
{
MessageBox.Show("Attraction does not yet exist");
}
else
{
Attraction attraction = executor.ExecuteReader(new GetAttractionDataDelegate(attractionID));
City city = executor.ExecuteReader(new LocationGetCityByCityIdDelegate(attraction.CityID));
uxAttractionName.Text = attraction.Name;
uxCity.Text = city.CityName;
uxCountry.Text = city.Country;
uxRegion.Text = city.Region;
}
}
else
{
MessageBox.Show(message);
}
}
/// <summary>
/// Get the cheapest amenities for each city within the database
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void CheaperOptions_Click(object sender, RoutedEventArgs args)
{
uxReportList.Items.Clear();
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
List <string> cheaperOptions = (List <string>)executor.ExecuteReader(new AgencyCheapestOptionsDelegate());
//Set label for columns
uxReportListLabel.Content = $"{Check.Format("City, Country", 35, true)}{Check.Format("Cheapest Hotel",30,true)}" +
$"{Check.Format("Cheapest Attraction",35,true)}";
if (cheaperOptions.Count > 0)
{
//Add each row and format into columns
foreach (string row in cheaperOptions)
{
string[] splitRow = row.Split('-');
TextBlock t = new TextBlock();
t.Text = $"{Check.Format(splitRow[0], 35, true)}{Check.Format(splitRow[1],35,true)}" +
$"{Check.Format(splitRow[2],35,true)}";
uxReportList.Items.Add(t);
}
}
}
/// <summary>
/// Get a monthly summary report of sales and the avererage customer per agent
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void MonthlySalesReport_Click(object sender, RoutedEventArgs args)
{
uxReportList.Items.Clear();
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
List <string> monthlyDetail = (List <string>)executor.ExecuteReader(new AgencyDetailByMonthDelegate());
//Set label for columns
uxReportListLabel.Content = $"{Check.Format(" Year, Month",20,true)}{Check.Format("Trip Count",15,true)}" +
$"{Check.Format("Ave. Customers Per Agent",40,true)}{Check.Format("Total Sales",20,true)}";
if (monthlyDetail.Count > 0)
{
//Get each row and format into columns
foreach (string row in monthlyDetail)
{
string[] splitRow = row.Split(',');
TextBlock t = new TextBlock();
t.Text = $" {Check.Format(splitRow[0],4,true)}, {Check.Format(splitRow[1],18,true)}{Check.Format(splitRow[2],28,true)}" +
$"{Check.Format(splitRow[3],35,true)}{Check.Format(splitRow[4],20,true)}";
uxReportList.Items.Add(t);
}
}
}
/// <summary>
/// Deletes the selected trip when the user clicks "Delete Trip" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void DeleteTrip_Click(object sender, RoutedEventArgs args)
{
if (uxTrips.SelectedItem != null)
{
if (uxTrips.SelectedItem is TextBlock t)
{
int tripID;
tripID = int.Parse(t.Text.Split(' ')[0].Trim());
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
//Find trip
Trip trip = executor.ExecuteReader(new AgencyFetchTripDelegate(tripID));
//Set isDeleted value for trip to 1
executor.ExecuteNonQuery(new AgencySaveTripDelegate(trip.TripID, trip.CustomerID, 1, trip.DateCreated, trip.AgentID));
uxTrips.Items.Remove(uxTrips.SelectedItem);
RefreshTripList();
MessageBox.Show("Trip " + tripID + " has been removed");
}
else
{
MessageBox.Show("Can't access selected item");
}
}
else
{
MessageBox.Show("Please select a trip to delete");
}
}
/// <summary>
/// Get an age group report
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void AgeReport_Click(object sender, RoutedEventArgs args)
{
uxReportList.Items.Clear();
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
List <string> ageGroups = (List <string>)executor.ExecuteReader(new AgencyAgeReportDelegate());
//Set label for columns
uxReportListLabel.Content = $"{Check.Format("Age Group", 12, true)}{Check.Format("Customers", 10, true)}" +
$"{Check.Format("Ave. Budget", 13, true)}{Check.Format("Low. Budget",13,true)}" +
$"{Check.Format("High. Budget",13,true)}{Check.Format("Ave. Age",10,true)}" +
$"{Check.Format("Trip Count",10,true)}";
if (ageGroups.Count > 0)
{
//Add each row and format into columns
foreach (string row in ageGroups)
{
string[] splitRow = row.Split(',');
TextBlock t = new TextBlock();
t.Text = $"{Check.Format(splitRow[0], 17, true)}{Check.Format(splitRow[1], 10, true)}" +
$"{Check.Format(splitRow[2], 15, true)}{Check.Format(splitRow[3],15,true)}" +
$"{Check.Format(splitRow[4],15,true)}{Check.Format(splitRow[5],12,true)}" +
$"{Check.Format(splitRow[6],10,true)}";
uxReportList.Items.Add(t);
}
}
}
/// <summary>
/// Autofills the hotel information from valid hotel id when user clicks "Autofill" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void Autofill_Click(object sender, RoutedEventArgs args)
{
string message = "";
if (Check.ValidPositiveInt("Hotel ID", uxHotelID.Text, out message))
{
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
int hotelID = int.Parse(uxHotelID.Text);
// Lookup hotel using hotelID
Hotel hotel = executor.ExecuteReader(new HotelsGetHotelDelegate(hotelID));
if (hotel == null)
{
MessageBox.Show("Hotel does not exist");
}
else
{
uxHotelName.Text = hotel.Name;
uxHotelAddress.Text = hotel.FullAddress;
City city = executor.ExecuteReader(new LocationGetCityByCityIdDelegate(hotel.CityID));
uxCity.Text = city.CityName;
uxRegion.Text = city.Region;
uxCountry.Text = city.Country;
}
}
else
{
MessageBox.Show(message);
}
}
private void ListView_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (e.AddedItems.Count > 0 && e.AddedItems[0] is SearchPersonToAssignCard person)
{
var accountNumber = TextPopupWindow.Prompt("Номер карты", "Внимание!");
if (accountNumber == null || accountNumber.Length != 16 || !long.TryParse(accountNumber, out _))
{
return;
}
var sql = $"INSERT INTO person_card (passport_serial, account_number) VALUES ('{person.PassportSerial}', '{accountNumber}')";
using (var sqlcmd = new SqlCommandExecutor(sql))
if (sqlcmd.TryExecuteScalar(out _))
{
WatermarkTextBox_TextChanged(sender, null);
ViewModel.AssigmentHistory.Add(new AssigmentHistory()
{
PassportSerial = person.PassportSerial,
LastName = person.LastName,
FirstName = person.FirstName,
Patronymic = person.Patronymic,
BirthDate = person.BirthDate,
AccountNumber = accountNumber.Substring(7, 8)
});
}
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
var path = $"{Directory.GetCurrentDirectory()}\\Documents\\Персонализация.xlsm";
using (var p = new ExcelPackage(new FileInfo(path)))
using (var sqlcmd = new SqlCommandExecutor("EXEC pr_GetReportForVTB"))
using (var sqlReader = sqlcmd.ExecuteReader())
{
var ws = p.Workbook.Worksheets.First();
int count = sqlReader.FieldCount;
for (var i = 5; sqlReader.Read(); i++)
{
for (int j = 1; j <= count; j++)
{
ws.Cells[i, j].Value = sqlReader.GetValue(j - 1);
}
}
// ws.Cells[] starts from 1, 1
// GetValue() starts from 0
var dlg = new VistaSaveFileDialog()
{
Filter = "Open XML Macro-Enabled Spreadsheet |*.xlsm",
FileName = "Персонализация ВТБ",
DefaultExt = ".xlsm",
OverwritePrompt = true,
CreatePrompt = false,
};
if (dlg.ShowDialog() == true)
{
using (var fs = File.Create(dlg.FileName))
p.SaveAs(fs);
}
}
}
private void Button_Report_Click(object sender, RoutedEventArgs e)
{
var path = $"{Directory.GetCurrentDirectory()}\\Documents\\Форма.xlsx";
using (var p = new ExcelPackage(new FileInfo(path)))
using (var sqlcmd = new SqlCommandExecutor("EXEC pr_GetAddedPersonsFromYesterday"))
using (var sqlReader = sqlcmd.ExecuteReader())
{
var ws = p.Workbook.Worksheets.First();
int count = sqlReader.FieldCount;
for (var i = 4; sqlReader.Read(); i++)
{
for (int j = 1; j <= count; j++)
{
ws.Cells[i, j].Value = sqlReader.GetValue(j - 1);
}
}
// ws.Cells[] starts from 1, 1
// GetValue() starts from 0
var dlg = new VistaSaveFileDialog()
{
Filter = "Open XML Spreadsheet |*.xlsx",
FileName = $"Залитые за {DateTime.Now.AddDays(-1).ToString("dd-MM-yyyy")}",
DefaultExt = ".xlsx",
OverwritePrompt = true,
CreatePrompt = false,
};
if (dlg.ShowDialog() == true)
{
using (var fs = File.Create(dlg.FileName))
p.SaveAs(fs);
}
}
}
private IEnumerable <object> ScopedHandle(Envelope <MessageContext, object> mainEnvelope)
{
var tenantId = mainEnvelope.Header.MetadataLookup["tenantId"].First() as string;
if (tenantId == null)
{
return new[] { new NotHandled(mainEnvelope) }
}
;
var scopedDispatcher = new Dispatcher();
scopedDispatcher.Register <WriteToStream>(writeToStream =>
{
var envelope = Envelope.Create(mainEnvelope.Header, writeToStream);
Task.WhenAll(EventStoreHandlers.WriteAsync(envelope, _eventStoreConnection)).Wait();
return(Enumerable.Empty <object>());
});
scopedDispatcher.Register <Envelope <MessageContext, ItemPurchased> >(envelope =>
{
var eventId = EventId.Create(mainEnvelope.Header.EventId);
return(InventoryProjectionHandlers.Project(envelope.Body, eventId, envelope.Header.StreamContext.EventNumber));
});
var connectionSettingsFactory = new MultitenantSqlConnectionSettingsFactory(tenantId);
var connectionStringSettings = connectionSettingsFactory.GetSettings("Projections");
var connectionString = connectionStringSettings.ConnectionString;
_hasInitialized.GetOrAdd(tenantId, _ =>
{
var executor = new SqlCommandExecutor(connectionStringSettings);
executor.ExecuteNonQuery(InventoryProjectionHandlers.CreateSchema());
return(Nothing.Value);
});
var sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
using (sqlConnection)
using (var transaction = sqlConnection.BeginTransaction())
{
var sqlExecutor = new ConnectedTransactionalSqlCommandExecutor(transaction);
scopedDispatcher.Register <SqlNonQueryCommand>(command =>
{
sqlExecutor.ExecuteNonQuery(command);
return(Enumerable.Empty <object>());
});
var typedMainEnvelope = Envelope.CreateGeneric(mainEnvelope.Header, mainEnvelope.Body);
var unhandled = scopedDispatcher.DispatchExhaustive(typedMainEnvelope);
transaction.Commit();
return(unhandled);
}
}
/// <summary>
/// Add the customer to database if valid inputs when the user clicks "Add Customer" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void AddCustomer_Click(object sender, RoutedEventArgs args)
{
if (CheckValidInputs())
{
string name = Check.FormatName(uxFirstName.Text) + " " + Check.FormatName(uxLastName.Text);
float budget = float.Parse(uxBudget.Text);
int age = int.Parse(uxAge.Text);
string sex = (bool)uxMale.IsChecked ? "Male" : "Female";
string address = uxAddress.Text + "\n" + Check.FormatName(uxCity.Text) + ", " + uxZipcode.Text;
string phone = uxAreaCode.Text + uxFirst3PhoneDigits.Text + uxLast4PhoneDigits.Text;
string email = uxEmail.Text;
string city = Check.FormatName(uxCity.Text);
string region = Check.FormatName(uxRegion.Text);
string country = Check.FormatName(uxCountry.Text);
// CONNNECT
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
LocationGetCityDelegate getCity = new LocationGetCityDelegate(city, country, region);
int cityID = 0;
// Lookup city using city, region, country
// if city == null
// create city
// cityID = newly created city
// else
// cityID = found city
City c = executor.ExecuteReader(getCity);
if (c == null)
{
LocationCreateCityDelegate createsCity = new LocationCreateCityDelegate(city, region, country);
c = executor.ExecuteNonQuery(createsCity);
cityID = c.CityID;
}
else
{
cityID = c.CityID;
}
// CONNECT
int contactID = 0;
int customerID = 0;
// Create new contact info using address, phone, email, cityID
AgencyCreateContactInfoDelegate saveInfo = new AgencyCreateContactInfoDelegate(address, phone, email, cityID);
// contactID = newly created contact
ContactInfo contactId = (ContactInfo)executor.ExecuteNonQuery(saveInfo);
// CONNECT
contactID = contactId.ContactID;
AgencyCreateCustomerDelegate cd = new AgencyCreateCustomerDelegate(name, budget, age, sex, contactID);
// Create new customer using budget, name, age, sex, contactID
// customerID = newly created customer
Customer customer = executor.ExecuteNonQuery(cd);
customerID = customer.CustomerID;
MessageBox.Show("Customer " + name + " has been successfully added. CustomerID = " + customerID);
}
}
private void ListView_TeamDetail_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
if (sender is ListViewItem item && item.IsMouseOver)
{
ViewModel.SelectedPersonFromTeam.IsSelected = true;
var sql = $"EXEC pr_AccountNumberRegisteredChanged \'{ViewModel.SelectedPersonFromTeam.PassportSerial}\', 1 ";
using (var sqlcmd = new SqlCommandExecutor(sql))
sqlcmd.TryExecuteNonQuery(out _);
}
}
IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh)
{
const string commandText = @"select name
from sys.server_principals sp (nolock)
where sp.type in('S','U','G')
order by name";
var request = new ExecuteReaderRequest(commandText);
var executor = new SqlCommandExecutor(_server.ConnectionString);
return(executor.ExecuteReader(request, 128, dataRecord => new LoginNode(dataRecord.GetString(0))));
}
public static IEnumerable <PersonAccountNumber> GetAll()
{
using (var sqlcmd = new SqlCommandExecutor("SELECT person.*, person_card.account_number FROM person LEFT JOIN person_card ON person.passport_serial = person_card.passport_serial"))
using (var sqlReader = sqlcmd.ExecuteReader())
{
while (sqlReader.Read())
{
yield return(new PersonAccountNumber(sqlReader));
}
}
}
public static IEnumerable <PersonFromTeam> GetAll(string inventory)
{
using (var sqlcmd = new SqlCommandExecutor($"EXEC pr_GetPersonsFromTeam {inventory}"))
using (var sqlReader = sqlcmd.ExecuteReader())
{
while (sqlReader.Read()) //Выводим в список
{
yield return(new PersonFromTeam(sqlReader));
}
}
}
public static IEnumerable <PersonsWithCardList> GetAll()
{
using (var sqlcmd = new SqlCommandExecutor("EXEC pr_GetPersonsWithCardList"))
using (var sqlReader = sqlcmd.ExecuteReader())
{
while (sqlReader.Read())
{
yield return(new PersonsWithCardList(sqlReader));
}
}
}
/// <summary>
/// If valid inputs, adds a hotel reservation to the trip when the user clicks "Add Reservation" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void AddReservation_Click(object sender, RoutedEventArgs args)
{
if (CheckValidInputs())
{
string hotelName = Check.FormatName(uxHotelName.Text);
string address = uxHotelAddress.Text;
string country = Check.FormatName(uxCountry.Text);
string region = Check.FormatName(uxRegion.Text);
string cityname = Check.FormatName(uxCity.Text);
double roomPrice = double.Parse(uxRoomPrice.Text);
DateTime checkInDate = (DateTime)uxCheckinDate.SelectedDate;
int cityID = 0;
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
//Lookup city
City citysearch = executor.ExecuteReader(new LocationGetCityDelegate(cityname, country, region));
//If city does not exist, add city
if (citysearch == null)
{
City city = executor.ExecuteNonQuery(new LocationCreateCityDelegate(cityname, region, country));
cityID = city.CityID;
}
else
{
cityID = citysearch.CityID;
}
int hotelID = 0;
//Lookup hotel
Hotel hotelsearch = executor.ExecuteReader(new HotelsFetchHotelDelegate(hotelName, cityID, address));
//If hotel does not exist, add hotel
if (hotelsearch == null)
{
Hotel hotel = executor.ExecuteNonQuery(new HotelsCreateHotelDelegate(hotelName, cityID, address));
hotelID = hotel.HotelID;
}
else
{
hotelID = hotelsearch.HotelID;
}
//Add hotel reservation
HotelReservation hr = executor.ExecuteNonQuery(new HotelsCreateHotelReservationDelegate(tripID, hotelID, checkInDate, roomPrice));
MessageBox.Show("Reservation at " + hotelName + " successfully added");
}
}
/// <summary>
/// If valid inputs, create new car rental reservation when the user clicks "Add Reservation" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void AddReservation_Click(object sender, RoutedEventArgs args)
{
if (CheckValidInputs())
{
string carAgencyName = Check.FormatName(uxCarRentalAgencyName.Text);
string carModel = Check.FormatName(uxCarModel.Text);
float rentalPrice = float.Parse(uxRentalPrice.Text);
DateTime rentalDate = (DateTime)uxRentalDate.SelectedDate;
string cityName = Check.FormatName(uxCity.Text);
string country = Check.FormatName(uxCountry.Text);
string region = Check.FormatName(uxRegion.Text);
int cityID = 0;
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
//Lookup city
City city = executor.ExecuteReader(new LocationGetCityDelegate(cityName, country, region));
//If city does not exist, add
if (city == null)
{
city = executor.ExecuteNonQuery(new LocationCreateCityDelegate(cityName, region, country));
cityID = city.CityID;
}
else
{
cityID = city.CityID;
}
int carRentalID = 0;
//Lookup car rental agency
CarRental agency = executor.ExecuteReader(new CarsGetAgencyByNameDelegate(carAgencyName, cityID));
//If agency does not exist, add
if (agency == null)
{
agency = executor.ExecuteNonQuery(new CarsCreateCarRentalDelegate(carAgencyName, cityID));
carRentalID = agency.CarRentalID;
}
else
{
carRentalID = agency.CarRentalID;
}
//Add new car rental reservation
CarRentalReservation carRentalReservation = executor.ExecuteNonQuery(new CarsCreateCarRentalReservationDelegate
(tripID, carRentalID, rentalDate, carModel, rentalPrice));
MessageBox.Show("Car successfully reserved with agency " + carAgencyName);
}
}
/// <summary>
/// If valid inputs, make new attraction ticket when the user clicks "Add Ticket" button
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
public void AddTicket_Click(object sender, RoutedEventArgs args)
{
if (CheckValidInputs())
{
string attractionName = Check.FormatName(uxAttractionName.Text);
float ticketPrice = float.Parse(uxTicketPrice.Text);
DateTime ticketDate = (DateTime)uxDate.SelectedDate;
string country = Check.FormatName(uxCountry.Text);
string region = Check.FormatName(uxRegion.Text);
string cityName = Check.FormatName(uxCity.Text);
int cityID = 0;
SqlCommandExecutor executor = new SqlCommandExecutor(connectionString);
//Lookup city
City city = executor.ExecuteReader(new LocationGetCityDelegate(cityName, country, region));
//If city does not exist, add
if (city == null)
{
city = executor.ExecuteNonQuery(new LocationCreateCityDelegate(cityName, region, country));
cityID = city.CityID;
}
else
{
cityID = city.CityID;
}
int attractionID = 0;
//Lookup attraction
Attraction attraction = executor.ExecuteReader(new GetAttractionByNameDelegate(attractionName, cityID));
//If attraction does not exist, add
if (attraction == null)
{
attraction = executor.ExecuteNonQuery(new CreateAttractionDelegate(attractionName, cityID));
attractionID = attraction.AttractionID;
}
else
{
attractionID = attraction.AttractionID;
}
//Add a new attraction ticket
AttractionTicket at = executor.ExecuteNonQuery(new CreateAttractionTicketDelegate(tripID, attractionID, ticketDate, ticketPrice));
MessageBox.Show("Attraction ticket successfully added for attraction " + attractionName);
}
}
private static object ExecuteCommmand(string spName, SqlCommandExecutor executor, params object[] parameterValues)
{
object res;
using (TransactionScope scope = GetRequiredTransactionScope( )) {
DbConnection conn = ConnectionCache.GetConnection( );
using (DbCommand cmd = CreateCommand(conn, spName, parameterValues)) {
res = executor(cmd);
}
scope.Complete( );
}
return(res);
}
// Убираем желтую полоску, т.е, что ему прописали в военнике карту
private void MenuItem_RevertAccountNumberAssigment_OnClick(object sender, RoutedEventArgs e)
{
ViewModel.SelectedPersonFromTeam.IsSelected = false;
var sql = $"EXEC pr_AccountNumberRegisteredChanged \'{ViewModel.SelectedPersonFromTeam.PassportSerial}\', 0 ";
using (var sqlcmd = new SqlCommandExecutor(sql))
sqlcmd.TryExecuteNonQuery(out _);
if (sender is ListView listView)
{
listView.SelectedIndex = -1;
}
}
// Убираем человека из команды
private void MenuItem_RemovePersonFromTeam_OnClick(object sender, RoutedEventArgs e)
{
if (MessageBox.Show("Вы уверены, что хотите убрать из команды призывника?", "Внимание!", MessageBoxButton.YesNo) != MessageBoxResult.Yes)
{
return;
}
using (var sqlcmd = new SqlCommandExecutor($"EXEC pr_DeassignTeam \'{ViewModel.SelectedPersonFromTeam.PassportSerial}\'"))
if (sqlcmd.TryExecuteNonQuery(out _))
{
ViewModel.PersonFromTeamList.Remove(ViewModel.SelectedPersonFromTeam);
}
}
public static IEnumerable <SearchPersonToAssignCard> GetAll(string lastName, string firstName, string patronymic, string passportSerial)
{
var sql = $@"EXEC pr_SearchPersonToAssignCard '{lastName}%','{firstName}%','{patronymic}%','{passportSerial}%'";
using (var sqlcmd = new SqlCommandExecutor(sql))
using (var sqlReader = sqlcmd.ExecuteReader())
{
while (sqlReader.Read())
{
yield return(new SearchPersonToAssignCard(sqlReader));
}
}
}
public static bool TryExecuteNonQuery(this SqlCommandExecutor comm, out int rowsAffected)
{
try
{
rowsAffected = comm.Command.ExecuteNonQuery();
return(true);
}
catch (Exception ex)// when(ex is SqlException)
{
CommonUtils.ShowException(ex);
rowsAffected = 0;
return(false);
}
}
public static bool TryExecuteScalar(this SqlCommandExecutor comm, out object?result)
{
try
{
result = comm.Command.ExecuteScalar();
return(true);
}
catch (Exception ex)
{
CommonUtils.ShowException(ex);
result = default;
return(false);
}
}
IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh)
{
var commandText = "select name from {0}..sysusers where issqlrole = 1 order by name";
commandText = string.Format(commandText, _database.Name);
var connectionString = _database.Databases.Server.ConnectionString;
var executor = new SqlCommandExecutor(connectionString);
var roleNodes = executor.ExecuteReader(new ExecuteReaderRequest(commandText), dataRecord =>
{
var name = dataRecord.GetString(0);
return(new RoleNode(_database, name));
});
return(roleNodes);
}
