public async void SpgSubmissionChangesCsvData_ReturnsProducerAsNew_WhenExistingProducerUploadButNoSubmissionForSameYearAndScheme()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload1 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload1.ComplianceYear = 2016;
memberUpload1.IsSubmitted = false;
modelHelper.CreateProducerAsCompany(memberUpload1, "1111");
var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload2.ComplianceYear = 2016;
modelHelper.CreateProducerAsCompany(memberUpload2, "1111");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload2.Id);
// Assert
Assert.Single(result);
Assert.Equal("New", result[0].ChangeType);
}
}
private void SubmitReminderButton_Click(object sender, Windows.UI.Xaml.RoutedEventArgs e)
{
if (this.ValidateForm())
{
this.PopulateReminderFromForm();
if (this.ReminderToEdit.ReminderID == -1)
{
StoredProcedures.CreateReminder(this.ReminderToEdit.Title, this.ReminderToEdit.ActivateDateAndTime, this.ReminderToEdit.Description);
}
else
{
StoredProcedures.UpdateReminder(this.ReminderToEdit.ReminderID, this.ReminderToEdit.Title, this.ReminderToEdit.ActivateDateAndTime, this.ReminderToEdit.Description, false);
}
UIUtils.GoBack(this, typeof(RemindersPage));
}
}
public Database(string typeName, string server, string name, string connectionString)
{
this.typeName = typeName;
this.server = server;
this.name = name;
this.connectionString = connectionString;
connection = new OdbcConnection(this.connectionString);
_tables = new Tables(this);
_views = new Views(this);
_sequences = new Sequences(this);
_storedprocedures = new StoredProcedures(this);
_functions = new Functions(this);
_modules = new Modules(this);
_mqts = new MQTS(this);
}
public async void SpgSubmissionChangesCsvData_ReturnsProducerAsChanged_WhenRemovedButReaddedPriorToSubmission()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload.ComplianceYear = 2016;
memberUpload.SubmittedDate = new DateTime(2016, 1, 1);
var producer = modelHelper.CreateProducerAsCompany(memberUpload, "1111");
producer.RegisteredProducer.Removed = true;
producer.RegisteredProducer.RemovedDate = new DateTime(2016, 1, 2);
var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload2.ComplianceYear = 2016;
memberUpload2.SubmittedDate = new DateTime(2016, 2, 2);
modelHelper.CreateProducerAsCompany(memberUpload2, "1111");
var memberUpload3 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload3.ComplianceYear = 2016;
memberUpload3.SubmittedDate = new DateTime(2016, 3, 3);
modelHelper.CreateProducerAsCompany(memberUpload3, "1111");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload3.Id);
// Assert
Assert.Equal(2, result.Count);
Assert.Equal("Amended", result[0].ChangeType);
Assert.Equal(new DateTime(2016, 3, 3), result[0].SubmittedDate);
Assert.Equal(string.Empty, result[1].ChangeType);
Assert.Equal(new DateTime(2016, 2, 2), result[1].SubmittedDate);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["ResetToken"] == null || Request.QueryString["SelectorToken"] == null)
{
Response.Redirect("ForgotPassword.aspx");
}
else
{
if (StoredProcedures.CheckIfPasswordResetTokenExpired(Request.QueryString["SelectorToken"]))
{
}
else
{
Response.Redirect("ForgotPassword.aspx");
}
}
}
/// <summary>
/// Overall hash code for the request
/// </summary>
/// <returns></returns>
public override int GetHashCode()
{
var hash = new HashCode();
SortedSet <int> objectHashes = new SortedSet <int>();
Tables.ForEach(c => objectHashes.Add(c.GetHashCode()));
Views.ForEach(c => objectHashes.Add(c.GetHashCode()));
StoredProcedures.ForEach(c => objectHashes.Add(c.GetHashCode()));
foreach (var objHash in objectHashes)
{
hash.Add(objHash);
}
return(hash.ToHashCode());
}
public async void SpgSubmissionChangesCsvData_ReturnsRecordsSortedByProducerRegistrationNumberAndSubmissionDateDescending()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload.ComplianceYear = 2016;
memberUpload.SubmittedDate = new DateTime(2016, 1, 1);
var producer = modelHelper.CreateProducerAsCompany(memberUpload, "1111");
var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload2.ComplianceYear = 2016;
memberUpload2.SubmittedDate = new DateTime(2016, 3, 3);
modelHelper.CreateProducerAsCompany(memberUpload2, "1111");
modelHelper.CreateProducerAsCompany(memberUpload2, "2222");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload2.Id);
// Assert
Assert.Equal(3, result.Count);
Assert.Equal("Amended", result[0].ChangeType);
Assert.Equal("1111", result[0].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 3, 3), result[0].SubmittedDate);
Assert.Equal(string.Empty, result[1].ChangeType);
Assert.Equal("1111", result[1].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 1, 1), result[1].SubmittedDate);
Assert.Equal("New", result[2].ChangeType);
Assert.Equal("2222", result[2].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 3, 3), result[2].SubmittedDate);
}
}
private void SetSite(int siteId)
{
IEnumerable <t_site> sites;
if (ActivityId.HasValue)
{
// Get sites user is allowed to access (view or update).
DataTable userSites = StoredProcedures.GetUserSites(CurrentUserId, ActivityId.Value);
var userSiteIds = userSites.AsEnumerable().Select(r => (int)r["site_id"]).ToArray();
sites = Context.t_site.Where(e => userSiteIds.Contains(e.site_id));
}
else
{
sites = new t_site[] { Context.t_site.Find(siteId) };
}
Sites = sites.OrderBy(e => e.name).ToDictionary(k => k.site_id, v => v.name);
SelectedSiteId = siteId;
}
private void btnContinue_Click(object sender, RoutedEventArgs e)
{
if (this.Validate())
{
// for each setting, select the appropriate option in the database
PageSettings.ForEach(setting =>
{
int settingID = setting.SettingID;
int selectedOptionID = setting.GetSelectedOption().OptionID;
StoredProcedures.SelectOption(settingID, selectedOptionID);
});
// mark the first time setup page as passed in the database
Setting firstTimeSetupSetting = StoredProcedures.QuerySettingByName("_FirstTimeSetupPassed");
firstTimeSetupSetting.SelectOption("true");
StoredProcedures.SelectOption(firstTimeSetupSetting.SettingID, firstTimeSetupSetting.GetSelectedOption().OptionID);
UIUtils.GoToMainPage(this);
}
}
private void SubmitAlarmButton_Click(object sender, Windows.UI.Xaml.RoutedEventArgs e)
{
if (this.ValidateForm())
{
this.PopulateAlarmFromForm();
if (this.AlarmToEdit.AlarmID == -1)
{
StoredProcedures.CreateAlarm(this.AlarmToEdit.Title, this.AlarmToEdit.ActivateDateAndTime);
AlarmAndReminderHelper.ScheduleAlarm(StoredProcedures.QueryLatestAlarm());
}
else
{
AlarmAndReminderHelper.RescheduleAlarm(this.AlarmToEdit);
StoredProcedures.UpdateAlarm(this.AlarmToEdit.AlarmID, this.AlarmToEdit.Title, this.AlarmToEdit.ActivateDateAndTime, false);
}
UIUtils.GoBack(this, typeof(AlarmsPage));
}
}
public void usp_MusteriSorgulaMusteriKeyIleTest()
{
Guid pk = Guid.NewGuid();
MusteriDal dal = new MusteriDal();
string ad = "DenemeAd";
string soyad = "DenemeSoyad";
Musteri m = new Musteri();
m.MusteriKey = pk;
m.Adi = ad;
m.Soyadi = soyad;
dal.Ekle(m);
DataTable dt = StoredProcedures.MusteriSorgulaMusteriKeyIle(pk);
Assert.AreEqual(dt.Rows[0][Musteri.PropertyIsimleri.Adi].ToString(), ad);
Assert.AreEqual(dt.Rows[0][Musteri.PropertyIsimleri.Soyadi].ToString(), soyad);
}
public DatabaseObjectInfo RemoveObjectById(string id)
{
var tres = Tables.FirstOrDefault(o => o.ObjectId == id);
if (tres != null)
{
Tables.Remove(tres);
return(tres);
}
var vres = Views.FirstOrDefault(o => o.ObjectId == id);
if (vres != null)
{
Views.Remove(vres);
return(vres);
}
var pres = StoredProcedures.FirstOrDefault(o => o.ObjectId == id);
if (pres != null)
{
StoredProcedures.Remove(pres);
return(pres);
}
var fres = Functions.FirstOrDefault(o => o.ObjectId == id);
if (fres != null)
{
Functions.Remove(fres);
return(fres);
}
var rres = Triggers.FirstOrDefault(o => o.ObjectId == id);
if (rres != null)
{
Triggers.Remove(rres);
return(rres);
}
return(null);
}
/// <summary>
/// Invoked when the application is launched normally by the end user. Other entry points
/// will be used such as when the application is launched to open a specific file.
/// </summary>
/// <param name="e">Details about the launch request and process.</param>
protected override async void OnLaunched(LaunchActivatedEventArgs e)
{
StoredProcedures.CreateDatabase().Wait();
Frame rootFrame = Window.Current.Content as Frame;
// Do not repeat app initialization when the Window already has content,
// just ensure that the window is active
if (rootFrame == null)
{
// Create a Frame to act as the navigation context and navigate to the first page
rootFrame = new Frame();
rootFrame.NavigationFailed += OnNavigationFailed;
if (e.PreviousExecutionState == ApplicationExecutionState.Terminated)
{
//TODO: Load state from previously suspended application
}
// Place the frame in the current Window
Window.Current.Content = rootFrame;
}
if (e.PrelaunchActivated == false)
{
if (rootFrame.Content == null)
{
// When the navigation stack isn't restored navigate to the first page,
// configuring the new page by passing required information as a navigation
// parameter
var passedFirstTimeSetupSetting = StoredProcedures.QuerySettingByName("_FirstTimeSetupPassed");
if (passedFirstTimeSetupSetting.GetSelectedOption().DisplayName == "true")
{
rootFrame.Navigate(typeof(MainPage), e.Arguments);
}
else
{
rootFrame.Navigate(typeof(FirstTimeSetup), e.Arguments);
}
}
// Ensure the current window is active
Window.Current.Activate();
}
}
public void DropMethods()
{
Console.WriteLine("Dropping methods");
Console.WriteLine("Dropping procedures");
StoredProcedures.ForEach(p => p.Drop());
Console.WriteLine("Dropping views");
Views.ForEach(v => v.Drop());
Console.WriteLine("Dropping user defined functions");
UserDefinedFunctions.ForEach(f => f.Drop());
Console.WriteLine("Dropping triggers");
TableTriggers.ForEach(t => t.Drop());
Console.WriteLine("Dropping user defined functions");
UserDefinedAggregates.ForEach(t => t.Drop());
}
public override void PerformAction()
{
//get list of searchable websites from database
List <SearchableWebsite> allSearchableWebsites = StoredProcedures.QueryAllSearchableWebsites();
//need to check if user provided a website to search
bool isUserProvidedWebsiteSearch = false;
//go through list of searchable websites. return true if user included the searchable website in search
//this will also set the website if there is a match
isUserProvidedWebsiteSearch = GetActionFromCommand(allSearchableWebsites);
string searchParameters;
string searchQuery;
if (isUserProvidedWebsiteSearch)
{
//find what is wanted to be searched and concatenate with + for end of url
searchParameters = GetSearchParameters(isUserProvidedWebsiteSearch);
searchQuery = BuildSearchQuery(desiredSearchableWebsite, searchParameters);
//launch browser. this will be done with the default browser
LaunchSearch(searchQuery);
}
else
{
//sets desiredSearchEngine, which is the default selected in settings
GetDefaultSearchEngine();
searchParameters = GetSearchParameters(isUserProvidedWebsiteSearch);
searchQuery = BuildSearchQuery(desiredSearchEngine, searchParameters);
//launch browser. this will be done with the default browser
LaunchSearch(searchQuery);
}
// show a link to the search
this.ClearArea();
var linkElement = new HyperlinkButton();
linkElement.Content = $"{searchParameters} on {(desiredSearchableWebsite != null ? desiredSearchableWebsite.Name : desiredSearchEngine?.Name)}";
linkElement.NavigateUri = new Uri(searchQuery);
linkElement.FontSize = 24;
RelativePanel.SetAlignHorizontalCenterWithPanel(linkElement, true);
RelativePanel.SetAlignVerticalCenterWithPanel(linkElement, true);
this.DynamicArea.Children.Add(linkElement);
// announce to the user that we're searching for something
TextToSpeechEngine.SpeakText(this.MediaElement, $"Sure, searching for {linkElement.Content}");
}
/// <summary>
/// Retrieves a paginated list of Platforms as a JSON Array.
/// Supports the following query string parameters:
/// page: The page number to retrieve, from 1 to n
/// items: The max number of rows to retrieve per page, for example 10
/// search: An optional parameter to specify the text to search platform names
/// </summary>
/// <returns>JSON Array of Platforms</returns>
public dynamic Get()
{
var qs = Request.GetQueryNameValuePairs().ToDictionary(m => m.Key, m => m.Value);
var page = 1;
if (qs.ContainsKey("page"))
{
page = Convert.ToInt32(qs["page"]);
}
page = Math.Max(1, page);
var totalItems = 0;
var itemsPerPage = 3;
if (qs.ContainsKey("items"))
{
itemsPerPage = Convert.ToInt32(qs["items"]);
}
itemsPerPage = Math.Max(3, itemsPerPage);
var totalPages = 1;
var search = qs.ContainsKey("search") ? qs["search"] : "";
totalItems = StoredProcedures.CountPlatforms(search);
totalPages = (int)Math.Ceiling((double)totalItems / (double)itemsPerPage);
page = Math.Min(totalPages, page);
var min = 1 + itemsPerPage * (page - 1);
var max = min + itemsPerPage - 1;
return(new
{
page = page,
total = totalPages,
platforms = from p in StoredProcedures.SearchPlatforms(search, min, max)
select new { id = p.ID, name = p.Name, type = p.Type, image = p.Image }
});
}
private void DeleteAlarm()
{
Alarm alarmToDelete = GetAlarmForClosestMatchToPassedDate();
if (alarmToDelete != null)
{
StoredProcedures.DeleteAlarm(alarmToDelete.AlarmID);
string message = new SSMLBuilder().Prosody("Alright, cancelled your alarm.", contour: "(0%, +5%) (10%,-5%) (50%,+1%) (80%,+5%)").Build();
TextToSpeechEngine.SpeakInflectedText(this.MediaElement, message);
ShowMessage($"Successfully deleted alarm {alarmToDelete.Title}");
}
else
{
this.ClearArea();
// no alarm found, tell the user
string message = new SSMLBuilder().Prosody("Sorry, but I wasn't able to find an alarm for that time.", contour: "(0%,+5%) (1%,-5%) (2%,+1%) (3%,-1%) (10%,+1%) (20%,-1%) (30%,+1%) (40%,-1%) (50%,+1%) (80%,-1%)").Build();
TextToSpeechEngine.SpeakInflectedText(this.MediaElement, message);
this.ShowMessage("Sorry, but I wasn't able to find an alarm for that time.");
}
}
private void SubmitReminderButton_Click(object sender, Windows.UI.Xaml.RoutedEventArgs e)
{
if (this.ValidateForm())
{
this.PopulateReminderFromForm();
if (this.ReminderToEdit.ReminderID == -1)
{
StoredProcedures.CreateReminder(this.ReminderToEdit.Title, this.ReminderToEdit.ActivateDateAndTime, this.ReminderToEdit.Description);
// schedule the notification for it
AlarmAndReminderHelper.ScheduleReminder(StoredProcedures.QueryLatestReminder());
}
else
{
StoredProcedures.UpdateReminder(this.ReminderToEdit.ReminderID, this.ReminderToEdit.Title, this.ReminderToEdit.ActivateDateAndTime, this.ReminderToEdit.Description, false);
// reschedule the reminder
AlarmAndReminderHelper.RescheduleReminder(this.ReminderToEdit);
}
UIUtils.GoBack(this, typeof(RemindersPage));
}
}
public void FillSource()
{
var TempStoredProcedures = new StoredProcedures();
var TempSource = new Source("My Source");
var ConstraintsToAdd = new List <dynamic>
{
new Dynamo(new
{
NAME = "Constraint A",
DEFINITION = "Definition A",
SCHEMA = ""
})
};
TempStoredProcedures.FillSource(ConstraintsToAdd, TempSource);
var Constraint = TempSource.StoredProcedures[0];
Assert.Equal("Definition A", Constraint.Definition);
Assert.Equal("Constraint A", Constraint.Name);
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
if (!ActionRouter.IsSetup)
{
ActionRouter.SetUp();
}
// if bob has not been introduced, then introduce him now
var hasIntroducedBob = StoredProcedures.QuerySettingByName("_ToldUserHowToUseBob");
if (hasIntroducedBob.GetSelectedOption() != null && hasIntroducedBob.GetSelectedOption().DisplayName == "false")
{
this.IntroduceBob();
hasIntroducedBob.SelectOption("true");
StoredProcedures.SelectOption(hasIntroducedBob.SettingID, hasIntroducedBob.GetSelectedOption().OptionID);
}
AudioPlayer.Start();
// if the user has elected to have speech recognition turned on, then request for microphone permissions
this.RequestMicrophoneAcessIfUserWantsVoiceDetection();
}
private void DeleteReminder()
{
Reminder reminderToDelete = this.GetReminderForClosestMatchToPassedDate();
if (reminderToDelete != null)
{
StoredProcedures.DeleteReminder(reminderToDelete.ReminderID);
string message = new SSMLBuilder().Prosody("Successfully deleted reminder.", contour: "(1%,+2%) (50%,-1%) (80%,-1%)").Build();
TextToSpeechEngine.SpeakInflectedText(this.MediaElement, message);
this.ShowMessage($"Successfully deleted reminder {reminderToDelete.Title}");
}
else
{
this.ClearArea();
// no reminder found, tell the user
string message = new SSMLBuilder().Prosody("Sorry, but I wasn't able to find a reminder for that time.", contour: "(0%,+5%) (1%,-5%) (2%,+1%) (3%,-1%) (10%,+1%) (20%,-1%) (30%,+1%) (40%,-1%) (50%,+1%) (80%,-1%)").Build();
TextToSpeechEngine.SpeakInflectedText(this.MediaElement, message);
this.ShowMessage("Sorry, but I wasn't able to find a reminder for that time.");
}
}
/// <summary>
/// Attempts to find the reminder whose activation date matches the passed date in this object's commandString
/// </summary>
/// <returns>the found reminder, which may be null if an reminder wasn't found</returns>
private Reminder GetReminderForClosestMatchToPassedDate()
{
List <Reminder> reminders = StoredProcedures.QueryAllUnexpiredReminders();
// get the date from the command text
var now = DateTime.Now;
// putting in the datetime to start at midnight of this day so that times don't get shifted around. I know it's marked as only used for tests, but it's what I had to do
DateTime commandDateTime = DateTimeParser.ParseDateTimeFromText(this.CommandString, new DateTime(now.Year, now.Month, now.Day, 0, 0, 0));
Reminder foundReminder = null;
// if there's only one reminder, that's the one we're getting
if (reminders.Count == 1)
{
foundReminder = reminders[0];
}
else
{
// find the first reminder whose hour and minute matches the command date time
foundReminder = reminders.Find(reminder => reminder.ActivateDateAndTime.Hour == commandDateTime.Hour && reminder.ActivateDateAndTime.Minute == commandDateTime.Minute);
}
return(foundReminder);
}
private async Task <Reminder> NewReminderAsync()
{
Reminder createdReminder = await this.CreateReminderAsync();
// if the reminder is null, then don't do anything
if (createdReminder != null)
{
// insert the reminder into the database
StoredProcedures.CreateReminder(createdReminder.Title, createdReminder.ActivateDateAndTime, createdReminder.Description);
// schedule a toast notification for the reminder
AlarmAndReminderHelper.ScheduleReminder(StoredProcedures.QueryLatestReminder());
string mainPart = $"Alright, reminder set for ";
string datePart = createdReminder.ActivateDateAndTime.ToString("MMM d");
string timePart = createdReminder.ActivateDateAndTime.ToString("h:mm tt");
string rawSSML = new SSMLBuilder().Add(mainPart).SayAs(datePart, SSMLBuilder.SayAsTypes.DATE).Add(" at ").SayAs(timePart, SSMLBuilder.SayAsTypes.TIME).BuildWithoutWrapperElement();
string prosodySSML = new SSMLBuilder().Prosody(rawSSML, pitch: "+5%", contour: "(10%,+5%) (50%,-5%) (80%,-5%)").Build();
TextToSpeechEngine.SpeakInflectedText(this.MediaElement, prosodySSML);
}
return(createdReminder);
}
private Alarm NewAlarm()
{
this.ClearArea();
Alarm createdAlarm = this.CreateAlarm();
// the alarm can be null if the user did not provide a date
if (createdAlarm != null)
{
// insert the alarm into the database
StoredProcedures.CreateAlarm(createdAlarm.Title, createdAlarm.ActivateDateAndTime);
// schedule a toast notification for the alarm
AlarmAndReminderHelper.ScheduleAlarm(StoredProcedures.QueryLatestAlarm());
string mainPart = $"Alright, alarm set for ";
string datePart = createdAlarm.ActivateDateAndTime.ToString("MMM d");
string timePart = createdAlarm.ActivateDateAndTime.ToString("h:mm tt");
string rawSSML = new SSMLBuilder().Add(mainPart).SayAs(datePart, SSMLBuilder.SayAsTypes.DATE).Add(" at ").SayAs(timePart, SSMLBuilder.SayAsTypes.TIME).BuildWithoutWrapperElement();
string prosodySSML = new SSMLBuilder().Prosody(rawSSML, pitch: "+5%", contour: "(10%,+5%) (50%,-5%) (80%,-5%)").Build();
TextToSpeechEngine.SpeakInflectedText(this.MediaElement, prosodySSML);
}
return(createdAlarm);
}
/// <summary>
/// Insert the specified <paramref name="maintainable"/> as dataflow if <paramref name="maintainable"/> is final.
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="maintainable">
/// The <see cref="IDataStructureObject"/>.
/// </param>
/// <param name="artefactStatus">
/// The artefact status.
/// </param>
/// <remarks>
/// This method inserts the specified data structure object (DSD) as dataflow. It is not an error.
/// </remarks>
private void InsertAsDataflow(DbTransactionState state, IDataStructureObject maintainable, ArtefactImportStatus artefactStatus)
{
if (maintainable.IsFinal.IsTrue)
{
var dataflowStoredProcedure = new StoredProcedures().InsertDataflow;
var artefactFinalStatus = GetFinalStatus(state, new StructureReferenceImpl(maintainable.AsReference, SdmxStructureEnumType.Dataflow));
if (artefactFinalStatus.IsEmpty)
{
this.InsertArtefactInternal(
state,
maintainable,
dataflowStoredProcedure,
command =>
{
dataflowStoredProcedure.CreateDsdIdParameter(command).Value = artefactStatus.PrimaryKeyValue;
dataflowStoredProcedure.CreateMapSetIdParameter(command);
});
}
}
}
[HttpGet("GetShowCast/{showID}/")] //Route: WebAPI/TermProject/GetShowCast
public List <Roles> GetShowCast(int showID)
{
DataSet myDS = new DataSet();
StoredProcedures stoPros = new StoredProcedures();
myDS = stoPros.getCastByShowID(showID);
DBConnect objDB = new DBConnect();
Roles role = new Roles();
List <Roles> dpts = new List <Roles>();
foreach (DataRow dr in myDS.Tables[0].Rows)
{
role = new Roles();
role.role = dr["Role"].ToString();
role.actorImage = dr["Actor_Image"].ToString();
role.actorName = dr["Actor_Name"].ToString();
role.actorID = int.Parse(dr["Actor_ID"].ToString());
dpts.Add(role);
}
return(dpts);
}
public ArrayList ListStoredProcedures()
{
ArrayList SPList = new ArrayList();
var fServer = new SQLServer();
fServer.Connect(server(), user(), pass());
try
{
Liste_procedure = fServer.Databases.Item(bd(), null).StoredProcedures;
foreach (StoredProcedure sp in Liste_procedure)
{
if (!SPList.Contains(sp.Owner + "." + sp.Name))
{
SPList.Add(sp.Owner + "." + sp.Name);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(SPList);
}
public static void dump(int companyId, Action<object,string> saveData, string cs, List<string> queries) {
var handler = new StoredProcedures(companyId, cs);
handler.queries = queries;
queries.Add("*** lmsql_CompanyDepartment");
saveData(handler._lmsql_CompanyDepartment(), "lmsql_CompanyDepartment");
queries.Add("*** lmsql_CompanyDepartmentCrsUser");
saveData(handler._lmsql_CompanyDepartmentCrsUser(), "lmsql_CompanyDepartmentCrsUser");
queries.Add("*** lmsql_CourseUser");
saveData(handler._lmsql_CourseUser(), "lmsql_CourseUser");
queries.Add("*** lmsql_Modules");
saveData(handler._lmsql_Modules(), "lmsql_Modules");
queries.Add("*** lmsql_MonthInfo");
saveData(handler._lmsql_MonthInfo(), "lmsql_MonthInfo");
queries.Add("*** lmsql_ScoreIntervals");
saveData(handler._lmsql_ScoreIntervals(), "lmsql_ScoreIntervals");
queries.Add("*** lmsql_SecIntervals");
saveData(handler._lmsql_SecIntervals(), "lmsql_SecIntervals");
queries.Add("*** lmsql_Time");
saveData(handler._lmsql_Time(), "lmsql_Time");
saveData(StoredProcedures.TocFlat.fromResources().Cast<StoredProcedures.TocFlat>().ToArray(), "lmsql_TocFlat");
queries.Add("*** lmsql_YearInfo");
saveData(handler._lmsql_YearInfo(), "lmsql_YearInfo");
}
private async Task <string> GetDirectionsLink(string destination)
{
string query = null;
Dictionary <string, double> coordinates = await LocationProvider.GetLatitudeAndLongitude();
if (coordinates != null)
{
double latitude = coordinates["latitude"];
double longitude = coordinates["longitude"];
Setting setting = StoredProcedures.QuerySettingByName("Map Provider");
MapProvider mapProvider = StoredProcedures.QueryMapProvider(setting.GetSelectedOption().DisplayName);
query = mapProvider.BaseURL.ToString();
query = query.Replace("{Latitude}", latitude.ToString()).Replace("{Longitude}", longitude.ToString()).Replace("{Destination}", HttpUtility.UrlEncode(destination));
}
else
{
// have bob tell the user that location information couldn't be retrieved
string message = "Sorry, but I couldn't get your location. In order to get you directions to a place, I need your location to use for the starting point.";
TextToSpeechEngine.SpeakText(this.MediaElement, message);
this.ShowMessage(message);
}
return(query);
}
/// <summary>
/// Carrega todos os objetos do assembly do banco de dados
/// </summary>
private void LoadObjects()
{
if (Assembly == null)
{
throw new Exception("Favor preencher a propriedade Assembly da classe de Banco de dados");
}
foreach (Type myT in Assembly.GetTypes())
{
if (myT.BaseType == null | myT.IsAbstract)
{
continue;
}
if (myT.BaseType == typeof(TableAdapter) || myT.BaseType.Name == typeof(TableAdapterDomain <Object>).Name)
{
var table = (TableAdapter)GetInstanceObject(null, myT);
Tables.Add(table);
}
else if (myT.BaseType.IsAbstract && myT.BaseType.BaseType == typeof(TableAdapter))
{
var table = (TableAdapter)GetInstanceObject(null, myT);
Tables.Add(table);
}
else if (myT.BaseType == typeof(StoredProcedure))
{
var mySp = GetInstanceObject(null, myT) as StoredProcedure;
Debug.Assert(mySp != null, "mySp != null");
mySp.LoadFiles(Assembly);
StoredProcedures.Add(mySp);
}
}
Tables.Add(new AppVersion {
DBName = DataBaseName
});
}
protected void btnEditReviewSubmit_OnClick(object sender, EventArgs e)
{
if (editReviewDescription.Value == null || editReviewDescription.Value == "" || editReviewDescription.Value == " ")
{
lblError.Text = "Please fill out the description";
}
lblError.Text = Session["ReviewID"].ToString();
lblTitleDescription.Text = ddlEditRating.SelectedValue;
Label2.Text = editReviewDescription.Value;
int rating = Convert.ToInt32(ddlEditRating.SelectedValue);
int id = Convert.ToInt32(Session["ReviewID"]);
int success = StoredProcedures.editReview(id, rating, editReviewDescription.Value);
if (success >= 1)
{
lblSuccessReview.Text = "Review edited successfully!";
lblError.Text = "";
addReviewLink.Visible = false;
addReviewPanel.Visible = false;
editReviewPanel.Visible = false;
bindGridview();
}
else
{
editReviewPanel.Visible = false;
addReviewLink.Visible = false;
addReviewPanel.Visible = false;
Response.Write("<script>alert('Sorry you can only edit your review once every session, refresh and try again')</script>");
lblError.Text = Session["ReviewID"].ToString();
Label1.Text = ddlEditRating.SelectedValue;
Label2.Text = editReviewDescription.Value;
}
hideNonMemberControls();
}
//public ColumnInfo FindColumn(string table, string column)
//{
// var tbl = FindTable(table);
// if (tbl == null) return null;
// return tbl.Columns.FirstOrDefault(c => String.Compare(c.Name, column, true) == 0);
//}
public DatabaseObjectInfo FindObjectById(string id)
{
DatabaseObjectInfo res;
res = Tables.FirstOrDefault(o => o.ObjectId == id);
if (res != null)
{
return(res);
}
res = Views.FirstOrDefault(o => o.ObjectId == id);
if (res != null)
{
return(res);
}
res = StoredProcedures.FirstOrDefault(o => o.ObjectId == id);
if (res != null)
{
return(res);
}
res = Functions.FirstOrDefault(o => o.ObjectId == id);
if (res != null)
{
return(res);
}
res = Triggers.FirstOrDefault(o => o.ObjectId == id);
if (res != null)
{
return(res);
}
return(null);
}
private bool DetectSingleSchema()
{
if (Tables.Any(o => o.Schema != DefaultSchema))
{
return(false);
}
if (Views.Any(o => o.Schema != DefaultSchema))
{
return(false);
}
if (StoredProcedures.Any(o => o.Schema != DefaultSchema))
{
return(false);
}
if (Functions.Any(o => o.Schema != DefaultSchema))
{
return(false);
}
if (Triggers.Any(o => o.Schema != DefaultSchema))
{
return(false);
}
return(true);
}
/// <summary>
/// Gets a data row.
/// </summary>
/// <param name="storedProcedure">The stored procedure as an enum.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>The Data Row returned as a result of the stored procedure</returns>
public DataRow GetDataRow(StoredProcedures storedProcedure, params Object[] parameters)
{
return GetDataRow(storedProcedure.ToString(), parameters);
}
/// <summary>
/// Gets a data table.
/// </summary>
/// <param name="storedProcedure">The stored procedure as an enum.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>The Data Table returned as a result of the stored procedure.</returns>
public DataTable GetDataTable(StoredProcedures storedProcedure, out int totalResults, params Object[] parameters)
{
totalResults = 0;
return GetDataTable(storedProcedure.ToString(), out totalResults, parameters);
}
public static IEnumerable<TimeInterval> getTimeIntervals(StoredProcedures proc) {
var periods = proc.getInterval(IntType.time, () => Intervals.TimeDefault(), cfg => cfg.Periods);
var min = periods.First().From; var max = periods.Last().From;
foreach (var r in Enumerable.Range(0, 50/*let*/).SelectMany(y => Enumerable.Range(0, 12).Select(m => Admin.Interval.MonthCode(m + 1, y + 2013))).Where(mc => mc >= min && mc <= max)) {
yield return new TimeInterval() {
period = periods.Last(p => r >= p.From),
monthCode = r,
};
}
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="storedProcedure">The stored procedure as an enum.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>the Int64 result of the stored procedure</returns>
public Int64 ExecuteScalar(StoredProcedures storedProcedure, params Object[] parameters)
{
Object result = null;
Int64 id = -1;
try
{
result = ExecuteScalarObject(storedProcedure, parameters);
if (result != null)
id = Int64.Parse(result.ToString());
}
catch (Exception ex)
{
throw new Exceptions.DataLayerException("Error executing the " + storedProcedure.ToString() + " stored procedure.", ex);
}
finally
{
if (Connection.State != ConnectionState.Closed) Connection.Close();
Connection.Dispose();
}
return id;
}
public async void SpgSubmissionChangesCsvData_ReturnsRecordsSortedByProducerRegistrationNumberAndSubmissionDateDescending()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload.ComplianceYear = 2016;
memberUpload.SubmittedDate = new DateTime(2016, 1, 1);
var producer = modelHelper.CreateProducerAsCompany(memberUpload, "1111");
var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload2.ComplianceYear = 2016;
memberUpload2.SubmittedDate = new DateTime(2016, 3, 3);
modelHelper.CreateProducerAsCompany(memberUpload2, "1111");
modelHelper.CreateProducerAsCompany(memberUpload2, "2222");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload2.Id);
// Assert
Assert.Equal(3, result.Count);
Assert.Equal("Amended", result[0].ChangeType);
Assert.Equal("1111", result[0].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 3, 3), result[0].SubmittedDate);
Assert.Equal(string.Empty, result[1].ChangeType);
Assert.Equal("1111", result[1].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 1, 1), result[1].SubmittedDate);
Assert.Equal("New", result[2].ChangeType);
Assert.Equal("2222", result[2].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 3, 3), result[2].SubmittedDate);
}
}
public async void SpgSubmissionChangesCsvData_ReturnsProducerAsChanged_AndDetailsOfPreviousAndMostRecentSubmittedUploadAffectingProducer_ForEachProducer()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload.ComplianceYear = 2016;
memberUpload.SubmittedDate = new DateTime(2016, 1, 1);
modelHelper.CreateProducerAsCompany(memberUpload, "1111");
modelHelper.CreateProducerAsCompany(memberUpload, "2222");
// Member upload does not affect producer 2222
var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload2.ComplianceYear = 2016;
memberUpload2.SubmittedDate = new DateTime(2016, 1, 2);
modelHelper.CreateProducerAsCompany(memberUpload2, "1111");
var memberUpload3 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload3.ComplianceYear = 2016;
memberUpload3.SubmittedDate = new DateTime(2016, 2, 2);
modelHelper.CreateProducerAsCompany(memberUpload3, "1111");
modelHelper.CreateProducerAsCompany(memberUpload3, "2222");
// Member upload which will be ignored as it is submitted after
var memberUpload4 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload4.ComplianceYear = 2016;
memberUpload4.SubmittedDate = new DateTime(2016, 3, 3);
modelHelper.CreateProducerAsCompany(memberUpload4, "1111");
modelHelper.CreateProducerAsCompany(memberUpload4, "2222");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload3.Id);
// Assert
Assert.Equal(4, result.Count);
Assert.Equal("Amended", result[0].ChangeType);
Assert.Equal("1111", result[0].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 2, 2), result[0].SubmittedDate);
Assert.Equal(string.Empty, result[1].ChangeType);
Assert.Equal("1111", result[1].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 1, 2), result[1].SubmittedDate);
Assert.Equal("Amended", result[2].ChangeType);
Assert.Equal("2222", result[2].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 2, 2), result[2].SubmittedDate);
Assert.Equal(string.Empty, result[3].ChangeType);
Assert.Equal("2222", result[3].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 1, 1), result[3].SubmittedDate);
}
}
public async void SpgSubmissionChangesCsvData_ReturnsProducerAsNew_ProducerRegisteredForDifferentSchemeWithinSameYear_ButFirstSubmissionForCurrentScheme()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload.ComplianceYear = 2016;
modelHelper.CreateProducerAsCompany(memberUpload, "1111", "B2B");
var scheme2 = modelHelper.CreateScheme();
var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme2);
memberUpload2.ComplianceYear = 2016;
modelHelper.CreateProducerAsCompany(memberUpload2, "1111", "B2C");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload2.Id);
// Assert
Assert.Single(result);
Assert.Equal("New", result[0].ChangeType);
}
}
public void reset()
{
_tables = new Tables(this);
_views = new Views(this);
_sequences = new Sequences(this);
_storedprocedures = new StoredProcedures(this);
_functions = new Functions(this);
_modules = new Modules(this);
_mqts = new MQTS(this);
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="storedProcedure">The stored procedure as an enum.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>the Object result of the stored procedure</returns>
public Object ExecuteScalarObject(StoredProcedures storedProcedure, params Object[] parameters)
{
MySqlCommand command = null;
Object result = null;
try
{
command = new MySqlCommand(storedProcedure.ToString(), _connection);
command.CommandType = CommandType.StoredProcedure;
for (Int32 index = 0; index <= (parameters.Length - 1); index = index + 2)
{
Object value;
if (parameters[index + 1] == null) value = DBNull.Value;
else value = parameters[index + 1];
command.Parameters.AddWithValue(parameters[index].ToString(), value);
}
result = command.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exceptions.DataLayerException("Error executing the " + storedProcedure.ToString() + " stored procedure.", ex);
}
finally
{
if (Connection.State != ConnectionState.Closed) Connection.Close();
Connection.Dispose();
}
return result;
}
public async void SpgSubmissionChangesCsvData_ReturnsProducerAsChanged_WhenRemovedButReaddedPriorToSubmission()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload.ComplianceYear = 2016;
memberUpload.SubmittedDate = new DateTime(2016, 1, 1);
var producer = modelHelper.CreateProducerAsCompany(memberUpload, "1111");
producer.RegisteredProducer.Removed = true;
producer.RegisteredProducer.RemovedDate = new DateTime(2016, 1, 2);
var memberUpload2 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload2.ComplianceYear = 2016;
memberUpload2.SubmittedDate = new DateTime(2016, 2, 2);
modelHelper.CreateProducerAsCompany(memberUpload2, "1111");
var memberUpload3 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload3.ComplianceYear = 2016;
memberUpload3.SubmittedDate = new DateTime(2016, 3, 3);
modelHelper.CreateProducerAsCompany(memberUpload3, "1111");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload3.Id);
// Assert
Assert.Equal(2, result.Count);
Assert.Equal("Amended", result[0].ChangeType);
Assert.Equal(new DateTime(2016, 3, 3), result[0].SubmittedDate);
Assert.Equal(string.Empty, result[1].ChangeType);
Assert.Equal(new DateTime(2016, 2, 2), result[1].SubmittedDate);
}
}
public async void SpgSubmissionChangesCsvData_ReturnsProducerAsChanged_AndDetailsOfPreviousSubmittedUpload_AndIgnoresUnsubmittedUpload()
{
using (var database = new DatabaseWrapper())
{
// Arrange
ModelHelper modelHelper = new ModelHelper(database.Model);
var scheme = modelHelper.CreateScheme();
var memberUpload = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload.ComplianceYear = 2016;
memberUpload.SubmittedDate = new DateTime(2016, 1, 1);
modelHelper.CreateProducerAsCompany(memberUpload, "1111");
// Un-submitted member upload, which should be ignored
var memberUpload2 = modelHelper.CreateMemberUpload(scheme);
memberUpload2.ComplianceYear = 2016;
memberUpload2.IsSubmitted = false;
modelHelper.CreateProducerAsCompany(memberUpload2, "1111");
var memberUpload3 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload3.ComplianceYear = 2016;
memberUpload3.SubmittedDate = new DateTime(2016, 3, 3);
modelHelper.CreateProducerAsCompany(memberUpload3, "1111");
// Member upload which will be ignored as it is submitted after
var memberUpload4 = modelHelper.CreateSubmittedMemberUpload(scheme);
memberUpload4.ComplianceYear = 2016;
memberUpload4.SubmittedDate = new DateTime(2016, 4, 4);
modelHelper.CreateProducerAsCompany(memberUpload4, "1111");
database.Model.SaveChanges();
StoredProcedures storedProcedures = new StoredProcedures(database.WeeeContext);
// Act
var result = await storedProcedures.SpgSubmissionChangesCsvData(memberUpload3.Id);
// Assert
Assert.Equal(2, result.Count);
Assert.Equal("Amended", result[0].ChangeType);
Assert.Equal("1111", result[0].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 3, 3), result[0].SubmittedDate);
Assert.Equal(string.Empty, result[1].ChangeType);
Assert.Equal("1111", result[1].ProducerRegistrationNumber);
Assert.Equal(new DateTime(2016, 1, 1), result[1].SubmittedDate);
}
}
