internal static bool UpdateProject(Project project)
{
bool success = false;
string cmdtext = project.id == -1 ?
"insert into project (name, description, owner, type, duration, gsf) output inserted.id values (@name, @description, @owner, @type, @duration, @gsf);"
:
"update project set name = @name, description = @description, owner = @owner, type = @type, duration = @duration, gsf = @gsf output inserted.id where id = @id;";
try
{
using (var connection = Connection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
using (var command = new SqlCommand(cmdtext, connection, transaction))
{
try
{
command.Parameters.AddWithValue("id", project.id);
command.Parameters.AddWithValue("name", project.name);
command.Parameters.AddWithValue("description", project.description);
command.Parameters.AddWithValue("owner", project.owner);
command.Parameters.AddWithValue("type", project.type);
command.Parameters.AddWithValue("duration", project.duration);
command.Parameters.AddWithValue("gsf", project.gsf);
project.id = (int)command.ExecuteScalar();
transaction.Commit();
success = project.id != -1;
}
catch (Exception ex)
{
Console.WriteLine("Error executing {0}\nerror:{1}", cmdtext, ex.Message);
PrivateClasses.SafeInvoke(() => MessageBox.Show(ex.Message));
transaction.Rollback();
success = false;
}
}
connection.Close();
}
}
catch (Exception e)
{
Console.WriteLine("Error executing {0}\nerror:{1}", cmdtext, e.Message);
PrivateClasses.SafeInvoke(() => MessageBox.Show(e.Message));
success = false;
}
return(success);
}
internal static int InsertPhase(Phase phase)
{
string cmdtext = "BEGIN " +
"IF NOT EXISTS (SELECT phase FROM project_phase WHERE phase = @phase and project_id = @project_id) " +
"BEGIN " +
"INSERT INTO project_phase (phase, project_id) " +
"output inserted.id values (@phase, @project_id) " +
"END " +
"ELSE " +
"SELECT id FROM project_phase WHERE phase = @phase and project_id = @project_id " +
"END";
try
{
using (var connection = Connection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
using (var command = new SqlCommand(cmdtext, connection, transaction))
{
try
{
command.Parameters.AddWithValue("phase", phase.phase);
command.Parameters.AddWithValue("project_id", phase.project.id);
phase.id = (int)command.ExecuteScalar();
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Error executing {0}\nerror:{1}", cmdtext, ex.Message);
PrivateClasses.SafeInvoke(() => MessageBox.Show(ex.Message));
transaction.Rollback();
}
}
connection.Close();
}
}
catch (Exception e)
{
Console.WriteLine("Error executing {0}\nerror:{1}", cmdtext, e.Message);
PrivateClasses.SafeInvoke(() => MessageBox.Show(e.Message));
}
return(phase.id);
}
public static bool ImportNewData(List <Record> records, List <Location> locations, List <Level1> level1s, List <Level2> level2s, List <Level3> level3s, List <Level4> level4s)
{
bool success_all = false;
string computer_that_imported = System.Windows.Forms.SystemInformation.ComputerName;
string level1txt = "BEGIN IF NOT EXISTS (select id from level_1 where UPPER(code) = UPPER(@code) and project_id = @project_id) " +
"BEGIN INSERT INTO level_1(code, project_id, description) output inserted.id values(@code, @project_id, @description) END " +
"ELSE SELECT id FROM level_1 WHERE UPPER(code) = UPPER(@code) and project_id = @project_id END; ";
string level2txt = "BEGIN IF NOT EXISTS (select id from level_2 where UPPER(code) = UPPER(@code) and level1_id = @level1_id) " +
"BEGIN INSERT INTO level_2(code, level1_id, description) output inserted.id values(@code, @level1_id, @description) END " +
"ELSE SELECT id FROM level_2 WHERE UPPER(code) = UPPER(@code) and level1_id = @level1_id END; ";
string level3txt = "BEGIN IF NOT EXISTS (select id from level_3 where UPPER(code) = UPPER(@code) and level2_id = @level2_id) " +
"BEGIN INSERT INTO level_3(code, level2_id, description) output inserted.id values(@code, @level2_id, @description) END " +
"ELSE SELECT id FROM level_3 WHERE UPPER(code) = UPPER(@code) and level2_id = @level2_id END; ";
string level4txt = "BEGIN IF NOT EXISTS (select id from level_4 where UPPER(code) = UPPER(@code) and level3_id = @level3_id) " +
"BEGIN INSERT INTO level_4(code, level3_id, description) output inserted.id values(@code, @level3_id, @description) END " +
"ELSE SELECT id FROM level_4 WHERE UPPER(code) = UPPER(@code) and level3_id = @level3_id END; ";
string level1qty = "WITH del AS (DELETE FROM level1_qty WHERE level1_id = @level1_id and phase_id = @phase_id) " +
"INSERT INTO level1_qty (level1_id, phase_id, qty, uom) VALUES (@level1_id, @phase_id, @qty, @uom) ; ";
string level2qty = "WITH del AS (DELETE FROM level2_qty WHERE level2_id = @level2_id and phase_id = @phase_id) " +
"INSERT INTO level2_qty (level2_id, phase_id, qty, uom) VALUES (@level2_id, @phase_id, @qty, @uom) ; ";
string level3qty = "WITH del AS (DELETE FROM level3_qty WHERE level3_id = @level3_id and phase_id = @phase_id) " +
"INSERT INTO level3_qty (level3_id, phase_id, qty, uom) VALUES (@level3_id, @phase_id, @qty, @uom) ; ";
string level4qty = "WITH del AS (DELETE FROM level4_qty WHERE level4_id = @level4_id and phase_id = @phase_id) " +
"INSERT INTO level4_qty (level4_id, phase_id, qty, uom) VALUES (@level4_id, @phase_id, @qty, @uom) ; ";
string templatetxt = "BEGIN IF NOT EXISTS (select id from template where UPPER(code) = UPPER(@code) and level4_id = @level4_id) " +
"BEGIN INSERT INTO template(code, level4_id, description, ut) output inserted.id values(@code, @level4_id, @description, @ut) END " +
"ELSE SELECT id FROM template WHERE UPPER(code) = UPPER(@code) and level4_id = @level4_id END; ";
string locationtxt = "BEGIN IF NOT EXISTS (select id from location where project_id = project_id and UPPER(code) = UPPER(@code) and UPPER(name) = UPPER(@name)) " +
"BEGIN INSERT INTO location(project_id, code, name, sf) output inserted.id values(@project_id, @code, @name, @sf) END " +
"ELSE select id from location where project_id = project_id and UPPER(code) = UPPER(@code) and UPPER(name) = UPPER(@name) END ";
string tradecodetxt = "BEGIN IF NOT EXISTS (select id from trade_code where UPPER(code) = UPPER(@code)) " +
"BEGIN INSERT INTO trade_code (code) output inserted.id values(@code) END " +
"ELSE SELECT id FROM trade_code WHERE UPPER(code) = UPPER(@code) END; ";
string csicodetxt = "BEGIN IF NOT EXISTS (select id from csi_code where UPPER(code) = UPPER(@code) and trade_code_id = @trade_code_id) " +
"BEGIN INSERT INTO csi_code (code, trade_code_id) output inserted.id values(@code, @trade_code_id) END " +
"ELSE SELECT id FROM csi_code WHERE UPPER(code) = UPPER(@code) and trade_code_id = @trade_code_id END; ";
string recordtxt = "INSERT INTO record (template_id, qty, extension, comments, csi_code_id, estimate_category, location_id, phase_id, batch_id, active) " +
"output inserted.id values (@template_id, @qty, @extension, @comments, @csi_code_id, @estimate_category, @location_id, @phase_id, @batch_id, 1); ";
string updateactive = "UPDATE record SET active = 0 WHERE phase_id = @phase_id; ";
string batchtxt = "insert into batch(user_id) output inserted.id values(@user_id); ";
Form1.Bar.SafeInvoke(x =>
{
x.Maximum = records.Count() + locations.Count() + level1s.Count() + +level2s.Count() + level3s.Count() + level4s.Count();
x.Value = 0;
x.Visible = true;
});
using (var connection = Connection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
using (var batchcmd = new SqlCommand(batchtxt, connection, transaction))
using (var updateactivecmd = new SqlCommand(updateactive, connection, transaction))
{
int project_id = records.First().phase.project.id;
int phase_id = records.First().phase.id;
try
{
records.ForEach(r =>
{
if (!level1s.Select(l => l.code).Contains(r.template.level4.level3.level2.level1.code))
{
level1s.Add(r.template.level4.level3.level2.level1);
}
if (!level2s.Select(l => l.code).Contains(r.template.level4.level3.level2.code))
{
level2s.Add(r.template.level4.level3.level2);
}
if (!level3s.Select(l => l.code).Contains(r.template.level4.level3.code))
{
level3s.Add(r.template.level4.level3);
}
if (!level4s.Select(l => l.code).Contains(r.template.level4.code))
{
level4s.Add(r.template.level4);
}
});
level1s.ForEach(level =>
{
using (var levelscmd = new SqlCommand(level1txt, connection, transaction))
using (var levelqtycmd = new SqlCommand(level1qty, connection, transaction))
{
levelscmd.Parameters.AddWithValue("code", level.code);
levelscmd.Parameters.AddWithValue("project_id", project_id);
levelscmd.Parameters.AddWithValue("description", level.description);
level.id = (int)levelscmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.template.level4.level3.level2.level1.code == level.code)
{
x.template.level4.level3.level2.level1.id = level.id;
}
});
levelqtycmd.Parameters.AddWithValue("phase_id", phase_id);
levelqtycmd.Parameters.AddWithValue("level1_id", level.id);
levelqtycmd.Parameters.AddWithValue("qty", level.Phase_UOM_qty.Item3);
levelqtycmd.Parameters.AddWithValue("uom", level.Phase_UOM_qty.Item2);
Form1.Bar.SafeInvoke(x => x.Value++);
}
});
level2s.ForEach(level =>
{
using (var levelscmd = new SqlCommand(level2txt, connection, transaction))
using (var levelqtycmd = new SqlCommand(level2qty, connection, transaction))
{
levelscmd.Parameters.AddWithValue("code", level.code);
levelscmd.Parameters.AddWithValue("level1_id", level.level1.id);
levelscmd.Parameters.AddWithValue("description", level.description);
level.id = (int)levelscmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.template.level4.level3.level2.code == level.code && level.level1.id == x.template.level4.level3.level2.level1.id)
{
x.template.level4.level3.level2.id = level.id;
}
});
levelqtycmd.Parameters.AddWithValue("phase_id", phase_id);
levelqtycmd.Parameters.AddWithValue("level2_id", level.id);
levelqtycmd.Parameters.AddWithValue("qty", level.Phase_UOM_qty.Item3);
levelqtycmd.Parameters.AddWithValue("uom", level.Phase_UOM_qty.Item2);
Form1.Bar.SafeInvoke(x => x.Value++);
}
});
level3s.ForEach(level =>
{
using (var levelscmd = new SqlCommand(level3txt, connection, transaction))
using (var levelqtycmd = new SqlCommand(level3qty, connection, transaction))
{
levelscmd.Parameters.AddWithValue("code", level.code);
levelscmd.Parameters.AddWithValue("level2_id", level.level2.id);
levelscmd.Parameters.AddWithValue("description", level.description);
level.id = (int)levelscmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.template.level4.level3.code == level.code && level.level2.id == x.template.level4.level3.level2.id)
{
x.template.level4.level3.id = level.id;
}
});
levelqtycmd.Parameters.AddWithValue("phase_id", phase_id);
levelqtycmd.Parameters.AddWithValue("level3_id", level.id);
levelqtycmd.Parameters.AddWithValue("qty", level.Phase_UOM_qty.Item3);
levelqtycmd.Parameters.AddWithValue("uom", level.Phase_UOM_qty.Item2);
Form1.Bar.SafeInvoke(x => x.Value++);
}
});
level4s.ForEach(level =>
{
using (var levelscmd = new SqlCommand(level4txt, connection, transaction))
using (var levelqtycmd = new SqlCommand(level4qty, connection, transaction))
{
levelscmd.Parameters.AddWithValue("code", level.code);
levelscmd.Parameters.AddWithValue("level3_id", level.level3.id);
levelscmd.Parameters.AddWithValue("description", level.description);
level.id = (int)levelscmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.template.level4.code == level.code && level.level3.id == x.template.level4.level3.id)
{
x.template.level4.id = level.id;
}
});
levelqtycmd.Parameters.AddWithValue("phase_id", phase_id);
levelqtycmd.Parameters.AddWithValue("level4_id", level.id);
levelqtycmd.Parameters.AddWithValue("qty", level.Phase_UOM_qty.Item3);
levelqtycmd.Parameters.AddWithValue("uom", level.Phase_UOM_qty.Item2);
Form1.Bar.SafeInvoke(x => x.Value++);
}
});
locations.ForEach(locs =>
{
using (var locationcmd = new SqlCommand(locationtxt, connection, transaction))
{
locationcmd.Parameters.AddWithValue("code", locs.code);
locationcmd.Parameters.AddWithValue("project_id", project_id);
locationcmd.Parameters.AddWithValue("name", locs.name);
locationcmd.Parameters.AddWithValue("sf", locs.sf);
locs.id = (int)locationcmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.location.code == locs.code)
{
x.location.id = locs.id;
}
});
Form1.Bar.SafeInvoke(x => x.Value++);
}
});
var templates = records.Select(x => x.template).DistinctBy(x => x.code + "$$" + x.level4.id.ToString()).ToList();
templates.ForEach(templ =>
{
using (var templatescmd = new SqlCommand(templatetxt, connection, transaction))
{
templatescmd.Parameters.AddWithValue("code", templ.code);
templatescmd.Parameters.AddWithValue("level4_id", templ.level4.id);
templatescmd.Parameters.AddWithValue("description", templ.description);
templatescmd.Parameters.AddWithValue("ut", templ.ut);
templ.id = (int)templatescmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.template.code == templ.code && x.template.level4.id == templ.level4.id)
{
x.template.id = templ.id;
}
});
}
});
var tradecodes = records.Select(x => x.csi_code.trade_code).DistinctBy(x => x.code).ToList();
tradecodes.ForEach(tcodes =>
{
using (var tradecmd = new SqlCommand(tradecodetxt, connection, transaction))
{
tradecmd.Parameters.AddWithValue("code", tcodes.code);
tcodes.id = (int)tradecmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.csi_code.trade_code.code == tcodes.code)
{
x.csi_code.trade_code.id = tcodes.id;
}
});
}
});
var csicodes = records.Select(x => x.csi_code).DistinctBy(x => x.code + "$$" + x.trade_code.id.ToString()).ToList();
csicodes.ForEach(csicode =>
{
using (var csicmd = new SqlCommand(csicodetxt, connection, transaction))
{
csicmd.Parameters.AddWithValue("code", csicode.code);
csicmd.Parameters.AddWithValue("trade_code_id", csicode.trade_code.id);
csicode.id = (int)csicmd.ExecuteScalar();
records.ForEach(x =>
{
if (x.csi_code.code == csicode.code && x.csi_code.trade_code.id == csicode.trade_code.id)
{
x.csi_code.id = csicode.id;
}
});
}
});
batchcmd.Parameters.AddWithValue("user_id", User.LoggedIn.id);
updateactivecmd.Parameters.AddWithValue("phase_id", phase_id);
updateactivecmd.ExecuteScalar();
int batch_id = (int)batchcmd.ExecuteScalar();
records.ForEach(record =>
{
using (var recordcmd = new SqlCommand(recordtxt, connection, transaction))
{
recordcmd.Parameters.AddWithValue("batch_id", batch_id);
recordcmd.Parameters.AddWithValue("template_id", record.template.id);
recordcmd.Parameters.AddWithValue("phase_id", phase_id);
recordcmd.Parameters.AddWithValue("location_id", record.location.id);
recordcmd.Parameters.AddWithValue("qty", record.qty);
recordcmd.Parameters.AddWithValue("extension", record.total);
recordcmd.Parameters.AddWithValue("comments", record.comments);
recordcmd.Parameters.AddWithValue("csi_code_id", record.csi_code.id);
recordcmd.Parameters.AddWithValue("estimate_category", record.estimate_category);
record.id = (int)recordcmd.ExecuteScalar();
//Console.WriteLine(JsonConvert.SerializeObject(record));
recordcmd.Dispose();
success_all = record.id != -1;
Form1.Bar.SafeInvoke(x => x.Value = x.Value == x.Maximum ? x.Value : (x.Value + 1));
}
});
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine("Error executing {0}\nerror:{1}", "ImportNewData", e.Message);
PrivateClasses.SafeInvoke(() => MessageBox.Show(Form1.Bar.Value.ToString() + " were successfully imported", "Some records were not imported"));
transaction.Rollback();
connection.Close();
Form1.Bar.SafeInvoke(x => x.Value = 0);
return(false);
}
}
connection.Close();
}
Form1.Bar.SafeInvoke(x => x.Visible = false);
return(success_all);
}