using System; using System.IO; using Scan2Compress.Business; using System.Linq; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; using System.Threading; namespace MergeCMInpro { class Program { static List vorgängeCM = new List(); static List vorgängeInpro = new List(); static string destinationFilePath = @"\\asbgvp02\Archivtransfer\"; static string constring = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dborap01.stadtdo.de)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=bgvp.stadtdo.de)));user id=leser;password=leser;"; static string csvPath = Path.Combine(Directory.GetFiles(Environment.CurrentDirectory, "*.csv").FirstOrDefault()); //static string csvPath = Path.Combine(Directory.GetFiles(@"S:\Bauvorhaben\0_SAGA\815.922 - 819.917 SAGA HD 21 2021_09_27", "*.csv").FirstOrDefault()); static string pathOutputFileCM = Path.Combine(Directory.GetParent(csvPath).FullName, "outputCM.csv"); static string pathOutputFileInpro = Path.Combine(Directory.GetParent(csvPath).FullName, "outputInpro.csv"); static void Main(string[] args) { Console.WriteLine("Working, please wait... or get yourself a tea."); SplitRow(csvPath); using (StreamWriter sw = new StreamWriter(pathOutputFileCM)) { foreach (var item in vorgängeCM) { sw.WriteLine(item.ToString()); } } using (StreamWriter sw = new StreamWriter(pathOutputFileInpro)) { foreach (var item in vorgängeInpro) { sw.WriteLine(item.ToString()); } } Console.WriteLine("{0} created.", pathOutputFileCM); Console.WriteLine("{0} created.", pathOutputFileInpro); Console.WriteLine(""); Console.WriteLine("You may close this window now."); Console.ReadLine(); } public static void SplitRow(string path) { int count = 0; int countRows = File.ReadAllLines(path).Length; using (StreamReader sr = new StreamReader(path, System.Text.Encoding.GetEncoding("ISO-8859-1"))) { string[] arr = new string[10]; while (!sr.EndOfStream) { count++; if (count != countRows) { Console.Write("\r{0}/{1} ", count, countRows); } else { Console.WriteLine("\r{0}/{1} ", count, countRows); } arr = sr.ReadLine().Split(";"); Vorgang v = new Vorgang(arr[0], arr[2], string.Concat(arr[5], " Teil ", arr[6]), arr[6], arr[8]); v.AbsoluteFilePath = GetFullFilePath(arr[8]); //arr[7]=jpm-Dateien if (v.AbsoluteFilePath == string.Empty) { Console.WriteLine("Datei {0} nicht gefunden", path); return; } v.CreationDate = GetCreationDate(v.AbsoluteFilePath); ConnectAndExecute(v); //Backup JPM-Files var z = ExtractFilePath(v.AbsoluteFilePath); var folder = string.Concat("Sicherungen jpm ", z.ElementAt(0), " bis ", z.ElementAt(2)); CopySourceFileToDestination(GetFullFilePath(arr[7]), Path.Combine(destinationFilePath, "Archiv", folder), arr[7]); } } } static string[] ExtractFilePath(string path) { var splittedAbsolutePath = path.Split("\\").SkipLast(2).LastOrDefault(); return splittedAbsolutePath.Split(" "); } public static DateTime GetCreationDate(string file) { FileInfo fi = new FileInfo(file); return fi.CreationTime; } public static string GetFullFilePath(string file) { string currentDirectory = Directory.GetParent(csvPath).FullName; //Directory.GetCurrentDirectory(); string res = string.Empty; res = Directory.GetFiles(currentDirectory, file, SearchOption.AllDirectories).FirstOrDefault(); if (res == null) return string.Empty; else { //res = Directory.GetParent(res).Parent.Name.Split('\\').LastOrDefault(); return res; } } public static string GenerateQuery(string az) { string sqlQuery = "SELECT " + "INPROP.PMGTVVORGANG.VORGANGID, " + "INPROP.PMGTVVORGANG.VORGANGDEFID, " + "INPROP.PMVBCHECKLIST.CHECKLISTID, " + "INPROP.PMVBCHECKLIST.DESCRIPTION, " + "INPROP.PMGTVVORGANG.STRASSE1ID, " + "INPROP.PMGTVVORGANG.STRASSE1, " + "INPROP.PMGTVVORGANG.HAUSNUMMER1, " + "INPROP.PMGTVVORGANG.HSNRVON, " + "INPROP.PMGTVVORGANG.HSNRVONZUSATZ, " + "INPROP.PMGTVVORGANG.HSNRBIS, " + "INPROP.PMGTVVORGANG.HSNRBISZUSATZ, " + "INPROP.PMGTVVORGANG.STRASSE2, " + "INPROP.PMGTVVORGANG.HAUSNUMMER2, " + "INPROP.PMGTVVORGANG.DATUMEINGANG, " + "INPROP.PMGTVVORGANG.DATUMABSCHLUSS " + "FROM INPROP.PMGTVVORGANG " + "INNER JOIN INPROP.PMVBCHECKLIST ON INPROP.PMGTVVORGANG.CHECKID = INPROP.PMVBCHECKLIST.CHECKLISTID " + "WHERE INPROP.PMGTVVORGANG.VORGANGID='" + az + "'"; return sqlQuery; } public static void ConnectAndExecute(Vorgang v) { try { using (Oracle.ManagedDataAccess.Client.OracleConnection oracle = new Oracle.ManagedDataAccess.Client.OracleConnection()) { oracle.ConnectionString = constring; string sqlQuery = GenerateQuery(v.Aktenzeichen); using (Oracle.ManagedDataAccess.Client.OracleCommand com = new Oracle.ManagedDataAccess.Client.OracleCommand(sqlQuery, oracle)) { oracle.Open(); using (Oracle.ManagedDataAccess.Client.OracleDataReader reader = com.ExecuteReader()) { while (reader.Read()) { v.Checkliste = reader.GetString(2); v.VorgangsDefinition = reader.GetString(1); v.BeschreibungVorgang = reader.GetString(3); v.Eingangsdatum = Convert.ToDateTime(reader.GetValue(13).ToString()); v.Enddatum = Convert.ToDateTime(reader.GetValue(14).ToString()); //var date = reader.GetValue(12); //if (date.ToString() == string.Empty) // v.Enddatum = DateTime.Now; //else // v.Enddatum = Convert.ToDateTime(reader.GetValue(12).ToString()); var strasseId = reader.GetValue(4); if (strasseId.ToString() == "") v.IdStrasse = "null"; else v.IdStrasse = strasseId.ToString(); v.Strasse1 = reader.GetValue(5).ToString().Trim(); v.Hausnummer1Von = reader.GetValue(6).ToString(); v.HsNrVonZusatz = reader.GetValue(8).ToString(); v.Hausnummer1Bis = reader.GetValue(9).ToString(); v.HsNrBisZusatz = reader.GetValue(10).ToString(); v.Strasse2 = reader.GetValue(11).ToString(); v.Hausnummer2 = reader.GetValue(12).ToString(); } //CM StringBuilder sbCM = new StringBuilder(); sbCM.Append(string.Concat(v.Id, ";")); sbCM.Append(string.Concat(v.Aktenzeichen, ";")); sbCM.Append(string.Concat(v.VorgangsDefinition.Trim(), ';')); sbCM.Append(string.Concat(v.BeschreibungVorgang, ";")); sbCM.Append(string.Concat(v.Strasse1, ";")); sbCM.Append(string.Concat(string.Concat(v.Hausnummer1Von, " ", v.HsNrVonZusatz).Trim(), ';')); sbCM.Append(string.Concat(v.Strasse2.Trim(), ";")); sbCM.Append(string.Concat(string.Concat(v.Hausnummer1Bis, " ", v.HsNrBisZusatz).Trim(), ';')); sbCM.Append(string.Concat(v.Enddatum.ToShortDateString(), ";")); sbCM.Append(string.Concat(v.FameId, ";")); sbCM.Append(string.Concat(v.CreationDate.ToShortDateString(), ";")); //Langbezeichnung string documentType = CheckedForDocumentType(v.DocType.Split(" ").FirstOrDefault()); sbCM.Append(string.Concat(documentType, " ", string.Concat(v.DocType.Split(" ").FirstOrDefault().Substring(0, 1), v.DocType.Split(" ").FirstOrDefault().Substring(1).ToLower()), " ", v.DocType.Split(" ", 2).Skip(1).FirstOrDefault(), ";")); sbCM.Append(string.Concat(v.AbsoluteFilePath.Split('\\').SkipLast(2).LastOrDefault(), " ", v.Id, ";")); sbCM.Append(string.Concat(v.FameId, ".pdf", ";")); vorgängeCM.Add(sbCM.ToString()); StringBuilder sbInpro = new StringBuilder(); sbInpro.Append(string.Concat(string.Empty, ";"));//Barcode sbInpro.Append(string.Concat(v.Aktenzeichen, ";"));//Vorgangsnummer sbInpro.Append(string.Concat(string.Empty, ";"));//Abteilung sbInpro.Append(string.Concat(v.IdStrasse, ";"));//Strasse1ID sbInpro.Append(string.Concat(v.Strasse1, ";").Trim());//Strasse1 sbInpro.Append(string.Concat(v.Hausnummer1Von, ";").Trim());//Hausnummer1 sbInpro.Append(string.Concat(v.Hausnummer1Von, ";").Trim());//Hausnummer1Von sbInpro.Append(string.Concat(v.HsNrVonZusatz, ";").Trim());//Hausnummer1VonZusatz sbInpro.Append(string.Concat(v.Hausnummer1Bis, ";").Trim());//Hausnummer1Bis sbInpro.Append(string.Concat(v.HsNrBisZusatz, ";").Trim());//Hausnummer1BisZusatz sbInpro.Append(string.Concat(v.Strasse2, ";").Trim());//Strasse2 sbInpro.Append(string.Concat(v.Hausnummer2, ";").Trim());//Hausnummer2 sbInpro.Append(string.Concat(v.DocType, ";"));//Bezeichnung1 sbInpro.Append(string.Concat(v.Eingangsdatum.ToShortDateString(), ";"));//Eingangsdatum sbInpro.Append(string.Concat(string.Empty, ";"));//Beschreibung1 sbInpro.Append(string.Concat(string.Empty, ";")); //Beschreibung2 sbInpro.Append(string.Concat(string.Empty, ";"));//Bezeichnung2 sbInpro.Append(string.Concat(v.DocsSZ, ";"));//Bezeichnung3 sbInpro.Append(string.Concat(v.FameId, ".pdf", ";"));//Dateiname sbInpro.Append(string.Concat(v.FameId, ";"));//FameId sbInpro.Append(string.Concat(v.VorgangsDefinition, ";").Trim());//Vorgangsart sbInpro.Append(string.Concat(v.Checkliste, ";").Trim());//Checkliste sbInpro.Append(string.Concat(v.Enddatum.ToShortDateString(), ";"));//Abschlussdatum sbInpro.Append(string.Concat(string.Concat(documentType, " ", string.Concat(v.DocType.Split(" ").FirstOrDefault().Substring(0, 1), v.DocType.Split(" ").FirstOrDefault().Substring(1).ToLower()), " ", v.DocType.Split(" ", 2).Skip(1).FirstOrDefault(), ";")));//Langbezeichnung vorgängeInpro.Add(sbInpro.ToString()); var tmp = ExtractFilePath(v.AbsoluteFilePath); var destFolder = string.Concat("pdf", tmp.ElementAt(5), " ", tmp.ElementAt(0), " bis ", tmp.ElementAt(1), " ", tmp.ElementAt(2)); CopySourceFileToDestination(v.AbsoluteFilePath, destFolder, string.Concat(v.FameId, ".pdf")); } } } } catch (Exception ex) { Console.WriteLine("ERROR fetching data for {0}:{1}", v.Aktenzeichen, ex.Message); } } static string CheckedForDocumentType(string value) { switch (value) { case "SCHRIFTVERKEHR": value = "01"; break; case "ZEICHNUNGEN": value = "02"; break; case "STATIK": value = "03"; break; case "SONSTIGES": value = "04"; break; case "GENEHMIGUNG": value = "05"; break; default: break; } return value; } static void CopySourceFileToDestination(string sourceFilePath, string destFolderName, string destFileName) { string destPath = Path.Combine(destinationFilePath, destFolderName); if (!Directory.Exists(destPath)) Directory.CreateDirectory(destPath); File.Copy(sourceFilePath, Path.Combine(destPath, destFileName),true); } } }