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

using WMS.Model.Base;
using Oracle.ManagedDataAccess.Client;

namespace WMS.SqlServerData.BaseData
{
    public class AddressAreaData :IAddressArea
    {
        #region 通过省市区名称获取省市区编号
        /// <summary>
        /// 通过省市区名称获取省市区编号
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetAddressArea(string province, string city, string district)
        {
            #region 查询SQL
            string sql =@" select c.ADD_ID,b.ADD_ID,a.ADD_ID
                            from T_BASE_ADDRESSAREA a
                            left join T_BASE_ADDRESSAREA b on a.add_id = b.add_pid
                            left join T_BASE_ADDRESSAREA c on b.add_id = c.add_pid
                            where 1 = 1 ";
            if (!string.IsNullOrEmpty(province))
            {
                sql += " and a.ADD_NAME like '" + province + "%'";
            }
            if (!string.IsNullOrEmpty(city))
            {
                sql += " and b.add_name like '" + city + "%'";

            } if (!string.IsNullOrEmpty(district))
            {
                sql += " and c.add_name like '" + district + "%'";
            }
            #endregion

            return SystemDataObject.Instance.GetDataTable(sql);
        }
        #endregion

        #region 获取区域信息
        /// <summary>
        /// 获取区域信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetAddressArea(AddressAreaModel model)
        {
            #region 查询SQL
            string sql =@"  select ADD_ID,         --地址编号
                                    ADD_NAME,       --地址名称
                                    ADD_PID,        --父级编号
                                    LEV,            --层级
                                    REMARK,         --备注
                                    VERSION         --版本号
                            from T_BASE_ADDRESSAREA
                            where 1 = 1 ";
            if (!string.IsNullOrEmpty(model.Add_id))
            {
                sql += " and ADD_ID='" + model.Add_id + "'";
            }
            if (!string.IsNullOrEmpty(model.Add_pid))
            {
                sql += " and ADD_PID='" + model.Add_pid + "'";

            } if (!string.IsNullOrEmpty(model.Lev))
            {
                sql += " and lev ='" + model.Lev + "'";
            }
            if (!string.IsNullOrEmpty(model.Add_name))
            {
                sql += " and ADD_NAME ='" + model.Add_name + "'";
            }

            sql += " order by ADD_NAME";

            #endregion

            return SystemDataObject.Instance.GetDataTable(sql);
        }
        #endregion

        #region 新增或修改区域信息
        /// <summary>
        /// 新增或修改区域信息
        /// </summary>
        /// <param name="model"></param>
        /// <param name="flg">0 新增，1 修改</param>
        /// <returns></returns>
        public int execAddressAreaAddUpdate(Model.Base.AddressAreaModel model, int flg)
        {
            string sql = "";
            if (flg == 0)//新增
            {
                sql +=@" insert into T_BASE_ADDRESSAREA(
                                    ADD_ID,         --地址编号
                                    ADD_NAME,       --地址名称
                                    ADD_PID,        --父级编号
                                    LEV,            --层级
                                    REMARK,         --备注
                                    VERSION         --版本号 
                                    ) 
                                    values(:ADD_ID,:ADD_NAME,:ADD_PID,
                                    :LEV,:REMARK,:VERSION)";
                #region 参数添加
                OracleParameter[] parameters = new OracleParameter[] { 
                   new OracleParameter(":ADD_ID",OracleDbType.Varchar2),
                   new OracleParameter(":ADD_NAME",OracleDbType.Varchar2),
                   new OracleParameter(":ADD_PID",OracleDbType.Varchar2),
                   new OracleParameter(":LEV",OracleDbType.Varchar2),
                   new OracleParameter(":REMARK",OracleDbType.Varchar2),
                   new OracleParameter(":VERSION",OracleDbType.Varchar2)
                };

                parameters[0].Value = model.Add_id;
                parameters[1].Value = model.Add_name;
                parameters[2].Value = model.Add_pid;
                parameters[3].Value = model.Lev;
                parameters[4].Value = model.Remark;
                parameters[5].Value = model.Version;

                #endregion

                return SystemDataObject.Instance.ExecuteSqlCommit(sql, parameters);
            }
            else//修改
            {
                sql +=@" update T_BASE_ADDRESSAREA set
                                    ADD_NAME =:ADD_NAME,                       --地址名称
                                    ADD_PID =:ADD_PID,                         --省份编号
                                    LEV =:LEV,                                 --市区编号
                                    REMARK =:REMARK,                           --备注
                                    VERSION =:VERSION where 
                                    ADD_ID =:ADD_ID                            --地址名称";
                #region 参数添加

                OracleParameter[] parameters = new OracleParameter[] { 
                   new OracleParameter(":ADD_NAME",OracleDbType.Varchar2),
                   new OracleParameter(":ADD_PID",OracleDbType.Varchar2),
                   new OracleParameter(":LEV",OracleDbType.Varchar2),
                   new OracleParameter(":REMARK",OracleDbType.Varchar2),
                   new OracleParameter(":VERSION",OracleDbType.Varchar2),
                   new OracleParameter(":ADD_ID",OracleDbType.Varchar2)};

                parameters[0].Value = model.Add_name;
                parameters[1].Value = model.Add_pid;
                parameters[2].Value = model.Lev;
                parameters[3].Value = model.Remark;
                parameters[4].Value = model.Version;
                parameters[5].Value = model.Add_id;

                #endregion

                return SystemDataObject.Instance.ExecuteSqlCommit(sql, parameters);
            }
        }
        #endregion

        #region 删除区域信息
        /// <summary>
        /// 删除区域信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int execAddressAreaDel(string id)
        {
            string sql =@" delete from T_BASE_ADDRESSAREA where ADD_ID=:ADD_ID";

            OracleParameter[] parameters = new OracleParameter[] { 
               new OracleParameter(":ADD_ID",OracleDbType.Varchar2)};
            parameters[0].Value = id;

            return SystemDataObject.Instance.ExecuteSqlCommit(sql, parameters);
        }
        #endregion
    }
}