protected void Page_Load(object sender, EventArgs e) { _role = CheckAccount(); Date.Text = DateTime.Now.ToLongDateString(); if (!string.IsNullOrEmpty(Request["t"])) { _type = int.Parse(Request["t"]); } if (!IsPostBack) { KOS.App_Code.ClearTemp clear = new App_Code.ClearTemp(Request); clear.DeleteOld(); UpdateLabel(); if (User.Identity.Name == "Миракс_Парк") { ButtonReg.Visible = false; } else if (User.Identity.Name == "Корона_1") { ButtonReg.Visible = false; } List <Data> data = GetData(); Out.DataSource = data; Out.DataBind(); } }
DataSet MyDataSet = new DataSet(); //for pdf protected void Page_Load(object sender, EventArgs e) { _role = CheckAccount(); if (!string.IsNullOrEmpty(Request["t"])) { _type = int.Parse(Request["t"]); } if (!IsPostBack) { KOS.App_Code.ClearTemp clear = new App_Code.ClearTemp(Request); clear.DeleteOld(); if (_type == 0) { Period.Visible = true; Beg.SelectedDate = DateTime.Now.AddDays(-2); End.SelectedDate = DateTime.Now; phReport.Visible = false; } else { Period.Visible = false; phReport.Visible = true; } UpdateLabel(); List <Data> data = GetData(); Out.DataSource = data; Out.DataBind(); } }
protected void Show_Click(object sender, EventArgs e) { string w = BuildWhere(); if (!string.IsNullOrEmpty(w)) { List <Report> data = new List <Report>(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); string s = "select wz.Id, wz.[Date], ui.Family, ui.[IO] from WorkerZayavky wz " + "join UserInfo ui on ui.UserId=wz.UserId"; s += w + " order by wz.[Date]"; SqlCommand cmd = new SqlCommand(s, conn); cmd.Parameters.AddWithValue("start", Calendar.SelectedDate.Date); cmd.Parameters.AddWithValue("end", CalendarEnd.SelectedDate.Date.AddDays(1)); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { data.Add(new Report() { Fio = dr["Family"].ToString() + " " + dr["IO"].ToString(), Date = ((DateTime)dr["Date"]).ToShortDateString(), Url = "clickIt('WZClose.aspx?wz=" + dr["Id"].ToString() + "')" }); } dr.Close(); } Out.DataSource = data; Out.DataBind(); Qst.Visible = false; phReport.Visible = true; } }
protected void Sklsdd_Click(object sender, EventArgs e) { phView.Visible = true; List <Data> datas = GetData1(); Out.DataSource = datas; Out.DataBind(); }
protected void DoIt_Click(object sender, EventArgs e) { List <Data> data = GetData(); Out.DataSource = data; Out.DataBind(); // Period.Visible = false; // phReport.Visible = true; }
protected void DocE_Click(object sender, EventArgs e) { List <Data> datadoc = GetData(); Out.DataSource = datadoc; Out.DataBind(); PartL.Visible = false; DocEv.Visible = true; }
protected void PoiskParam_Click(object sender, EventArgs e) { Msg.Text = ""; phView.Visible = true; phPoisk.Visible = false; phZap.Visible = false; phVse.Visible = false; List <Data> datas = GetData1(); Out.DataSource = datas; Out.DataBind(); }
protected void Save_Click(object sender, EventArgs e) { Msg.Text = ""; if (string.IsNullOrEmpty(DocName.Text)) { Msg.Text = "Внимание! Вы забыли ввести наименование доумента!"; return; } // запись using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into Documents " + "([NumEvent], [Name], [Image], [NameFile], [Status], [Prim], [Usr]) " + "values (@nev, @name, @img, @namefile, @st, @prim, @user) ", conn); cmd.Parameters.AddWithValue("nev", _wz); cmd.Parameters.AddWithValue("name", DocName.Text); cmd.Parameters.AddWithValue("st", "подписан"); cmd.Parameters.AddWithValue("prim", " "); cmd.Parameters.AddWithValue("user", User.Identity.Name); // DateTime date; date = DateTime.Now; // cmd.Parameters.AddWithValue("date", date); //выбор из устройства string namePhoto = FileUpload1.FileName; //преобразование в двоичный код byte[] photo = FileUpload1.FileBytes; if (string.IsNullOrEmpty(namePhoto)) { Msg.Text = "Внимание! Документ не выбран!"; return; } //Запись документа и его имени файла в БД cmd.Parameters.Add("img", SqlDbType.Image).Value = photo; cmd.Parameters.Add("namefile", SqlDbType.NVarChar).Value = namePhoto; cmd.ExecuteNonQuery(); } Msg.Text = "Документ присоединен к Событию! Добавьте ещё документ или нажмите на логотип [EMICATECH] для возврата на страницу Активных событий!"; List <Data> datadoc = GetData(); Out.DataSource = datadoc; Out.DataBind(); DocVw.Visible = true; }
protected void Page_Load(object sender, EventArgs e) { _role = CheckAccount(); // _role = "ODS"; if (!string.IsNullOrEmpty(Request["t"])) { _type = int.Parse(Request["t"]); } if (!IsPostBack) { KOS.App_Code.ClearTemp clear = new App_Code.ClearTemp(Request); clear.DeleteOld(); List <Data> data = GetData(); Out.DataSource = data; Out.DataBind(); } }
protected void Page_Load(object sender, EventArgs e) { CheckAccount(); if (!string.IsNullOrEmpty(Request["PlanId"])) { _planId = Int32.Parse(Request["PlanId"]); } if (!string.IsNullOrEmpty(Request["ret"])) { _retUrl = HttpUtility.HtmlDecode(Request["ret"]); } else { Grafik.Visible = false; } App_Code.Base db = new App_Code.Base(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()); _planIds = db.GetPlans(User.Identity.Name, _planId); _prim = db.GetPrim(User.Identity.Name, _planId); List <TheN> n = new List <TheN>(); foreach (DataRow dr in _prim.Rows) { n.Add(new TheN() { N = (int)dr["N"] }); } _data = db.GetReglamentWorks(_planId); _data.Columns.Add("bDone", Type.GetType("System.Boolean")); _data.Columns.Add("AddPrim", Type.GetType("System.String")); _data.Columns.Add("PrimExists", Type.GetType("System.String")); foreach (DataRow dr in _data.Rows) { if (dr["Done"] is DBNull || bool.Parse(dr["Done"].ToString()) == false) { dr["bDone"] = false; } else { dr["bDone"] = true; } // if (dr["WorksId"] is DBNull) // было закомментировано dr["AddPrim"] = "~/AddPrim.aspx?PlanId=" + _planIds + "&ReglamentId=" + dr["ReglamentId"].ToString(); // else // было закомментировано 2 стр. // dr["AddPrim"] = "~/AddPrim.aspx?WorksId=" + dr["WorksId"].ToString() + "&PlanId=" + _planId + "&ReglamentId=" + dr["ReglamentId"].ToString(); dr["PrimExists"] = ""; if (!(dr["N"] is DBNull)) { int N = (int)dr["N"]; if (n.Find(delegate(TheN i) { return(i.N == N); }) != null) { dr["PrimExists"] = "!!!"; } } } _liftsId = db.GetLiftsId(_planId); _liftId = db.GetLiftId(_planId); /* string liftsId = string.Empty;// начало коммента * foreach (string s in _liftsId) * if (string.IsNullOrEmpty(liftsId)) * liftsId = s; * else * liftsId += "," + s; */ // конец коммента List <Data> data = new List <Data>(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); SqlCommand cmd = new SqlCommand("select TpId from [Plan] where Id=@id", conn); cmd.Parameters.AddWithValue("id", _planId); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { _TpId = dr["TpId"].ToString(); } dr.Close(); } // using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); TpId.Text = _TpId; LiftsId.Text = _liftId; SqlCommand cmd = new SqlCommand("select r.[Title] from [Reglament] r " + "where r.[TpId]=@rId", conn); /* if (TpId.Text == "ТР") * cmd.Parameters.AddWithValue("rId", "ТР"); * else if (TpId.Text == "ТР1") * cmd.Parameters.AddWithValue("rId", "ТР1"); * else if (TpId.Text == "ТР2") * cmd.Parameters.AddWithValue("rId", "ТР2"); * else if (TpId.Text == "ТР3") * cmd.Parameters.AddWithValue("rId", "ТР3"); * else */ cmd.Parameters.AddWithValue("rId", TpId.Text); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { data.Add(new Data() { Title = dr["Title"].ToString() }); Out.DataSource = data; Out.DataBind(); } dr.Close(); } // if (!IsPostBack) { ReglamentWorks.DataSource = _data; ReglamentWorks.DataBind(); Msg.Text = string.Empty; List <Base.ZPrim> list = db.GetNotDonePrim(_liftId, Request.Url.ToString()); ZPrim.DataSource = list; ZPrim.DataBind(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); SqlCommand cmd = new SqlCommand("select Done from [Plan] where Id=@id", conn); cmd.Parameters.AddWithValue("id", _planId); object o = cmd.ExecuteScalar(); if (o != null) { Done.Checked = bool.Parse(o.ToString()); } cmd = new SqlCommand("select Prn from [Plan] where Id=@id", conn); cmd.Parameters.AddWithValue("id", _planId); string op = cmd.ExecuteScalar().ToString(); if (op == "") { Prin.Checked = false; } else { Prin.Checked = bool.Parse(op.ToString()); } } if (User.Identity.Name == "Volodin" || User.Identity.Name == "Puzin" || User.Identity.Name == "manager") { Prin.Visible = true; btnPrin.Visible = true; } if (TpId.Text == "ВР" && User.Identity.Name == "manager") { AddBP.Visible = true; TextBox1.Visible = true; L1.Visible = true; } } }
protected void Page_Load(object sender, EventArgs e) { if (string.IsNullOrEmpty(Request["zId"])) { // if (!string.IsNullOrEmpty(Request["zId"]) && !IsPostBack) Response.Redirect("~/About.aspx"); } _wz = Int32.Parse(Request["zId"]); // if (User.Identity.Name != "ODS14") { Treg.Visible = false; Tzak.Visible = false; } if (!IsPostBack) { App_Code.Base db = new App_Code.Base(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()); DataTable data = db.GetEvent(_wz); if (data.Rows.Count < 1) { Response.Redirect("~/About.aspx"); } Id.Text = _wz.ToString(); Sourse.Text = data.Rows[0]["Sourse"].ToString(); // Text1.Text = data.Rows[0]["Sourse"].ToString(); IO.Text = data.Rows[0]["IO"].ToString(); DataId.Text = data.Rows[0]["DataId"].ToString(); dateReg = ((DateTime)data.Rows[0]["DataId"]); ZayavId.Text = data.Rows[0]["ZayavId"].ToString(); RegistrId.Text = data.Rows[0]["RegistrId"].ToString(); LiftId.Text = data.Rows[0]["LiftId"].ToString(); // TextBox4.Text = data.Rows[0]["LiftId"].ToString(); TypeId.Text = data.Rows[0]["TypeId"].ToString(); EventId.Text = data.Rows[0]["EventId"].ToString(); // Text.Text = data.Rows[0]["EventId"].ToString(); ToApp.Text = data.Rows[0]["ToApp"].ToString(); // Text2.Text = data.Rows[0]["ToApp"].ToString(); DateToApp.Text = data.Rows[0]["DateToApp"].ToString(); Who.Text = data.Rows[0]["Who"].ToString(); Comment.Text = data.Rows[0]["Comment"].ToString(); // Text5.Text = data.Rows[0]["Prim"].ToString(); Prim.Text = data.Rows[0]["Prim"].ToString(); DateWho.Text = data.Rows[0]["DateWho"].ToString(); // clos = ((DateTime)data.Rows[0]["DateWho"]); Date.Text = DateTime.Now.ToLongDateString(); string d = string.Empty; string d2 = string.Empty, t2 = string.Empty; TimeSpan pr = DateTime.Now - ((DateTime)data.Rows[0]["DataId"]); if (!(data.Rows[0]["DateWho"] is DBNull)) { d2 = ((DateTime)data.Rows[0]["DateWho"]).Date.ToString(); t2 = ((DateTime)data.Rows[0]["DateWho"]).TimeOfDay.ToString(); pr = ((DateTime)data.Rows[0]["DateWho"]) - ((DateTime)data.Rows[0]["DataId"]); } if (!(data.Rows[0]["DateToApp"] is DBNull)) { d = ((DateTime)data.Rows[0]["DateToApp"]).ToString(); } string prostoy = ((int)pr.TotalDays).ToString() + " дн. " + pr.Hours.ToString() + " час. " + pr.Minutes.ToString() + " мин."; Timing.Text = prostoy; DocName.Text = "документ"; //Год/месяц/день /* List<string> xx = new List<string>() { "Акт неисправности", "Акт внеплановых работ", "Платежное поручение", "Счет" }; * if (!IsPostBack) * { * DocName.DataSource = xx; * DocName.DataBind(); * DocName.SelectedIndex = 0; * } */ using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); SqlCommand cmd = new SqlCommand("select count(d.Id) from Documents d" + " where d.NumEvent=@id and d.Usr=@usr", conn); cmd.Parameters.AddWithValue("id", _wz); cmd.Parameters.AddWithValue("usr", User.Identity.Name); UsrDoc.Text = cmd.ExecuteScalar().ToString(); } List <Data> datadoc = GetData(); Out.DataSource = datadoc; Out.DataBind(); List <Data> datdoc = GetData1(); Out1.DataSource = datdoc; Out1.DataBind(); // DocPodp.Visible = true; } }
DataSet MyDataSet = new DataSet(); //for pdf protected void Page_Load(object sender, EventArgs e) { _role = CheckAccount(); if (!string.IsNullOrEmpty(Request["t"])) { _type = int.Parse(Request["t"]); } if (!IsPostBack) { KOS.App_Code.ClearTemp clear = new App_Code.ClearTemp(Request); clear.DeleteOld(); if (_type == 0) { Period.Visible = true; Beg.SelectedDate = DateTime.Now.AddDays(-2); End.SelectedDate = DateTime.Now; phReport.Visible = false; } else { Period.Visible = true; phReport.Visible = true; } UpdateLabel(); List <Data> data = GetData(); Out.DataSource = data; Out.DataBind(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); SqlCommand cmd = new SqlCommand(); // адреса ОДС if (_role == "ODS") { cmd = new SqlCommand("select tt.Ttx, tt.Id from Ttx tt " + "join LiftsTtx lt on lt.TtxId=tt.Id " + "join ODSLifts o on o.LiftId=lt.LiftId " + "join Users u on u.UserId=o.UserId " + "where tt.TtxTitleId=1 and u.UserName=@userName " + "group by tt.Ttx, tt.Id", conn); if (User.Identity.Name == "Миракс_Парк") { cmd.Parameters.AddWithValue("userName", "ODS13"); } else if (User.Identity.Name == "Корона_1") { cmd.Parameters.AddWithValue("userName", "ODS14"); } else { cmd.Parameters.AddWithValue("userName", User.Identity.Name); } } // Все адреса! if (_role == "Manager" || _role == "Cadry") { cmd = new SqlCommand("select tt.Ttx, tt.Id from Ttx tt " + "where tt.TtxTitleId=1 " + "group by tt.Ttx, tt.Id", conn); } List <string> adr = new List <string>() { "все" }; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { // addresses.Add(new ListData() { Title = dr[0].ToString(), Id = Int32.Parse(dr[1].ToString()) }); adr.Add(dr[0].ToString()); } dr.Close(); if (!IsPostBack) { DropDownList3.DataSource = adr; DropDownList3.DataBind(); DropDownList3.SelectedIndex = 0; } } List <string> uslugy = new List <string>() { "все", "Эксплуатация зданий", "Эксплуатация лифтов", "Электроснабжение", "Отопление", "Водоснабжение", "Водоотведение", "Охрана", "Клининг" }; if (!IsPostBack) { DropDownList1.DataSource = uslugy; DropDownList1.DataBind(); DropDownList1.SelectedIndex = 0; // NLift.Text = "Внести!"; // Uslugy_TextChanged(this, EventArgs.Empty); } List <string> category = new List <string>() { "все", "застревание", "останов", "заявка", "плановые работы", "внеплановые ремонты" }; if (!IsPostBack) { DropDownList2.DataSource = category; } DropDownList2.DataBind(); DropDownList2.SelectedIndex = 0; } }
DataSet MyDataSet = new DataSet(); //for pdf protected void Page_Load(object sender, EventArgs e) { _role = CheckAccount(); if (!string.IsNullOrEmpty(Request["t"])) { _type = int.Parse(Request["t"]); } if (!IsPostBack) { KOS.App_Code.ClearTemp clear = new App_Code.ClearTemp(Request); clear.DeleteOld(); if (_type == 0) { Period.Visible = true; Beg.SelectedDate = DateTime.Now.AddDays(-2); End.SelectedDate = DateTime.Now; phReport.Visible = false; } else { Period.Visible = false; phReport.Visible = true; } UpdateLabel(); List <Data> data = GetData(); Out.DataSource = data; Out.DataBind(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); SqlCommand cmd; // адреса ОДС { cmd = new SqlCommand("select tt.Ttx, tt.Id from Ttx tt " + "join LiftsTtx lt on lt.TtxId=tt.Id " + "join ODSLifts o on o.LiftId=lt.LiftId " + "join Users u on u.UserId=o.UserId " + "where tt.TtxTitleId=1 and u.UserName=@userName " + "group by tt.Ttx, tt.Id", conn); if (User.Identity.Name == "Миракс_Парк") { cmd.Parameters.AddWithValue("userName", "ODS13"); } else if (User.Identity.Name == "Корона_1") { cmd.Parameters.AddWithValue("userName", "ODS14"); } } /* Все адреса! * { * cmd = new SqlCommand("select tt.Ttx, tt.Id from Ttx tt " + * "where tt.TtxTitleId=1 " + * "group by tt.Ttx, tt.Id", conn); * } */ } List <string> uslugy = new List <string>() { "Эксплуатация зданий", "Эксплуатация лифтов", "Электроснабжение", "Отопление", "Водоснабжение", "Водоотведение", "Охрана", "Клининг" }; if (!IsPostBack) { DropDownList1.DataSource = uslugy; DropDownList1.DataBind(); DropDownList1.SelectedIndex = 0; Uslugy_TextChanged(this, EventArgs.Empty); } } }
protected void DoIt_Click(object sender, EventArgs e) { FillLabels(); List <Report> r = new List <Report>(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); string s = "select l.LiftId, t.Ttx, ui.Family, ui.[IO], YEAR(z.Start) as yStart, MONTH(z.Start) as MStart, " + "DAY(z.Start) as dStart, DATEPART(hour,z.Start) as hStart, DATEPART(n,z.Start) as minStart, z.Id as zId, z.Category, " + "z.[From], z.[Status], uip.Family as pFamily, uip.[IO] as pIO, uiw.Family as wFamily, uiw.[IO] as wIO, " + "YEAR(z.Finish) as yFinish, MONTH(z.Finish) as MFinish, DAY(z.Finish) as dFinish, " + "DATEPART(hour,z.Finish) as hFinish, DATEPART(n,z.Finish) as minFinish, z.Start, z.Finish from Zayavky z " + "join Lifts l on l.LiftId=z.LiftId join LiftsTtx lt on lt.LiftId=z.LiftId " + "join Ttx t on t.Id=lt.TtxId and t.TtxTitleId=1 join UserInfo ui on ui.UserId=z.UserId " + "left join UserInfo uip on uip.UserId=z.Prinyal left join UserInfo uiw on uiw.UserId=z.Worker "; if (_role == "Worker") { s += "join WorkerLifts wl on wl.LiftId=z.LiftId join Users u on u.UserId=wl.UserId and u.UserName=@userName "; } string w = BuildWhere(); if (w == null) { // empty report комментарий Out.DataSource = r; Out.DataBind(); return; } else if (w.Length > 0) { s += w; } s += "group by l.LiftId, t.Ttx, ui.Family, ui.[IO], YEAR(z.Start), MONTH(z.Start), " + "DAY(z.Start), DATEPART(hour,z.Start), DATEPART(n,z.Start), z.Id, z.Category, " + "z.[From], z.[Status], uip.Family, uip.[IO], uiw.Family, uiw.[IO], " + "YEAR(z.Finish), MONTH(z.Finish), DAY(z.Finish), DATEPART(hour,z.Finish), DATEPART(n,z.Finish), z.Start, z.Finish"; SqlCommand cmd = new SqlCommand(s, conn); cmd.Parameters.AddWithValue("Start", CalendarStart.SelectedDate.Date); cmd.Parameters.AddWithValue("StartEnd", CalendarStartEnd.SelectedDate.Date.AddDays(1)); cmd.Parameters.AddWithValue("Finish", CalendarFinish.SelectedDate.Date); cmd.Parameters.AddWithValue("FinishEnd", CalendarFinishEnd.SelectedDate.Date.AddDays(1)); if (_role == "Worker") { cmd.Parameters.AddWithValue("userName", User.Identity.Name); } SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (_role == "ODS") //для ОДС возможность редактировать заявку { cmd.Parameters.AddWithValue("userName", User.Identity.Name); } SqlDataAdapter da1 = new SqlDataAdapter(cmd); DataTable dt1 = new DataTable(); da1.Fill(dt1); foreach (DataRow dr in dt.Rows) { string url = string.Empty; int zId = Int32.Parse(dr["zId"].ToString()); if (zId != 0) { url = "~/ZayavkaView.aspx?zId=" + zId; } string prinyal = string.Empty; if (!(dr["pFamily"] is DBNull)) { prinyal = dr["pFamily"].ToString() + " " + dr["pIO"].ToString(); } string worker = string.Empty; if (!(dr["wFamily"] is DBNull)) { worker = dr["wFamily"].ToString() + " " + dr["wIO"].ToString(); } string finish = string.Empty; string prostoy = string.Empty; if (!(dr["Finish"] is DBNull)) { finish = dr["Finish"].ToString(); prostoy = (((DateTime)dr["Finish"]) - ((DateTime)dr["Start"])).ToString(); } else { prostoy = (DateTime.Now - ((DateTime)dr["Start"])).ToString(); } r.Add(new Report() { Address = dr["Ttx"].ToString(), LiftId = dr["LiftId"].ToString(), Url = url, Category = dr["Category"].ToString(), Prinyal = prinyal, Worker = worker, From = dr["From"].ToString(), Finish = finish, Start = dr["Start"].ToString(), Prostoy = prostoy }); } Out.DataSource = r; Out.DataBind(); } Qst.Visible = false; phReport.Visible = true;// вывод отчета }
protected void DoIt_Click(object sender, EventArgs e) { FillLabels(); List <Report> r = new List <Report>(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); string s = "select rw.Id, rw.[Index], p.LiftId, p.[Date], rw.Done, ui.Family, ui.IO, t.Ttx from ReglamentWorks rw " + "join [Plan] p on p.Id=rw.PlanId join Lifts l on l.LiftId=p.LiftId " + "join UserInfo ui on ui.UserId=rw.UserId join LiftsTtx lt on lt.LiftId=p.LiftId " + "join Ttx t on t.Id=lt.TtxId and t.TtxTitleId=1 "; string w = string.Empty; w = BuildVarcharWhere(IdU, w, "l.IdU"); w = BuildVarcharWhere(IdM, w, "l.IdM"); w = BuildVarcharWhere(IdL, w, "l.IdL"); w = BuildVarcharWhere(Address, w, "t.Ttx"); w = BuildCategory(Category, w, "rw.[Index]"); w = BuildDone(Done, w, "rw.Done"); w = BuildPeriod(w, "p.[Date]"); if (w == null) { return; } s += w + " and rw.Prim is not null"; SqlCommand cmd = new SqlCommand(s, conn); cmd.Parameters.AddWithValue("Start", Start.SelectedDate); cmd.Parameters.AddWithValue("End", End.SelectedDate); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { r.Add(new Report() { LiftId = dr["LiftId"].ToString(), Address = dr["Ttx"].ToString(), Url = "~/Prim.aspx?rwId=" + dr["Id"].ToString(), Category = dr["Index"].ToString(), Done = (bool.Parse(dr["Done"].ToString()) ? "выполнено" : "висит"), Date = ((DateTime)dr["Date"]).ToShortDateString(), UserName = dr["Family"].ToString() + " " + dr["IO"].ToString() }); } dr.Close(); s = "select z.Id, z.Category, z.LiftId, z.[Date], z.Done, ui.Family, ui.IO, t.Ttx from ZPrim z " + "join Lifts l on l.LiftId=z.LiftId " + "join UserInfo ui on ui.UserId=z.WhoWrote join LiftsTtx lt on lt.LiftId=z.LiftId " + "join Ttx t on t.Id=lt.TtxId and t.TtxTitleId=1 "; w = string.Empty; w = BuildVarcharWhere(IdU, w, "l.IdU"); w = BuildVarcharWhere(IdM, w, "l.IdM"); w = BuildVarcharWhere(IdL, w, "l.IdL"); w = BuildVarcharWhere(Address, w, "t.Ttx"); w = BuildCategory(Category, w, "z.Category"); w = BuildDone(Done, w, "z.Done"); w = BuildPeriod(w, "z.[Date]"); if (w == null) { return; } s += w; cmd = new SqlCommand(s, conn); cmd.Parameters.AddWithValue("Start", Start.SelectedDate); cmd.Parameters.AddWithValue("End", End.SelectedDate); dr = cmd.ExecuteReader(); while (dr.Read()) { r.Add(new Report() { LiftId = dr["LiftId"].ToString(), Address = dr["Ttx"].ToString(), Url = "~/ZPrimEdit.aspx?Id=" + dr["Id"].ToString(), Category = dr["Category"].ToString(), Done = (bool.Parse(dr["Done"].ToString()) ? "устранено" : "не устранено"), Date = ((DateTime)dr["Date"]).ToShortDateString(), UserName = dr["Family"].ToString() + " " + dr["IO"].ToString() }); } dr.Close(); r.Sort(delegate(Report r1, Report r2) { int i = DateTime.Compare(DateTime.Parse(r1.Date), DateTime.Parse(r2.Date)); if (i != 0) { return(i); } return(string.Compare(r1.LiftId, r2.LiftId)); }); } Out.DataSource = r; Out.DataBind(); Qst.Visible = false; phReport.Visible = true; }
protected void DoIt_Click(object sender, EventArgs e) { FillLabels(); List <Report> r = new List <Report>(); DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { conn.Open(); string s = "select l.LiftId, t.Ttx, p.TpId, ui.Family, ui.IO, YEAR(rw.[Date]) as y, MONTH(rw.[Date]) as M, DAY(rw.[Date]) as d, rw.Id as rwId, p.Id as planId, p.Done from [Plan] p " + "join ReglamentWorks rw on rw.PlanId=p.Id " + "join Lifts l on l.LiftId=p.LiftId " + "join LiftsTtx lt on lt.LiftId=p.LiftId " + "join Ttx t on t.Id=lt.TtxId and t.TtxTitleId=1 " + "join UserInfo ui on ui.UserId=rw.UserId "; string w = BuildWhere(); if (w == null) { // empty report Out.DataSource = r; Out.DataBind(); return; } else if (w.Length > 0) { s += w; s += " and rw.Prim is not null"; } else { s += "where rw.Prim is not null"; } s += " group by l.LiftId, t.Ttx, p.TpId, ui.Family, ui.IO, YEAR(rw.[Date]), MONTH(rw.[Date]), DAY(rw.[Date]), rw.Id, p.Id, p.Done"; s += " union select l.LiftId, t.Ttx, p.TpId, ui.Family, ui.IO, YEAR(ISNULL(rw.[Date],p.[Date])) as y, MONTH(ISNULL(rw.[Date],p.[Date])) as M, DAY(ISNULL(rw.[Date],p.[Date])) as d, 0 as rwId, p.Id as planId, p.Done from [Plan] p " + "left join ReglamentWorks rw on rw.PlanId=p.Id " + "join Lifts l on l.LiftId=p.LiftId " + "join LiftsTtx lt on lt.LiftId=p.LiftId " + "join Ttx t on t.Id=lt.TtxId and t.TtxTitleId=1 " + "join UserInfo ui on ui.UserId=p.UserId " + w + " group by l.LiftId, t.Ttx, p.TpId, ui.Family, ui.IO, YEAR(ISNULL(rw.[Date],p.[Date])), MONTH(ISNULL(rw.[Date],p.[Date])), DAY(ISNULL(rw.[Date],p.[Date])), p.Id, p.Done"; SqlCommand cmd = new SqlCommand(s, conn); cmd.Parameters.AddWithValue("DateStart", Calendar.SelectedDate.Date); cmd.Parameters.AddWithValue("DateEnd", CalendarEnd.SelectedDate.Date.AddDays(1)); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); foreach (DataRow dr in dt.Rows) { string url = string.Empty; int rwId = Int32.Parse(dr["rwId"].ToString()); if (rwId != 0) { url = "~/Prim.aspx?rwId=" + rwId; } string name = dr["family"].ToString() + " " + dr["IO"].ToString(); string done = (bool.Parse(dr["Done"].ToString()) ? "выполнено" : "не выполнено"); r.Add(new Report() { Address = dr["Ttx"].ToString(), LiftId = dr["LiftId"].ToString(), Date = dr["y"].ToString() + "." + dr["M"].ToString() + "." + dr["d"].ToString(), Name = name, TpId = dr["TpId"].ToString(), Url = url, UrlTitle = string.IsNullOrEmpty(url) ? "" : "Замечание", PlanId = "~/Reglament.aspx?PlanId=" + Int32.Parse(dr["planId"].ToString()) + "&ret=" + HttpUtility.HtmlEncode(Request.Url), Done = done }); } // удаление работ без замечаний, дублирующих работы с замечаниями for (int i = 0; i < r.Count; i++) { if (string.IsNullOrEmpty(r[i].UrlTitle)) { Report j = r.Find(delegate(Report f) { return(f.UrlTitle == "Замечание" && f.LiftId == r[i].LiftId && f.Date == r[i].Date && f.TpId == r[i].TpId); }); if (j != null) { r.RemoveAt(i--); } } } Out.DataSource = r; Out.DataBind(); Qst.Visible = false; phReport.Visible = true; } }