jisco_winform_wzjl/CreateAccess/frmHisQuery.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Infragistics.Win.UltraWinGrid;
using System.IO;
using System.Data.OleDb;        //连接Access数据库
using ADOX;
using System.Reflection;
using System.Threading;
using Infragistics.Win;

namespace CreateAccess
{
    public partial class frmHisQuery : Form
    {
        ServiceReference1.WebServerClient webServer = new ServiceReference1.WebServerClient();
        public frmHisQuery()
        {
            InitializeComponent();
        }

        private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
        {
            switch (e.Tool.Key)
            {
                case "btnQuery":
                    {
                        btnQuery();
                        break;
                    }
                case "btnImpl": 
                    {
                        openFileDialog1.Filter = "Access数据库文件(mdb)|*.mdb";//只允许mdb文件
                        openFileDialog1.Title = "选择要导入的Access数据文件"; //弹出框头部显示
                        openFileDialog1.AddExtension = true; //自动增加后缀
                        openFileDialog1.AutoUpgradeEnabled = true; //是否随系统自动升级弹出窗口样式
                        openFileDialog1.InitialDirectory = Application.StartupPath;//默认打开当前目录
                        if (openFileDialog1.ShowDialog() == DialogResult.OK) 
                        {
                            btnImpl(openFileDialog1.FileName);
                        };
                        break;
                    }
                default: break;
            }
        }

        private void btnImpl(string fileName)
        {
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Persist Security Info=False";
            DataSet ds = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConnection))
            {
                conn.Open();

                string strSql = "select * from 生产数据";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);


                da.Fill(ds, "生产数据");

                try
                {
                    ServiceReference1.meterWorkStorageActual[] lm = new ServiceReference1.meterWorkStorageActual[ds.Tables[0].Rows.Count];
                    string preNo = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                    string strBaseSpotNo = "", strBaseSpotName = "";
                    int i = 0;
                    foreach (DataRow dr in ds.Tables[0].Rows) 
                    {
                        switch (dr["包装线代号"].ToString().Trim()) 
                        {
                            case "1":
                                strBaseSpotNo = "001034001";
                                strBaseSpotName = "铜1号线";
                                break;
                            case "2":
                                strBaseSpotNo = "001034002";
                                strBaseSpotName = "铜2号线";
                                break;
                        }
                        lm[i++] = new ServiceReference1.meterWorkStorageActual
                        {
                            predictionNo = preNo,

                            batchNo = dr["批号"].ToString().Trim(),
                            valueFlag = "1",
                            checkFlag = "0",
                            createManNo = dr["计量员编号"].ToString().Trim(),
                            createManName = dr["计量员"].ToString().Trim(),
                            createTime = DateTime.Now,
                            createTimeSpecified = true,
                            //productNo = "",s
                            productName = dr["品名"].ToString().Trim(),
                            //gradeNo = "",
                            gradeName = dr["牌号"].ToString().Trim(),
                            //standardNo = "",
                            standardName = dr["标准"].ToString().Trim(),
                            packageNo = dr["包号"].ToString().Trim(),
                            blockNum = dr["片数"].ToString().Trim(),
                            productDate = Convert.ToDateTime(dr["生产日期"].ToString().Trim()),
                            productDateSpecified = true,
                            grossWeight = Convert.ToDecimal(dr["毛重"].ToString().Trim()),
                            grossWeightSpecified = true,
                            tareWeight = Convert.ToDecimal(dr["毛重"].ToString().Trim()) - Convert.ToDecimal(dr["主称净重"].ToString().Trim()),
                            tareWeightSpecified = true,
                            netWeight = Convert.ToDecimal(dr["主称净重"].ToString().Trim()),
                            netWeightSpecified = true,

                            baseSpotNo = strBaseSpotNo,
                            baseSpotName = strBaseSpotName,

                            actualWeight = Convert.ToDecimal(dr["主称净重"].ToString().Trim()),
                            actualWeightSpecified = true,
                            referWeight = Convert.ToDecimal(dr["副称净重"].ToString().Trim()),
                            referWeightSpecified = true
                        };
                    }

                    da.Dispose();

                    ServiceReference1.resTfulResult res = webServer.doAccessCuImpl(lm);
                    if (res.succeed)
                    {
                        MessageBox.Show("导入完成");
                        btnQuery();
                    }
                    else
                    {
                        MessageBox.Show("部分数据导入失败");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导入失败："+ex.Message);
                    System.GC.Collect();
                }
            }
        }

        private void btnQuery()
        {
            try
            {
                if (DateTime.Compare(dtStartTime.Value, dtEndTime.Value) > 0)
                {
                    MessageBox.Show("开始时间不能大于结束时间");
                    return;
                }
                if ((dtEndTime.Value - dtStartTime.Value).Days > 7)
                {
                    MessageBox.Show("时间跨度不能大于7天");
                    return;
                }


                ServiceReference1.commonPage cp = new ServiceReference1.commonPage();
                cp.startTime = dtStartTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                cp.endTime = dtEndTime.Value.ToString("yyyy-MM-dd HH:mm:ss");
                cp.param = new ServiceReference1.meterWorkStorageActual { valueFlag = "1", baseSpotNo = cmbPoint.Value?.ToString(), productNo = cmbMatterNo.Text.Trim() };


                ServiceReference1.meterWorkStorageActual[] ls = webServer.doQueryTimeWf(cp);

                DataTable dt = this.dataTable1.Clone();
                if (ls != null && ls.Length > 0)
                {
                    dt = ListToDataTable<ServiceReference1.meterWorkStorageActual>(ls);
                }
                CopyDataToDatatable(ref dt, ref this.dataTable1, true);
                RefreshAndAutoSize(ultraGridSj);
            }
            catch (Exception ex)
            {
                MessageBox.Show("查询异常：" + ex.Message);
            }
        }

        private void frmHisQuery_Load(object sender, EventArgs e)
        {
            dtStartTime.Value = Convert.ToDateTime((DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"));
            dtEndTime.Value = Convert.ToDateTime((DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59"));

            SetUltraGridRowFilter(ref ultraGridSj, true);
            #region
            /*
            ServiceReference1.comBaseInfo cp = new ServiceReference1.comBaseInfo();
            cp.validFlag = "1";
            cp.pBaseCode = "001025";

            ServiceReference1.comBaseInfo[] ls = webServer.doQueryComBaseInfoWf(cp);

            if (ls != null && ls.Length > 0)
            {
                SetUltraComboDataSource(cmbMatterNo, ListToDataTable<ServiceReference1.comBaseInfo>(ls), "baseCode", "baseName");
            }
            else 
            {
                MessageBox.Show("产品信息载入失败");
                return;
            }
            //*/

            ServiceReference1.comBaseInfo co = new ServiceReference1.comBaseInfo();
            co.validFlag = "1";
            co.pBaseCode = "001034";
            ServiceReference1.comBaseInfo[] lo = webServer.doQueryComBaseInfoWf(co);
            if (lo != null && lo.Length > 0)
            {
                SetUltraComboDataSource(cmbPoint, ListToDataTable<ServiceReference1.comBaseInfo>(lo), "baseCode", "baseName");
            }
            else 
            {
                MessageBox.Show("计量点载入失败");
                return;
            }
            #endregion
        }


        #region 控件数据设置
        public void RefreshAndAutoSize(UltraGrid ultraGrid)
        {
            try
            {
                ultraGrid.BeginUpdate();
                foreach (UltraGridBand band in ultraGrid.DisplayLayout.Bands)
                {
                    foreach (UltraGridColumn column in band.Columns)
                    {
                        column.PerformAutoResize(PerformAutoSizeType.AllRowsInBand, band.ColHeadersVisible ? true : false);
                    }
                }
            }
            catch { }
            ultraGrid.EndUpdate();
        }

        public void CopyDataToDatatable(ref DataTable src, ref DataTable dest, bool ClearExists)
        {
            if (src == null)
            {
                dest = null;
                return;
            }

            if (dest == null)
            {
                dest = new DataTable();
            }


            if (ClearExists)
            {
                if (dest != null) dest.Rows.Clear();
            }

            DataRow CurRow, NewRow;

            if (src.Rows.Count > 0)
                for (int i = 0; i < src.Rows.Count; i++)
                {
                    CurRow = src.Rows[i];
                    NewRow = dest.NewRow();

                    for (int j = 0; j < src.Columns.Count; j++)
                    {
                        try
                        {
                            if (dest.Columns.Contains(src.Columns[j].ColumnName))
                            {
                                NewRow[src.Columns[j].ColumnName] = CurRow[j];
                            }
                        }
                        catch { }
                    }

                    dest.Rows.Add(NewRow);
                }

            dest.AcceptChanges();
        }

        public void SetUltraGridRowFilter(ref UltraGrid myGrid1, bool bAllowFilter)
        {
            try
            {
                if (bAllowFilter)
                {
                    if (myGrid1.DisplayLayout.Override.FilterUIType != FilterUIType.FilterRow)
                        myGrid1.DisplayLayout.Override.FilterUIType = FilterUIType.FilterRow;

                    for (int i = 0; i < myGrid1.DisplayLayout.Bands.Count; i++)
                    {
                        for (int j = 0; j < myGrid1.DisplayLayout.Bands[i].Columns.Count; j++)
                        {
                            try
                            {
                                if (!myGrid1.DisplayLayout.Bands[i].Columns[j].Hidden && myGrid1.DisplayLayout.Bands[i].Columns[j].RowLayoutColumnInfo.LabelPosition != LabelPosition.LabelOnly)
                                {
                                    //string sKey = myGrid1.DisplayLayout.Bands[i].Columns[j].Key; //UNCK  UNCH
                                    myGrid1.DisplayLayout.Bands[i].Columns[j].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True;
                                }
                            }
                            catch { }
                        }
                    }
                }
                else
                {
                    if (myGrid1.DisplayLayout.Override.FilterUIType != FilterUIType.HeaderIcons)
                        myGrid1.DisplayLayout.Override.FilterUIType = FilterUIType.HeaderIcons;

                    for (int i = 0; i < myGrid1.DisplayLayout.Bands.Count; i++)
                    {
                        try
                        {
                            myGrid1.DisplayLayout.Bands[i].ColumnFilters.ClearAllFilters();
                        }
                        catch { }

                        for (int j = 0; j < myGrid1.DisplayLayout.Bands[i].Columns.Count; j++)
                        {
                            try
                            {
                                myGrid1.DisplayLayout.Bands[i].Columns[j].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False;
                            }
                            catch { }
                        }
                    }
                }
            }
            catch { }
        }

        public void SetUltraComboDataSource(UltraCombo cmb, DataTable table, string ColumnValue, string ColumnDisplay)
        {
            if (table == null || !table.Columns.Contains(ColumnValue) || !table.Columns.Contains(ColumnDisplay)) return;
            try
            {
                if (cmb != null)
                {
                    cmb.DataSource = table;
                    cmb.DisplayLayout.Override.TipStyleScroll = TipStyle.Hide;
                    cmb.DisplayLayout.Override.BorderStyleCell = UIElementBorderStyle.None;
                    cmb.DisplayLayout.Override.BorderStyleRow = UIElementBorderStyle.None;
                    cmb.DisplayLayout.Override.SelectTypeRow = SelectType.Single;
                    cmb.DisplayLayout.Override.HeaderStyle = HeaderStyle.Standard;
                    cmb.DisplayLayout.Override.HeaderAppearance.TextHAlign = HAlign.Center;
                    cmb.DisplayLayout.Override.RowAppearance.TextVAlign = VAlign.Middle;
                    cmb.DisplayLayout.Override.ActiveRowAppearance.BackColor = Color.FromArgb(51, 153, 255);
                    cmb.DisplayLayout.Override.ActiveRowAppearance.ForeColor = Color.FromArgb(255, 255, 255);
                    cmb.DisplayLayout.Override.SelectedRowAppearance.BackColor = Color.FromArgb(51, 153, 255);
                    cmb.DisplayLayout.Override.SelectedRowAppearance.ForeColor = Color.FromArgb(255, 255, 255);
                    //cmb.DisplayLayout.Override.ActiveRowAppearance.FontData.Bold = DefaultableBoolean.True;
                    foreach (UltraGridBand band in cmb.DisplayLayout.Bands)
                    {
                        band.ColHeadersVisible = false;
                        foreach (UltraGridColumn column in band.Columns)
                        {
                            if (!column.Key.Equals(ColumnDisplay)) column.Hidden = true;
                            column.Width = cmb.Width;
                            //column.PerformAutoResize(PerformAutoSizeType.AllRowsInBand);
                        }
                    }
                    cmb.ValueMember = ColumnValue;
                    cmb.DisplayMember = ColumnDisplay;
                    cmb.DropDownWidth = -1;
                }
            }
            catch { }
        }


        public DataTable ListToDataTable<T>(T[] items)
        {
            DataTable dataTable = new DataTable();

            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                dataTable.Columns.Add(prop.Name);
            }

            foreach (T obj in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    values[i] = Props[i].GetValue(obj, null);
                }
                dataTable.Rows.Add(values);
            }

            return dataTable;
        }
        #endregion
    }
}