private void bExportDocuments_Click(object sender, EventArgs ea)
{
if (treeView1.SelectedNode == null || treeView1.SelectedNode.Nodes.Count > 0)
{
MessageBox.Show("Select a database.");
return;
}
int total = 0;
long startTicks = 0;
long lastTicks = 0;
string timeLeft = "";
string timeElapsed = "0:00:00";
string databasePath = treeView1.SelectedNode.Name;
ProgressDialog pDialog = new ProgressDialog();
pDialog.Title = "Exporting Documents";
#region Export Documents
pDialog.DoWork += delegate(object dialog, DoWorkEventArgs e)
{
try
{
//export documents
NotesSession nSession = initSession(notesPassword);
Dictionary <string, Table> tables = new Dictionary <string, Table>();
NotesDatabase db;
if (onLocalComputer)
{
db = nSession.GetDatabase("", databasePath, false);
}
else
{
db = nSession.GetDatabase(notesServer + "//" + notesDomain, databasePath, false);
}
//get all documents
total = db.AllDocuments.Count;
NotesDocumentCollection allDocuments = db.AllDocuments;
NotesDocument doc = allDocuments.GetFirstDocument();
startTicks = DateTime.Now.Ticks;
for (int i = 0; i < total; i++)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
if (doc.HasItem("Form") && (string)doc.GetItemValue("Form")[0] != "")
{
//get form
string form = (string)doc.GetItemValue("Form")[0];
if (!tables.ContainsKey(form))
{
tables.Add(form, new Table(form));
}
int row = tables[form].AddRow();
//get fields
//set multiple values
foreach (NotesItem item in doc.Items)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
string field = item.Name;
//exclude fields that start with $ and the Form field and Readers field
if (field == null || excludeField.IsMatch(field))
{
continue;
}
string type = "";
switch (item.type)
{//TODO: get more types
case IT_TYPE.NUMBERS:
type = "decimal(20,10)";
break;
case IT_TYPE.DATETIMES:
type = "datetime";
break;
default:
type = "text";
break;
}
object values = item.Values;
bool multiple = item.Values.Length > 1;
if (!tables[form].Columns.ContainsKey(field))
{
tables[form].Columns.Add(field, new Column(field, type));
}
if (multiple && !tables[form].Columns[field].MultipleValues)
{
tables[form].Columns[field].MultipleValues = multiple;
}
if (!tables[form].Columns[field].Values.ContainsKey(row))
{
tables[form].Columns[field].Values.Add(row, values);
}
else
{
int j = 1;
while (tables[form].Columns.ContainsKey(field + j) && tables[form].Columns[field + j].Values.ContainsKey(row))
{
j++;
}
field += j;
if (!tables[form].Columns.ContainsKey(field))
{
tables[form].Columns.Add(field, new Column(field, type));
}
if (multiple && !tables[form].Columns[field].MultipleValues)
{
tables[form].Columns[field].MultipleValues = multiple;
}
tables[form].Columns[field].Values.Add(row, values);
}
}
}
//update progress
pDialog.ReportProgress(i, "Parsing Documents");
doc = allDocuments.GetNextDocument(doc);
}
//add tables for columns with multiple values
Dictionary <string, Table> newTables = new Dictionary <string, Table>(tables.Count);
lastTicks = 0;
startTicks = DateTime.Now.Ticks;
total = tables.Count;
int count = 0;
foreach (Table table in tables.Values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Formatting Tables");
Dictionary <string, Column> columns = new Dictionary <string, Column>(table.Columns);
foreach (Column column in columns.Values)
{
if (column.MultipleValues)
{
string tableName = table.Name + "_" + column.Name;
Table newTable = new Table(tableName, table.Name);
Column values = new Column(column.Name, column.Type);
Column ids = new Column(table.Name + "id", "int");
foreach (KeyValuePair <int, object> cell in column.Values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
int id = cell.Key;
object[] valueArray;
if (cell.Value.GetType().IsArray)
{
valueArray = (object[])cell.Value;
}
else
{
valueArray = new object[] { cell.Value };
}
foreach (object value in valueArray)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
int row = newTable.AddRow();
ids.Values.Add(row, id);
values.Values.Add(row, value);
}
}
newTable.Columns.Add(table.Name + "id", ids);
newTable.Columns.Add(column.Name, values);
newTables.Add(tableName, newTable);
table.Columns.Remove(column.Name);
}
else
{
Dictionary <int, object> values = new Dictionary <int, object>(column.Values);
foreach (KeyValuePair <int, object> cell in values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
int id = cell.Key;
object value;
if (cell.Value.GetType().IsArray)
{
if (((object[])cell.Value).Length > 0)
{
value = ((object[])cell.Value)[0];
}
else
{
value = null;
}
}
else
{
value = cell.Value;
}
column.Values[id] = value;
}
}
}
newTables.Add(table.Name, table);
}
//format to sql
total = newTables.Count;
bool complete = false;
do
{
lastTicks = 0;
count = 0;
DialogResult result = DialogResult.Cancel;
Invoke((MethodInvoker) delegate() { result = MessageBox.Show(pDialog.Window, "Do you want to export to a MySQL server?", "Export to a server?", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1); });
if (result == DialogResult.Yes)
{
InputBox input = null;
Invoke((MethodInvoker) delegate() { input = InputBox.Show(pDialog.Window, "SQL server info?", new InputBoxItem[] { new InputBoxItem("Server", mysqlServer), new InputBoxItem("Database", mysqlDatabase), new InputBoxItem("Username", mysqlUsername), new InputBoxItem("Password", mysqlPassword, true), new InputBoxItem("Number of rows per INSERT", mysqlNumRowsPerInsert.ToString()) }, InputBoxButtons.OKCancel); });
if (input.Result == InputBoxResult.OK)
{
mysqlServer = input.Items["Server"];
mysqlDatabase = input.Items["Database"];
mysqlUsername = input.Items["Username"];
mysqlPassword = input.Items["Password"];
int.TryParse(input.Items["Number of rows per INSERT"], out mysqlNumRowsPerInsert);
MySqlConnection conn = new MySqlConnection("SERVER=" + mysqlServer + ";USERNAME="******";PASSWORD="******";");
try
{
startTicks = DateTime.Now.Ticks;
conn.Open();
MySqlCommand command = conn.CreateCommand();
command.CommandText = createDatabase(mysqlDatabase);
command.ExecuteNonQuery();
foreach (Table table in newTables.Values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Inserting SQL");
if (table.Columns.Count > 0)
{
command.CommandText = createTable(table);
command.ExecuteNonQuery();
List <string> rows = insertTableRows(table);
for (int i = 0; i < rows.Count; i += mysqlNumRowsPerInsert)
{
command.CommandText = beginInsertTable(table);
command.CommandText += String.Join(",", rows.GetRange(i, Math.Min(rows.Count - i, mysqlNumRowsPerInsert))) + ";\n";
command.CommandText += endInsertTable(table);
command.ExecuteNonQuery();
pDialog.ReportProgress(count, "Inserting SQL");
}
}
}
command.CommandText = restoreVariables();
command.ExecuteNonQuery();
complete = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
}
else if (result == DialogResult.No)
{
saveFileDialog1.FileName = "export.sql";
result = DialogResult.Cancel;
Invoke((MethodInvoker) delegate() { result = saveFileDialog1.ShowDialog(pDialog.Window); });
if (result == DialogResult.OK)
{
InputBox input = null;
Invoke((MethodInvoker) delegate() { input = InputBox.Show(pDialog.Window, "Database name?", "Database Name", mysqlDatabase, InputBoxButtons.OKCancel); });
if (input.Result == InputBoxResult.OK)
{
mysqlDatabase = input.Items["Database Name"];
StreamWriter file = new StreamWriter(saveFileDialog1.FileName, false);
try
{
startTicks = DateTime.Now.Ticks;
file.WriteLine(createDatabase(mysqlDatabase));
foreach (Table table in newTables.Values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Formatting SQL");
if (table.Columns.Count > 0)
{
file.WriteLine(createTable(table));
file.WriteLine(beginInsertTable(table));
file.WriteLine(String.Join(",", insertTableRows(table)) + ";");
file.WriteLine(endInsertTable(table));
}
}
file.WriteLine(restoreVariables());
complete = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
file.Close();
}
}
}
}
else
{
e.Cancel = true;
return;
}
} while (!complete);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
e.Cancel = true;
}
};
#endregion
pDialog.ProgressChanged += delegate(object dialog, ProgressChangedEventArgs e)
{
if (lastTicks == 0)
{
lastTicks = DateTime.Now.Ticks;
timeLeft = "Calculating...";
}
else if (e.ProgressPercentage > 0 && DateTime.Now.Ticks > lastTicks + 10000000)
{
lastTicks = DateTime.Now.Ticks;
long ticksPassed = lastTicks - startTicks;
long thingsCompleted = e.ProgressPercentage;
long thingsLeft = total - thingsCompleted;
long ticks = thingsLeft * ticksPassed / thingsCompleted;
timeLeft = ticksToString(ticks);
timeElapsed = ticksToString(ticksPassed);
}
pDialog.Message = e.UserState.ToString() + ": " + e.ProgressPercentage + "/" + total + " Time Remaining: " + timeLeft + " Time Elapsed: " + timeElapsed;
if (total == 0)
{
pDialog.Progress = 0;
}
else
{
pDialog.Progress = (100 * e.ProgressPercentage / total) % 101;
}
};
pDialog.Completed += delegate(object dialog, RunWorkerCompletedEventArgs e)
{
if (!e.Cancelled)
{
MessageBox.Show("Completed Successfully");
}
};
pDialog.Run();
}
/// <summary>
/// Поиск всех пользователей в системе Lotus Notes
/// </summary>
/// <returns></returns>
public ModelComparableUser FindAllUsersAndAttribute()
{
var modelComparableUsers = new ModelComparableUser();
try
{
var modelList = new List <FullModelUserAllSystem>();
Db.LotusConnectedDataBaseServer(Config.LotusServer, Config.LotusImns);
DocumentCollectionDepartment = Db.Db.Search(String.Format("Select(Form= \"Department\")"), null, 0);
DocumentDepartment = DocumentCollectionDepartment.GetFirstDocument();
while (DocumentDepartment != null)
{
string repDep = DocumentDepartment.GetItemValue("Abbreviation")[0];
DocumentCollectionUsers = Db.Db.Search(String.Format("Select (Abbreviation= \"" + repDep + "\"&Form= \"Department\"| @AllChildren)"), null, 0);
DocumentUsers = DocumentCollectionUsers.GetFirstDocument();
while (DocumentUsers != null)
{
string nameNull = DocumentUsers.GetItemValue("Abbreviation")[0];
if (DocumentUsers.GetItemValue("Discharged")[0] != "1" && nameNull.Length == 0)
{
var modelUsers = new FullModelUserAllSystem();
modelUsers.SystemDataBase = "Lotus Notes";
modelUsers.Surname = DocumentUsers.GetItemValue("LastName")[0];
modelUsers.Name = DocumentUsers.GetItemValue("FirstName")[0];
modelUsers.Patronymic = DocumentUsers.GetItemValue("Person_MiddleName")[0];
modelUsers.FullName = DocumentUsers.GetItemValue("Person_Name")[0];
modelUsers.Department = DocumentDepartment.GetItemValue("Dep_Name")[0];
modelUsers.JobTitle = DocumentUsers.GetItemValue("Person_Post")[0];
modelUsers.Ranks = DocumentUsers.GetItemValue("Person_Title")[0];
modelUsers.Room = null;
modelUsers.Telephon = DocumentUsers.GetItemValue("Comm_Phone")[0];
modelUsers.NameMail = DocumentUsers.GetItemValue("MailAddress")[0];
modelList.Add(modelUsers);
}
DocumentUsers = DocumentCollectionUsers.GetNextDocument(DocumentUsers);
}
DocumentDepartment = DocumentCollectionDepartment.GetNextDocument(DocumentDepartment);
}
modelComparableUsers.FullModelUserAllSystem = modelList.ToArray();
}
catch (Exception ex)
{
Loggers.Log4NetLogger.Error(ex);
throw;
}
finally
{
if (DocumentDepartment != null)
{
Marshal.ReleaseComObject(DocumentDepartment);
}
DocumentDepartment = null;
if (DocumentUsers != null)
{
Marshal.ReleaseComObject(DocumentUsers);
}
DocumentUsers = null;
if (DocumentCollectionDepartment != null)
{
Marshal.ReleaseComObject(DocumentCollectionDepartment);
}
DocumentCollectionDepartment = null;
if (DocumentCollectionUsers != null)
{
Marshal.ReleaseComObject(DocumentCollectionUsers);
}
DocumentCollectionUsers = null;
Loggers.Log4NetLogger.Info(new Exception("Сбор данных по справочнику ИМНС Завершен!!!"));
}
return(modelComparableUsers);
}