using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Collections; using Infragistics.Win; using Infragistics.Win.UltraWinGrid; //using Core.Mes.ClientFrameWork; using Core.StlMes.Client.LgCommon; using CoreFS.CA06; using Core.Mes.Client.Comm.Control; namespace Core.StlMes.Client.LgIntegrationQuery { public partial class reportSlagSplashInfo : frmStyleBase { bool bFirst = false; //在页面Load事件中调用proc_Query()则true，否则false public reportSlagSplashInfo() { InitializeComponent(); } private void reportLFData_Load(object sender, EventArgs e) { //reportCommon.SetGridFontData(ref this.ultraGrid1, DefaultableBoolean.False); ultraDateTimeEditor1.DateTime = DateTime.Today; ultraDateTimeEditor2.DateTime = DateTime.Today; try { cbbPos.SelectedIndex = 0; } catch { } //reportCommon.SetUltraGridColumnsReadOnly(ref this.ultraGrid1); } private void proc_setButtonEnable(bool bEnabled) { ultraToolbarsManager1.Toolbars[0].Tools["Query"].SharedProps.Enabled = bEnabled; ultraToolbarsManager1.Toolbars[0].Tools["Export"].SharedProps.Enabled = bEnabled; } private string GeneralSqlText(string strWhere, string strWhereJob, string strWhereAdd, string strWhereCF) { string strSql = "select A.HEATNO, F.AIM_GRADE_CODE STEELNAME,A.STATIONCODE, " + "A.CaO, A.SiO2, A.Al2O3, A.TFe, A.MnO, A.S, A.MgO, A.P2O5, A.JIANDU," + "to_char(A.ASSAYDATE, 'yyyy-MM-dd HH24:mi:ss') ASSAYDATE, " + "case substr(B.SHIFTCODE,1,1) when '1' then '白' when '2' then '中' when '3' then '夜' end BC, " + "case substr(B.SHIFTCODE,2,1) when '1' then '甲' when '2' then '乙' when '3' then '丙' when '4' then '丁' end BB, " + "D.Si Si_Iron, E.C C_LD, C.BYS, C.SHJRL, C.KSJRL " + "from " + "(" + "select t.*, case when nvl(SiO2, 0) <> 0 then trim(to_char(round(CaO / (SiO2), 3), '9990.000')) else '' end JIANDU from STL_CHEMELEMENT t " + "where substr(t.checkno, length(t.checkno), 1) = 'S' " + strWhere + " " + "union " + "select t.*, case when nvl(SiO2, 0) <> 0 then trim(to_char(round(CaO / (SiO2), 3), '9990.000')) else '' end JIANDU from J#STL_CHEMELEMENT t " + "where substr(t.checkno, length(t.checkno), 1) = 'S' " + strWhere + " " + ") A, " + "(" + "select * from " + "(" + "select t.*, row_number() over (partition by t.HEATNO order by t.OPTDATE) XH " + "from " + "(" + "select * from STL_BOF_OPTINFO where 1 = 1 " + strWhereJob + " " + "union all " + "select * from J#STL_BOF_OPTINFO where 1 = 1 " + strWhereJob + " " + ") t " + ")" + "where XH = 1 " + ") B, " + "(" + "select distinct HEATNO, " + "max(case when MATERIELCODE = '10301010028' then JMWGT end) over(partition by HEATNO) BYS, " + "max(case when MATERIELCODE = '10301010008' then JMWGT end) over(partition by HEATNO) SHJRL, " + "max(case when MATERIELCODE = '10301010039' then JMWGT end) over(partition by HEATNO) KSJRL " + "from " + "(" + "select distinct HEATNO, MATERIELCODE, " + "sum(nvl(JMWGT, 0)) over (partition by HEATNO, MATERIELCODE) JMWGT " + "from STL_ADDITIVES where (MATERIELCODE = '10301010028' " + "or MATERIELCODE = '10301010008' or MATERIELCODE = '10301010039')" + " " + strWhereAdd + " " + ") " + ") C, " + "(select y.* from" + " (select x.*, row_number() over (partition by x.HEATNO order by x.ASSAYTYPECODE desc) xh from" + " (" + " select * from STL_CHEMELEMENT " + " where (upper(substr(ASSAYTYPECODE,1,2)) = 'TS' or upper(substr(STATIONCODE,1,1)) = 'A') " + strWhereCF + "" + " union " + " select * from J#STL_CHEMELEMENT " + " where (upper(substr(ASSAYTYPECODE,1,2)) = 'TS' or upper(substr(STATIONCODE,1,1)) = 'A') " + strWhereCF + "" + " ) x" + " ) y where y.xh = 1 " + ") D," + "(select y.* from" + " (select x.*, row_number() over (partition by x.HEATNO order by x.ASSAYTYPECODE) xh from" + " (" + " select * from STL_CHEMELEMENT " + " where (upper(substr(ASSAYTYPECODE,1,2)) = 'LD' or upper(substr(STATIONCODE,1,1)) = 'C') " + strWhereCF + "" + " union " + " select * from J#STL_CHEMELEMENT " + " where (upper(substr(ASSAYTYPECODE,1,2)) = 'LD' or upper(substr(STATIONCODE,1,1)) = 'C') " + strWhereCF + "" + " ) x" + " ) y where y.xh = 1 " + ") E, " + "PPC_STEEL_HEAT F " + "where A.HEATNO = B.HEATNO(+) and A.HEATNO = C.HEATNO(+) " + "and A.HEATNO = D.HEATNO(+) and A.HEATNO = E.HEATNO(+) and A.HEATNO = F.HEATNO(+) and A.STATIONCODE like '%C%'" + "order by A.ASSAYDATE desc "; return strSql; } // 样渣信息查询 /// /// 样渣信息查询 /// private void proc_Query() { if (!bFirst) { proc_setButtonEnable(false); Application.DoEvents(); } try { if (ultraDateTimeEditor1.DateTime.Date > ultraDateTimeEditor2.DateTime.Date) { MessageBox.Show("查询开始日期不能大于结束日期！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } string strFrom = ultraDateTimeEditor1.DateTime.ToString("yyyy-MM-dd"); string strTo = ultraDateTimeEditor2.DateTime.ToString("yyyy-MM-dd"); string strWhere = "and to_char(ASSAYDATE,'yyyy-MM-dd') between '" + strFrom + "' and '" + strTo + "'"; strFrom = ultraDateTimeEditor1.DateTime.AddDays(-5).ToString("yyyy-MM-dd"); strTo = ultraDateTimeEditor2.DateTime.AddDays(1).ToString("yyyy-MM-dd"); string strWhereJob = "and to_char(OPTDATE,'yyyy-MM-dd') between '" + strFrom + "' and '" + strTo + "'"; string strWhereAdd = "and to_char(JMDATE,'yyyy-MM-dd') between '" + strFrom + "' and '" + strTo + "'"; string strWhereCF = "and to_char(ASSAYDATE,'yyyy-MM-dd') between '" + strFrom + "' and '" + strTo + "'"; strWhereAdd += " and substr(STATIONCODE,1,1) = 'C'"; string str = ""; str = cbbNum.Value.ToString(); if (str!="C00") { strWhere += " and STATIONCODE = '" + str + "'"; } // strWhere += " and substr(STATIONCODE,1,1) = '" + str + "'"; if (cbBC.Checked && cbbBC.SelectedIndex > -1) { str = Convert.ToString(cbbBC.SelectedIndex + 1); strWhere += " and substr(SHIFTCODE,1,1) = '" + str + "'"; } if (cbBB.Checked && cbbBB.SelectedIndex > -1) { str = Convert.ToString(cbbBB.SelectedIndex + 1); strWhere += " and substr(SHIFTCODE,2,1) = '" + str + "'"; } string strSql = GeneralSqlText(strWhere, strWhereJob, strWhereAdd, strWhereCF); Hashtable vale_list = GetValuelistStation(); string strOut = ""; CoreClientParam ccp = new CoreClientParam(); ccp.ServerName = "Core.LgMes.Server.DEV.Methods.DEVPublicMethods"; ccp.MethodName = "QueryMethods"; ccp.ServerParams = new Object[] { strSql }; DataTable dt = new DataTable(); ccp.SourceDataTable = dt; ob.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal); DataSet ds = new DataSet(); ds.Tables.Add(dt); strOut = ccp.ReturnInfo; //DataSet ds = ClientCommon._RemotingHelp.ExecuteMethod("Report", "Core.LgMes.Server.Report.reportMethod", "ExecuteCommand_Return", new object[] { strSql }, out strOut) as DataSet; if (strOut != "") { MessageBox.Show(strOut, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (ds != null && ds.Tables.Count > 0) { this.dataTable1.Rows.Clear(); if (ds.Tables[0].Rows.Count == 0) return; DataRow dr; for (int iRow = 0; iRow < ds.Tables[0].Rows.Count; iRow++) { dr = this.dataTable1.NewRow(); try { for (int jCol = 0; jCol < ds.Tables[0].Columns.Count; jCol++) { try { if (this.dataTable1.Columns.Contains(ds.Tables[0].Columns[jCol].ColumnName)) if (ds.Tables[0].Columns[jCol].ColumnName == "STATIONCODE") { dr[ds.Tables[0].Columns[jCol].ColumnName] = vale_list[Convert.ToString(ds.Tables[0].Rows[iRow][jCol])]; } else { dr[ds.Tables[0].Columns[jCol].ColumnName] = Convert.ToString(ds.Tables[0].Rows[iRow][jCol]); } } catch { } } this.dataTable1.Rows.Add(dr); } catch { } } } PublicMethod.RefreshAndAutoSize(ultraGrid1); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (!bFirst) proc_setButtonEnable(true); else bFirst = false; } } public override void ToolBar_Click(object sender, string ToolbarKey) { switch (ToolbarKey) { case "Query": proc_Query(); break; case "Export": try { //Core.Mes.Client.Common.Globals.ulGridToExcel(ultraGrid1, Text); GridHelper.ulGridToExcel(ultraGrid1, Text); } catch (Exception ex) { MessageBox.Show(ex.Message); } break; case "Close": this.Close(); break; default: break; } } private object[] proc_GetPosition(string value) { switch (value) { case "C": return new object[] { "所有", "1#转炉", "2#转炉", "3#转炉", "4#转炉", "5#转炉" }; default: return new object[] { "所有" }; } } private void cbbPos_SelectedIndexChanged(object sender, EventArgs e) { try { try { cbbNum.Items.Clear(); string value = cbbPos.Value.ToString(); switch (value) { case "C": cbbNum.Items.Add("C00", "所有"); cbbNum.Items.Add("C01", "1#转炉"); cbbNum.Items.Add("C02", "2#转炉"); cbbNum.Items.Add("C03", "3#转炉"); cbbNum.Items.Add("C04", "4#转炉"); cbbNum.Items.Add("C05", "5#转炉"); break; default: break; } cbbNum.SelectedIndex = 0; } catch { } } catch { } } private void cbBC_CheckedChanged(object sender, EventArgs e) { cbbBC.Enabled = cbBC.Checked; } private void cbBB_CheckedChanged(object sender, EventArgs e) { cbbBB.Enabled = cbBB.Checked; } private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e) { switch (e.Tool.Key) { case "Query": { proc_Query(); break; } case "Export": { try { //Core.Mes.Client.Common.Globals.ulGridToExcel(ultraGrid1, Text); GridHelper.ulGridToExcel(ultraGrid1, Text); } catch (Exception ex) { MessageBox.Show(ex.Message); } break; } case "Close": { this.Close(); break; } } } private void uCkEditorForFilter_CheckedChanged(object sender, EventArgs e) { try { if (uCkEditorForFilter.Checked) { this.ultraGrid1.DisplayLayout.Override.AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True; } else { this.ultraGrid1.DisplayLayout.Bands[0].ColumnFilters.ClearAllFilters(); this.ultraGrid1.DisplayLayout.Override.AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False; } } catch { } } public Hashtable GetValuelistStation() { Hashtable vlist = new Hashtable(); try { vlist.Add("B01", "1#预处理"); vlist.Add("B02", "2#预处理"); vlist.Add("C01", "1#转炉"); vlist.Add("C02", "2#转炉"); vlist.Add("C03", "3#转炉"); vlist.Add("C04", "4#转炉"); vlist.Add("C05", "5#转炉"); vlist.Add("D01", "1#吹氩站"); vlist.Add("D02", "2#吹氩站"); vlist.Add("D03", "3#吹氩站"); vlist.Add("D04", "4#吹氩站"); vlist.Add("D05", "5#吹氩站"); vlist.Add("E01", "1#LF炉"); vlist.Add("E02", "2#LF炉"); vlist.Add("E03", "3#LF炉"); vlist.Add("G01", "1#铸机"); vlist.Add("G02", "2#铸机"); vlist.Add("G03", "3#铸机"); vlist.Add("G04", "4#铸机"); vlist.Add("G05", "5#铸机"); } catch { } return vlist; } } }