XinYu_LG2MES/ClientSolution/新建文件夹/20160321/reportHMPData.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.UltraWinGrid;
using CoreFS.CA06;
using Core.Mes.Client.Common;

namespace Core.LgMes.Client.Report
{
    public partial class reportHMPData : frmStyleBase
    {
        bool bFirst = false;             //在页面Load事件中调用proc_Query()则true，否则false
        public reportHMPData()
        {
            InitializeComponent();
        }

        private void reportHMPData_Load(object sender, EventArgs e)
        {
            ultraDateTimeEditor1.DateTime = DateTime.Today;
            ultraDateTimeEditor2.DateTime = DateTime.Today;

            reportCommon.SetUltraGridColumnsReadOnly(ref this.ultraGrid1);         
        }

        private void proc_setButtonEnable(bool bEnabled)
        {

        }

        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 strEquipmentNo = "";
                string strMolirnSource = "";
                string strShiftCode = "";
                string strShiftCBB = "";

                if (cbPos.Checked && cbbPos.SelectedIndex > -1)
                {
                    //strEquipmentNo = Convert.ToString(cbbPos.SelectedIndex + 1);
                    switch (cbbPos.Text.Trim())
                    {
                        case"1#脱硫":
                            strEquipmentNo = "1";
                            strMolirnSource = "K";
                            break;
                        case "2#脱硫":
                            strEquipmentNo = "2";
                            strMolirnSource = "K";
                            break;
                        case "1#转炉":
                            strEquipmentNo = "1";
                            strMolirnSource = "J";
                            break;
                        case "2#转炉":
                            strEquipmentNo = "2";
                            strMolirnSource = "J";
                            break;
                    }
                }
                if (chkMolirnSource.Checked && cboMolirnSource.SelectedIndex > -1)
                {
                    //strMolirnSource = Convert.ToString(chkMolirnSource.SelectedIndex + 1);
                    switch (cboMolirnSource.Text.Trim())
                    {
                        case "脱硫":
                            strMolirnSource = "K";
                            break;                        
                        case "转炉":
                            strMolirnSource = "J";
                            break;                        
                    }
                }

                if (cbBC.Checked && cbbBC.SelectedIndex > -1)
                {
                    strShiftCode = Convert.ToString(cbbBC.SelectedIndex + 1);
                }

                if (cbBB.Checked && cbbBB.SelectedIndex > -1)
                {
                    strShiftCBB = Convert.ToString(cbbBB.SelectedIndex + 1);
                }

                string sqlstr = this.appendAllSql(ultraDateTimeEditor1.DateTime, ultraDateTimeEditor2.DateTime, strEquipmentNo, strMolirnSource, strShiftCode, strShiftCBB);
                DataSet ds = this.returnNewDataSet(Core.Mes.Client.Common.DbCommon.returnDataSet(sqlstr,this.ob));              
                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();
                        for (int jCol = 0; jCol < ds.Tables[0].Columns.Count; jCol++)
                        {
                            dr[ds.Tables[0].Columns[jCol].ColumnName] = ds.Tables[0].Rows[iRow][jCol];

                        }
                        this.dataTable1.Rows.Add(dr);
                    }
                }

                try
                {
                    ArrayList alistColumns = new ArrayList();
                    ArrayList alistFormat = new ArrayList();
                  

                    alistColumns.Add("ARRIVEWGT");
                    alistColumns.Add("LEAVEWGT");
                    alistColumns.Add("IRONPOTBOFWGT");
                    alistColumns.Add("DREGSSOLVENT");
                    alistColumns.Add("DFRWGT");
                    alistColumns.Add("LIMESTONE");

                    alistFormat.Add(" {0:############0.0}");
                    alistFormat.Add(" {0:############0.0}");
                    alistFormat.Add(" {0:############0.0}");
                    alistFormat.Add(" {0:############0.0}");
                    alistFormat.Add(" {0:############0.0}");
                    alistFormat.Add(" {0:############0.0}");


                    reportCommon.proc_Statics(ref ultraGrid1, alistColumns, true, " {0:############0.0}");
                    

                    reportCommon.proc_Statics_Avg2(ref ultraGrid1, alistColumns, false, alistFormat);
                    

                }
                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 = cbPos.Checked;
        }

        private void cbBC_CheckedChanged(object sender, EventArgs e)
        {
            cbbBC.Enabled = cbBC.Checked;
        }

        private void cbBB_CheckedChanged(object sender, EventArgs e)
        {
            cbbBB.Enabled = cbBB.Checked;
        }

        public override void ToolBar_Click(object sender, string ToolbarKey)
        {
            switch (ToolbarKey)
            {
                case "Query":
                    {
                        proc_Query();
                        break;
                    }

                case "Export":
                    {
                        if (!reportCommon.ExcelInstalled()) return;
                        proc_setButtonEnable(false);
                        reportCommon.ExportDataWithSaveDialog(ref ultraGrid1, this.Text);
                        proc_setButtonEnable(true);
                        break;
                    }

                case "Close":
                    {
                        this.Close();
                        break;
                    }
            }
        }

        private void uCkEditorForFilter_CheckedChanged(object sender, EventArgs e)
        {
            reportCommon.SetGridRowFilter(ref this.ultraGrid1, uCkEditorForFilter.Checked);
        }

        private void uCkEditorStatistics_CheckedChanged(object sender, EventArgs e)
        {
            reportCommon.SetStatisticsMod(ref this.ultraGrid1, uCkEditorStatistics.Checked, ref uCkEditorStatistics);
        }

        private void chkMolirnSource_CheckedChanged(object sender, EventArgs e)
        {
            cboMolirnSource.Enabled = chkMolirnSource.Checked;
        }

        private string returnSqlstr()
        {
            return @"select distinct t.misid,
                t.stoveid,
                t.stovetime,
                t.tapholeno,
                to_char(b.changestarttime, 'HH24:mi') changestarttime,
                 round(changestarttime -leavetime ) WaitingTime,
                to_char(t.hstapholetime, 'HH24:mi') hstapholetime,
                to_char(a.dr_bef_endtime, 'HH24:mi') dr_bef_endtime,
                to_char(a.dr_aft_endtime, 'HH24:mi') dr_aft_endtime,
               t.BFIRONTEMTURE,
               t.IRONTEMPERATURE,
                case substr(t.shiftcode, 1, 1)
                  when '1' then
                   '白'
                  when '2' then
                   '中'
                  when '3' then
                   '晚'
                end bc,
                case substr(t.shiftcode, 2, 1)
                  when '1' then
                   '甲'
                  when '2' then
                   '乙'
                  when '3' then
                   '丙'
                  when '4' then
                   '丁'
                end bb,
                decode(substr(t.molirnsource, 1, 1), 'J', '转炉', '脱硫') molirnsource,
                a.hudwid,
                round(t.tfpotwgt, 2) arrivewgt,
                case
                  when nvl(a.leavewgt, 0) > 0
                       and nvl(a.arrivewgt, 0) > 0
                       and nvl(a.leavewgt, 0) < nvl(a.arrivewgt, 0) then
                   round(t.tfpotwgt -
                         (nvl(a.arrivewgt, 0) - nvl(a.leavewgt, 0)), 2)
                  else
                   t.tfpotwgt
                end leavewgt,
                round(t.tfpotwgt - (t.tfpotwgt - (nvl(a.arrivewgt, 0) -
                      nvl(a.leavewgt, 0))), 2) drquantity,
                case
                  when nvl(a.leavewgt, 0) > 0 then
                   (nvl(a.leavewgt, 0) - nvl(t4.tareweight, 0))
                  else
                   0
                end leavewgt_out,
                case
                  when nvl(a.arrivewgt, 0) > 0 then
                   (nvl(a.arrivewgt, 0) - nvl(t4.tareweight, 0))
                  else
                   0
                end arrivewgt_out,
                
                t4.tareweight ironpotbofwgt,
                t.tfpotid || '-' || t4.age ironpwrapno,
                case
                  when b.plansteel is not null then
                   b.plansteel
                  else
                   a.plansteel
                end plansteel,
                 decode(substr(t.molirnsource, 1, 1), 'J', to_char(t.OPERATIONDATE, 'HH24:mi'),  to_char(a.arrivetime, 'HH24:mi'))  arrivetime,
                 decode(substr(t.molirnsource, 1, 1), 'J', to_char(t.OPERATIONDATE, 'yyyy-MM-dd HH24:mi'),  to_char(a.leavetime, 'yyyy-MM-dd HH24:mi')) leavetime,
              
                a.dr_bef_time,
                a.dr_aft_time,
                a.swbtemperature,
                a.swftemperature,
                a.dregssolvent,
                a.churnupdate,
                a.mostrotatespeed,
                a.sggage,
                a.dfrwgt,
                case
                  when a.heatno is not null then
                   a.heatno
                  when substr(t.molirnsource, 1, 1) = 'J' then
                   t.molirnsource
                end heatno,
                t.si si_bf,
                t.mn mn_bf,
                t.p p_bf,
                t.s s_bf,
                t2.si,
                t2.mn,
                t2.p,
                t2.s,
                t3.si si2,
                t3.mn mn2,
                t3.p p2,
                t3.s s2,
                abs(t.Mn - t2.Mn) Mn差,
                abs(t.si - t2.si) si差,
                abs(t.s - t2.s) S差,
                abs(t.p - t2.p) P差,
                 to_char(t2.assaydate, 'yyyy-MM-dd HH24:mi')assaydate,
               '' LIMESTONE,
                a.memo";
              //to_char(a.arrivetime, 'HH24:mi') arrivetime,
              //  to_char(a.leavetime, 'HH24:mi') leavetime,
        }


        private string returnSqlOfStl_mis_importiron(string sqlCondition)
        {
            string sqlStr = "";
            sqlStr = @" from (select *  from stl_mis_importiron   where  1 = 1 {0}) t,";

            return string.Format(sqlStr,sqlCondition);
        }

        private string returnSqlOfStl_hmp_optinfo(string sqlCondition)
        {
            string sqlStr = "";
            sqlStr = @"  (select *    from   stl_hmp_optinfo  where   1 = 1 {0}
                    union   select *   from   j#stl_hmp_optinfo   where  1 = 1 {0}) a,";

            return string.Format(sqlStr, sqlCondition);
        }


        private string returnSqlOfStl_bof_optinfo(string sqlCondition)
        {
            string sqlStr = "";
            sqlStr = @"   (select *     from   stl_bof_optinfo     where  1 = 1 {0}      union    select *   from   j#stl_bof_optinfo   where  1 = 1 {0}) b,";

            return string.Format(sqlStr, sqlCondition);
        }

        private string returnSqlOfStl_chemelement(string sqlCondition)
        {
            string sqlStr = "";
            sqlStr = @"   
              (select heatno,  si,
               mn,
               p,
               s,
               assaytypecode
               from   stl_chemelement
              where  upper(substr(assaytypecode, 1, 1)) = 'B'   {0}
              union
              select heatno,   si,
               mn,
               p,
               s,
               assaytypecode
               from   j#stl_chemelement
               where  upper(substr(assaytypecode, 1, 1)) = 'B'  {0}) t2,";

            return string.Format(sqlStr, sqlCondition);
        }

        private string returnSqlOfStl_chemelement_s(string sqlCondition)
        {
            string sqlStr = "";
            sqlStr = @" (select y.*  from   (select x.*,  row_number() over(partition by x.heatno, substr(assaytypecode, 1, 1) order by x.assaytypecode desc) xh,
                        substr(assaytypecode, 1, 1) typecode    from   (select *  from   stl_chemelement   where  upper(substr(assaytypecode, 1, 1)) = 'B'
                       {0}  union     select *   from   j#stl_chemelement  where  upper(substr(assaytypecode, 1, 1)) = 'B' {0}) x) y
                       where  y.xh = 1) t3,  dev_ib_base_info t4";

            return string.Format(sqlStr, sqlCondition);
        }


        private string returnSqlOfSqlWhere(string sqlCondition)
        {
            string sqlStr = "";
            sqlStr = @" where  1 = 1and t.molirnsource = a.hudwid(+)  and    t.molirnsource = b.heatno(+) and    t.misid = t2.heatno(+)
                    and    a.hudwid = t3.heatno(+)  and    t.tfpotid = t4.ladleid(+) {0}";

            return string.Format(sqlStr, sqlCondition);
        }


        private string returnSqlConditionOfDate(string conditionColumn,string startDate,string endDate)
        {
            string sqlCondition = "";
            sqlCondition = " and {0} >= trunc(to_date('{1}','yyyy-mm-dd'))  and {0} < trunc(to_date('{2}','yyyy-mm-dd') + 1)";
            sqlCondition = string.Format(sqlCondition,conditionColumn,startDate,endDate);
            return sqlCondition;        

 
        }

        private string returnSqlConditionOther(string moSource,string heatStation,string shfitCode,string shiftCodeBb)
        {
            string sqlStr = "";
                   sqlStr = @"  AND substr(T.MOLIRNSOURCE,3, 1) =
                            decode('{0}', null, substr(T.MOLIRNSOURCE, 3, 1), '{0}')
                            and substr(T.MOLIRNSOURCE,1, 1) =
                            decode('{1}', null, substr(T.MOLIRNSOURCE, 1, 1), '{1}')
                            and substr(T.SHIFTCODE, 1, 1) =
                            decode('{2}', null, substr(T.SHIFTCODE, 1, 1), '{2}')
                            and substr(T.SHIFTCODE, 2, 1) =
                            decode('{3}', null, substr(T.SHIFTCODE, 2, 1),'{3}')";
                  sqlStr = string.Format(sqlStr,moSource,heatStation,shfitCode,shiftCodeBb);

                  return sqlStr;
 
        }


        private string appendAllSql(DateTime startDate, DateTime endDate, string moSource, string heatStation, string shfitCode, string shiftCodeBb)
        {
            string sqlStr = "";
            string strStart = startDate.AddDays(-1).ToString("yyyy-MM-dd");
            string strEnd =  endDate.AddDays(1).ToString("yyyy-MM-dd");

            sqlStr += returnSqlstr();
            sqlStr += returnSqlOfStl_mis_importiron(returnSqlConditionOfDate("operationdate", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd")));
            sqlStr += returnSqlOfStl_hmp_optinfo(returnSqlConditionOfDate("optdate",strStart,strEnd));
            sqlStr += returnSqlOfStl_bof_optinfo(returnSqlConditionOfDate("optdate", strStart, strEnd));
            sqlStr += returnSqlOfStl_chemelement(returnSqlConditionOfDate("assaydate", strStart, strEnd));
            sqlStr += returnSqlOfStl_chemelement_s(returnSqlConditionOfDate("assaydate", strStart, strEnd));
            sqlStr += returnSqlOfSqlWhere(returnSqlConditionOther(moSource,heatStation,shfitCode,shiftCodeBb));
            sqlStr += "  order  by t.misid ";
            return sqlStr;
        }


      

        private DataSet returnBofDs(string sqlCondtion)
        {
            DataSet ds = new DataSet();
            try
            {               
                ds = Core.Mes.Client.Common.DbCommon.returnDataSet(Core.LgMes.Client.lgBll.lgResMgt.CommonAtomForLgResMgt.returnSqlOfStl_Bof_Optinfo(sqlCondtion),this.ob);
            }
            catch
            { 
            }
            
            return ds;
 
        }

        private string returnAllHeatNo(DataSet ds,string tbColumn)
        {
            string sqlWhere = "";

            try
            {
                if (ds != null & ds.Tables.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        if (dr[tbColumn.ToUpper()].ToString().Length > 0)

                            sqlWhere += string.Format(" {1} = '{0}'", dr[tbColumn.ToUpper()].ToString(), tbColumn) + " or ";
                    }
                }
            }
            catch
            { 
            }

          

            if (sqlWhere.Length > 0)
            {
                sqlWhere = " and (" + sqlWhere + " 1 = 2 )";
            }





            return sqlWhere;
        }

        private DataSet returnHmpDs(string sqlCondtion)
        {
            DataSet ds = new DataSet();
            try
            {
                 ds = Core.Mes.Client.Common.DbCommon.returnDataSet(Core.LgMes.Client.lgBll.lgResMgt.CommonAtomForLgResMgt.returnSqlOfStl_Hmp_Optinfo(sqlCondtion),this.ob);
            }
            catch
            {
            }
           
            return ds;

        }



        private double returnHmpDataInfo(string vHuwid,DataSet dsSource)
        {
            double result = 0;
           
            if (vHuwid.Trim().Length == 0)
                return result;
            string rowFilter = string.Format("HUDWID = '{0}'", vHuwid);
            DataRow[] drTemp = dsSource.Tables[0].Select(rowFilter);

            try
            {
                if (drTemp.Length > 0)
                result = Convert.ToDouble(drTemp[0]["LIMESTONE"]);

            }
            catch
            { 
            }
            return result;

           
        }


        private ArrayList returnHuwid(DataSet ds)
        {
            ArrayList list = new ArrayList();

            if (lgCommon.isDataSetDataFoundCheck(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    if (dr["HUDWID"].ToString().Length > 0)
                    {
                        list.Add(dr["HUDWID"].ToString());
                    }
                }
            }
            

            return list;
        }


        private DataSet returnHuwidByList(DataSet dsSource)
        {
            DataSet ds = null;
            try
            {
                 ds = Core.LgMes.Client.lgBll.lgResMgt.CommonAtomStlHmpMgt.returnDsOfHmpInfo(new ClientParamWithSqlConditionAndOpenBase(lgCommon.returnSqlWhereConditionByColumnListAndColumnName(returnHuwid(dsSource), "HUDWID"), this.ob));
            }
            catch
            {
 
            }
            return ds;
        }




        private DataSet  returnNewDataSet(DataSet ds)
        {
            DataSet dsReturn = new DataSet();
            if (ds != null & ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count == 0)
                    return dsReturn;

                dsReturn = ds.Copy();
                DataSet dsBof = this.returnBofDs(this.returnAllHeatNo(dsReturn, "HEATNO"));
                DataSet dsHmp = returnHuwidByList(returnHuwidByList(ds));
              
                foreach (DataRow dr in dsReturn.Tables[0].Rows)
                {
                    try
                    {
                        try
                        {
                            dr["LIMESTONE"] = "" + returnHmpDataInfo(dr["HUDWID"].ToString(), dsHmp);
                        }
                        catch
                        { }
                        string rowFilter = string.Format("HEATNO = '{0}'", dr["HEATNO"].ToString());
                        DataRow[] drTemp = dsBof.Tables[0].Select(rowFilter);
                        dr["PLANSTEEL"] = drTemp[0]["PLANSTEEL"];
                        DateTime dateTime = Convert.ToDateTime(drTemp[0]["CHANGESTARTTIME"]);
                        dr["CHANGESTARTTIME"] = dateTime.ToString("HH:mm");
                        DateTime  leavetime = Convert.ToDateTime(dr["LEAVETIME"]);
                        dr["WaitingTime"] = returnTimeSubByDifferentTimeDate(dateTime, leavetime);
                        //dr["CHANGESTARTTIME"] = dateTime.ToString();
                        dr["LEAVETIME"] = leavetime.ToString("HH:mm");
                    }
                    catch
                    {
                    }

                }

            }
           

            return dsReturn;
            

        }
        /// <summary>
        /// 计算两个时间差（分钟）
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        public double returnTimeSubByDifferentTimeDate(System.DateTime dt1, System.DateTime dt2)
        {
            try
            {
                TimeSpan ts = dt1.Subtract(dt2);

               // string s1 = (Convert.ToSingle(ts.Hours))*60 + Convert.ToSingle(ts.Minutes) +"." + Convert.ToSingle(ts.Seconds);
                double s1 = (Convert.ToSingle(ts.Hours)) * 60 + Convert.ToSingle(ts.Minutes) + (Convert.ToSingle(ts.Seconds)/60);
                double result = Convert.ToDouble(s1); ;

                return Math.Round(s1);
               // return Math.Round(result);
            }
            catch
            {
                return 0;
            }
        }
    }
}