using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WMS.DBUtility;
using WMS.IData.ICommon;
using WMS.IData;using Oracle.ManagedDataAccess.Client;

namespace WMS.SqlServerData.Common
{
    public class CommonData : ICommon
    {

        public DataTable GetTable(string sql)
        {
            DataTable table = SystemDataObject.Instance.GetDataTable(sql);
            return table;
        }
        /// <summary>
        /// 通过指定的字段属性查询数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetCustomCtrlData(string tableName, string clmValue, string clmName, string whereSelect, string clmClassName)
        {
            string SqlStr = string.Empty;

            if (string.IsNullOrEmpty(clmClassName))
            {
                SqlStr = "select " + clmValue + "," + clmName + " from " + tableName + " where 1 = 1 ";
            }
            else {
                SqlStr = "select " + clmValue + "," + clmName + "," + clmClassName + " from " + tableName + " where 1 = 1 ";
            }

            
            if (whereSelect != "")
            {
                SqlStr = SqlStr + " and " + whereSelect;
            }

            SqlStr = SqlStr + "order by " + clmValue;
            DataTable table = SystemDataObject.Instance.GetDataTable(SqlStr);
            return table;
        }
        /// <summary>
        /// 返回通用查询的数据
        /// </summary>
        /// <typeparam name="T">实体类的数据类型</typeparam>
        /// <param name="column"></param>
        /// <param name="clmValue"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetListData(List<string> column, Dictionary<string, string> clmValue, string tableName)
        {

            //   T classType = new T();
            //    classType.GetType();
            string clmTable = string.Empty;
            foreach (var clm in column)
            {
                if (clmTable.Trim().Length == 0)
                {
                    clmTable = clm;

                }
                else
                {
                    clmTable = clmTable + "," + clm;
                }


            }
            string whereTable = string.Empty;
            foreach (var whe in clmValue)
            {

                if (whereTable.Trim().Length == 0)
                {
                    whereTable = "  where t." + whe.Key + " like  '%" + whe.Value + "%'";
                }
                else
                {
                    whereTable = "  and t." + whe.Key + " like  '%" + whe.Value + "%'";
                }
            }

            string SqlStr = "select distinct " + clmTable + " from " + tableName + whereTable;

            //  List<object> ListData = new List<object>();

            DataTable table = SystemDataObject.Instance.GetDataTable(SqlStr);
            return table;

        }
        /// <summary>
        /// 获取商品信息
        /// </summary>
        /// <param name="strGooID">商品分类编号</param>
        /// <param name="user_id">用户编号</param>
        /// <returns></returns>
        public DataSet Get_GoodsInfo(string strGooID, string user_id)
        {
            DataSet ds = null;
            string sqlStr = "SELECT * FROM VIEW_GET_GOODSINFO where 1 = 1 ";
            if (strGooID != "")
            {
                sqlStr = sqlStr + " and GOO_SORT_ID in (" + strGooID + ") ";
            }

            if (user_id != "")
            {
                sqlStr = sqlStr + " and  GOO_SORT_ID in (select distinct GOO_SORT_ID from t_base_GoodsType where Role_Id in (select Role_Id from t_base_Role where User_Id = '" + user_id + "')) ";
            }

            ds = SystemDataObject.Instance.GetDataSet(sqlStr);
            return ds;
        }

        /// <summary>
        /// 获取商品分类
        /// </summary>
        /// <param name="status">1使用 0停用</param>
        /// <param name="user_id">用户编号</param>
        /// <returns></returns>
        public DataSet GetGoods_Category(string status, string user_id)
        {
            DataSet ds = null;

            string sqlStr =@"select * from VIEW_GOODS_CATEGORY where 1 = 1";

            if (status != "")
            {
                sqlStr = sqlStr + " and STATUS = '" + status + "' ";
            }
            if (user_id != "")
            {
                sqlStr = sqlStr + " and  GOO_SORT_ID in (select distinct GOO_SORT_ID from t_base_GoodsType where Role_Id in (select Role_Id from t_base_Role where User_Id = '" + user_id + "')) or GOO_SORT_ID = '-1' ";
            }
            sqlStr = sqlStr + " order by GOO_SORT_ID ";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }

        /// <summary>
        /// 获取采购类型
        /// </summary>
        /// <returns></returns>
        public DataSet GetPurchaseType()
        {
            DataSet ds = null;

            string sqlStr = "select TYPE_ID,TYPE_NAME from t_base_purchasetype";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }

        /// <summary>
        /// 获取来源类型
        /// </summary>
        /// <returns></returns>
        public DataSet GetSourceType()
        {
            DataSet ds = null;

            string sqlStr = "select TYPE_ID,TYPE_NAME from t_base_sourcetype";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }
        public DataSet GetActTypte()
        {
            DataSet ds = null;

            string sqlStr = "select TYPE_ID,TYPE_NAME from T_Act_ActType";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }
        /// <summary>
        /// 获取仓库信息
        /// </summary>
        /// <param name="user_id">用户编号</param>
        /// <returns></returns>
        public DataSet GetStorage(string user_id)
        {
            DataSet ds = null;

            string sqlStr = "select STORAGE_NAME,STORAGE_ID from t_base_storageinfo where 1 = 1 ";
            if (user_id != "")
            {
                sqlStr = sqlStr + " and Storage_id in (select distinct Storage_id from t_base_Area where Role_Id in (select Role_Id from t_base_Role where User_Id = '" + user_id + "')) ";
            }

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }
        /// <summary>
        /// 获取库区信息
        /// </summary>
        /// <param name="user_id">用户编号</param>
        /// <returns></returns>
        public DataSet GetArea(string id, string user_id)
        {
            DataSet ds = null;
            string sqlStr = "select area_name,area_id from t_base_areainfo where storage_id='" + id + "'";
            if (user_id != "")
            {
                sqlStr = sqlStr + " and Area_Id in (select distinct Area_Id from t_base_Area where Role_Id in (select Role_Id from t_base_Role where User_Id = '" + user_id + "')) ";
            }
            ds = SystemDataObject.Instance.GetDataSet( sqlStr);
            return ds;
        }
        /// <summary>
        /// 根据仓库编号获取库位信息
        /// </summary>
        /// <param name="StorageID"></param>
        /// <returns></returns>
        public DataSet GetLocation(string StorageID)
        {
            DataSet ds = null;
            string sql = string.Format("SELECT STO_LOC_ID,STO_LOC_NAME FROM T_BASE_LOCATIONINFO WHERE STORAGE_ID ='{0}'", StorageID);
            ds = SystemDataObject.Instance.GetDataSet( sql);
            return ds;
        }
        /// <summary>
        /// 获取物流公司信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetLogisticsCom()
        {
            DataSet ds = null;
            string sqlStr = "select log_com_id,log_com_name from t_base_LogisticsComInfo";
            ds = SystemDataObject.Instance.GetDataSet( sqlStr);
            return ds;
        }
        /// <summary>
        /// 获取卸车人信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetUnloadMan()
        {
            DataSet ds = null;
            string sqlStr = "select name from t_rk_unloadgoods";
            ds = SystemDataObject.Instance.GetDataSet( sqlStr);
            return ds;
        }

        /// <summary>
        /// 供应商信息
        /// </summary>
        /// <param name="user_id">用户编号</param>
        /// <returns></returns>
        public DataSet GetProviderType(string user_id)
        {
            DataSet ds = null;

            string sqlStr = "select PROVIDER_ID,PROVIDER_NAME,ARR_DAYS from t_base_Provider_Dictionary where 1 = 1 ";
            if (user_id != "")
            {
                sqlStr = sqlStr + " and Provider_Id in (select distinct Provider_Id from t_base_Provider where Role_Id in (select Role_Id from t_base_Role where User_Id = '" + user_id + "')) ";
            }

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }

        /// <summary>
        /// 付款方式类型
        /// </summary>
        /// <returns></returns>
        public DataSet GetPayType()
        {
            DataSet ds = null;

            string sqlStr = "select PAY_ID,PAY_NAME,IS_IMPREST from t_base_pay_type";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }

        ///// <summary>
        ///// 获取库存管理模块的各项类型
        ///// </summary>
        ///// <returns></returns>
        //public DataSet GetWMSType(WMS.Model.TypeModel.WMSType type)
        //{
        //    DataSet ds = null;
        //    string sql = string.Format(@"select TYPE_ID,TYPE_NAME FROM T_BASE_TYPEINFO where dictionary_type_id={0}", Convert.ToInt32(type));
        //    ds = SystemDataObject.Instance.GetDataSet( sql);
        //    return ds;
        //}

        /// <summary>
        /// 物流公司信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetLogisticsComInfo()
        {
            DataSet ds = null;

            string sqlStr = "select * from t_base_LogisticsComInfo";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }



        /// <summary>
        /// 获取结果集
        /// </summary>
        /// <param name="col">列名</param>
        /// <param name="table">表名</param>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public DataSet GetDataSet(string col, string table, string where)
        {
            DataSet ds = null;

            string sqlStr = "select distinct " + col + " from " + table + " where 1 = 1 ";
            sqlStr = sqlStr + where;

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }


        /// <summary>
        /// 获取公司类型
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyType()
        {
            DataSet ds = null;

            string sqlStr = "select SHOP_TYPE_ID,SHOP_TYPE_NAME from t_base_company_type";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }

        /// <summary>
        /// 获取公司分类
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyCategory()
        {
            DataSet ds = null;

            string sqlStr =@"select '-1' COM_ID,'' GROUP_ID,'公司分类' COM_ID_NAME,'公司分类' 
                              COM_NAME,'' SHORT 
                                union all
                              select 
                                COM_ID,
                                GROUP_ID,
                                COM_ID  + '-'+ COM_NAME COM_ID_NAME,
                                COM_NAME,
                                SHORT
                              from T_BASE_COMPANY
                            where 1 = 1
                              order by COM_ID ";
            //select '-1' COM_ID,'' GROUP_ID,'公司分类' COM_ID_NAME,'公司分类' 
            //                  COM_NAME,'' SHORT from dual
            //                    union all
            //                  select 
            //                    COM_ID,
            //                     ISNULL(GROUP_ID,'-1') GROUP_ID,
            //                    COM_ID  ||'-'|| COM_NAME COM_ID_NAME,
            //                    COM_NAME,
            //                    SHORT
            //                  from T_BASE_COMPANY
            //                where 1 = 1
            //                  order by COM_ID

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }

        /// <summary>
        /// 获取公司编号和公司名称
        /// </summary>
        /// <returns></returns>
        public DataSet GetCompanyIDName()
        {
            DataSet ds = null;

            string sqlStr = "select COM_ID,COM_NAME from t_base_company";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }


        #region 获取部门编号和部门名称
        /// <summary>
        /// 获取部门编号和部门名称
        /// </summary>
        /// <returns></returns>
        public DataSet GetDepartmentIDName()
        {
            DataSet ds = null;

            string sqlStr = "select DEP_ID,DEP_NAME from t_base_departmentinfo";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }
        #endregion

        #region 获取集团编号和集团名称
        /// <summary>
        /// 获取集团编号和集团名称
        /// </summary>
        /// <returns></returns>
        public DataSet GetGroupIDName()
        {
            DataSet ds = null;

            string sqlStr =@"  select '' COM_ID, '' COM_NAME  union all 
                                select COM_ID,COM_NAME from t_base_company t where t.com_type = '0'";//from dual

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }
        #endregion

        /// <summary>
        /// 获取公司部门
        /// </summary>
        /// <returns></returns>
        public DataTable GetComDetDataTable()
        {
            string sql = "select ID,NAME,PID from view_com_dep";
            return SystemDataObject.Instance.GetDataTable(sql);
        }

        /// <summary>
        /// 获取员工信息
        /// </summary>
        /// <param name="ComId">公司编号</param>
        /// <param name="DetId">部门编号</param>
        /// <param name="UserName">员工名称</param>
        /// <returns></returns>
        public DataTable GetUserDataTable(string ComId, string DetId, string UserName)
        {
            string sql =@"select 
                              COM_NAME,
                              t_base_UserInfo.User_Id,
                              t_base_UserInfo.Name
                           from 
                                t_base_UserInfo
                                   left outer join   
                                t_Base_Role
                                on t_base_UserInfo.User_Id = t_Base_Role.User_Id
                                   left outer join
                                t_base_Company
                                on t_base_UserInfo.Com_Id = t_base_Company.Com_Id
                                   
                            where 1 = 1 ";

//            if (ComId != "")
//            {
//                sql = sql +@" and t_base_UserInfo.Dep_Id in 
//                                (
//                                select  ID  from VIEW_COM_DEP where substr(ID,0,2)='bm'
//                                start with ID = '" + ComId +@"'
//                                connect by prior ID = PID
//                                ) ";
//            }

            if (UserName != "")
            {
                sql = sql + " and t_base_UserInfo.Name like '%" + UserName + "%' ";
            }
            sql = sql + " order by t_base_Company.Com_Id";
            return SystemDataObject.Instance.GetDataTable(sql);
        }

        #region 获取供应商类型
        /// <summary>
        /// 获取供应商类型
        /// </summary>
        /// <returns></returns>
        public DataSet GetProviderType()
        {
            DataSet ds = null;

            string sqlStr =@" select PRO_TYPE_ID, PRO_TYPE_NAME from t_base_providertype where STATUS = '1'  ";

            ds = SystemDataObject.Instance.GetDataSet( sqlStr);

            return ds;
        }
        #endregion

        #region 获取省份
        /// <summary>
        /// 获取省份
        /// </summary>
        /// <returns></returns>
        public DataSet GetProvince()
        {
            DataSet ds = null;
            string strSql =@"select PRO_ID, PRO_NAME from T_BASE_PROVINCE";
            ds = SystemDataObject.Instance.GetDataSet( strSql);
            return ds;
        }
        #endregion

        #region 根据省份编号获取城市
        /// <summary>
        /// 根据省份编号获取城市
        /// </summary>
        /// <param name="strProId">省份编号</param>
        /// <returns></returns>
        public DataSet GetCityByPRO_ID(string strProId)
        {
            DataSet ds = null;
            string strSql =@"select CITY_ID, CITY_NAME from t_base_city t
                                  where 1 = 1 ";
            if (!"".Equals(strProId))
            {
                strSql += " and t.PRO_ID = '" + strProId + "' ";
            }
            ds = SystemDataObject.Instance.GetDataSet( strSql);
            return ds;
        }
        #endregion

        #region 根据城市编号获取区
        /// <summary>
        /// 根据城市编号获取区
        /// </summary>
        /// <param name="CityId">城市编号</param>
        /// <returns></returns>
        public DataSet GetTownByCityId(string strCityId)
        {
            DataSet ds = null;
            string strSql =@"select TOWN_ID, TOWN_NAME from t_base_town t where 1 = 1";
            if (!"".Equals(strCityId))
            {
                strSql += " and t.CITY_ID = '" + strCityId + "' ";
            }
            ds = SystemDataObject.Instance.GetDataSet( strSql);
            return ds;
        }
        #endregion

        #region 获取字典类型对照信息
        /// <summary>
        /// 获取字典类型对照信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetDictionaryType()
        {
            DataSet ds = null;
            string strSql =@"select DICTIONARY_TYPE_ID, DICTIONARY_TYPE_NAME from T_BASE_DICTIONARY_TYPE ";
            ds = SystemDataObject.Instance.GetDataSet( strSql);
            return ds;
        }
        #endregion

        #region 根据类型代码查询出类型字典信息

        #endregion

        /// <summary>
        /// 根据类型代码查询出类型字典信息
        /// </summary>
        /// <param name="strTypeId">类型代码</param>
        /// <returns></returns>
        public DataSet GetTypeInfo(int strTypeId)
        {
            DataSet ds = null;
            string strSql =@"select TYPE_ID, TYPE_NAME from T_BASE_TYPEINFO t where 1 = 1";
            if (!"".Equals(strTypeId))
            {
                strSql += " and t.dictionary_type_id = '" + strTypeId + "' ";
            }
            ds = SystemDataObject.Instance.GetDataSet( strSql);
            return ds;
        }

        ///// <summary>
        ///// 单据打印记录
        ///// </summary>
        ///// <param name="orderPrintModel"></param>
        ///// <returns></returns>
        //public int PrintRecord(OrderPrintModel orderPrintModel)
        //{
        //    //            string sql =@"insert into t_dd_dddyjl 
        //    //                            (DJDYBH,DJBH,DYCS,DYRQ,DYR,DJLX)
        //    //                           values 
        //    //                            (SEQ_DDDYJL_ID.NEXTVAL,)";
        //    return 0;
        //}



        //#region 获取托盘下拉列表
        ///// <summary>
        ///// 获取托盘类型下拉列表
        ///// </summary>
        ///// <returns></returns>
        //public DataSet GetPalletType()
        //{

        //    DataSet ds = null;
        //    string str = "select VC_DICTIONARY_ID,VC_DICTIONARY_NAME from T_SYS_DICTIONARY_TAB where VC_DICTIONARY_TYPE ='pallet_type'";
        //    ds = SystemDataObject.Instance.GetDataSet(str);
        //    return ds;
        //}
        //#endregion
    }
}