XinYu_LG2MES/ClientSolution/Core.LgMes.Client.lgIntegrationQuery/reportSlagSplashInfo.cs

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.Mes.Client.Common;

namespace Core.LgMes.Client.LgIntegrationQuery
{
    public partial class reportSlagSplashInfo : Core.Mes.Client.Common.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
            {
                cbPos.Checked = true;
                cbbPos.SelectedIndex = 0;
                cbPos.Enabled = cbbPos.Enabled = false;
            }
            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.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 JOB_CHEMELEMENT t "
            + "where substr(t.checkno, length(t.checkno) - 1, 2) = '-4' " + 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#JOB_CHEMELEMENT t "
            + "where substr(t.checkno, length(t.checkno) - 1, 2) = '-4' " + strWhere + " "
            + ") A, "

            + "("
            + "select * from "
            + "("
            + "select t.*, row_number() over (partition by t.HEATNO order by t.OPTDATE) XH "
            + "from "
            + "("
            + "select * from JOB_BOF_OPTINFO where 1 = 1 " + strWhereJob + " "
            + "union all "
            + "select * from J#JOB_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 RES_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 JOB_CHEMELEMENT "
            + "   where (upper(substr(ASSAYTYPECODE,1,2)) = 'TS' or upper(substr(STATIONCODE,1,1)) = 'A') " + strWhereCF + ""
            + "   union "
            + "   select * from J#JOB_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 JOB_CHEMELEMENT "
            + "   where (upper(substr(ASSAYTYPECODE,1,2)) = 'LD' or upper(substr(STATIONCODE,1,1)) = 'C') " + strWhereCF + ""
            + "   union "
            + "   select * from J#JOB_CHEMELEMENT "
            + "   where (upper(substr(ASSAYTYPECODE,1,2)) = 'LD' or upper(substr(STATIONCODE,1,1)) = 'C') " + strWhereCF + ""
            + "  ) x"
            + " ) y where y.xh = 1 "
            + ") E, "

            + "JH2_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(+) "

            + "order by A.ASSAYDATE ";

            return strSql;
        }

        private void proc_Query()
        {
            if (!bFirst)
            {
                proc_setButtonEnable(false);
                Application.DoEvents();
            }
            try
            {
                if (ultraDateTimeEditor2.DateTime.Date.Subtract(ultraDateTimeEditor1.DateTime.Date).Days > 180)
                {
                    MessageBox.Show("查询时间不可超过6个月。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                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 = "";
                if (cbPos.Checked && cbbPos.SelectedIndex > -1)
                {
                    str = "C";
                    if (cbbNum.SelectedIndex == 0)
                    {
                        strWhere += " and substr(STATIONCODE,1,1) = '" + str + "'";
                    }
                    else if (cbbNum.SelectedIndex > 0)
                    {
                        strWhere += " and substr(STATIONCODE,1,2) = '" + str + Convert.ToString(cbbNum.SelectedIndex) + "'";
                    }
                }
                else
                {
                    strWhere += " and substr(STATIONCODE,1,1) = 'C'";
                }

                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);

                string strOut = "";
                //DataSet ds = ClientCommon._RemotingHelp.ExecuteMethod("Report", "Core.LgMes.Server.Report.reportMethod", "ExecuteCommand_Return", new object[] { strSql }, out strOut) as DataSet;
                DataSet ds = null;
                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))
                                        dr[ds.Tables[0].Columns[jCol].ColumnName] = Convert.ToString(ds.Tables[0].Rows[iRow][jCol]);
                                }
                                catch { }
                            }
                            this.dataTable1.Rows.Add(dr);
                        }
                        catch { }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (!bFirst)
                    proc_setButtonEnable(true);
                else
                    bFirst = false;
            }
        }

        private void cbPos_CheckedChanged(object sender, EventArgs e)
        {
            cbbPos.Enabled = cbbNum.Enabled = cbPos.Checked;
        }

        private object[] proc_GetPosition(int index)
        {
            switch (index)
            {
                case 0:
                    return new object[] { "所有", "1#转炉", "2#转炉", "3#转炉" };
                default:
                    return new object[] { "所有" };
            }
        }

        private void cbbPos_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                try
                {
                    cbbNum.Items.Clear();
                    cbbNum.Items.AddRange(proc_GetPosition(cbbPos.SelectedIndex));
                    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":
                    {
                        //if (!reportCommon.ExcelInstalled()) return;
                        //proc_setButtonEnable(false);
                        //reportCommon.ExportDataWithSaveDialog(ref this.ultraGrid1, this.Text);
                        //proc_setButtonEnable(true);
                        //break;
                        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 { }
        }
    }
}