public string AddOrUpdateProject(ProjectManagement.Web.Models.Project project)
{
string connectionString = ConfigurationManager.ConnectionStrings["MBProjectConnectionString"].ConnectionString;
string message = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
try {
var cmd = new SqlCommand
{
Connection = conn
};
conn.Open();
// Addresses
var updateQuery = "UPDATE Address " +
"SET AddressLine1 = @AddressLine1, AddressLine2 = @AddressLine2, CompanyName = @CompanyName, " +
"County = @County, Postcode = @Postcode, TownOrCity = @TownOrCity " +
"WHERE Id = @AddressId";
var insertQuery = "INSERT INTO Address (AddressLine1, AddressLine2, CompanyName, County, Postcode, TownOrCity) " +
"VALUES (@AddressLine1, @AddressLine2, @CompanyName, @County, @Postcode, @TownOrCity);" +
"SELECT SCOPE_IDENTITY();";
// Add/update client address
var clientAddressId = project.ClientAddress.Id;
if (project.ClientAddress.IsValid)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@AddressLine1", project.ClientAddress.AddressLine1 ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@AddressLine2", project.ClientAddress.AddressLine2 ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@CompanyName", project.ClientAddress.CompanyName ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@County", project.ClientAddress.County ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@Postcode", project.ClientAddress.Postcode ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@TownOrCity", project.ClientAddress.TownOrCity ?? (object)DBNull.Value);
if (project.ClientAddress.Id != null)
{
cmd.Parameters.AddWithValue("@AddressId", project.ClientAddress.Id);
cmd.CommandText = updateQuery;
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = insertQuery;
var response = cmd.ExecuteScalar();
clientAddressId = Convert.ToInt32(response);
}
}
// Add/update invoice address
var invoiceAddressId = project.InvoiceAddress.Id;
if (project.InvoiceAddress.IsValid)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@AddressLine1", project.InvoiceAddress.AddressLine1 ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@AddressLine2", project.InvoiceAddress.AddressLine2 ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@CompanyName", project.InvoiceAddress.CompanyName ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@County", project.InvoiceAddress.County ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@Postcode", project.InvoiceAddress.Postcode ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@TownOrCity", project.InvoiceAddress.TownOrCity ?? (object)DBNull.Value);
if (project.InvoiceAddress.Id != null)
{
cmd.Parameters.AddWithValue("@AddressId", project.InvoiceAddress.Id);
cmd.CommandText = updateQuery;
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = insertQuery;
var response = cmd.ExecuteScalar();
invoiceAddressId = Convert.ToInt32(response);
}
}
// Project
updateQuery = "UPDATE Project " +
"SET [Project Code] = @projectcode, [Project Name] = @projectname, StartDate = @startdate, EndDate = @enddate, Contact = @Contact, Description = @description, Detailed = @detailed, " +
"StatusID = @StatusID, ProjectManager = @ProjectManager, DepartmentID = @DepartmentID, CountyId = @CountyId, PlanningAuthorityId = @PlanningAuthorityId, " +
"ClientAddressId = @ClientAddressId, InvoiceAddressId = @InvoiceAddressId, Introducer = @Introducer, InvoiceContact = @InvoiceContact, ProjectCity = @ProjectCity, ClientTypeId = @ClientTypeId " +
"WHERE(Project_ID = @project_id)";
insertQuery = "INSERT INTO Project " +
"([Project Code], [Project Name], StartDate, EndDate, Lat, Lon, Contact, Description, Detailed, StatusID, ProjectManager, DepartmentID, CountyId, PlanningAuthorityId, ClientAddressId, InvoiceAddressId, Introducer, InvoiceContact, ProjectCity, ClientTypeId) " +
"VALUES (@projectcode, @projectname, @startdate, @enddate, @Latitude, @Longitude, @Contact, @description, @detailed, @StatusID, @ProjectManager, @DepartmentID, @CountyId, @PlanningAuthorityId, @ClientAddressId, @InvoiceAddressId, @Introducer, @InvoiceContact, @ProjectCity, @ClientTypeId);" +
"SELECT SCOPE_IDENTITY();";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@projectcode", project.Code);
cmd.Parameters.AddWithValue("@projectname", project.Name);
cmd.Parameters.AddWithValue("@startdate", project.StartDate ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@enddate", project.EndDate ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@Contact", project.Contact);
cmd.Parameters.AddWithValue("@description", project.Description);
cmd.Parameters.AddWithValue("@detailed", project.Detailed);
cmd.Parameters.AddWithValue("@StatusID", project.Status);
cmd.Parameters.AddWithValue("@ProjectManager", project.ProjectManager);
cmd.Parameters.AddWithValue("@DepartmentID", project.Department);
cmd.Parameters.AddWithValue("@CountyId", project.CountyId ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@PlanningAuthorityId", project.PlanningAuthorityId ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@ClientAddressId", clientAddressId ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@InvoiceAddressId", invoiceAddressId ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@Introducer", project.Introducer);
cmd.Parameters.AddWithValue("@InvoiceContact", project.InvoiceContact);
cmd.Parameters.AddWithValue("@ProjectCity", string.IsNullOrEmpty(project.ProjectCity) ? (object)DBNull.Value : project.ProjectCity);
cmd.Parameters.AddWithValue("@ClientTypeId", project.ClientTypeId);
var projectId = project.Id;
if (projectId != null)
{
cmd.Parameters.AddWithValue("@project_id", project.Id);
cmd.CommandText = updateQuery;
cmd.ExecuteNonQuery();
}
else
{
cmd.Parameters.AddWithValue("@Latitude", project.Latitude);
cmd.Parameters.AddWithValue("@Longitude", project.Longitude);
cmd.CommandText = insertQuery;
var response = cmd.ExecuteScalar();
projectId = Convert.ToInt32(response);
}
// Delete current sectors associated with the project
cmd.CommandText = "DELETE FROM ProjectSector WHERE Project_ID = @project_id";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@project_id", projectId);
cmd.ExecuteNonQuery();
// Add new sectors
foreach (var sectorId in project.Sectors)
{
cmd.CommandText = "INSERT INTO ProjectSector (Project_ID, Sector_ID) VALUES (@project_id, @sector_id)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@project_id", projectId);
cmd.Parameters.AddWithValue("@sector_id", Convert.ToInt32(sectorId));
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
message = ex.Message;
Console.WriteLine(ex.Message);
}
finally {
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(message);
}
private ProjectManagement.Web.Models.Project PopulateProject()
{
Label LblId = (Label)DetailsView2.FindControl("LBLProjectID");
TextBox TxtProjectname = (TextBox)DetailsView2.FindControl("TxtPorjectname");
DropDownList DDLstatus = (DropDownList)DetailsView2.FindControl("DDlstatus");
DropDownList DDLDepartment = (DropDownList)DetailsView2.FindControl("DDLDepartment");
TextBox TxtContact = (TextBox)DetailsView2.FindControl("TxtContact");
CheckBox ChkDetailed = (CheckBox)DetailsView2.FindControl("ChkDetailed");
TextBox TxtDescription = (TextBox)DetailsView2.FindControl("TxtDescription");
TextBox TxtProjectManager = (TextBox)DetailsView2.FindControl("TxtManager");
DropDownList DDLCounty = (DropDownList)DetailsView2.FindControl("DDLCounty");
DropDownList DDLClientType = (DropDownList)DetailsView2.FindControl("DDLClientType");
TextBox TxtPlanningAuthority = (TextBox)DetailsView2.FindControl("txtPlanningAuthority");
Label LblLat = (Label)DetailsView2.FindControl("LblLat");
Label LblLng = (Label)DetailsView2.FindControl("LblLng");
// Client address
var clientAddressId = GetLabelFieldValue("LblClientAddressId");
var clientAddress = new ProjectManagement.Web.Models.Address
{
AddressLine1 = GetAddressField("TxtClientAddressLine1"),
AddressLine2 = GetAddressField("TxtClientAddressLine2"),
CompanyName = GetAddressField("TxtClientCompanyName"),
County = GetAddressField("TxtClientCounty"),
Id = !string.IsNullOrWhiteSpace(clientAddressId) ? int.Parse(clientAddressId) : (int?)null,
Postcode = GetAddressField("TxtClientPostcode"),
TownOrCity = GetAddressField("TxtClientTownOrCity")
};
// Invoice address
var invoiceAddressId = GetLabelFieldValue("LblInvoiceAddressId");
var invoiceAddress = new ProjectManagement.Web.Models.Address
{
AddressLine1 = GetAddressField("TxtInvoiceAddressLine1"),
AddressLine2 = GetAddressField("TxtInvoiceAddressLine2"),
CompanyName = GetAddressField("TxtInvoiceCompanyName"),
County = GetAddressField("TxtInvoiceCounty"),
Id = !string.IsNullOrWhiteSpace(invoiceAddressId) ? int.Parse(invoiceAddressId) : (int?)null,
Postcode = GetAddressField("TxtInvoicePostcode"),
TownOrCity = GetAddressField("TxtInvoiceTownOrCity")
};
// Project
var project = new ProjectManagement.Web.Models.Project
{
ClientAddress = clientAddress, // New
ClientTypeId = int.Parse(DDLClientType.SelectedValue),
Code = GetTextFieldValue("TxtProjectCode"),
Contact = TxtContact.Text,
CountyId = !string.IsNullOrWhiteSpace(DDLCounty.SelectedValue) ? int.Parse(DDLCounty.SelectedValue) : (int?)null,
Department = int.Parse(DDLDepartment.SelectedValue),
Description = TxtDescription.Text,
Detailed = ChkDetailed.Checked,
Id = !string.IsNullOrWhiteSpace(LblId.Text) ? int.Parse(LblId.Text) : (int?)null,
Introducer = GetTextFieldValue("TxtIntroducer"), // New
InvoiceAddress = invoiceAddress, // New
InvoiceContact = GetTextFieldValue("TxtInvoiceContact"), // New
Latitude = !string.IsNullOrWhiteSpace(LblLat.Text) ? double.Parse(LblLat.Text) : (double?)null,
Longitude = !string.IsNullOrWhiteSpace(LblLng.Text) ? double.Parse(LblLng.Text) : (double?)null,
Name = TxtProjectname.Text,
PlanningAuthorityId = !string.IsNullOrWhiteSpace(TxtPlanningAuthority.Text) ? int.Parse(TxtPlanningAuthority.Text) : (int?)null,
ProjectCity = GetTextFieldValue("TxtProjectCity"),
ProjectManager = TxtProjectManager.Text,
Status = int.Parse(DDLstatus.SelectedValue),
};
TextBox TxtStartDate = (TextBox)DetailsView2.FindControl("TxtStartdate");
TextBox TxtEndDate = (TextBox)DetailsView2.FindControl("TxtEndDate");
if (!string.IsNullOrEmpty(TxtStartDate.Text))
{
string[] date = TxtStartDate.Text.Split('/');
project.StartDate = new DateTime(int.Parse(date[2]), int.Parse(date[1]), int.Parse(date[0]));
}
if (!string.IsNullOrEmpty(TxtEndDate.Text))
{
string[] date = TxtEndDate.Text.Split('/');
project.EndDate = new DateTime(int.Parse(date[2]), int.Parse(date[1]), int.Parse(date[0]));
}
ListBox DDLSector = (ListBox)DetailsView2.FindControl("DDLSector");
project.Sectors = DDLSector
.GetSelectedIndices()
.Select(index => Convert.ToInt32(DDLSector.Items[index].Value))
.ToList();
return(project);
}
