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";
}
}
