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

using WMS.Model.SystemManage;

namespace WMS.SqlServerData.SystemManageData
{
    /// <summary>
    /// 字典明细
    /// </summary>
    public class DictionaryTabData : IDictionaryTab
    {
        /// <summary>
        /// 根据字典名称查询字典值
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable GetGetDictionaryTabName(string name)
        {
            string sqlStr = " select VC_DICTIONARY_ID from [dbo].[T_SYS_DICTIONARY_TAB] where VC_DICTIONARY_NAME='"+name+"'  ";
            return SystemDataObject.Instance.GetDataTable(sqlStr);
        }
        /// <summary>
        /// 获取字典明细
        /// </summary>
        /// <param name="strDictionaryType">字典类型</param>
        /// <returns>字典明细</returns>
        public DataTable GetDictionaryTab(string dictionaryType)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("select");
            sqlBuilder.AppendLine("     vc_dictionary_type,                                        --对应字典类型表的主键");
            sqlBuilder.AppendLine("     vc_dictionary_id,                                          --字典类型值ID");
            sqlBuilder.AppendLine("     vc_dictionary_name,                                        --字典类型值");
            sqlBuilder.AppendLine("      vc_char_value,                       --字符值");
            sqlBuilder.AppendLine("        isnull(n_number_value,0) n_number_value,                      --数值");
            sqlBuilder.AppendLine("     vc_sort_id,                                                --排序");
            sqlBuilder.AppendLine("            isnull(vc_pinyin_code,'') vc_pinyin_code,                     --助记码");
            sqlBuilder.AppendLine("         isnull(vc_description,'') vc_description,                     --描述");
            sqlBuilder.AppendLine("     ( case c_flag when '0' then '不使用'   when '1' then  '使用'  when '2' then '冻结' end)c_flag ,  --是否使用 0否 1是");
            sqlBuilder.AppendLine("     dictionaryname,                                            --所属字典类型");
            sqlBuilder.AppendLine("     a.operatorid,                                              --操作人");
            sqlBuilder.AppendLine("     a.operatedate                                              --操作日期");
            sqlBuilder.AppendLine("from");
            sqlBuilder.AppendLine("     t_sys_dictionary_tab a,t_sys_dictionarytype b ");
            sqlBuilder.AppendLine("where  a.vc_dictionary_type = b.dictionarytype ");
            if (!string.IsNullOrEmpty(dictionaryType))
            {
                sqlBuilder.AppendLine(" and vc_dictionary_type = '" + dictionaryType + "' ");
            }
            sqlBuilder.AppendLine("order by vc_dictionary_type,vc_sort_id,vc_char_value");

            return SystemDataObject.Instance.GetDataTable(sqlBuilder.ToString());
            //return   SystemDataObject.Instance.GetDataSet( SqlBuilder.ToString());
        }
        public void InsertDictionTab(DictionaryTabModel dictionaryTabModel)
        {
          string  sqlstr = "insert into     t_sys_dictionary_tab ("
                                     + "    vc_dictionary_type,"
                                    + "     vc_dictionary_id,  "
                                     + "    vc_dictionary_name,"
                                     + "    vc_char_value,    "
                                    + "     n_number_value,   "
                                      + "   vc_sort_id,        "
                                     + "    vc_pinyin_code,    "
                                     + "    vc_description,    "
                                    + "     c_flag,               "
                                     + "    operatorid,             "
                                      + "   operatedate         "
                                      + "       ) "


                                 + " values ('" + dictionaryTabModel.VC_DICTIONARY_TYPE + "','" + dictionaryTabModel.VC_DICTIONARY_ID + "','" + dictionaryTabModel.VC_DICTIONARY_NAME + "',"
                                 + " '" + dictionaryTabModel.VC_CHAR_VALUE + "','" + dictionaryTabModel.N_NUMBER_VALUE + "','" + dictionaryTabModel.VC_SORT_ID + "',"
                                 + " '" + dictionaryTabModel.VC_PINYIN_CODE + "','" + dictionaryTabModel.VC_DESCRIPTION + "',"
                                 + " '" + dictionaryTabModel.C_FLAG + "','" + dictionaryTabModel.OPERATORID + "',getdate() )";
            SystemDataObject.Instance.ExecuteSql(sqlstr);
        }
        /// <summary>
        /// 插入或修改字典明细
        /// </summary>
        /// <param name="dictionaryTabModel">字典类型详细Model</param>
        /// <param name="Flag">操作表示符 0:新增； 1:修改</param>
        public void UpdateDictionaryTab(DictionaryTabModel dictionaryTabModel)
        {
            
            
               string sqlstr =@"update 
	                        t_sys_dictionary_tab 
                        set 
	                        vc_dictionary_type=:vc_dictionary_type, --对应字典类型表的主键
	                        vc_dictionary_name=:vc_dictionary_name, --字典类型值
	                        vc_char_value=:vc_char_value,           --字符值
	                        n_number_value=:n_number_value,         --数值
	                        vc_sort_id=:vc_sort_id,                 --排序
	                        vc_pinyin_code=:vc_pinyin_code,         --助记码
	                        vc_description=:vc_description,         --描述
	                        c_flag=:c_flag,                         --是否使用 0否 1是           
	                        operatorid=:operatorid,                 --操作人
                            operatedate=getdate()  
                        where 
	                        vc_dictionary_id=:vc_dictionary_id      --字典类型值ID";
                OracleParameter[] parameter = new OracleParameter[] 
                    {
                       new OracleParameter(":vc_dictionary_type",OracleDbType.Varchar2),
                       new OracleParameter(":vc_dictionary_name",OracleDbType.Varchar2),
                       new OracleParameter(":vc_char_value",     OracleDbType.Varchar2),
                       new OracleParameter(":n_number_value",    OracleDbType.Decimal),
                       new OracleParameter(":vc_sort_id",        OracleDbType.Decimal),
                       new OracleParameter(":vc_pinyin_code",    OracleDbType.Varchar2),
                       new OracleParameter(":vc_description",    OracleDbType.Varchar2),
                       new OracleParameter(":c_flag",            OracleDbType.Char),
				       new OracleParameter(":operatorid",        OracleDbType.Varchar2),
                       new OracleParameter(":vc_dictionary_id",  OracleDbType.Varchar2),
                    };
                
                
                parameter[0].Value = dictionaryTabModel.VC_DICTIONARY_TYPE;
                parameter[1].Value = dictionaryTabModel.VC_DICTIONARY_NAME;
                parameter[2].Value = dictionaryTabModel.VC_CHAR_VALUE;
                parameter[3].Value = dictionaryTabModel.N_NUMBER_VALUE;
                parameter[4].Value = dictionaryTabModel.VC_SORT_ID;
                parameter[5].Value = dictionaryTabModel.VC_PINYIN_CODE;
                parameter[6].Value = dictionaryTabModel.VC_DESCRIPTION;
                parameter[7].Value = dictionaryTabModel.C_FLAG.ToCharArray()[0];
                parameter[8].Value = dictionaryTabModel.OPERATORID;
                parameter[9].Value = dictionaryTabModel.VC_DICTIONARY_ID;

                SystemDataObject.Instance.ExecuteSqlCommit(sqlstr, parameter);
                //OracleHelper.ExecuteNonQuery(CommandType.Text, sqlstr, parameter);
             
        }

        /// <summary>
        /// 删除字典明细
        /// </summary>
        /// <param name="vc_dictionary_id">字典明细ID</param>
        public void DeleteDictionaryTab(string vc_dictionary_id)
        {
            string sqlstr =@"delete from t_sys_dictionary_tab where vc_dictionary_id=:vc_dictionary_id";
            OracleParameter[] parameter = new OracleParameter[]{
                   new OracleParameter(":vc_dictionary_id",OracleDbType.Varchar2),
                };
            parameter[0].Value = vc_dictionary_id;

            SystemDataObject.Instance.ExecuteSqlCommit(sqlstr, parameter);
            //OracleHelper.ExecuteNonQuery(CommandType.Text, sql, parameter);
        }

        /// <summary>
        /// 删除属于某字典类型的字典明细信息
        /// </summary>
        /// <param name="dictionaryType">某字典类型</param>
        public void DeleteDictionaryTabByDictionaryType(string dictionaryType)
        {
            string sqlstr =@"delete from t_sys_dictionary_tab where vc_dictionary_type=:vc_dictionary_type";
            OracleParameter[] parameter = new OracleParameter[]{
                   new OracleParameter(":vc_dictionary_type",OracleDbType.Varchar2),
                };
            parameter[0].Value = dictionaryType;

            SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
            //OracleHelper.ExecuteNonQuery(CommandType.Text, sql, parameter);
        }
    }
}