private void ProcessDataBaseCorrection ()
{
WCFStandardsNS.WCFStandards DataBase = new WCFStandardsNS.WCFStandards ();
DataSet WrongVideoFiles = DataBase.GetCommonDataSet ("SELECT DISTINCT FoldedArchivePath, COUNT(*) AS Number "
+ "FROM VideoFiles "
+ "GROUP BY FoldedArchivePath "
+ "ORDER BY FoldedArchivePath");
int NumberOfNonAssignedVideoFiles = 0;
int NumberOfSingleAssignedVideoFiles = 0;
int NumberOfMultipleAssignedVideoFiles = 0;
int OverallNumberOfWrongEntries = 0;
foreach (DataRow WrongEntry in WrongVideoFiles.Tables ["VideoFiles"].Rows)
{
int NumberOfWrongEntries = Convert.ToInt32 (WrongEntry ["Number"]);
if (NumberOfWrongEntries < 2)
continue;
OverallNumberOfWrongEntries++;
String WrongFoldedPath = WrongEntry ["FoldedArchivePath"].ToString ();
DataSet VideoFilesPlusMaterial = DataBase.GetCommonMultiDataSet (new String[]
{
String.Format (
"Select * from VideoFiles where FoldedArchivePath = '{0}' order by ModifyTimeStamp",
WrongFoldedPath),
String.Format (
"Select * from StructuredMaterialDataBase where FoldedArchivePath = '{0}' order by ArchiveID",
WrongFoldedPath)
});
if (WCFStandardsNS.WCFStandards.GetErrorMessages(VideoFilesPlusMaterial) != null)
{
String[] ErrorStrings = WCFStandardsNS.WCFStandards.GetErrorMessages (VideoFilesPlusMaterial);
}
List<String> FoundIDs = new List<string> ();
int NumberOfAssignedVideoFiles = 0;
String ArchiveIDs = String.Empty;
foreach (DataRow VideoRow in VideoFilesPlusMaterial.Tables ["VideoFiles"].Rows)
{
String FileID = VideoRow ["FileID"].ToString ();
DataSet Zuordnung = DataBase.GetCommonDataSet (String.Format
("Select * from VideoFilesZuBeitraege where FileID = '{0}'", FileID));
String ModifyTimeStamp = VideoRow ["ModifyTimeStamp"].ToString ();
if (Zuordnung.Tables ["VideoFilesZuBeitraege"].Rows.Count == 0)
{
FoundIDs.Add (FileID);
}
else
{
NumberOfAssignedVideoFiles++;
FoundIDs.Add (FileID);
foreach (DataRow ZuordnungRow in Zuordnung.Tables ["VideoFilesZuBeitraege"].Rows)
{
}
}
}
foreach (DataRow MaterialRow in VideoFilesPlusMaterial.Tables ["StructuredMaterialDataBase"].Rows)
{
ArchiveIDs = ArchiveIDs + MaterialRow ["ArchiveID"].ToString () + " ";
}
if (NumberOfAssignedVideoFiles > 1)
{
NumberOfMultipleAssignedVideoFiles++;
MessageBox.Show ("Bei \"" + WrongFoldedPath + "\" ist mehr als ein VideoFile Entry zugeordnet\r\n"
+ String.Join ("\r\n", FoundIDs.ToArray ()));
}
if (NumberOfAssignedVideoFiles== 1)
{
NumberOfSingleAssignedVideoFiles++;
MessageBox.Show ("Bei \"" + WrongFoldedPath + "\" ist ein VideoFile Entry zugeordnet\r\n"
+ String.Join ("\r\n", FoundIDs.ToArray ()));
}
if (NumberOfAssignedVideoFiles == 0)
{
NumberOfNonAssignedVideoFiles++;
DataSet MaterialToChange = DataBase.GetCommonDataSet
(String.Format ("Select * from StructuredMaterialDataBase where FoldedArchivePath = '{0}'",
WrongFoldedPath));
String IDToRetain = FoundIDs [0];
FoundIDs.RemoveAt (0);
foreach (DataRow MaterialRowToChange in MaterialToChange.Tables ["StructuredMaterialDataBase"].Rows)
{
String MaterialID = MaterialRowToChange ["ID"].ToString ();
String OldFileID = MaterialRowToChange ["FileID"].ToString ();
if (OldFileID != IDToRetain)
DataBase.RunSQLBatch (String.Format ("Update StructuredMaterialDataBase set FileID = '{0}' where ID = '{1}'",
IDToRetain, MaterialID));
}
foreach (String IDToDelete in FoundIDs)
{
DataBase.RunSQLBatch (String.Format ("Delete VideoFiles where FileID = '{0}'",
IDToDelete));
}
//MessageBox.Show ("Bei \"" + WrongFoldedPath + "\" ist kein VideoFile Entry zugeordnet\r\n"
// + String.Join ("\r\n", FoundIDs.ToArray ()));
}
}
MessageBox.Show ("Insgesamt falsch = " + Convert.ToString (OverallNumberOfWrongEntries) + "\r\n"
+ "Non Assigned = " + Convert.ToString (NumberOfNonAssignedVideoFiles) + "\r\n"
+ "Single Assigned = " + Convert.ToString (NumberOfSingleAssignedVideoFiles) + "\r\n"
+ "Multi Assigned = " + Convert.ToString (NumberOfMultipleAssignedVideoFiles) + "\r\n");
}
private void RemoveDuplicateStructuredMaterialDataBaseEntries ()
{
WCFStandardsNS.WCFStandards DataBase = new WCFStandardsNS.WCFStandards ();
DataSet MaterialTypenDataSet = DataBase.GetCommonDataSet ("Select TechnicalTyp from MaterialType");
DataTable MaterialTable = MaterialTypenDataSet.Tables ["MaterialType"];
DataSet FileIDsDataSet = DataBase.GetCommonDataSet ("Select Distinct FileID from "
+ "StructuredMaterialDataBase");
int DeleteCounter = 0;
int UniqueCounter = 0;
foreach (DataRow FileIDRow in FileIDsDataSet.Tables ["StructuredMaterialDataBase"].Rows)
{
DataSet EntriesForOneFileID = DataBase.GetCommonDataSet
(String.Format ("Select * from StructuredMaterialDataBase where FileID = '{0}' order by ArchiveID, "
+ "TechnicalTyp, ModifyTimeStamp",
FileIDRow ["FileID"].ToString ()));
UniqueCounter++;
String OldArchiveID = String.Empty;
String OldTechnicalTyp = String.Empty;
DateTime OldModifyTimeStamp = DateTime.MinValue;
List<String> FoundIDs = new List<string> ();
String FoldedArchivePath = String.Empty;
foreach (DataRow MaterialRow in EntriesForOneFileID.Tables ["StructuredMaterialDataBase"].Rows)
{
FoldedArchivePath = MaterialRow ["FoldedArchivePath"].ToString ();
String ArchiveID = MaterialRow ["ArchiveID"].ToString ();
String TechnicalTyp = MaterialRow ["TechnicalTyp"].ToString ();
DateTime ModifyTimeStamp;
if (Convert.IsDBNull (MaterialRow ["ModifyTimeStamp"]))
ModifyTimeStamp = DateTime.Parse ("1990-01-01 23:59:59");
else
ModifyTimeStamp = Convert.ToDateTime (MaterialRow ["ModifyTimeStamp"]);
if ((OldArchiveID == ArchiveID)
&& (OldTechnicalTyp == TechnicalTyp)
&& (OldModifyTimeStamp == ModifyTimeStamp))
{
DeleteCounter++;
FoundIDs.Add (MaterialRow ["ID"].ToString ());
DataBase.RunSQLBatch (String.Format ("Delete StructuredMaterialDataBase where ID = '{0}'",
MaterialRow ["ID"].ToString ()));
}
else
{
OldArchiveID = ArchiveID;
OldTechnicalTyp = TechnicalTyp;
OldModifyTimeStamp = ModifyTimeStamp;
}
}
//if (FoundIDs.Count > 0)
// MessageBox.Show ("Bei FoldedArchivePath \"" + FoldedArchivePath + "\"\r\n"
// + String.Join("\r\n", FoundIDs.ToArray ()));
}
MessageBox.Show ("Insgesamt Gelöscht = " + Convert.ToString (DeleteCounter) + "\r\n"
+ "Remainig Entries = " + Convert.ToString (UniqueCounter) + "\r\n");
}