using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using WMS.Model.Base;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using System.Data;
using WMS.IData.IBase;

namespace WMS.SqlServerData.BaseData
{
    public class UserAreaData : IUserArea
    {

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        //public bool Exists(string USER_ID, string AREA_ID, string WORKKIND_ID, decimal WORKLOAD)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select count(1) from T_BASE_USERAREA");
        //    strSql.Append(" where USER_ID=:USER_ID and AREA_ID=:AREA_ID and WORKKIND_ID=:WORKKIND_ID and WORKLOAD=:WORKLOAD ");
        //    OracleParameter[] parameters = {
        //           new OracleParameter(":USER_ID",OracleDbType.Varchar2,30),
        //           new OracleParameter(":AREA_ID",OracleDbType.Varchar2,30),
        //           new OracleParameter(":WORKKIND_ID",OracleDbType.Varchar2,30),
        //           new OracleParameter(":WORKLOAD", OracleDbType.Decimal,4)			};
        //    parameters[0].Value = USER_ID;
        //    parameters[1].Value = AREA_ID;
        //    parameters[2].Value = WORKKIND_ID;
        //    parameters[3].Value = WORKLOAD;

        //    return DbHelperOra.Exists(strSql.ToString(), parameters);
        //}

        /// <summary>
        /// 通过工种编号获得隶属该工种的人员
        /// </summary>
        /// <param name="mo"></param>
        /// <returns></returns>
        public DataTable GetWorkKindUserData(UserAreaModel mo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select distinct t.USer_ID,t.workkind_id,t.workload, u.name USER_NAME
                              from T_BASE_USERAREA t
                              left join T_BASE_USERINFO u
                                on t.user_id = u.user_id
                             where workkind_id = '" + mo.WORKKIND_ID + "'");
            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void AddUserAreaData(UserAreaModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_BASE_USERAREA(");
            strSql.Append("USER_ID,AREA_ID,WORKKIND_ID,WORKLOAD,STORAGE_ID)");
            strSql.Append(" values (");
            strSql.Append(":USER_ID,:AREA_ID,:WORKKIND_ID,:WORKLOAD,:STORAGE_ID)");
            OracleParameter[] parameters = {
					new OracleParameter(":USER_ID",OracleDbType.Varchar2,30),
					new OracleParameter(":AREA_ID",OracleDbType.Varchar2,30),
					new OracleParameter(":WORKKIND_ID",OracleDbType.Varchar2,30),
					new OracleParameter(":WORKLOAD", OracleDbType.Decimal,4),
                                          new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2)};
            parameters[0].Value = model.USER_ID;
            parameters[1].Value = model.AREA_ID;
            parameters[2].Value = model.WORKKIND_ID;
            parameters[3].Value = model.WORKLOAD;
            parameters[4].Value = model.STORAGE_ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void UpdateUserAreaData(UserAreaModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_BASE_USERAREA set ");
            strSql.Append("USER_ID=:USER_ID,");
            strSql.Append("AREA_ID=:AREA_ID,");
            strSql.Append("WORKKIND_ID=:WORKKIND_ID,");
            strSql.Append("STORAGE_ID=:STORAGE_ID,");
            strSql.Append("WORKLOAD=:WORKLOAD");
            strSql.Append(" where USER_ID=:USER_ID and AREA_ID=:AREA_ID and WORKKIND_ID=:WORKKIND_ID");
            OracleParameter[] parameters = {
					new OracleParameter(":USER_ID",OracleDbType.Varchar2,30),
					new OracleParameter(":AREA_ID",OracleDbType.Varchar2,30),
					new OracleParameter(":WORKKIND_ID",OracleDbType.Varchar2,30),
                   new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
                                           new OracleParameter(":WORKLOAD",OracleDbType.Varchar2),
                                           new OracleParameter(":USER_ID",OracleDbType.Varchar2),
                                           new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
                                           new OracleParameter(":WORKKIND_ID",OracleDbType.Varchar2)};
            parameters[0].Value = model.USER_ID;
            parameters[1].Value = model.AREA_ID;
            parameters[2].Value = model.WORKKIND_ID;
            parameters[3].Value = model.STORAGE_ID;
            parameters[4].Value = model.WORKLOAD;
            parameters[5].Value = model.USER_ID;
            parameters[6].Value = model.AREA_ID;
            parameters[7].Value = model.WORKKIND_ID;
            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void DeleteUserAreaData(UserAreaModel model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_BASE_USERAREA ");
            strSql.Append(" where USER_ID=:USER_ID");
            OracleParameter[] parameters = {
					new OracleParameter(":USER_ID",OracleDbType.Varchar2,30)};
            parameters[0].Value = model.USER_ID;
            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetUserAreaData(UserAreaModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct t.USER_ID,t.AREA_ID,t.WORKKIND_ID,t.WORKLOAD,t.STORAGE_ID,s.STORAGE_NAME ");
            strSql.Append(" FROM T_BASE_USERAREA t left join t_Base_Storageinfo s on t.STORAGE_ID=s.STORAGE_ID where 1 =1");
            if (model.USER_ID.Trim() != "")
            {
                strSql.Append(" and USER_ID='" + model.USER_ID + "'");
            }
            if (!string.IsNullOrEmpty(model.WORKKIND_ID.ToString()))
            {
                strSql.Append(" and WORKKIND_ID='" + model.WORKKIND_ID + "'");
            }
            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }
    }
}