/// <summary>
/// Alter columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void AlterColumn(string table, TableRowModel model)
{
OracleCommand command = Database.CreateCommand(string.Format(SqlModifyColumn, TableNameWithOwnerCaseSensitve(table), model));
Output.WriteLine(command.CommandText);
command.ExecuteNonQuery();
}
/// <summary>
/// Add columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void AddColumn(string table, TableRowModel model)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.AddNewColumn(table, model);
}
}
/// <summary>
/// Alter columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void UpdateColumn(string table, TableRowModel model)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.AlterColumn(table, model);
}
}
/// <summary>
/// Read data from reader
/// </summary>
/// <param name="reader">Sql data reader</param>
/// <returns>Collection of read data</returns>
private TableModel ReadTableDetails(SqlDataReader reader)
{
var res = new TableModel();
while (reader.Read())
{
int i = 0;
var row = new TableRowModel();
row.Id = reader.GetInt32(i++).ToString();
row.Name = reader.GetString(i++);
row.AllowNull = reader.GetBoolean(i++);
var typeName = reader.GetString(i++);
var len = reader.GetInt16(i++);
var precision = reader.GetByte(i++);
var scale = reader.GetByte(i++);
var type =
row.DatatypesItemSource.FirstOrDefault(t => t.Name.Equals(typeName, StringComparison.InvariantCultureIgnoreCase));
type.Lenght = type.HasLenght ? (int)len : type.Lenght;
type.Precision = type.HasPrecision ? (int)precision : type.Precision;
type.Scale = type.HasScale ? (int)scale : type.Scale;
row.Datatype = type;
row.LoadeDatatypeFromDb = type;
row.SelectedDatatype = type;
res.Attributes.Add(row);
}
return(res);
}
/// <summary>
/// Alter columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void AlterColumn(string table, TableRowModel model)
{
SqlCommand command = Database.CreateCommand(string.Format(SqlAlterColumn, table, model));
Output.WriteLine(command.CommandText);
command.ExecuteNonQuery();
}
/// <summary>
/// Alter columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void AlterColumn(string table, TableRowModel model, bool modifyNull = true)
{
if (!modifyNull)
{
OracleCommand command = Database.CreateCommand(string.Format(SqlModifyColumn, TableNameWithOwnerCaseSensitve(table), $"{model.Name} {model.Datatype}"));
command.ExecuteNonQuery();
return;
}
AlterColumn(table, model);
}
/// <summary>
/// Read data from reader
/// </summary>
/// <param name="reader">Sql data reader</param>
/// <returns>Read data fro table</returns>
private TableModel ReadTableDetails(OracleDataReader reader)
{
var res = new TableModel();
while (reader.Read())
{
int i = 0;
var row = new TableRowModel();
row.Name = reader.GetString(i++);
row.Id = reader.GetInt32(i++).ToString();
var typeName = reader.GetString(i++);
var len = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
i++;
var precision = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
i++;
var scale = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
i++;
row.AllowNull = !reader.GetString(i++).Equals("N");
var type =
row.DatatypesItemSource.FirstOrDefault(t => t.Name.Equals(typeName, StringComparison.InvariantCultureIgnoreCase));
if (type == null)
{
var regex = new Regex("^timestamp(\\((\\d)\\))?");
if (regex.IsMatch(typeName.ToLower()))
{
type =
row.DatatypesItemSource.FirstOrDefault(
t => t.Name.Equals("timestamp", StringComparison.InvariantCultureIgnoreCase));
}
if (type == null)
{
continue;
}
}
type.Lenght = type.HasLenght ? (int)len : type.Lenght;
type.Precision = type.HasPrecision ? (int)precision : type.Precision;
type.Scale = type.HasScale ? (int)scale : type.Scale;
row.Datatype = type;
row.LoadeDatatypeFromDb = type;
row.SelectedDatatype = type;
res.Attributes.Add(row);
}
return(res);
}
/// <summary>
/// Add columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="column">Column model</param>
/// <returns>Exception message if failed, NULL if not</returns>
public string AddColumn(string table, TableRowModel column)
{
try
{
var ctx = new DatabaseContext(SessionProvider.Instance.ConnectionType);
ctx.AddColumn(table, column);
return(null);
}
catch (Exception exception) when(exception is SqlException || exception is OracleException)
{
Output.WriteLine(OutputPanelListener.PrepareException(exception.Message));
return(exception.Message);
}
}
/// <summary>
/// Read data from reader
/// </summary>
/// <param name="reader">Sql data reader</param>
/// <param name="model">Table model</param>
/// <returns>Collection of read data</returns>
private void ReadPrimaryKeys(SqlDataReader reader, TableModel model)
{
while (reader.Read())
{
var name = reader.GetString(3);
var constraintName = reader.GetString(5);
TableRowModel rowModel = model.Attributes.FirstOrDefault(t => t.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase));
if (rowModel != null)
{
rowModel.PrimaryKey = true;
rowModel.PrimaryKeyConstraintName = constraintName;
}
}
}
/// <summary>
/// Read data from reader
/// </summary>
/// <param name="reader">Oracle data reader</param>
/// <param name="table">Table model</param>
/// <param name="consName">PK Constraint name</param>
/// <returns>Collection of read data</returns>
private void ReadTablePrimaryKey(OracleDataReader reader, TableModel table, string consName)
{
while (reader.Read())
{
var column = reader.GetString(0);
TableRowModel model = table.Attributes.FirstOrDefault(t => t.Name.Equals(column, StringComparison.InvariantCultureIgnoreCase));
if (model != null)
{
model.PrimaryKey = true;
model.PrimaryKeyConstraintName = consName;
}
}
}
/// <summary>
/// Alter columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void UpdateColumn(string table, TableRowModel model)
{
using (IOracleMapper mapper = new OracleMapper())
{
TableModel details = mapper.SelectTableDetails(string.Empty, table);
TableRowModel row = details.Attributes.FirstOrDefault(t => t.Name.Equals(model.Name));
bool modifyNull = true;
if (row != null)
{
modifyNull = row.AllowNull != model.AllowNull;
}
mapper.AlterColumn(table, model, modifyNull);
}
}
/// <summary>
/// Add new column or update existing in DB
/// </summary>
/// <param name="model">Table model</param>
/// <param name="column">Column model</param>
/// <param name="refreshedModel">Model with new attributes</param>
/// <param name="args">Arguments provided by add/update event</param>
/// <returns>Exception message if failed, NULL if not</returns>
public string AddOrUpdateCollumn(TableModel model, TableRowModel column, out TableModel refreshedModel, ref EditRowEventArgs args)
{
var err = args == null?AddColumn(model.Title, column) : UpdateColumn(model.Title, column);
if (args != null)
{
if (!args.RowModel.Name.Equals(column.Name))
{
err = RenameColumn(model.Title, args.RowModel.Name, column.Name);
}
}
refreshedModel = err != null ? null : RefreshModel(model);
args = null;
return(err);
}
public ActionResult TableClickStatistics(int AdId, String StartDate = "", String EndDate = "")
{
SimpleAdModel ad = service.GetAdById(AdId);
List <TableRowModel> table = new List <TableRowModel>();
DateTime StartDateTime = DateTime.MinValue;
DateTime EndDateTime = DateTime.MaxValue;
if (StartDate != "")
{
StartDateTime = DateTime.Parse(StartDate);
}
if (EndDate != "")
{
EndDateTime = DateTime.Parse(EndDate);
}
var sessionsByIP = ad.AdSessions.Where(b => b.DateTimeStart.CompareTo(StartDateTime) > 0 && b.DateTimeEnd.CompareTo(EndDateTime) < 0).GroupBy(a => a.UserIp);
foreach (var session in sessionsByIP)
{
TableRowModel row = new TableRowModel();
row.IP = session.Key;
row.Locale = session.First().UserLocale;
row.Browser = session.First().UserBrowser;
var clicks = session
.SelectMany(a => a.Activities)
.SelectMany(b => b.Clicks).GroupBy(d => d.ClickStamp).OrderByDescending(e => e.Count());
string transitions = ":";
foreach (var clickGroup in clicks)
{
transitions += (":" + clickGroup.First().ClickStamp);
}
row.Stages.Add(new Stage
{
Name = "Переходы",
Value = transitions
});
//row.FirstStage = session.SelectMany(a => a.Activities).SelectMany(b => b.Clicks).Where(c => c.ClickType == 0).Count().ToString();
//row.SecondStage = session.SelectMany(a => a.Activities).SelectMany(b => b.Clicks).Where(c => c.ClickType == 1).Count().ToString();
//row.ThirdStage = session.SelectMany(a => a.Activities).SelectMany(b => b.Clicks).Where(c => c.ClickType == 2).Count().ToString();
table.Add(row);
}
return(PartialView(table));
}
private void CreateTableRows()
{
// Read
string userPath = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
string folderPath = userPath + @"\WindowsBackupFoldersToExternalDisk\backupOverview";
if (Directory.Exists(folderPath))
{
// Process the list of files found in the directory.
IEnumerable<string> fileEntries = Directory.EnumerateFiles(folderPath);
foreach (string file in fileEntries)
{
// Read file "directoriesFiles"
foreach (string line in File.ReadLines(directoriesFile)
{
if (!(line.Equals("")))
{
// Design: dateTime + "|" + source + "|" + rootDirectory + "|" + sourceDirectoryName + "|" + sourcePath + "|" + sourceFile + "|" + targetLetter + "|" + targetDiskName
// Example: 28 juli 2019 21:29:10|C:\SourceA|C:\SourceA\7875000|7875000|C:\SourceA\7875000\Raw images|img003.txt|D:\|-
string[] lineArray = line.Split(new[] {'|'}, StringSplitOptions.None);
String dateTime = lineArray[0];
String source = lineArray[1];
String rootDirectory = lineArray[2];
String sourceDirectoryName = lineArray[3];
String sourcePath = lineArray[4];
String sourceFile = lineArray[5];
String targetLetter = lineArray[6];
String targetDiskName = lineArray[7];
var tableRowModel = new TableRowModel(
dateTime,
source,
rootDirectory,
sourceDirectoryName,
sourcePath,
sourceFile,
targetLetter,
targetDiskName);
this.TableRows.Add(tableRowModel);
}
}
}
}
}
/// <summary>
/// Alter columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void UpdateColumn(string table, TableRowModel model)
{
_strategy.UpdateColumn(table, model);
}
public MainWindowViewModel()
{
DatabaseModelDesignerViewModels = new List <DatabaseModelDesignerViewModel>();
FlyoutRowModel = new TableRowModel();
}
/// <summary>
/// Add columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void AddColumn(string table, TableRowModel model)
{
_strategy.AddColumn(table, model);
}
public async Task <TableRowModel> AddGroupTableRowAsync(GroupModel group, DriveItemModel driveItem, string tableName, TableRowModel tableRow)
{
return((await PostAsync($"groups/{group.Id}/drive/items/{driveItem.Id}/workbook/tables/{tableName}/rows",
new TableRowsModel
{
Values = new[] { tableRow }
})).Values.FirstOrDefault());
}