private void DetailsView2Databinding()
{
string ProjectID = Request.Params["ProjectID"];
if (!string.IsNullOrEmpty(ProjectID))
{
Project.ProjectDataTable p = projectBLL.GetDataByID(ProjectID);
DetailsView2.DataSource = p;
GenerateJobSheet(p);
var fieldCount = DetailsView2.Fields.Count;
// Assumes there is one field after these four fields
var FieldColumns = new Dictionary <string, int>();
FieldColumns.Add("AddedAt", fieldCount - 5);
FieldColumns.Add("JobSheetSubmitted", fieldCount - 4);
FieldColumns.Add("FeeProposalSubmitted", fieldCount - 3);
FieldColumns.Add("AcceptanceOfServiceSubmitted", fieldCount - 2);
foreach (KeyValuePair <string, int> item in FieldColumns)
{
DetailsView2.Fields[item.Value].Visible = p.Rows[0][item.Key] != DBNull.Value;
}
DetailsView2.DataBind();
HasJobSheet = p.Rows[0]["JobSheetSubmitted"] != DBNull.Value;
JobSheetMandatoryMarker.Visible = !HasJobSheet;
}
DetailsView2.CssClass = DetailsView2.CurrentMode.ToString().ToLower();
}
private void GenerateJobSheet(Project.ProjectDataTable p)
{
Project.ProjectRow row = p.Rows[0] as Project.ProjectRow;
TxtJobCode.Text = "";
TxtManager.Text = "";
TxtDetails.Text = "";
TxtAddress.Text = "";
TxtJobCode.Text = row.Project_Code;
if (!row.IsContactNull())
{
TxtContact.Text = row.Contact;
}
TxtDate.Text = DateTime.Today.ToShortDateString();
if (!row.IsDescriptionNull())
{
TxtDetails.Text = row.Description;
}
if (!row.IsAddressNull())
{
TxtAddress.Text = row.Address;
}
if (!row.IsCityNull())
{
TxtAddress.Text += "\n" + row.City;
}
if (!row.IsProjectManagerNull())
{
TxtManager.Text = row.ProjectManager;
}
}
public Project.ProjectDataTable GetDataByprojectCode(string projectcode)
{
Project.ProjectDataTable table = Adapter.GetDataByProjectCode(projectcode);
foreach (Project.ProjectRow row in table.Rows)
{
row.Project_Code =
ReplaceString(row.Project_Code, projectcode,
string.Format("<span style='background-color:yellow;'>{0}</span>", projectcode));
row.AcceptChanges();
table.AcceptChanges();
}
return(table);
}
//public string GetIconPath()
//{
// return "GGIcon" + "/" + Dropdown_Status.SelectedItem.Text.Trim() + "/" + DropDownList_department.SelectedItem.Text.Trim() + ".png";
//}
public void BindingData()
{
Int32? statusId = Int32.Parse(DropDownList1.SelectedValue);
Int32? departmentId = Int32.Parse(DropDownList2.SelectedValue);
Int32? sectorId = Int32.Parse(DropDownList3.SelectedValue);
string projectSearchText = txtProjectSearch.Text.Trim();
int? authorityId = int.Parse(AuthorityDropDown.SelectedValue);
statusId = statusId >= 0 ? statusId : null;
departmentId = departmentId >= 0 ? departmentId : null;
sectorId = sectorId >= 0 ? sectorId : null;
projectSearchText = !string.IsNullOrEmpty(projectSearchText) ? projectSearchText : null;
authorityId = authorityId >= 0 ? authorityId : null;
projectTable = _projectbll.GetProjects(statusId, departmentId, sectorId, authorityId, projectSearchText);
total = projectTable.Count;
GridView1.DataSource = projectTable;
if (projectTable.Count != 0)
{
StringBuilder script = new StringBuilder("<script type='text/javascript'>");
script.AppendLine("var markers = [];");
foreach (Project.ProjectRow row in projectTable)
{
string iconPath = "GGIcon" + "/" + row.Status.Trim() + "/" + row.Name.Trim().Replace('/', '-') + ".png";
if (row.IsProject_CodeNull())
{
row.Project_Code = string.Empty;
}
row.Project_Code = row.Project_Code.Replace("'", "\"");
script.AppendLine(string.Format("markers.push(addDatamarkers({0},{1},'{2}','{3}','{4}','{5}','{6}'));", row.lat, row.lon, Server.HtmlEncode(row.Project_Code), iconPath, row.Project_ID, row.Status, row.Name));
}
script.Append("</script>");
ClientScript.RegisterStartupScript(this.GetType(), "add", script.ToString());
}
GridView1.DataBind();
}
public Project.ProjectDataTable GetData()
{
Project.ProjectDataTable table = Adapter.GetData();
return(Adapter.GetData());
}
public Project.ProjectDataTable GetDataBydepandStatus(int statusID, int depID)
{
Project.ProjectDataTable tabel = Adapter.GetDataByDepAndStatus(depID, statusID);
return(tabel);
}
protected void BtnSheet_Click(object sender, EventArgs e)
{
string ProjectID = Request.QueryString["projectID"];
Project.ProjectDataTable p = projectBLL.GetDataByID(ProjectID);
Project.ProjectRow row = p.Rows[0] as Project.ProjectRow;
String originalJobSheetPath = Server.MapPath("JobSheet_2018-12-09.xls");
String modifiedJobSheetPath = Server.MapPath("newJobsheet1.xls");
File.Copy(originalJobSheetPath, modifiedJobSheetPath, true);
String conn = String.Format(ExcelConnectionString, modifiedJobSheetPath);
String updateQuery = "update [Project Sheet${0}:{0}] set F1 = \"{1}\"";
using (OleDbConnection connection = new OleDbConnection(conn))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand
{
Connection = connection
};
if (!row.IsProject_CodeNull())
{
cmd.CommandText = String.Format(updateQuery, "G5", row.Project_Code);
cmd.ExecuteNonQuery();
}
var introducer = row["Introducer"].ToString();
if (!string.IsNullOrWhiteSpace(introducer))
{
cmd.CommandText = String.Format(updateQuery, "G7", introducer);
cmd.ExecuteNonQuery();
}
var sectors = row["SectorList"].ToString();
if (!string.IsNullOrWhiteSpace(sectors))
{
cmd.CommandText = String.Format(updateQuery, "G8", sectors);
cmd.ExecuteNonQuery();
}
var startDate = row["StartDate"].ToString();
if (!string.IsNullOrWhiteSpace(startDate))
{
var formattedStartDate = Convert.ToDateTime(startDate).ToShortDateString();
cmd.CommandText = String.Format(updateQuery, "G10", formattedStartDate);
cmd.ExecuteNonQuery();
}
if (!row.IsStatusNull())
{
cmd.CommandText = String.Format(updateQuery, "G11", row.Status.Trim());
cmd.ExecuteNonQuery();
}
if (!row.IsDescriptionNull())
{
cmd.CommandText = String.Format(updateQuery, "G13", row.Description);
cmd.ExecuteNonQuery();
}
var clientCompanyName = row["ClientCompanyName"].ToString();
if (!string.IsNullOrWhiteSpace(clientCompanyName))
{
cmd.CommandText = String.Format(updateQuery, "G18", clientCompanyName);
cmd.ExecuteNonQuery();
}
var clientType = row["ClientType"].ToString();
if (!string.IsNullOrWhiteSpace(clientType))
{
cmd.CommandText = String.Format(updateQuery, "G20", clientType);
cmd.ExecuteNonQuery();
}
if (!row.IsContactNull())
{
cmd.CommandText = String.Format(updateQuery, "A23", row.Contact);
cmd.ExecuteNonQuery();
}
var clientAddressFields = GetAddressFieldNames("Client", row);
var rowIndex = 26;
foreach (var field in clientAddressFields)
{
cmd.CommandText = String.Format(updateQuery, "A" + rowIndex.ToString(), field);
cmd.ExecuteNonQuery();
rowIndex++;
}
var invoiceContact = row["InvoiceContact"].ToString();
if (!string.IsNullOrWhiteSpace(invoiceContact))
{
cmd.CommandText = String.Format(updateQuery, "G23", invoiceContact);
cmd.ExecuteNonQuery();
}
var invoiceAddressFields = GetAddressFieldNames("Invoice", row);
rowIndex = 26;
foreach (var field in invoiceAddressFields)
{
cmd.CommandText = String.Format(updateQuery, "G" + rowIndex.ToString(), field);
cmd.ExecuteNonQuery();
rowIndex++;
}
connection.Close();
}
Response.Redirect("newJobsheet1.xls");
}