void PostMedicine()
{
GridView5.DataSource = ClassDataManager.LoadMedicines(illnessc);
GridView5.DataBind();
}
protected void grdvw_List_RowEditing(object sender, GridViewEditEventArgs e)
{
strSelectedId = grdvw_List.Rows[e.NewEditIndex].Cells[1].Text;
txt_ItemName.Text = grdvw_List.Rows[e.NewEditIndex].Cells[3].Text;
txt_wtdw.Text = grdvw_List.Rows[e.NewEditIndex].Cells[2].Text;
txt_man.Text = grdvw_List.Rows[e.NewEditIndex].Cells[4].Text;
txt_tel.Text = grdvw_List.Rows[e.NewEditIndex].Cells[5].Text;
if (grdvw_List.Rows[e.NewEditIndex].Cells[2].Text.Trim() != " ")
{
txt_AccessTime.Text = grdvw_List.Rows[e.NewEditIndex].Cells[7].Text;
}
else
{
txt_AccessTime.Text = "";
}
txt_person0.Text = grdvw_List.Rows[e.NewEditIndex].Cells[6].Text;
txt_ItemName.ReadOnly = true;
txt_person0.ReadOnly = true;
txt_wtdw.ReadOnly = true;
txt_man.ReadOnly = true;
txt_tel.ReadOnly = true;
//备注绑定
//项目接收备注显示
string remarkstr = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='1' order by t_Y_Detail.id";
DataSet ds_Remark1 = new MyDataOp(remarkstr).CreateDataSet();
string strtemp = "select Name,UserID from t_R_UserInfo";
DataSet ds_User = new MyDataOp(strtemp).CreateDataSet();
foreach (DataRow dr in ds_Remark1.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView1.DataSource = ds_Remark1;
GridView1.DataBind();
string remarkstr2 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='2' order by t_Y_Detail.id";
DataSet ds_Remark2 = new MyDataOp(remarkstr2).CreateDataSet();
foreach (DataRow dr in ds_Remark2.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView2.DataSource = ds_Remark2;
GridView2.DataBind();
string remarkstr3 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='3' order by t_Y_Detail.id";
DataSet ds_Remark3 = new MyDataOp(remarkstr3).CreateDataSet();
foreach (DataRow dr in ds_Remark3.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView3.DataSource = ds_Remark3;
GridView3.DataBind();
string remarkstr4 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='4' order by t_Y_Detail.id";
DataSet ds_Remark4 = new MyDataOp(remarkstr4).CreateDataSet();
foreach (DataRow dr in ds_Remark4.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView4.DataSource = ds_Remark4;
GridView4.DataBind();
string remarkstr5 = "select CreateDate 备注时间,bz 备注及意见,userid 用户名 from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='5' order by t_Y_Detail.id";
DataSet ds_Remark5 = new MyDataOp(remarkstr5).CreateDataSet();
foreach (DataRow dr in ds_Remark5.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView5.DataSource = ds_Remark5;
GridView5.DataBind();
//备注绑定
//有回退的，则显示回退备注，非回退，不显示
string backremarkstr = "select createdate 备注时间,t_Y_BackInfo.remark 备注及意见,userid 用户名 from t_Y_BackInfo inner join t_Y_FlowInfo on t_Y_BackInfo.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_BackInfo.functionid where t_Y_BackInfo.itemid='" + strSelectedId + "' and t_Y_BackInfo.functionid='7' order by t_Y_BackInfo.id";
// string backremarkstr = "select * from t_Y_BackInfo where itemid='" + strSelectedId + "' and functionid='2'";
DataSet ds_Remark_back = new MyDataOp(backremarkstr).CreateDataSet();
foreach (DataRow dr in ds_Remark_back.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView_back.DataSource = ds_Remark_back;
GridView_back.DataBind();
//回退的数据编辑，显示前面的备注信息，只读
string remarkstr_now = "select ItemName 项目类型, name 阶段,CreateDate 备注时间,bz 备注及意见,userid 用户名,flag,t_Y_Detail.id from t_Y_Detail inner join t_Y_FlowInfo on t_Y_Detail.itemid=t_Y_FlowInfo.id inner join t_Y_FlowDetail on t_Y_FlowDetail.id= t_Y_Detail.statusid where t_Y_Detail.itemid='" + strSelectedId + "' and t_Y_Detail.statusid='6' order by t_Y_Detail.id";
DataSet ds_Remark_now = new MyDataOp(remarkstr_now).CreateDataSet();
foreach (DataRow dr in ds_Remark_now.Tables[0].Rows)
{
foreach (DataRow drr in ds_User.Tables[0].Rows)
{
if (dr["用户名"].ToString() == drr["UserID"].ToString())
{
dr["用户名"] = drr["Name"].ToString();
}
}
}
GridView_now.DataSource = ds_Remark_now;
GridView_now.DataBind();
DataRow[] dr_remark = ds_Remark_now.Tables[0].Select("flag=0");
if (dr_remark.Length > 0)
{
txt_Remark_now.Text = dr_remark[0][3].ToString();
SelectedId = dr_remark[0][6].ToString();
}
if (ds_Remark_back.Tables[0].Rows.Count > 0)
{
Panel_back.Visible = true;
}
else
{
Panel_back.Visible = false;
}
ds_Remark1.Dispose();
ds_Remark2.Dispose();
ds_Remark3.Dispose();
ds_Remark4.Dispose();
ds_Remark5.Dispose();
// ds_Remark6.Dispose();
//ds_Remark7.Dispose();
// ds_Remark8.Dispose();
// ds_Remark9.Dispose();
//ds_Remark10.Dispose();
//ds_Remark11.Dispose();
//ds_Remark12.Dispose();
//ds_Remark13.Dispose();
//ds_Remark14.Dispose();
ds_Remark_back.Dispose();
ds_Remark_now.Dispose();
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", "showAddEdit();", true);
}
void DislayGrids()
{
GridView1.DataSource = c.GeneralPatientInfo(pid);
if (GridView1.DataSource != null)
{
Label1.Text = "";
GridView1.DataBind();
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
foreach (GridViewRow row in GridView1.Rows)
{
foreach (TableCell cell in row.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
}
}
else
{
Label1.Text = "No Personal Info";
}
GridView2.DataSource = c.prediagnosis(pid);
if (GridView2.DataSource != null)
{
Label2.Text = "";
GridView2.DataBind();
foreach (TableCell cell in GridView2.HeaderRow.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
foreach (GridViewRow row in GridView2.Rows)
{
foreach (TableCell cell in row.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
}
}
else
{
Label2.Text = "No Previos Diagnosis";
}
GridView3.DataSource = c.getsurgeries(pid);
if (GridView3.DataSource != null)
{
Label3.Text = "";
GridView3.DataBind();
foreach (TableCell cell in GridView3.HeaderRow.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
foreach (GridViewRow row in GridView3.Rows)
{
foreach (TableCell cell in row.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
}
}
else
{
Label3.Text = "No Previous Surgeries";
}
GridView4.DataSource = c.bloodtransfer(pid);
if (GridView4.DataSource != null)
{
Label4.Text = "";
GridView4.DataBind();
foreach (TableCell cell in GridView4.HeaderRow.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
foreach (GridViewRow row in GridView4.Rows)
{
foreach (TableCell cell in row.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
}
}
else
{
Label4.Text = "No Previous BloodTransfusions";
}
GridView5.DataSource = c.Getallergies(pid);
if (GridView5.DataSource != null)
{
Label5.Text = "";
GridView5.DataBind();
foreach (TableCell cell in GridView5.HeaderRow.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
foreach (GridViewRow row in GridView5.Rows)
{
foreach (TableCell cell in row.Cells)
{
cell.Attributes.CssStyle["text-align"] = "center";
}
}
}
else
{
Label5.Text = "No Allergies";
}
}
public void resultRepeat()
{
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("S.S.T", bd);
if (registros.Rows.Count > 0)
{
GridView1.DataSource = registros;
GridView1.DataBind();
}
else
{
GridView1.DataSource = null;
GridView1.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("P.E.S.V", bd);
if (registros.Rows.Count > 0)
{
GridView2.DataSource = registros;
GridView2.DataBind();
}
else
{
GridView2.DataSource = null;
GridView2.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("RRHH", bd);
if (registros.Rows.Count > 0)
{
GridView3.DataSource = registros;
GridView3.DataBind();
}
else
{
GridView3.DataSource = null;
GridView3.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("Correspondencia", bd);
if (registros.Rows.Count > 0)
{
GridView4.DataSource = registros;
GridView4.DataBind();
}
else
{
GridView4.DataSource = null;
GridView4.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("Plan_Contingencia", bd);
if (registros.Rows.Count > 0)
{
GridView5.DataSource = registros;
GridView5.DataBind();
}
else
{
GridView5.DataSource = null;
GridView5.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("Covid_19", bd);
if (registros.Rows.Count > 0)
{
GridView6.DataSource = registros;
GridView6.DataBind();
}
else
{
GridView6.DataSource = null;
GridView6.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("Examen_MA", bd);
if (registros.Rows.Count > 0)
{
GridView7.DataSource = registros;
GridView7.DataBind();
}
else
{
GridView7.DataSource = null;
GridView7.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("Plan_MA", bd);
if (registros.Rows.Count > 0)
{
GridView8.DataSource = registros;
GridView8.DataBind();
}
else
{
GridView8.DataSource = null;
GridView8.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("CursosMA", bd);
if (registros.Rows.Count > 0)
{
GridView9.DataSource = registros;
GridView9.DataBind();
}
else
{
GridView9.DataSource = null;
GridView9.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
try
{
String bd = Session["BD"].ToString();
var registros = Controlasql.ClistFiles("Formatos_sanidad", bd);
if (registros.Rows.Count > 0)
{
GridView10.DataSource = registros;
GridView10.DataBind();
}
else
{
GridView10.DataSource = null;
GridView10.DataBind();
}
}
catch (Exception ex)
{
alerta.MessageBox(this, $"{ex}");
// Response.Redirect("Exceptionnet.aspx");
}
}
protected void GridView5_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView5.PageIndex = e.NewPageIndex;
GridView5.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();
SqlConnection con = new SqlConnection(connectionString);
try
{
con.Open();
}
catch (Exception)
{
con.Close();
return;
throw;
}
string pNumber = TextBoxNumber.Text;
string pName = TextBoxName.Text;
string personalID = TextBoxPersonalID.Text;
string pType = "C";
string category = TextBoxCategory.Text;
string materials = TextBoxMaterials.Text;
string sqlStrPiece = "INSERT INTO Piece_T (PieceNumber, PieceName, PieceDescription, YearOfMade, EntryDay, EntryMonth, EntryYear, EntryHour, CountryOfOrigin, PersonalID, PieceType) VALUES("
+ pNumber + ", '" + pName + "', ";
string sqlStrClothes = "INSERT INTO Clothes_T (CPieceNumber, Category, ClothesOwner) VALUES(" + pNumber + ", '" + category + "', ";
string sqlStrClothesMaterials;
if (!string.IsNullOrEmpty(TextBoxDecription.Text))
{
sqlStrPiece += "'" + TextBoxDecription.Text + "', ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxYoM.Text))
{
sqlStrPiece += TextBoxYoM.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxDay.Text))
{
sqlStrPiece += TextBoxDay.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxMonth.Text))
{
sqlStrPiece += TextBoxMonth.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxYear.Text))
{
sqlStrPiece += TextBoxYear.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxHour.Text))
{
sqlStrPiece += TextBoxHour.Text + ", ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxCoO.Text))
{
sqlStrPiece += "'" + TextBoxCoO.Text + "', ";
}
else
{
sqlStrPiece += "NULL, ";
}
if (!string.IsNullOrEmpty(TextBoxOwner.Text))
{
sqlStrClothes += "'" + TextBoxOwner.Text + "');";
}
else
{
sqlStrClothes += "NULL);";
}
sqlStrPiece += personalID + ", '" + pType + "');";
SqlDataAdapter adapter1 = new SqlDataAdapter();
SqlCommand execPiece = new SqlCommand(sqlStrPiece, con);
adapter1.InsertCommand = new SqlCommand(sqlStrPiece, con);
adapter1.InsertCommand.ExecuteNonQuery();
execPiece.Dispose();
SqlDataAdapter adapter2 = new SqlDataAdapter();
SqlCommand execTools = new SqlCommand(sqlStrClothes, con);
adapter2.InsertCommand = new SqlCommand(sqlStrClothes, con);
adapter2.InsertCommand.ExecuteNonQuery();
execTools.Dispose();
using (StringReader reader = new StringReader(materials))
{
string line;
while ((line = reader.ReadLine()) != null)
{
sqlStrClothesMaterials = "INSERT INTO Clothes_Materials_T (CPieceNumber, Material) VALUES(" + pNumber + ", '" + line + "');";
SqlDataAdapter adapter3 = new SqlDataAdapter();
SqlCommand execTMat = new SqlCommand(sqlStrClothesMaterials, con);
adapter3.InsertCommand = new SqlCommand(sqlStrClothesMaterials, con);
adapter3.InsertCommand.ExecuteNonQuery();
execTMat.Dispose();
}
}
DataSet ds1 = new DataSet();
string sqlstr = "select * from Piece_T";
SqlDataAdapter da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds1);
GridView3.DataSource = ds1;
GridView3.DataBind();
DataSet ds2 = new DataSet();
sqlstr = "select * from Clothes_T";
da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds2);
GridView4.DataSource = ds2;
GridView4.DataBind();
DataSet ds3 = new DataSet();
sqlstr = "select * from Clothes_Materials_T";
da = new SqlDataAdapter(sqlstr, con);
da.Fill(ds3);
GridView5.DataSource = ds3;
GridView5.DataBind();
}
void PostDrugClass()
{
GridView5.DataSource = ClassDataManager.LoadDrugClass(drugi);
GridView5.DataBind();
}
protected void TextBox7_TextChanged(object sender, EventArgs e)
{
Label9.Text = "";
bool check1 = true, check2 = true;
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
string query = "SELECT a.accountID, a.fullName, a.email, SUM(h.workingHour) AS totalHour, h.requestID FROM HourSpent AS h JOIN Account AS a ON h.accountID=a.accountID WHERE h.accountID=@ittechID GROUP BY h.requestID, a.accountID, a.fullName, a.email ORDER BY a.accountID";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@ittechID", TextBox7.Text.Trim());
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
GridView5.DataSource = sdr;
GridView5.DataBind();
}
else
{
check1 = false;
}
con.Close();
con.Open();
query = "SELECT OverWorkingHour.accountID, Account.fullName, Account.email, OverWorkingHour.rate, SUM(OverWorkingHour.overHour) AS totalOverHour, OverWorkingHour.requestID FROM OverWorkingHour JOIN Account ON OverWorkingHour.accountID = Account.accountID WHERE OverWorkingHour.accountID=@ittechID GROUP BY OverWorkingHour.requestID, OverWorkingHour.accountID, Account.fullName, Account.email, OverWorkingHour.rate ORDER BY OverWorkingHour.accountID";
cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@ittechID", TextBox7.Text.Trim());
sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
GridView6.DataSource = sdr;
GridView6.DataBind();
}
else
{
check2 = false;
}
con.Close();
if (!check1 && !check2)
{
Label9.Text = "No Data Found. Please refer to two tables below.";
}
else if (check1 && !check2)
{
Label9.Text = "No Data Found. Please refer to the total overtime hour table.";
}
else if (!check1 && check2)
{
Label9.Text = "No Data Found. Please refer to the total office hour table.";
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["user"] != "Project Manager")
{
Response.Redirect("login.aspx");
}
string accountID = "";
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
string userN = Session["userN"].ToString();
string que = "SELECT accountID FROM Account WHERE userName LIKE '" + userN + "%'";
SqlCommand cmd2 = new SqlCommand(que, con);
SqlDataReader sdr2 = cmd2.ExecuteReader();
while (sdr2.Read())
{
accountID = sdr2["accountID"].ToString();
}
con.Close();
con.Open();
string query = "SELECT Request.requestID, Account.fullName, Account.email, Request.issueName, Request.description, convert(varchar, Request.requestDate, 101) AS requestDate FROM Request JOIN Account ON Request.accountID = Account.accountID AND personInCharge=1";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader sdr = cmd.ExecuteReader();
GridView1.DataSource = sdr;
GridView1.DataBind();
con.Close();
con.Open();
query = "SELECT accountID, 'IT Techinician with ID: ' + CONVERT(varchar(10), accountID) AS fullDesc FROM Account WHERE accountID != 1 AND role = 'IT Technician'";
cmd = new SqlCommand(query, con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
if (!Page.IsPostBack)
{
DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "fullDesc";
DropDownList1.DataValueField = "accountID";
DropDownList1.DataBind();
}
con.Close();
con.Open();
query = "SELECT Account.accountID, Account.fullName, Account.email, Request.requestID, Request.issueName, Request.description, convert(varchar, Request.assignedDate, 101) AS assignedDate, ITTechStatus = (SELECT CASE WHEN COUNT(requestID) >2 THEN 'Busy' ELSE 'Available' END AS ITStatus FROM Request WHERE personInCharge = Account.accountID AND status != 'Completed' AND assignedDate IS NOT NULL GROUP BY personInCharge ) FROM Request JOIN Account ON Request.personInCharge = Account.accountID AND Request.assignedDate IS NOT NULL AND Request.status != 'Completed'";
cmd = new SqlCommand(query, con);
sdr = cmd.ExecuteReader();
GridView2.DataSource = sdr;
GridView2.DataBind();
con.Close();
con.Open();
query = "SELECT OverWorkingHour.overTimeID, Account.fullName, Account.email, OverWorkingHour.requestID, convert(varchar, OverWorkingHour.date, 101) AS date, OverWorkingHour.overHour, OverWorkingHour.rate, (OverWorkingHour.rate * OverWorkingHour.overHour) AS earn FROM OverWorkingHour JOIN Account ON OverWorkingHour.accountID = Account.accountID";
cmd = new SqlCommand(query, con);
sdr = cmd.ExecuteReader();
GridView3.DataSource = sdr;
GridView3.DataBind();
con.Close();
con.Open();
query = "SELECT Request.requestID, Request.issueName, Request.description, Request.personInCharge, convert(varchar, Request.requestDate, 101) AS requestDate, convert(varchar, Request.assignedDate, 101) AS assignedDate, convert(varchar, getdate(), 101) AS todayDate, DATEDIFF(day, Request.assignedDate, getdate()) AS dateSpent FROM Request WHERE personInCharge!= 1 AND status != 'Completed' AND DATEDIFF(day, Request.assignedDate, getdate()) > 7";
cmd = new SqlCommand(query, con);
sdr = cmd.ExecuteReader();
GridView4.DataSource = sdr;
GridView4.DataBind();
con.Close();
con.Open();
query = "SELECT accountID, 'IT Techinician with ID: ' + CONVERT(varchar(10), accountID) AS fullDesc FROM Account WHERE accountID != 1 AND role = 'IT Technician'";
cmd = new SqlCommand(query, con);
sda = new SqlDataAdapter(cmd);
ds = new DataSet();
sda.Fill(ds);
if (!Page.IsPostBack)
{
DropDownList2.DataSource = ds;
DropDownList2.DataTextField = "fullDesc";
DropDownList2.DataValueField = "accountID";
DropDownList2.DataBind();
}
con.Close();
con.Open();
query = "SELECT a.accountID, a.fullName, a.email, SUM(h.workingHour) AS totalHour, h.requestID FROM HourSpent AS h JOIN Account AS a ON h.accountID=a.accountID GROUP BY h.requestID, a.accountID, a.fullName, a.email ORDER BY a.accountID";
cmd = new SqlCommand(query, con);
sdr = cmd.ExecuteReader();
GridView5.DataSource = sdr;
GridView5.DataBind();
con.Close();
con.Open();
query = "SELECT OverWorkingHour.accountID, Account.fullName, Account.email, OverWorkingHour.rate, SUM(OverWorkingHour.overHour) AS totalOverHour, OverWorkingHour.requestID FROM OverWorkingHour JOIN Account ON OverWorkingHour.accountID = Account.accountID GROUP BY OverWorkingHour.requestID, OverWorkingHour.accountID, Account.fullName, Account.email, OverWorkingHour.rate ORDER BY OverWorkingHour.accountID";
cmd = new SqlCommand(query, con);
sdr = cmd.ExecuteReader();
GridView6.DataSource = sdr;
GridView6.DataBind();
con.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (Button1.Text == "Clear")
{
Button1.Text = "Submit";
ddlstate_countynew.SelectedIndex = -1;
ddlfpisnew.SelectedIndex = -1;
ddlProcessingtypenew.SelectedIndex = -1;
ddlFileTypenew.SelectedIndex = -1;
ddlIssueTypenew.SelectedIndex = -1;
txtTitlenew.Text = "";
txtIssueDetailsnew.Text = "";
txtResolutionnew.Text = "";
txtEditionnew.Text = "";
txtVersionnew.Text = "";
txtSubmitternew.Text = "";
txtRelatedLinksnew.Text = "";
txtICPnew.Text = "";
Panel2.GroupingText = "Add New";
GridView5.SelectedIndex = -1;
GridView5.DataBind();
}
string FIPSCounty = "00000";
if (ddlstate_countynew.SelectedValue.ToString() == "NW")
{
FIPSCounty = "00000";
}
else if (ddlstate_countynew.SelectedValue.ToString() == "0")
{
if (ddlfpisnew.SelectedValue.ToString() != "0")
{
FIPSCounty = ddlfpisnew.SelectedValue.ToString();
}
}
else
{
FIPSCounty = ddlstate_countynew.SelectedValue.ToString();
}
string IDProcessingType = ddlProcessingtypenew.SelectedValue.ToString();
string IDFileType = ddlFileTypenew.SelectedValue.ToString();
string IDIssueType = ddlIssueTypenew.SelectedValue.ToString();
string Edition = txtEditionnew.Text;
string strVersion = txtVersionnew.Text;
string Title = txtTitlenew.Text;
string IssueDetails = txtIssueDetailsnew.Text;
string Resolution = txtResolutionnew.Text;
string Submitter = txtSubmitternew.Text;
string Relatedlink = txtRelatedLinksnew.Text;
string ICP = txtICPnew.Text;
string IssueCreatedDate = DateTime.Now.ToString("yyyy/MM/dd");
string command = "insert into [Issuetbl](FIPSCounty,IDProcessingType,IDFileType,IDIssueType,Edition,Version,Title,IssueDetails,Resolution," +
"Submitter,Relatedlink,ICP,IssueCreatedDate) " +
" Values('" + FIPSCounty + "'," + IDProcessingType + "," + IDFileType + "," + IDIssueType + "," + Edition + "," +
strVersion + ",'" + Title + "','" + IssueDetails + "','" + Resolution + "','" + Submitter + "','" + Relatedlink + "'," + ICP + ",'" +
IssueCreatedDate + "')";
try
{
SqlConnection conn = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand(command, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
ddlstate_countynew.SelectedIndex = 0;
ddlfpisnew.SelectedIndex = 0;
ddlProcessingtypenew.SelectedIndex = 0;
ddlFileTypenew.SelectedIndex = 0;
ddlIssueTypenew.SelectedIndex = 0;
txtEditionnew.Text = "";
txtVersionnew.Text = "";
txtTitlenew.Text = "";
txtIssueDetailsnew.Text = "";
txtResolutionnew.Text = "";
txtICPnew.Text = "";
txtSubmitternew.Text = "";
txtRelatedLinksnew.Text = "";
GridView5.DataBind();
//GridView6.DataBind();
GridViewResult.DataBind();
}
catch (Exception ex)
{
}
}
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = 'C:\Users\Neel Rayal\Documents\Ad_project2.mdf'; Integrated Security = True; Connect Timeout = 30");
string temp1 = " select * from page1db where Id = " + "('" + Session["Id"] + "')";
SqlCommand cmd1 = new SqlCommand(temp1, con);
SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
GridView1.DataSource = dt1;
GridView1.DataBind();
string temp2 = " select * from page2db where Id = " + "('" + Session["Id"] + "')";
SqlCommand cmd2 = new SqlCommand(temp2, con);
SqlDataAdapter sda2 = new SqlDataAdapter(cmd2);
DataTable dt2 = new DataTable();
sda2.Fill(dt2);
GridView2.DataSource = dt2;
GridView2.DataBind();
string temp3 = " select * from page3db where Id = " + "('" + Session["Id"] + "')";
string temp4 = " select * from page4db where Id = " + "('" + Session["Id"] + "')";
string temp5 = " select * from page5db where Id = " + "('" + Session["Id"] + "')";
string temp6 = " select * from page6db where Id = " + "('" + Session["Id"] + "')";
SqlCommand cmd3 = new SqlCommand(temp3, con);
SqlCommand cmd4 = new SqlCommand(temp4, con);
SqlCommand cmd5 = new SqlCommand(temp5, con);
SqlCommand cmd6 = new SqlCommand(temp6, con);
SqlDataAdapter sda3 = new SqlDataAdapter(cmd3);
SqlDataAdapter sda4 = new SqlDataAdapter(cmd4);
SqlDataAdapter sda5 = new SqlDataAdapter(cmd5);
SqlDataAdapter sda6 = new SqlDataAdapter(cmd6);
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable dt5 = new DataTable();
DataTable dt6 = new DataTable();
sda3.Fill(dt3);
GridView3.DataSource = dt3;
GridView3.DataBind();
sda4.Fill(dt4);
GridView4.DataSource = dt4;
GridView4.DataBind();
sda5.Fill(dt5);
GridView5.DataSource = dt5;
GridView5.DataBind();
sda6.Fill(dt6);
GridView6.DataSource = dt6;
GridView6.DataBind();
}