MES_Client/TGMES/Core.StlMes.Client.Sale.Util/UtilDataSet.cs

using System;
using System.Reflection;
using System.Collections;
using System.Resources;
using System.Windows.Forms;
using System.Data;
using System.Collections.Generic;


namespace Core.StlMes.Client.Sale.Util
{
    public class UtilDataSet
    {


        #region  dataset 组合构造
         
        /// <summary>
        /// 根据列名组合一个ds，类型默认string
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static DataSet GetDataSetByDataColumn(string[] columnName)
        {
            DataSet ds = new DataSet();
            if (columnName == null || columnName.Length == 0)
                return null;
            DataColumn dc = null;
            DataTable dt = new DataTable();
            Hashtable hsColumnCaption =
            TbColumnAndCaptionDefine
             .GetInstance().GetTbColumnAndCaption();  
            foreach (string s in columnName)
            {
                dc = new DataColumn(s, System.Type.GetType("System.String"));
                if (hsColumnCaption.ContainsKey(s.ToUpper()))
                {
                    dc.Caption = hsColumnCaption[s.ToUpper()].ToString();
                }
                dt.Columns.Add(dc);
            }

            ds.Tables.Add(dt);

            return ds;
        }

        /// <summary>
        /// 设置Ds数据集，并检查ds的同字段类型是否和数据源一致，
        /// 不一致则更改和数据源一致
        /// </summary>
        /// <param name="dsStruct"></param>
        /// <param name="dsSource"></param>
        public static void SetDataSetByMergeSameColumnName(ref DataSet dsStruct,DataSet dsSource)
        {
            if (dsStruct == null || dsStruct.Tables.Count == 0)
                return ;
            if (dsSource == null || dsSource.Tables.Count == 0)
                return ;
            dsStruct.Clear();
            foreach (DataColumn dc in dsStruct.Tables[0].Columns)
            {
                if (dsSource.Tables[0].Columns.Contains(dc.ColumnName) &&
                    dsSource.Tables[0].Columns[dc.ColumnName].DataType != dc.DataType)
                    
                {
                    try
                    {
                        if (dsSource.Tables[0].Columns[dc.ColumnName].DataType == System.Type.GetType("System.DateTime"))
                        dc.DataType = dsSource.Tables[0].Columns[dc.ColumnName].DataType;
                    }
                    catch
                    { }
                }
            }
            // 数据结构没有的列不需要显示

            dsStruct.Merge(dsSource, false, System.Data.MissingSchemaAction.Ignore);
            dsStruct.AcceptChanges();
           

         
        }

        /// <summary>
        /// 从数据集中获取某一字段的值（string)，并存入list
        /// </summary>
        /// <param name="dsSource"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static List<String> GetColumnStringValueFromDataSourceByColumnName(DataSet dsSource, string columnName)
        {
            List<String> list = new List<string>();
            if (dsSource == null || dsSource.Tables.Count == 0 || dsSource.Tables[0].Rows.Count == 0 || columnName.Length == 0)
                return list;
            if (!dsSource.Tables[0].Columns.Contains(columnName.ToUpper()))
                return list;

            try
            {
                foreach (DataRow dr in dsSource.Tables[0].Rows)
                {
                    try
                    {
                        list.Add(dr[columnName.ToUpper()].ToString());
                    }
                    catch
                    {
                        break;
                    }
                }
            }
            catch
            {
            }

            return list;
        }
        #endregion

        #region ds 客户端排序，过滤

        /// <summary>
        /// 根据某个字段名称降序排ds
        /// </summary>
        /// <param name="dsSource"></param>
        /// <param name="sortColumn"></param>
        /// <returns></returns>

        public static DataSet GetDsByDescOrderFromSourceDataSet(DataSet dsSource, string sortColumn)
        {
            DataSet ds = new DataSet();
            try
            {

                if (dsSource != null & dsSource.Tables.Count > 0)
                {
                    if (!dsSource.Tables[0].Columns.Contains(sortColumn))
                        return dsSource.Copy();
                    if (dsSource.Tables[0].Rows.Count == 0)
                        ds = dsSource.Clone();
                    else
                    {
                        DataView dv = new DataView(dsSource.Tables[0]);
                        dv.Sort = string.Format("{0} desc", sortColumn.ToUpper());
                        ds.Clear();
                        ds.Tables.Add(dv.ToTable());

                    }
                }
            }
            catch
            {

            }
            return ds;
        }

        /// <summary>
        /// 根据某个字段名称升序排ds
        /// </summary>
        /// <param name="dsSource"></param>
        /// <param name="sortColumn"></param>
        /// <returns></returns>
        public static DataSet GetDsByAscOrderFromSourceDataSet(DataSet dsSource, string sortColumn)
        {
            DataSet ds = new DataSet();

            try
            {
                if (dsSource != null & dsSource.Tables.Count > 0)
                {
                    if (!dsSource.Tables[0].Columns.Contains(sortColumn))
                        return dsSource.Copy();
                    if (dsSource.Tables[0].Rows.Count == 0)
                        ds = dsSource.Clone();
                    else
                    {
                        DataView dv = new DataView(dsSource.Tables[0]);
                        dv.Sort = string.Format("{0} asc", sortColumn.ToUpper());
                        ds.Clear();
                        ds.Tables.Add(dv.ToTable());

                    }
                }
            }
            catch
            {

            }
            return ds;
        }

        /// <summary>
        /// 通过过滤条件过滤数据集,只能对string类型
        /// </summary>
        /// <param name="dsSource"></param>
        /// <param name="rowFilter"></param>
        /// <returns></returns>
        public static DataSet GetDataSetWithRowFilter(DataSet dsSource, string columnName,string value)
        {
            DataSet dsReturn = new DataSet();
            if (dsSource == null || dsSource.Tables.Count == 0 || dsSource.Tables[0].Rows.Count == 0)
                return dsSource;
         
         
            if (!dsSource.Tables[0].Columns.Contains(columnName.ToUpper()))
            {
                return dsSource;
            }

            string rowFilter = "";
            if (value.Length == 0)
            {
                rowFilter = string.Format("  {0} is null ", columnName);
            }
            else
            {
                rowFilter = string.Format("  {0} = '{1}' ", columnName, value);
            }
            DataView dv = null;
            try
            {
                dv = dsSource.Tables[0].DefaultView;
                dv.RowFilter = rowFilter;
            }
            catch
            {
                dv = new DataView();
            }
            dsReturn.Tables.Add(dv.ToTable());
            return dsReturn;
        }

        public static DataSet GetDataSetWithRowFilter(DataSet dsSource, string columnName, string[] value)
        {
         
            if (dsSource == null || dsSource.Tables.Count == 0 || dsSource.Tables[0].Rows.Count == 0)
                return dsSource;

            if (value == null || value.Length == 0)
                return dsSource;
            if (!dsSource.Tables[0].Columns.Contains(columnName.ToUpper()))
            {
                return dsSource;
            }

            DataSet dsReturn = dsSource.Clone();

            string rowfil = "";
            if (value.Length >= 300)
            {
                rowfil = columnName + " IN ('" + string.Join("','", value) + "')";
            }
            else {
                foreach (string s in value)
                {
                    string rowf = "";
                    if (s.Length == 0)
                    {
                        rowf = string.Format("  {0} is null ", columnName);
                    }
                    else
                    {
                        rowf = string.Format("  {0} = '{1}' ", columnName, s);
                    }
                    rowf += " or";

                    rowfil += rowf;


                }
                rowfil = "( " + rowfil + "  1= 2 )";
            }
            DataView dvs = null;
            try
            {
                dvs = dsSource.Tables[0].DefaultView;
                dvs.RowFilter = rowfil;
            }
            catch
            {
                dvs = new DataView();
            }

            dsReturn.Tables[0].Merge(dvs.ToTable());
            return dsReturn;

        }

        public static DataSet GetDataSetWithRowFilterByArray(DataSet dsSource, string columnName, string[] value)
        {

            if (dsSource == null || dsSource.Tables.Count == 0 || dsSource.Tables[0].Rows.Count == 0)
                return dsSource;

            if (value == null || value.Length == 0)
                return dsSource;
            if (!dsSource.Tables[0].Columns.Contains(columnName.ToUpper()))
            {
                return dsSource;
            }

            DataSet dsReturn = dsSource.Clone();

       

            foreach (String s in value)
            {


                string rowFilter = "";
                if (s.Length == 0)
                {
                    rowFilter = string.Format("  {0} is null ", columnName);
                }
                else
                {
                    rowFilter = string.Format("  {0} = '{1}' ", columnName, s);
                }

                DataView dv = null;
                try
                {
                    dv = dsSource.Tables[0].DefaultView;
                    dv.RowFilter = rowFilter;
                }
                catch
                {
                    dv = new DataView();
                }
                dsReturn.Tables[0].Merge(dv.ToTable());
            }
            return dsReturn;
        }

        /// <summary>
        /// 模糊匹配
        /// </summary>
        /// <param name="dsSource"></param>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataSet GetDataSetWithRowFilterWithApproximateMatch(DataSet dsSource, string columnName, string value)
        {
            DataSet dsReturn = new DataSet();
            if (dsSource == null || dsSource.Tables.Count == 0 || dsSource.Tables[0].Rows.Count == 0)
                return dsSource;

            if (value.Length == 0)
                return dsSource;
            if (!dsSource.Tables[0].Columns.Contains(columnName.ToUpper()))
            {
                return dsSource;
            }
            string rowFilter = string.Format("  {0}  like '*{1}*'   ", columnName, value);
            DataView dv = null;
            try
            {
                dv = dsSource.Tables[0].DefaultView;
                dv.RowFilter = rowFilter;
            }
            catch
            {
                dv = new DataView();
            }
            dsReturn.Tables.Add(dv.ToTable());
            return dsReturn;
        }


        #endregion

        #region
        /// <summary>
        /// 在客户端对dataset 进行分组统计
        /// </summary>
        /// <param name="dsSource">待统计数据元</param>
        /// <param name="groupColumnName">待统计列</param>
        /// <param name="showName">需显示列</param>
        /// <param name="staticsSumColumn">需统计列，求和</param>
        /// <returns></returns>
        public static DataSet GetDateSetByGroupColumnAndStaticsColumnName(DataSet dsSource,List<String> groupColumnName,List<String> showName,List<String> staticsSumColumn )
        {
            string countStr = "CNTSTA";
            string dsSourceKey = "GROUPKEY";
            DataSet dsReturn = new DataSet();
            if (groupColumnName == null || groupColumnName.Count == 0)
                return dsReturn;
            if (dsSource == null || dsSource.Tables.Count == 0 || dsSource.Tables[0].Rows.Count == 0)
                return dsSource;
            if (staticsSumColumn == null || staticsSumColumn.Count == 0)
            {
                staticsSumColumn = new List<string>();
                staticsSumColumn.Add(countStr);
            }
            else
            {
                staticsSumColumn.Add(countStr);
            }
            System.Collections.Hashtable hsKeyAndValue = new Hashtable();
            System.Collections.Hashtable hsKeyAndTotalSum = new Hashtable();
            System.Collections.Hashtable hsKeyAndTotalSumKe = new Hashtable();
            List<string> listColumnName = new List<string>();
            if (!dsSource.Tables[0].Columns.Contains(dsSourceKey))
            {
                DataColumn dcKey = new DataColumn(dsSourceKey, System.Type.GetType("System.String"));
                dsSource.Tables[0].Columns.Add(dcKey);
            }
            dsReturn = dsSource.Clone();
            // ds 中如果没有分组字段，返回源数据源,待统计列可以不管
            foreach (string s in groupColumnName)
            {
                if (!dsSource.Tables[0].Columns.Contains(s.ToUpper()))
                    return dsSource;
                if (!listColumnName.Contains(s.ToUpper()))
                    listColumnName.Add(s.ToUpper());
            }

            try
            {
                foreach (String s in showName)
                {
                    if (!listColumnName.Contains(s.ToUpper()))
                        listColumnName.Add(s.ToUpper());

                }
            }
            catch
            {
            }


            try
            {
                foreach (String s in staticsSumColumn)
                {
                    if (!listColumnName.Contains(s.ToUpper()))
                        listColumnName.Add(s.ToUpper());
                    if (!hsKeyAndTotalSum.Contains(s))
                    {
                        hsKeyAndTotalSum.Add(s, 0);
                    }

                }
            }
            catch
            {
            }

            string key = "";
            DataRow value = null;
            System.Text.StringBuilder sb = null;
            foreach (DataRow dr in dsSource.Tables[0].Rows)
            {
                sb = new System.Text.StringBuilder();
                value = dr;
                foreach (String s in groupColumnName)
                {
                    key = sb.Append(dr[s.ToUpper()].ToString()).ToString();
                }
                if (!hsKeyAndValue.ContainsKey(key))
                {
                    value[dsSourceKey] = key;
                    hsKeyAndValue.Add(key, value);
                    hsKeyAndTotalSumKe.Add(key, new Hashtable());
                }

                double temp = 0;
                hsKeyAndTotalSum = (Hashtable)hsKeyAndTotalSumKe[key];
                foreach (string s in staticsSumColumn)
                {
                    temp = 0;
                    try
                    {
                        if (hsKeyAndTotalSum.ContainsKey(s))
                            temp = ConverObject.ConvertToDouble(hsKeyAndTotalSum[s]) + ((s == countStr) ? 1 : ConverObject.ConvertToDouble(dr[s]));
                        else
                            temp = (s == countStr) ? 1 : ConverObject.ConvertToDouble(dr[s]);

                        if (hsKeyAndTotalSum.ContainsKey(s))
                            hsKeyAndTotalSum.Remove(s);
                        hsKeyAndTotalSum.Add(s, temp);
                    }
                    catch
                    {
                    }
                }

                hsKeyAndTotalSumKe[key] = hsKeyAndTotalSum;

            }



            foreach (string keys in hsKeyAndValue.Keys)
            {
                DataRow d = (DataRow)hsKeyAndValue[keys];
                dsReturn.Tables[0].Rows.Add(d.ItemArray);
            }


            DataSet dsStruct = new DataSet();
            DataColumn dc = null;
            DataTable dt = new DataTable();
            listColumnName.Add(dsSourceKey);
            foreach (string s in listColumnName)
            {
                dc = new DataColumn(s, System.Type.GetType("System.String"));
                if (staticsSumColumn.Contains(s))
                    dc.DataType = System.Type.GetType("System.Double");
                dt.Columns.Add(dc);

            }
            dsStruct.Tables.Add(dt);

            // 
            SetDataSetByMergeSameColumnName(ref dsStruct, dsReturn);

            foreach (DataRow dr in dsStruct.Tables[0].Rows)
            {
                sb = new System.Text.StringBuilder();
                Hashtable hsStatics = (Hashtable)hsKeyAndTotalSumKe[dr[dsSourceKey].ToString()];
                foreach (string s in staticsSumColumn)
                {
                    dr[s] = ConverObject.ConvertToDouble(hsStatics[s]);
                }
            }
            dsStruct.Tables[0].Columns.Remove(dsSourceKey);
            return dsStruct;
        }
        #endregion
    }
}