/*This code uses the SQL command updates the IsDuplicate field to 1 where the DateTime fields are equal*/
private void btn_AcceptDuplicates_Click(object sender, EventArgs e)
{
using (var db = new cdrv4.Database.cdrdbContainer())
{
string table = txb_caseName_tp.Text;
db.Database.ExecuteSqlCommand("Update [dbo].["+table+"] SET [IsDuplicate] = 1 WHERE [DateTime] IN (SELECT [DateTime] FROM [dbo].["+table+"] GROUP BY [DateTime] HAVING COUNT([DateTime])>1);");
btn_CheckDuplicates.PerformClick();
MessageBox.Show("Duplicates accepted");
}
}
/*This code deletes a duplicate item rather than merges them - The merging will be required in future work*/
private void btn_MergeDuplicates_Click(object sender, EventArgs e)
{
using (var db = new cdrv4.Database.cdrdbContainer())
{
string table = txb_caseName_tp.Text;
db.Database.ExecuteSqlCommand("DELETE FROM [dbo].[" + table + "] WHERE IsDuplicate = 1 AND TypeofCall = 'Calls Forwarded';");
MessageBox.Show("De-duplicated");
}
}
/*This code uses the SQL command to create the table using the case name entered by the user as the table name */
private void createTable()
{
using (var db = new cdrv4.Database.cdrdbContainer())
{
string table = txb_CaseName.Text;
db.Database.ExecuteSqlCommand("CREATE TABLE [dbo].[" + table + "] ([LineId] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,[DateofCall] DATE NULL,[TimeofCall] TIME NULL,[TypeofCall] VARCHAR(70) NULL,[CallingNumber] VARCHAR(15) NULL,[CalledNumber] VARCHAR(15) NULL, [CallingIMEI] VARCHAR(20) NULL, [CalledIMEI] VARCHAR(20) NULL,[Duration] VARCHAR(30) NULL,[DateTime] VARCHAR(50) NULL, [IsDuplicate] INT NULL, [FirstCellEasting] VARCHAR(50) NULL, [FirstCellNorthing] VARCHAR(50) NULL, [LastCellEasting] VARCHAR(50) NULL, [LastCellNorthing] VARCHAR(50) NULL, [FirstCellLatitude] VARCHAR(50) NULL, [FirstCellLongitude] VARCHAR(50) NULL, [LastCellLatitude] VARCHAR(50) NULL, [LastCellLongitude] VARCHAR(50) NULL);");
db.SaveChanges();
MessageBox.Show("Table created in database, the table name is: " + table);
copyCSV();
}
}
