private DataTable ExecuteQuery(SqlCommand cmd, string action)
{
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
cmd.Connection = con;
switch (action)
{
case "SELECT":
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return(dt);
}
}
case "UPDATE":
con.Open();
cmd.ExecuteNonQuery();
con.Close();
break;
}
return(null);
}
}
protected string getDefaultPage(string username)
{
string defaultPage = string.Empty;
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.GetUserDefaultPage";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", username);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
defaultPage = reader[0].ToString().ToUpper();
}
}
else
{
defaultPage = string.Empty;
}
conn.Close();
}
}
return(defaultPage);
}
protected void ShowGrid(string id)
{
this.gvSummary.DataSource = null;
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.Summary4";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", id);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
this.gvSummary.DataSource = ds;
this.gvSummary.DataBind();
}
}
}
}
protected void gvUserList_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Select")
{
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow selectedRow = this.gvUserList.Rows[index];
TableCell username = selectedRow.Cells[1];
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.DeleteUser";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", username);
cmd.ExecuteNonQuery();
conn.Close();
}
}
//Response.Redirect("Users.aspx");
}
}
protected void btnSaveAdd_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.InsertIncident";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RaisedBy", this.txtRaisedBy.Text);
cmd.Parameters.AddWithValue("@Office", this.txtOffice.Text);
cmd.Parameters.AddWithValue("@Project", this.txtProject.Text);
cmd.Parameters.AddWithValue("@Area", this.txtArea.Text);
if (this.txtDate.Text == string.Empty)
{
cmd.Parameters.AddWithValue("@Date", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Date", this.txtDate.Text);
}
if (this.txtTime.Text == string.Empty)
{
cmd.Parameters.AddWithValue("@Time", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Time", this.txtTime.Text);
}
cmd.Parameters.AddWithValue("@Detailed_Issue", this.txtIssue.Text);
cmd.Parameters.AddWithValue("@POL_Coreworx", this.ddPolCwx.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Status", this.ddStatus.SelectedItem.Text);
cmd.Parameters.AddWithValue("@AssignedTo", this.txtAssignedTo.Text);
cmd.Parameters.AddWithValue("@Solution", this.txtSolution.Text);
if (this.txtDate.Text == string.Empty)
{
cmd.Parameters.AddWithValue("@DateCompleted", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@DateCompleted", this.txtDateCompleted.Text);
}
if (this.txtTime.Text == string.Empty)
{
cmd.Parameters.AddWithValue("@TimeCompleted", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@TimeCompleted", this.txtTimeCompleted.Text);
}
cmd.Parameters.AddWithValue("@CompletedBy", this.txtCompletedBy.Text);
cmd.Parameters.AddWithValue("@UserId", hfUserId.Value);
cmd.ExecuteNonQuery();
conn.Close();
}
}
Response.Redirect("IssuesSummary.aspx");
}
protected void DownloadFile(object sender, EventArgs e)
{
int id = int.Parse((sender as LinkButton).CommandArgument);
byte[] bytes;
string fileName, contentType;
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.DownloadIncidentFile";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
bytes = (byte[])reader["DocumentFile"];
contentType = reader["DocumentContent"].ToString();
fileName = reader["DocumentFileName"].ToString();
}
conn.Close();
}
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = contentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string filename = Path.GetFileName(fuUpload.PostedFile.FileName);
string contentType = fuUpload.PostedFile.ContentType;
using (Stream fs = fuUpload.PostedFile.InputStream)
{
using (BinaryReader br = new BinaryReader(fs))
{
byte[] bytes = br.ReadBytes((Int32)fs.Length);
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.InsertIncidentFile";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectNumber", this.txtReqNo.Text);
cmd.Parameters.AddWithValue("@DocumentFileName", filename);
cmd.Parameters.AddWithValue("@DocumentDescription", this.txtUpload.Text);
cmd.Parameters.AddWithValue("@DocumentOwner", hfUserId.Value);
cmd.Parameters.AddWithValue("@DocumentContent", contentType);
cmd.Parameters.AddWithValue("@DocumentFile", bytes);
cmd.Parameters.AddWithValue("@UserId", hfUserId.Value);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
}
Response.Redirect(Request.Url.AbsoluteUri);
}
protected bool getUserDB(string username)
{
bool valid = false;
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.GetUser";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
valid = true;
}
else
{
valid = false;
}
conn.Close();
}
}
return(valid);
}
protected void ShowDetails(string ReqNo)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.SelectIncident";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReqNo", ReqNo);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
this.txtReqNo.Text = reader[0].ToString();
this.txtRaisedBy.Text = reader[1].ToString();
this.txtOffice.Text = reader[2].ToString();
this.txtProject.Text = reader[3].ToString();
this.txtArea.Text = reader[4].ToString();
this.txtDate.Text = reader[5].ToString();
this.txtTime.Text = reader[6].ToString();
this.txtIssue.Text = reader[7].ToString();
this.ddPolCwx.SelectedItem.Text = reader[8].ToString();
this.ddStatus.SelectedItem.Text = reader[9].ToString();
this.txtAssignedTo.Text = reader[10].ToString();
this.txtSolution.Text = reader[11].ToString();
this.txtDateCompleted.Text = reader[12].ToString();
this.txtTimeCompleted.Text = reader[13].ToString();
this.txtCompletedBy.Text = reader[14].ToString();
}
reader.Close();
}
}
}
private void showRoles()
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.Roles";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
ddRole.DataSource = ds;
ddRole.DataTextField = "RoleName";
ddRole.DataValueField = "RoleId";
ddRole.DataBind();
}
}
}
ddRole.Items.Insert(0, new ListItem("", "0"));
}
private void uploadCLS()
{
string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
fuUpload.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[8] {
new DataColumn("Project_Location", typeof(string)),
new DataColumn("Project_Count", typeof(string)),
new DataColumn("Project_No", typeof(string)),
new DataColumn("Project_Name", typeof(string)),
new DataColumn("Project_Request_Date", typeof(string)),
new DataColumn("Project_Close_Date", typeof(string)),
new DataColumn("Project_Type", typeof(string)),
new DataColumn("Project_Duration_Years", typeof(string))
});
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.tblStageClose";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.Bulk_Upload3";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
private void UploadIncidentsLog()
{
string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
fuUpload.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[15] {
new DataColumn("ReqNo", typeof(string)),
new DataColumn("RaisedBy", typeof(string)),
new DataColumn("Office", typeof(string)),
new DataColumn("Project", typeof(string)),
new DataColumn("Area", typeof(string)),
new DataColumn("Date", typeof(string)),
new DataColumn("Time", typeof(string)),
new DataColumn("Detailed_Issue", typeof(string)),
new DataColumn("POL_Coreworx", typeof(string)),
new DataColumn("Status", typeof(string)),
new DataColumn("AssignedTo", typeof(string)),
new DataColumn("Solution", typeof(string)),
new DataColumn("DateCompleted", typeof(string)),
new DataColumn("TimeCompleted", typeof(string)),
new DataColumn("CompletedBy", typeof(string))
});
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.tblStageIssues";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
private void Export(String query)
{
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
//Build the CSV file data as a Comma separated string.
string csv = string.Empty;
foreach (DataColumn column in dt.Columns)
{
//Add the Header row for CSV file.
csv += column.ColumnName + ',';
}
//Add new line.
csv += "\r\n";
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
//Add the Data rows.
csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';
}
//Add new line.
csv += "\r\n";
}
//Download the CSV file.
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv");
Response.Charset = "";
Response.ContentType = "application/text";
Response.Output.Write(csv);
Response.Flush();
Response.End();
}
}
}
}
}
protected void updateDMSSize()
{
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
using (SqlCommand command = con.CreateCommand())
{
command.CommandText = "dbo.updateDMSSize";
con.Open();
command.ExecuteNonQuery();
con.Close();
}
}
}
protected void getUserPermission(string username)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.GetPortalRolePermissionByUser";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
if (reader[3].ToString().ToUpper() == "ProjectsPortal".ToUpper())
{
if (reader[2].ToString().ToUpper() == "WRITE")
{
Session.Add("ISREADONLYPROJECT", false);
}
else
{
Session.Add("ISREADONLYPROJECT", true);
}
}
if (reader[3].ToString().ToUpper() == "IncidentsPortal".ToUpper())
{
if (reader[2].ToString().ToUpper() == "WRITE")
{
Session.Add("ISREADONLYINCIDENT", false);
}
else
{
Session.Add("ISREADONLYINCIDENT", true);
}
}
}
}
else
{
Session.Add("ISREADONLYPROJECT", true);
Session.Add("ISREADONLYINCIDENT", true);
}
conn.Close();
}
}
}
private void uploadActivity()
{
string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
fuUpload.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[10] {
new DataColumn("Project_No", typeof(string)),
new DataColumn("Project_Name", typeof(string)),
new DataColumn("Project", typeof(string)),
new DataColumn("Location", typeof(string)),
new DataColumn("Data_Center", typeof(string)),
new DataColumn("Region", typeof(string)),
new DataColumn("LastSeen", typeof(string)),
new DataColumn("Size_in_MB", typeof(string)),
new DataColumn("TS_FreeMB", typeof(string)),
new DataColumn("Activity", typeof(string))
});
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split('\t'))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.DMS_ActivityTablespace";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.NewProjectLocation";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectLocation", this.txtProjectLocation.Text);
cmd.ExecuteNonQuery();
conn.Close();
}
}
this.txtProjectLocation.Text = "";
}
protected void DeleteFile(object sender, EventArgs e)
{
int id = int.Parse((sender as LinkButton).CommandArgument);
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.DeleteIncidentFile";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", id);
cmd.Parameters.AddWithValue("@UserId", hfUserId.Value);
cmd.ExecuteNonQuery();
conn.Close();
}
}
Response.Redirect(Request.Url.AbsoluteUri);
}
private void addUser(string username, string firstname, string lastname, int roleid)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.InsertUser";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Parameters.AddWithValue("@FirstName", firstname);
cmd.Parameters.AddWithValue("@LastName", lastname);
cmd.Parameters.AddWithValue("@RoleId", roleid);
cmd.ExecuteNonQuery();
conn.Close();
}
}
Response.Redirect("Users.aspx");
}
private DataTable GetData(string username, int parentMenuId)
{
DataTable dt = new DataTable();
string cmdText = "dbo.GetMenuList";
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@ParentMenuId", parentMenuId);
cmd.CommandType = CommandType.StoredProcedure;
adapter.Fill(dt);
}
conn.Close();
}
return(dt);
}
private void showUsers()
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.GetUserList";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvUserList.DataSource = ds;
gvUserList.DataBind();
}
}
}
}
protected void ShowFiles(string projectNo)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.SelectIncidentFiles";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectNumber", projectNo);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvFiles.DataSource = ds;
gvFiles.DataBind();
}
}
}
}
public void InsertProjectCWX(string project_name, string project_number, string sbu, string business_line, string reponsible_office)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.InsertCWX";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectLocation", DBNull.Value);
cmd.Parameters.AddWithValue("@DataCenter", DBNull.Value);
cmd.Parameters.AddWithValue("@SBU", sbu);
cmd.Parameters.AddWithValue("@BusinessLine", business_line);
cmd.Parameters.AddWithValue("@ProjectNumber", project_number);
cmd.Parameters.AddWithValue("@ProjectName", project_name);
cmd.Parameters.AddWithValue("@RequestDate", DBNull.Value);
cmd.Parameters.AddWithValue("@Status", "Pending");
cmd.Parameters.AddWithValue("@Notes", DBNull.Value);
cmd.Parameters.AddWithValue("@Priority", DBNull.Value);
cmd.Parameters.AddWithValue("@ForecastCloseDate", DBNull.Value);
cmd.Parameters.AddWithValue("@PlanForCloseout", DBNull.Value);
cmd.Parameters.AddWithValue("@InboundTVend", DBNull.Value);
cmd.Parameters.AddWithValue("@InboundTEngg", DBNull.Value);
cmd.Parameters.AddWithValue("@DocDistribution", DBNull.Value);
cmd.Parameters.AddWithValue("@Squadcheck", DBNull.Value);
cmd.Parameters.AddWithValue("@OutboundTransmittal", DBNull.Value);
cmd.Parameters.AddWithValue("@WebAPI", DBNull.Value);
cmd.Parameters.AddWithValue("@Brava", DBNull.Value);
cmd.Parameters.AddWithValue("@MDR", DBNull.Value);
cmd.Parameters.AddWithValue("@SetupDate", DBNull.Value);
cmd.Parameters.AddWithValue("@SetUpAssignedTo", DBNull.Value);
cmd.Parameters.AddWithValue("@UserId", hfUserId.Value);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
protected void ShowGrid()
{
this.gvSummary.DataSource = null;
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsCloseProject";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
this.gvSummary.DataSource = ds;
this.gvSummary.DataBind();
}
}
}
}
protected void viewStatistics(int year)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.Statistics1";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvStatistics.DataSource = ds;
gvStatistics.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.SiteCategory";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvSiteCategory.DataSource = ds;
gvSiteCategory.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsMigration";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvMigrationStatus.DataSource = ds;
gvMigrationStatus.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsHis";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvStatisticsHis.DataSource = ds;
gvStatisticsHis.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsRTP";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvDataCenterStatistics1.DataSource = ds;
gvDataCenterStatistics1.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsSG";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvDataCenterStatistics2.DataSource = ds;
gvDataCenterStatistics2.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsPerth";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvDataCenterStatistics3.DataSource = ds;
gvDataCenterStatistics3.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsERR_RTP";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvStatisticsERR1.DataSource = ds;
gvStatisticsERR1.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsERR_SG";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvStatisticsERR2.DataSource = ds;
gvStatisticsERR2.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsPD_RTP";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvStatisticsPD1.DataSource = ds;
gvStatisticsPD1.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsPD_SG";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvStatisticsPD2.DataSource = ds;
gvStatisticsPD2.DataBind();
}
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.StatisticsPD_CLOSED";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", year);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
gvStatisticsPD_Closed.DataSource = ds;
gvStatisticsPD_Closed.DataBind();
}
}
}
}
protected void btnSearchIncident_Click(object sender, EventArgs e)
{
this.gvSummary.DataSource = null;
if (this.txtSearchIncident.Visible == true)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.SelectIncidentByDescription";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Detailed_Issue", this.txtSearchIncident.Text);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
this.gvSummary.DataSource = ds;
this.gvSummary.DataBind();
}
}
}
}
if (this.ddSearchStatus.Visible == true)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.SelectIncidentByStatus";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Status", this.ddSearchStatus.SelectedValue);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
this.gvSummary.DataSource = ds;
this.gvSummary.DataBind();
}
}
}
}
if (this.txtSearchIncidentRaisedBy.Visible == true)
{
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.SelectIncidentByRaisedBy";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RaisedBy", this.txtSearchIncidentRaisedBy.Text);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
this.gvSummary.DataSource = ds;
this.gvSummary.DataBind();
}
}
}
}
}
private void uploadCWX()
{
string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
fuUpload.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[21] {
new DataColumn("Project_Location", typeof(string)),
new DataColumn("Project_Count", typeof(string)),
new DataColumn("Data_Center", typeof(string)),
new DataColumn("Project_No", typeof(string)),
new DataColumn("Project_Name", typeof(string)),
new DataColumn("Request_Date", typeof(string)),
new DataColumn("Status", typeof(string)),
new DataColumn("Notes", typeof(string)),
new DataColumn("Priority", typeof(string)),
new DataColumn("Forecast_Clos_Date", typeof(string)),
new DataColumn("Plan_For_Closeout", typeof(string)),
new DataColumn("SBU", typeof(string)),
new DataColumn("Business_Line", typeof(string)),
new DataColumn("ITV", typeof(string)),
new DataColumn("ITE", typeof(string)),
new DataColumn("Doc_Distribution", typeof(string)),
new DataColumn("Squadcheck", typeof(string)),
new DataColumn("Outboiund_Transmittal", typeof(string)),
new DataColumn("Web_API", typeof(string)),
new DataColumn("Brava", typeof(string)),
new DataColumn("MDR", typeof(string))
});
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.tblStageCWX";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.Bulk_Upload2";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
private void uploadPOL()
{
string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
fuUpload.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[27]
{
new DataColumn("Project_Location", typeof(string)),
new DataColumn("Project_Count", typeof(string)),
new DataColumn("Data_Center", typeof(string)),
new DataColumn("Project_No", typeof(string)),
new DataColumn("Project_Name", typeof(string)),
new DataColumn("Request_Date", typeof(string)),
new DataColumn("Forecast_Close_Date", typeof(string)),
new DataColumn("Migrate_To_CWX", typeof(string)),
new DataColumn("Migrate_To_CWX_Cnt", typeof(string)),
new DataColumn("Inactive_1", typeof(string)),
new DataColumn("Inactive_2", typeof(string)),
new DataColumn("Plan_For_Closeout", typeof(string)),
new DataColumn("ERR_Request_Date", typeof(string)),
new DataColumn("Delete_Request_Date", typeof(string)),
new DataColumn("Inactive_Project_For", typeof(string)),
new DataColumn("Migration_Plan", typeof(string)),
new DataColumn("Column1", typeof(string)),
new DataColumn("Column2", typeof(string)),
new DataColumn("Column3", typeof(string)),
new DataColumn("Notes", typeof(string)),
new DataColumn("Global_Template_Version", typeof(string)),
new DataColumn("No_Active_PCS", typeof(string)),
new DataColumn("EDR1", typeof(string)),
new DataColumn("AP_Data_Center", typeof(string)),
new DataColumn("EDR2", typeof(string)),
new DataColumn("ERR_Use", typeof(string)),
new DataColumn("No_ERR_Docs", typeof(string))
});
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
using (SqlConnection con = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.tblStagePOL";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
using (SqlConnection conn = new SqlConnection(GlobalProperties.SqlConnectionString()))
{
string cmdText = "dbo.Bulk_Upload1";
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
