protected void bindStatistic() { MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator(); string query; query = "select r.产品, t.output as 产量,t.output/s.output as 产量占比,r.* from hv_qlt_month_prod_report r left join (select t.PROD_CODE,substr(t.STARTTIME,1,7) as month, sum(decode(t.para_code,'7030500029',t.SEG_VALUE)) * sum(decode(t.para_code,'7030500030',t.SECTION_CODE)) as output from hv_prod_report t where t.SECTION_CODE = '70305' and t.starttime like '" + txtStartTime.Text + "%' group by substr(t.starttime,1,7),t.prod_code) t on t.PROD_CODE = r.prod_code and t.month = r.month left join (select substr(t.STARTTIME,1,7) as month, sum(decode(t.para_code,'7030500029',t.SEG_VALUE)) * sum(decode(t.para_code,'7030500030',t.SEG_VALUE)) as output from hv_prod_report t where t.SECTION_CODE = '70305' and t.starttime like '" + txtStartTime.Text + "%' group by substr(t.starttime,1,7)) s on s.month = r.month where r.month = '" + txtStartTime.Text + "'"; DataSet data = opt.CreateDataSetOra(query); GridAll.DataSource = data; GridAll.DataBind(); if (data != null && data.Tables[0].Rows.Count > 0) { labout.Text = opt.GetSegValue("select substr(t.STARTTIME,1,7) as month, sum(decode(t.para_code,'7030500029',t.SEG_VALUE)) * sum(decode(t.para_code,'7030500030',t.SEG_VALUE)) as output from hv_prod_report t where t.SECTION_CODE = '70305' and t.starttime like '" + txtStartTime.Text + "%' group by substr(t.starttime,1,7)", "output"); labout.Text = (labout.Text == "NoRecord" || labout.Text == "") ? "0" : labout.Text; if (labout.Text != "0") { double total = Convert.ToDouble(labout.Text); double score = 0; foreach (DataRow row in data.Tables[0].Rows) { score += Convert.ToDouble(row["产量"].ToString()) / total * Convert.ToDouble(row["产品得分"].ToString()); } labScore.Text = score.ToString(); } } }
protected void bindGrid() { MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator(); string query = "select g4.team_name as 班组,g4.team_code,g1.班组得分,g2.产品,g3.output as 产量,g3.rate as 产量占比,g2.在线考核分,g2.理化检测得分,g2.感观评测得分,g2.过程检测得分,g2.产品得分 from (select r.team,sum(t.rate *r.产品得分) as 班组得分 from hv_qlt_month_prod_Trep r left join hv_prod_month_output t on t.PROD_CODE = r.prod_code and t.month = '" + txtStartTime.Text + "' where r.month = '" + txtStartTime.Text + "' group by r.team) g1 left join hv_qlt_month_prod_Trep g2 on g2.team = g1.team and g2.month = '" + txtStartTime.Text + "' left join hv_prod_month_output g3 on g3.PROD_CODE = g2.prod_code and g3.month = '" + txtStartTime.Text + "' left join ht_sys_team g4 on g4.team_code = g1.team order by g4.team_code,g2.产品"; DataSet data = opt.CreateDataSetOra(query); GridAll.DataSource = data; GridAll.DataBind(); for (int i = 0; i < 2; i++) { TableCell oldtc = GridAll.Rows[0].Cells[i]; oldtc.RowSpan = 1; for (int j = 1; j < GridAll.Rows.Count; j++) { TableCell newtc = GridAll.Rows[j].Cells[i]; if (newtc.Text == oldtc.Text) { newtc.Visible = false; oldtc.RowSpan = oldtc.RowSpan + 1; oldtc.VerticalAlign = VerticalAlign.Middle; } else { oldtc = newtc; oldtc.RowSpan = 1; } } } }
/// <summary> /// LINQ to show all cars in the data grid /// <param name="sender"></param> /// <param name="e"></param> /// </summary> protected void Page_Load(object sender, EventArgs e) { var db = new CarsDBEntities(); var query = (from car in db.CarsTables select car).ToList(); GridAll.DataSource = query; GridAll.DataBind(); }
protected void bindStatistic() { MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator(); string query; if (listTeam.SelectedValue == "") { query = "select a5.prod_name as 产品, a1.score as 在线考核分,a2.score as 理化检测得分,a3.score as 感观评测得分,a4.score as 过程检测得分, (select weight from ht_qlt_weight where id='1')*nvl(a1.score,100) + (select weight from ht_qlt_weight where id='2')*nvl(a2.score,100)+ (select weight from ht_qlt_weight where id='3')*nvl(a3.score,100) + (select weight from ht_qlt_weight where id='4')*nvl(a4.score,100) as 总得分 from (select distinct prod_code from ht_qlt_inspect_record r where substr(r.record_time,1,10) = '" + txtStartTime.Text + "' union select distinct prod_code from ht_qlt_data_record where substr(b_time,1,10) = '" + txtStartTime.Text + "') a left join ht_pub_prod_design a5 on a5.prod_code = a.prod_code left join (select sum(r.score * s.weight) as score,r.prod_code from (select a.section,sum(a.quarate * c.weight*100) as score,a.prod_code from (select r.prod_code, substr(r.para_code,1,5) as section,r.para_code,sum(r.quarate*r.count)/sum(r.count) as quarate from ht_qlt_data_record r where substr(r.b_time,1,10) = '" + txtStartTime.Text + "' group by r.para_code,r.prod_code) a left join ht_pub_tech_para b on b.para_code = a.para_code and b.para_type like '______1%' left join ht_qlt_weight c on c.id = a.para_code group by a.section,a.prod_code )r left join ht_pub_tech_section s on s.section_code = r.section group by r.prod_code )a1 on a5.prod_name = a.prod_code left join (select round(avg(得分),2) as score,产品 from hv_phychem_daily_report r left join ht_pub_prod_design t on r.产品 = t.prod_name where substr(r.检测时间,1,10) = '" + txtStartTime.Text + "' group by 产品)a2 on a5.prod_name = a2.产品 left join (select round(avg(得分),2) as score,产品 from hv_sensor_daily_report r left join ht_pub_prod_design t on r.产品 = t.prod_name where substr(r.检测时间,1,10) = '" + txtStartTime.Text + "' group by 产品)a3 on a5.prod_name = a3.产品 left join (select round(avg(得分),2) as score,产品 from hv_process_daily_report r left join ht_pub_prod_design t on r.产品 = t.prod_name where substr(r.检测时间,1,10) = '" + txtStartTime.Text + "' group by 产品)a4 on a5.prod_name = a4.产品 order by a5.prod_name"; } else { query = " select a5.prod_name as 产品,a6.team_name as 班组, a1.score as 在线考核分,a2.score as 理化检测得分,a3.score as 感观评测得分,a4.score as 过程检测得分, (select weight from ht_qlt_weight where id='1')*nvl(a1.score,100) + (select weight from ht_qlt_weight where id='2')*nvl(a2.score,100)+ (select weight from ht_qlt_weight where id='3')*nvl(a3.score,100) + (select weight from ht_qlt_weight where id='4')*nvl(a4.score,100) as 总得分 from (select distinct team_id as team, prod_code from ht_qlt_inspect_record where substr(record_time,1,10) = '" + txtStartTime.Text + "' union select distinct team, prod_code from ht_qlt_data_record where substr(b_time,1,10) = '" + txtStartTime.Text + "') a left join ht_pub_prod_design a5 on a5.prod_code = a.prod_code left join ht_sys_team a6 on a6.team_code = a.team left join (select sum(r.score * s.weight) as score,r.prod_code,r.team from (select a.section,sum(a.quarate * c.weight*100) as score,a.prod_code,a.team from (select r.prod_code,r.team, substr(r.para_code,1,5) as section,r.para_code,sum(r.quarate*r.count)/sum(r.count) as quarate from ht_qlt_data_record r where substr(r.b_time,1,10) = '" + txtStartTime.Text + "' group by r.para_code,r.prod_code,r.team) a left join ht_pub_tech_para b on b.para_code = a.para_code and b.para_type like '______1%' left join ht_qlt_weight c on c.id = a.para_code group by a.section,a.prod_code ,a.team)r left join ht_pub_tech_section s on s.section_code = r.section group by r.prod_code,r.team )a1 on a.prod_code = a1.prod_code and a6.team_code = a1.team left join (select round(avg(得分),2) as score,产品,班组 from hv_phychem_daily_report r left join ht_pub_prod_design t on r.产品 = t.prod_name where substr(r.检测时间,1,10) = '" + txtStartTime.Text + "' group by 产品,班组)a2 on a5.prod_name = a2.产品 and a6.team_name = a2.班组 left join (select round(avg(得分),2) as score,产品,班组 from hv_sensor_daily_report r left join ht_pub_prod_design t on r.产品 = t.prod_name where substr(r.检测时间,1,10) = '" + txtStartTime.Text + "' group by 产品,班组)a3 on a5.prod_name = a3.产品 and a6.team_name = a3.班组 left join (select round(avg(得分),2) as score,产品,班组 from hv_process_daily_report r left join ht_pub_prod_design t on r.产品 = t.prod_name where substr(r.检测时间,1,10) = '" + txtStartTime.Text + "' group by 产品,班组)a4 on a5.prod_name = a4.产品 and a6.team_name = a4.班组 where a6.team_code = '" + listTeam.SelectedValue + "'"; } GridAll.DataSource = opt.CreateDataSetOra(query); GridAll.DataBind(); query = "select * from hv_online_daily_report t where substr(时间,1,10) = '" + txtStartTime.Text + "'"; if (listTeam.SelectedValue != "") { query += " and t.班组 = '" + listTeam.SelectedItem.Text + "'"; } query += " order by t.产品,t.班组"; GridView1.DataSource = opt.CreateDataSetOra(query); GridView1.DataBind(); query = "select * from hv_process_daily_report t where substr(检测时间,1,10) = '" + txtStartTime.Text + "'"; if (listTeam.SelectedValue != "") { query += " and t.班组 = '" + listTeam.SelectedItem.Text + "'"; } query += " order by t.产品,t.班组"; GridView2.DataSource = opt.CreateDataSetOra(query); GridView2.DataBind(); query = "select * from hv_phychem_daily_report t where substr(检测时间,1,10) = '" + txtStartTime.Text + "'"; if (listTeam.SelectedValue != "") { query += " and t.班组 = '" + listTeam.SelectedItem.Text + "'"; } query += " order by t.产品,t.班组"; GridView3.DataSource = opt.CreateDataSetOra(query); GridView3.DataBind(); query = "select * from hv_sensor_daily_report t where substr(检测时间,1,10) = '" + txtStartTime.Text + "'"; if (listTeam.SelectedValue != "") { query += " and t.班组 = '" + listTeam.SelectedItem.Text + "'"; } query += " order by t.产品,t.班组"; GridView4.DataSource = opt.CreateDataSetOra(query); GridView4.DataBind(); }
public void BindGridAll() { try { int retrow = 0; DataTable dt = new DataTable(); string Dex2 = ""; if (CHK_SKIP_BRN.Checked == true) { Dex2 = "Y"; } else if (CHK_SKIP_BRN.Checked == false) { Dex2 = "N"; } dt = BS.PreBalance(Dex2, Session["BRCD"].ToString(), txtFromDate.Text, txtToDate.Text, Session["UserName"].ToString());//Amruta 20170427 if (dt != null) { if (dt.Rows.Count != 0) { GridAll.DataSource = dt; GridAll.DataBind(); retrow = dt.Rows.Count; } else { dt.Rows.Add(dt.NewRow()); GridAll.DataSource = dt; GridAll.DataBind(); int TotalColumns = GridAll.Rows[0].Cells.Count; GridAll.Rows[0].Cells.Clear(); GridAll.Rows[0].Cells.Add(new TableCell()); GridAll.Rows[0].Cells[0].ColumnSpan = TotalColumns; GridAll.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center; GridAll.Rows[0].Cells[0].Text = "No Record Found"; } } } catch (Exception Ex) { ExceptionLogging.SendErrorToText(Ex); //Response.Redirect("FrmLogin.aspx", true); } }
protected void bindStatistic() { MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator(); string query; if (listProd.SelectedValue == "") { query = "select * from hv_qlt_month_prod_report t where t.month = '" + txtStartTime.Text + "' order by prod_code"; GridAll.DataSource = opt.CreateDataSetOra(query); GridAll.DataBind(); GridView1.DataSource = null; GridView1.DataBind(); GridView2.DataSource = null; GridView2.DataBind(); GridView3.DataSource = null; GridView3.DataBind(); GridView4.DataSource = null; GridView4.DataBind(); } else { query = " select * from hv_qlt_month_prod_report t where t.month = '" + txtStartTime.Text + "' and t.prod_code = '" + listProd.SelectedValue + "'"; GridAll.DataSource = opt.CreateDataSetOra(query); GridAll.DataBind(); query = "select t.section_name as 工艺段,t.para_name as 工艺点,t.quarate as 合格率,t.stddev as 标准差,t.absdev as 绝对差,t.avg as 均值,t.cpk from hv_qlt_online_month_report t where t.month = '" + txtStartTime.Text + "' and t.prod_code = '" + listProd.SelectedValue + "'"; GridView1.DataSource = opt.CreateDataSetOra(query); GridView1.DataBind(); query = "select r.team_name as 班组,t.Record_time as 检测时间, t.* from hv_qlt_phychem_daily_report t left join ht_sys_team r on r.team_code = t.team_id where t.Record_time like '" + txtStartTime.Text + "%' and t.prod_code = '" + listProd.SelectedValue + "'"; GridView2.DataSource = opt.CreateDataSetOra(query); GridView2.DataBind(); query = "select t.record_time as 检测时间,t.* from hv_qlt_sensor_realrec t where t.产品 = '" + listProd.SelectedValue + "' and t.sensor_month = '" + txtStartTime.Text + "'"; //query = "select t.record_time as 评测时间, t.产品名称, t.* from hv_qlt_sensor_realrec2 t where t.产品 = '" + listProd.SelectedValue + "' and t.sensor_month = '" + txtStartTime.Text + "'"; GridView3.DataSource = opt.CreateDataSetOra(query); GridView3.DataBind(); query = "select r.team_name as 班组,t.Record_time as 检测时间, t.* from hv_qlt_process_daily_report t left join ht_sys_team r on r.team_code = t.team_id where t.Record_time like '" + txtStartTime.Text + "%' and t.prod_code = '" + listProd.SelectedValue + "'"; GridView4.DataSource = opt.CreateDataSetOra(query); GridView4.DataBind(); } }
protected void Page_Load(object sender, EventArgs e) { try { string connString = ConfigurationManager.ConnectionStrings["CarModel"].ConnectionString; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand("SELECT * FROM CarsTable", conn); cmd.CommandType = System.Data.CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, "id"); GridAll.DataSource = ds.Tables["id"]; GridAll.DataBind(); RecordLabel.Text = "Records are found"; } catch { RecordLabel.Text = "Records not found"; } }