private string ScriptTableSchema()
{
string sqlScript = "";
StringBuilder sb = new StringBuilder();
ScriptingOptions options = new ScriptingOptions();
options.NoCollation = true;
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.IncludeIfNotExists = true;
options.Triggers = true;
options.SchemaQualify = true;
StringCollection coll = SelectedTable.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
sb.AppendLine("GO");
sqlScript = sb.ToString();
return(sqlScript);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM ModificationLog where 1=1");
if (SearchText != "")
{
//sql.Add("and (1=0"); // custom sql
//sql.Add("or ([--Field--] like ", SearchText.SqlizeLike(), ")"); // custom sql
//sql.Add(")"); // custom sql
//sql.AddKeywordSearch(SearchText, "FirstName,LastName,Email", true); // search more than one field
sql.AddKeywordSearch(SearchText, new Models.ModificationLog().GetNameField().Name, true); // just search by name
}
// handle an fk (rename fkid, then uncomment)
//sql.Add("and landingPageID=", landingPageID);
//sql.AddRawSqlString("and [extra raw sql here]");
var weekEnding = WeekBeginning.AddDays(7);
sql.Add("and UpdateDate >= ", WeekBeginning.SqlizeDate(), "and UpdateDate < ", weekEnding.SqlizeDate());
if (SelectedTable != "(all)")
{
sql.Add("and TableName = ", SelectedTable.SqlizeText());
}
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.ModificationLog().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
private async void ImportTableAudio(object obj)
{
await Task.Run(() =>
{
ImportMedia(SelectedTable, ImportMediaType.TableAudio);
SelectedTable.RunAudit();
});
}
private async void ImportDMD(object obj)
{
await Task.Run(() =>
{
ImportMedia(SelectedTable, ImportMediaType.Dmd, Data.MediaLocation.ConvertOnImport);
SelectedTable.RunAudit();
});
}
private void PreviewPlayfield(object obj)
{
if (SelectedTable == null)
{
return;
}
PreviewMusicVideo(SelectedTable.Playfield);
SelectedTable.RunAudit();
}
private void PreviewLaunchAudio(object obj)
{
if (SelectedTable == null)
{
return;
}
PreviewMusicVideo(SelectedTable.LMusic);
SelectedTable.RunAudit();
}
private void PreviewWheel(object obj)
{
if (SelectedTable == null)
{
return;
}
PreviewImage(SelectedTable.Wheel);
SelectedTable.RunAudit();
}
private void PreviewDMD(object obj)
{
if (SelectedTable == null)
{
return;
}
PreviewMusicVideo(SelectedTable.DMD);
SelectedTable.RunAudit();
}
private void PreviewBackglass(object obj)
{
if (SelectedTable == null)
{
return;
}
PreviewMusicVideo(SelectedTable.Backglass);
SelectedTable.RunAudit();
}
private void LoadTableData()
{
IsLoading = true;
StringBuilder sb = new StringBuilder();
ScriptingOptions options = new ScriptingOptions();
options.NoCollation = true;
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.IncludeIfNotExists = true;
options.Triggers = true;
options.SchemaQualify = true;
StringCollection coll = SelectedTable.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
sb.AppendLine("GO");
SQLScript = sb.ToString();
DataSet ds = new DataSet();
ds = SelectedDB.ExecuteWithResults(GetTableSelectStatement());
TableData = ds;
var results = ds.Tables[0].Select().Skip(2).Take(50).ToList <object>();
DataRow tmp = results[5] as DataRow;
IsLoading = false;
}
private async Task <List <SelectedTable> > GetTables(string connectionString)
{
var result = new List <SelectedTable>();
using (var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
var command = new SqlCommand("SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables", connection);
var reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
var selectedTable = new SelectedTable
{
IsSelected = true
};
var tableDefinition = new TableDefinition
{
TableSchema = reader.GetString(0),
TableName = reader.GetString(1),
ColumnDefinitions = new List <ColumnDefinition>()
};
selectedTable.TableDefinition = tableDefinition;
await GetColumnDefinitions(tableDefinition.TableName, connectionString, tableDefinition.ColumnDefinitions);
result.Add(selectedTable);
}
reader.Close();
}
return(result);
}
/// <summary>A factory for a MappedClass instance</summary>
/// <param name="fieldAliases">The fields that have been selected in the query and that should fill the class properties</param>
/// <param name="type">The mapping of the type</param>
/// <param name="selectedTable">The table of the fields to map or null if the object must be created empty</param>
/// <returns>The mapping from the database query to the instancied object</returns>
public static MappedClass MapClass(IList <SelectedField> fieldAliases, TypeMapping type, SelectedTable selectedTable)
{
if (fieldAliases == null)
{
return(null);
}
var mappedClass = new MappedClass();
var idProperty = type.Key;
mappedClass.constructor = type.Type.GetTypeInfo().GetConstructor(Type.EmptyTypes);
if (idProperty != null)
{
var selectedField = fieldAliases.SingleOrDefault(f => f.Field.Table == selectedTable && f.Field.Column == idProperty);
mappedClass.primaryKeyField = new MappedField {
SelectedField = selectedField, PropertyMapping = idProperty
};
}
foreach (var columnPair in type.Columns)
{
var propertyMapping = columnPair.Value;
if (idProperty != null && propertyMapping == idProperty)
{
continue;
}
var fieldInfo = fieldAliases.SingleOrDefault(f => f.Field.Table == selectedTable && f.Field.Column == propertyMapping);
bool isForeign = propertyMapping.Reference != null;
if (fieldInfo != null || isForeign)
{
var mappedField = new MappedField {
PropertyMapping = propertyMapping, SelectedField = fieldInfo
};
if (isForeign)
{
if (selectedTable != null && selectedTable.Children.ContainsKey(propertyMapping))
{
mappedField.MappedClass = MapClass(fieldAliases, propertyMapping.Reference,
selectedTable.Children[propertyMapping]);
}
else
{
// The table has not been selected but the class must be created anyway with empty fields (or it may come from cache)
mappedField.MappedClass = MapClass(fieldAliases, propertyMapping.Reference, null);
}
}
mappedClass.fields.Add(mappedField);
}
}
MapCollections(type, mappedClass);
return(mappedClass);
}
public ExportStatusUpdateEvent(SelectedTable table, bool completed)
{
SelectedTable = table;
Completed = completed;
}
public static SelectedTable AppendSelectedColumns(this BaseQueryBuilder builder, SelectedTable selectedTable, IEnumerable <PropertyMapping> columns)
{
foreach (var column in columns)
{
if (column.Reference != null && column.Reference.IsComplexType)
{
var subTable = builder.RegisterTable(selectedTable, column);
builder.AppendAllSelects(subTable);
}
else
{
var visitable = new Field(selectedTable, column);
var selectedField = builder.SelectField(visitable);
selectedField.Accept(builder.StringBuilder);
}
}
return(selectedTable);
}
public static void AppendTable(this SqlStringBuilder query, SelectedTable table)
{
query.DuringTable(table.Mapping.TableSchema, table.Mapping.TableName);
query.DuringAliasDefinition(table.Alias);
}
public static SelectedTable AppendAllSelects(this BaseQueryBuilder builder, SelectedTable selectedTable)
{
return(builder.AppendSelectedColumns(selectedTable, selectedTable.Mapping.Columns.Values));
}
private void serviceStart_Click(object sender, RoutedEventArgs e)
{
Boolean alreadyWorking = false;
int serviceID = -1;
String query;
MySqlCommand cmd;
MySqlConnection con = null;
//Comprobar si existe
try
{
ConfigurationHandler Config = new ConfigurationHandler();
String host = Config.getSetting("host", "Connection");
String port = Config.getSetting("port", "Connection");
String database = Config.getSetting("database", "Connection");
String user = Config.getSetting("username", "Connection");
String pass = Config.getSetting("password", "Connection");
String ruta = "Data Source=" + host + ";port=" + port + ";Database=" + database + ";Uid=" + user + ";Password="******"SELECT `actualServiceID` FROM tables WHERE id = ?selectedTableID;";
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?selectedTableID", SelectedTable.ToString());
System.Data.IDataReader dr;
dr = cmd.ExecuteReader();
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
alreadyWorking = true;
}
}
}
catch (Exception exc)
{
MessageBox.Show("Hubo un error al comrpobar la existencia de un pedido en esta mesa: \n" + exc.Message.ToString() + ".", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (con != null)
{
con.Close();
}
}
Console.WriteLine(alreadyWorking.ToString());
if (alreadyWorking == false)
{
//Crear servicio
try
{
ConfigurationHandler Config = new ConfigurationHandler();
String host = Config.getSetting("host", "Connection");
String port = Config.getSetting("port", "Connection");
String database = Config.getSetting("database", "Connection");
String user = Config.getSetting("username", "Connection");
String pass = Config.getSetting("password", "Connection");
String ruta = "Data Source=" + host + ";port=" + port + ";Database=" + database + ";Uid=" + user + ";Password="******"INSERT INTO services (start) values (CURRENT_TIMESTAMP); SELECT id FROM `services` WHERE `start`= CURRENT_TIMESTAMP;";
cmd = new MySqlCommand(query, con);
System.Data.IDataReader dr;
dr = cmd.ExecuteReader();
if (dr.Read())
{
serviceID = Int32.Parse(dr.GetString(0));
}
else
{
MessageBox.Show("Error al crear el servicio.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception exc)
{
MessageBox.Show("Hubo un error al añadir el usuario: \n" + exc.Message.ToString() + ".", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (con != null)
{
con.Close();
}
}
//Asignar a mesa actual
if (serviceID != -1)
{
try
{
ConfigurationHandler Config = new ConfigurationHandler();
String host = Config.getSetting("host", "Connection");
String port = Config.getSetting("port", "Connection");
String database = Config.getSetting("database", "Connection");
String user = Config.getSetting("username", "Connection");
String pass = Config.getSetting("password", "Connection");
String ruta = "Data Source=" + host + ";port=" + port + ";Database=" + database + ";Uid=" + user + ";Password="******"UPDATE `tables` SET `actualServiceID` = ?serviceID WHERE `tables`.`id` = ?selectedTableID;";
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?serviceID", serviceID);
cmd.Parameters.AddWithValue("?selectedTableID", SelectedTable.ToString());
System.Data.IDataReader dr;
dr = cmd.ExecuteReader();
if (dr.Read())
{
MessageBox.Show("Error al asignar el servicio a esta mesa.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
else
{
//TODO OK
//Recargar la vista
SelectedService = true;
updateTables();
}
}
catch (Exception exc)
{
MessageBox.Show("Hubo un error al añadir el usuario: \n" + exc.Message.ToString() + ".", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
}
}
private void serviceFinish_Click(object sender, RoutedEventArgs e)
{
Boolean setTime = false;
String query;
MySqlCommand cmd;
MySqlConnection con = null;
//Crear servicio
try
{
ConfigurationHandler Config = new ConfigurationHandler();
String host = Config.getSetting("host", "Connection");
String port = Config.getSetting("port", "Connection");
String database = Config.getSetting("database", "Connection");
String user = Config.getSetting("username", "Connection");
String pass = Config.getSetting("password", "Connection");
String ruta = "Data Source=" + host + ";port=" + port + ";Database=" + database + ";Uid=" + user + ";Password="******"UPDATE `services` SET `end` = CURRENT_TIMESTAMP WHERE `services`.`id` = (SELECT actualServiceID FROM tables where id = ?selectedTableID)";
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?selectedTableID", SelectedTable.ToString());
System.Data.IDataReader dr;
dr = cmd.ExecuteReader();
if (dr.Read())
{
// error
MessageBox.Show("Error al cerrar el servicio.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
else
{
setTime = true;
}
}
catch (Exception exc)
{
MessageBox.Show("Hubo un error al crear el pedido: \n" + exc.Message.ToString() + ".", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (con != null)
{
con.Close();
}
}
//Asignar a mesa actual
if (setTime)
{
try
{
ConfigurationHandler Config = new ConfigurationHandler();
String host = Config.getSetting("host", "Connection");
String port = Config.getSetting("port", "Connection");
String database = Config.getSetting("database", "Connection");
String user = Config.getSetting("username", "Connection");
String pass = Config.getSetting("password", "Connection");
String ruta = "Data Source=" + host + ";port=" + port + ";Database=" + database + ";Uid=" + user + ";Password="******"UPDATE `tables` SET `actualServiceID` = null WHERE `tables`.`id` = ?selectedTableID;";
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?selectedTableID", SelectedTable.ToString());
System.Data.IDataReader dr;
dr = cmd.ExecuteReader();
if (dr.Read())
{
MessageBox.Show("Error al limpiar la mesa.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
else
{
//TODO OK
//Recargar la vista
SelectedService = false;
updateTables();
}
}
catch (Exception exc)
{
MessageBox.Show("Hubo un error al cerrar el pedido: \n" + exc.Message.ToString() + ".", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
updateProductsList();
}
private void productAdd_Click(object sender, RoutedEventArgs e)
{
new products_selection(SelectedTable.ToString()).ShowDialog();
updateTables();
updateProductsList();
}
public ExportStatusUpdateEvent(SelectedTable table) : this(table, false)
{
}
private static void AddParameters <T, TMe>(ISetTarget <T, TMe> target, object value, TypeMapping typeMapping, SelectedTable table,
BaseQueryBuilder baseQueryBuilder, string baseName)
{
foreach (var property in typeMapping.Columns.Values)
{
if (property.Readonly)
{
continue;
}
var parameter = value != null?property.PropertyInfo.GetValue(value) : null;
if (property.Reference != null && property.Reference.IsComplexType)
{
AddParameters(target, parameter, property.Reference, table, baseQueryBuilder, property.ComposeName(baseName));
}
else
{
if (property.IsJson)
{
parameter = JsonConvert.SerializeObject(parameter);
}
target.AppendSet();
string tableName = table.Alias;
baseQueryBuilder.StringBuilder.DuringColumn(tableName, property.ComposeName(baseName));
baseQueryBuilder.StringBuilder.Append("=");
var index = baseQueryBuilder.AddParameter(parameter);
baseQueryBuilder.StringBuilder.DuringParameter(index);
}
}
}
