/// <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); }
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(); }
public void ExtractGraphicsCafeDocuments() { var specificCategories = ReadSpecificCategories(); Console.WriteLine("Initialize the Extract with Cred: Server: " + LotusNotesServer + " Password: "******"Reading file: " + db.FileName); if (db != null && db.FileName.ToLower() == "Graphics Cafe.nsf".ToLower()) { Console.WriteLine("Reading file: " + db.FileName); isAlive = false; try { if (!db.IsOpen) { db.Open(); } } catch (Exception ex) { Console.WriteLine("Unable to open database: " + db.FileName); Console.WriteLine("Exception: " + ex); db = d.GetNextDatabase(); continue; } NotesDocumentCollection docs = db.AllDocuments; if (docs != null) { Console.WriteLine("Documents Count: " + db.AllDocuments.Count); string rootFolderName = Path.GetFileNameWithoutExtension(db.FileName); string baseDirectoryPath = BaseDirectoryPath + rootFolderName + "\\"; for (int rowCount = 1; rowCount <= docs.Count; rowCount++) { NotesDocument document = docs.GetNthDocument(rowCount); if (document.HasEmbedded && document.HasItem("$File")) { object[] AllDocItems = (object[])document.Items; string category = string.Empty; List <string> categories = new List <string>(); string subCategory = string.Empty; List <string> subCategories = new List <string>(); Console.WriteLine("RowIndex: " + rowCount); string documentDescription = string.Empty; string documentDate = string.Empty; string documentNotes = string.Empty; string documentAuthor = string.Empty; var notesItems = new List <NotesItem>(); foreach (object CurItem in AllDocItems) { notesItems.Add((NotesItem)CurItem); } var categoryItem = notesItems.FirstOrDefault(x => x.Name == "Category"); if (categoryItem != null && !string.IsNullOrEmpty(categoryItem.Text)) { category = categoryItem.Text; } var subcategoryItem = notesItems.FirstOrDefault(x => x.Name == "SubCategory"); if (subcategoryItem != null && !string.IsNullOrEmpty(subcategoryItem.Text)) { subCategory = subcategoryItem.Text; } var descriptionItem = notesItems.FirstOrDefault(x => x.Name == "Description"); if (descriptionItem != null && !string.IsNullOrEmpty(descriptionItem.Text)) { documentDescription = descriptionItem.Text; } var notesItem = notesItems.FirstOrDefault(x => x.Name == "Notes"); if (notesItem != null && !string.IsNullOrEmpty(notesItem.Text)) { documentNotes = notesItem.Text; } var dateItem = notesItems.FirstOrDefault(x => x.Name == "tmpDate"); if (dateItem != null && !string.IsNullOrEmpty(dateItem.Text)) { documentDate = dateItem.Text; } var authorItem = notesItems.FirstOrDefault(x => x.Name == "tmpAuthor"); if (authorItem != null && !string.IsNullOrEmpty(authorItem.Text)) { documentAuthor = authorItem.Text; } if (!string.IsNullOrEmpty(category)) { categories = category.Split(';').ToList(); } if (!string.IsNullOrEmpty(subCategory)) { subCategories = subCategory.Split(';').ToList(); } if (specificCategories.Count == 0 || (specificCategories.Count > 0 && (categories.Any(x => specificCategories.Contains(x)) || subCategories.Any(x => specificCategories.Contains(x))))) { List <NotesItem> documentItems = notesItems.Where(x => x.type == IT_TYPE.ATTACHMENT).ToList(); if (documentItems != null && documentItems.Count > 0) { foreach (var nItem in documentItems) { if (IT_TYPE.ATTACHMENT == nItem.type) { var pAttachment = ((object[])nItem.Values)[0].ToString(); Console.WriteLine("Description: " + documentDescription); Console.WriteLine("Date: " + documentDate); Console.WriteLine("Notes: " + documentNotes); Console.WriteLine("Author: " + documentAuthor); foreach (var cat in categories) { Console.WriteLine("Category: " + cat); string destPath = baseDirectoryPath; if (!string.IsNullOrEmpty(cat)) { if (!Directory.Exists(baseDirectoryPath + cat)) { Directory.CreateDirectory(baseDirectoryPath + cat); } destPath = destPath + cat + "\\"; } foreach (var subcat in subCategories) { Console.WriteLine("SubCategory: " + subcat); if (!string.IsNullOrEmpty(subcat)) { if (!Directory.Exists(baseDirectoryPath + cat + "\\" + subcat)) { Directory.CreateDirectory(baseDirectoryPath + cat + "\\" + subcat); } destPath = destPath + subcat + "\\"; } } Console.WriteLine("Final Destination Path: " + destPath + pAttachment); if (!File.Exists(destPath + pAttachment)) { try { document.GetAttachment(pAttachment).ExtractFile(destPath + pAttachment); } catch (Exception exe) { LogDetails("File not extracted: " + destPath + pAttachment); LogExceptionDetails("File not extracted: " + destPath + pAttachment, exe); } } else { Console.WriteLine("File already exists: " + destPath + pAttachment); } } } } } } } } } } if (isAlive) { db = d.GetNextDatabase(); } } } catch (Exception ex) { Console.WriteLine("Extract Graphics Cafe Documents Exception:" + ex); } }