using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMS.IData.IBase;
using WMS.Model.Base;
using System.Data;
using WMS.Common;
using WMS.IData;using Oracle.ManagedDataAccess.Client;

using WMS.DBUtility;

namespace WMS.SqlServerData.BaseData
{
    /// <summary>
    /// 数据访问类:T_BASE_MESSAGE
    /// </summary>
    public partial class MessageData : IMessage
    {
        #region  BasicMethod
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(MessageModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_BASE_MESSAGE(");
            strSql.Append("ID,MESS_TYPE,MESS_STAR,MESS_TO,MESS_CONTENT,MESS_DATE,MESS_ENDDATE,MESS_STATUS,MESS_CHANGE,MESS_CHANGE_DATE,MESS_DEP,MESS_FLAG,AREA_ID,STO_ID,MENU_FORM,WORK_TYPE,WORK_KIND,RELATIVE_NUMBER)");
            strSql.Append(" values (");
            strSql.Append(":ID,:MESS_TYPE,:MESS_STAR,:MESS_TO,:MESS_CONTENT,:MESS_DATE,:MESS_ENDDATE,:MESS_STATUS,:MESS_CHANGE,:MESS_CHANGE_DATE,:MESS_DEP,:MESS_FLAG,:AREA_ID,:STO_ID,:MENU_FORM,:WORK_TYPE,:WORK_KIND,:RELATIVE_NUMBER)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID",OracleDbType.Varchar2),
					new OracleParameter(":MESS_TYPE",OracleDbType.Varchar2),
					new OracleParameter(":MESS_STAR",OracleDbType.Varchar2),
					new OracleParameter(":MESS_TO",OracleDbType.Varchar2),
					new OracleParameter(":MESS_CONTENT",OracleDbType.Varchar2),
					new OracleParameter(":MESS_DATE", OracleDbType.Date),
					new OracleParameter(":MESS_ENDDATE", OracleDbType.Date),
					new OracleParameter(":MESS_STATUS",OracleDbType.Varchar2),
					new OracleParameter(":MESS_CHANGE",OracleDbType.Varchar2),
					new OracleParameter(":MESS_CHANGE_DATE", OracleDbType.Date),
					new OracleParameter(":MESS_DEP",OracleDbType.Varchar2),
					new OracleParameter(":MESS_FLAG",OracleDbType.Varchar2),
                   new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":STO_ID",OracleDbType.Varchar2),
                   new OracleParameter(":MENU_FORM",OracleDbType.Varchar2),
                   new OracleParameter(":WORK_TYPE",OracleDbType.Varchar2),
                   new OracleParameter(":WORK_KIND",OracleDbType.Varchar2),
                   new OracleParameter(":RELATIVE_NUMBER",OracleDbType.Varchar2)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.MESS_TYPE;
            parameters[2].Value = model.MESS_STAR;
            parameters[3].Value = model.MESS_TO;
            parameters[4].Value = model.MESS_CONTENT;
            parameters[5].Value = model.MESS_DATE;
            parameters[6].Value = model.MESS_ENDDATE;
            parameters[7].Value = model.MESS_STATUS;
            parameters[8].Value = model.MESS_CHANGE;
            parameters[9].Value = model.MESS_CHANGE_DATE;
            parameters[10].Value = model.MESS_DEP;
            parameters[11].Value = model.MESS_FLAG;
            parameters[12].Value = model.AREA_ID;
            parameters[13].Value = model.STO_ID;
            parameters[14].Value = model.MENU_FORM;
            parameters[15].Value = model.WORK_TYPE;
            parameters[16].Value = model.WORK_KIND;
            parameters[17].Value = model.RELATIVE_NUMBER;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(MessageModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_BASE_MESSAGE set ");
            strSql.Append("MESS_TYPE=:MESS_TYPE,");
            strSql.Append("MESS_STAR=:MESS_STAR,");
            strSql.Append("MESS_TO=:MESS_TO,");
            strSql.Append("MESS_CONTENT=:MESS_CONTENT,");
            strSql.Append("MESS_DATE=:MESS_DATE,");
            strSql.Append("MESS_ENDDATE=:MESS_ENDDATE,");
            strSql.Append("MESS_STATUS=:MESS_STATUS,");
            strSql.Append("MESS_CHANGE=:MESS_CHANGE,");
            strSql.Append("MESS_CHANGE_DATE=:MESS_CHANGE_DATE,");
            strSql.Append("MESS_DEP=:MESS_DEP,");
            strSql.Append("MESS_FLAG=:MESS_FLAG,");
            strSql.Append("AREA_ID=:AREA_ID,");
            strSql.Append("STO_ID=:STO_ID,");
            strSql.Append("MENU_FORM=:MENU_FORM,");
            strSql.Append("WORK_TYPE=:WORK_TYPE,");
            strSql.Append("WORK_KIND=:WORK_KIND");
            strSql.Append(" where ");
            strSql.Append("ID=:ID,");
            OracleParameter[] parameters = {
					new OracleParameter(":MESS_TYPE",OracleDbType.Varchar2),
					new OracleParameter(":MESS_STAR",OracleDbType.Varchar2),
					new OracleParameter(":MESS_TO",OracleDbType.Varchar2),
					new OracleParameter(":MESS_CONTENT",OracleDbType.Varchar2),
					new OracleParameter(":MESS_DATE", OracleDbType.Date),
					new OracleParameter(":MESS_ENDDATE", OracleDbType.Date),
					new OracleParameter(":MESS_STATUS",OracleDbType.Varchar2),
					new OracleParameter(":MESS_CHANGE",OracleDbType.Varchar2),
					new OracleParameter(":MESS_CHANGE_DATE", OracleDbType.Date),
					new OracleParameter(":MESS_DEP",OracleDbType.Varchar2),
					new OracleParameter(":MESS_FLAG",OracleDbType.Varchar2),
                   new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
					new OracleParameter(":STO_ID",OracleDbType.Varchar2),
                   new OracleParameter(":MENU_FORM",OracleDbType.Varchar2),
                   new OracleParameter(":WORK_TYPE",OracleDbType.Varchar2),
                   new OracleParameter(":WORK_KIND",OracleDbType.Varchar2),
                   new OracleParameter(":ID",OracleDbType.Varchar2),};

            parameters[0].Value = model.MESS_TYPE;
            parameters[1].Value = model.MESS_STAR;
            parameters[2].Value = model.MESS_TO;
            parameters[3].Value = model.MESS_CONTENT;
            parameters[4].Value = model.MESS_DATE;
            parameters[5].Value = model.MESS_ENDDATE;
            parameters[6].Value = model.MESS_STATUS;
            parameters[7].Value = model.MESS_CHANGE;
            parameters[8].Value = model.MESS_CHANGE_DATE;
            parameters[9].Value = model.MESS_DEP;
            parameters[10].Value = model.MESS_FLAG;
            parameters[11].Value = model.AREA_ID;
            parameters[12].Value = model.STO_ID;
            parameters[13].Value = model.MENU_FORM;
            parameters[14].Value = model.WORK_TYPE;
            parameters[15].Value = model.WORK_KIND;
            parameters[16].Value = model.ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(MessageModel model)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_BASE_MESSAGE ");
            strSql.Append(" where ID=:ID ");
            strSql.Append(" and   MESS_DATE between  convert(varchar(100),'" + DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd") + "',23) and  convert(varchar(100),'" + DateTime.Now.AddDays(-2).ToString("yyyy-MM-dd") + "','23')");

            OracleParameter[] parameters = {
                     new OracleParameter(":ID",OracleDbType.Varchar2),
                     new OracleParameter(":MESS_DATE",OracleDbType.Date)
			};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.MESS_DATE;
            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList(MessageModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.ID,");
            strSql.Append("t.MESS_TYPE,");
            strSql.Append("t.MESS_STAR,");
            strSql.Append("t.MESS_TO,");
            strSql.Append("t.MESS_CONTENT,");
            strSql.Append("t.MESS_DATE,");
            strSql.Append("t.MESS_ENDDATE,");
            strSql.Append("t.MESS_STATUS,");
            strSql.Append("t.MESS_CHANGE,");
            strSql.Append("t.MESS_CHANGE_DATE,");
            strSql.Append("t.MESS_DEP,");
            strSql.Append("t.MESS_FLAG,");
            strSql.Append("t.AREA_ID,");
            strSql.Append("g.STORAGE_AREA_ID STORAGE_AREA_NAME,");
            strSql.Append("t.STO_ID,");
            strSql.Append("t.MENU_FORM,");
            strSql.Append("t.WORK_TYPE,");
            strSql.Append("a.WORK_NAME WORK_TYPE_NAME,");
            strSql.Append("t.WORK_KIND,");
            strSql.Append("b.vc_dictionary_name WORK_KIND_NAME,");
            strSql.Append("f.STORAGE_ID STORAGE_NAME");
            strSql.Append(" FROM T_BASE_MESSAGE t");
            strSql.Append(" left join t_base_storageinfo f on t.STO_ID = f.storage_id");
            strSql.Append(" left join t_base_storage_area g on t.AREA_ID = g.STORAGE_AREA_ID");
            strSql.Append(" left join T_BASE_WORKTYPE a on t.WORK_TYPE = a.WORK_ID");
            strSql.Append(" left join t_sys_dictionary_tab b on t.WORK_KIND = b.VC_CHAR_VALUE and vc_dictionary_id = 'workkind_type'");
            strSql.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(model.ID))
            {
                strSql.Append(" and ID='" + model.ID + "'");
            }
            strSql.Append(" order by t.MESS_DATE desc");
            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }
        #endregion  BasicMethod



        /// <summary>
        /// 通过当前登录人 获得这个人能查看的信息
        /// </summary>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public DataTable GetMessageDataByUser(string user_id)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("select t.ID,");
            //strSql.Append("t.MESS_TYPE,");
            //strSql.Append("t.MESS_STAR,");
            //strSql.Append("t.MESS_TO,");
            //strSql.Append("t.MESS_CONTENT,");
            //strSql.Append("t.MESS_DATE,");
            //strSql.Append("t.MESS_ENDDATE,");
            //strSql.Append("t.MESS_STATUS,");
            //strSql.Append("t.MESS_CHANGE,");
            //strSql.Append("t.MESS_CHANGE_DATE,");
            //strSql.Append("t.MESS_DEP,");
            //strSql.Append("t.MESS_FLAG,");
            //strSql.Append("t.AREA_ID,");
            //strSql.Append("g.STORAGE_AREA_ID STORAGE_AREA_NAME,");
            //strSql.Append("t.STO_ID,");
            //strSql.Append("t.MENU_FORM,");
            //strSql.Append("t.WORK_TYPE,");
            //strSql.Append("a.WORK_NAME WORK_TYPE_NAME,");
            //strSql.Append("t.WORK_KIND,");
            //strSql.Append("b.vc_dictionary_name WORK_KIND_NAME,");
            //strSql.Append("f.STORAGE_ID STORAGE_NAME");
            //strSql.Append(" FROM T_BASE_MESSAGE t");
            //strSql.Append(" left join t_base_storageinfo f on t.STO_ID = f.storage_id");
            //strSql.Append(" left join t_base_storage_area g on t.AREA_ID = g.STORAGE_AREA_ID");
            //strSql.Append(" left join T_BASE_WORKTYPE a on t.WORK_TYPE = a.WORK_ID");
            //strSql.Append(" left join t_sys_dictionary_tab b on t.WORK_KIND = b.VC_CHAR_VALUE and vc_dictionary_id = 'workkind_type'");
            //strSql.Append(" where 1=1 ");
            //strSql.Append(" and t.work_kind in ( select workkind_ID  from T_BASE_USERAREA where user_id ='" + user_id + "' and T_BASE_USERAREA.WORKKIND_ID in (select work_KIND from T_BASE_MESSAGE)) ");
            //strSql.Append(" and t.work_type in(select S_TYPE  from T_SYS_SCHEDULING where S_USER ='" + user_id + "' and T_SYS_SCHEDULING.S_TYPE in (select WORK_TYPE from T_BASE_MESSAGE)) ");
            //strSql.Append(" or t.mess_star='" + user_id + "'");

            strSql.Append(@"           select *
             from T_BASE_MESSAGE
            where work_kind in
                  (select distinct workkind_id
                     from T_BASE_USERAREA
                    where user_id = '" + user_id +@"'
                      and workkind_id in
                          (select work_kind from T_BASE_MESSAGE))
              and work_type in
                  (select work_type
                     from t_Base_Worktypecontack
                    where work_kind in (select distinct workkind_id
                                          from T_BASE_USERAREA
                                         where user_id = '" + user_id + "'))");


            return SystemDataObject.Instance.GetDataTable(strSql.ToString());

        }

        /// <summary>
        /// 通过当前登录人查询消息表中的消息传出人
        /// </summary>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public DataTable GetMessageDataByUserStar(UserDataModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select id,
                               mess_type,
                               mess_star,
                               mess_to,
                               mess_content,
                               mess_date,
                               mess_enddate,
                               mess_status,
                               mess_change,
                               mess_change_date,
                               mess_dep,
                               mess_flag,
                               mess_form,
                               sto_id,
                               area_id,
                               work_type,
                               work_kind
                          from t_base_message
                         where 1 = 1 ");
            strSql.Append(" and mess_star = '" + model.USER_ID + "'");

            return SystemDataObject.Instance.GetDataTable(strSql.ToString());

        }

        /// <summary>
        /// 更改消息状态
        /// </summary>
        public void UpdateMessageStatus(MessageModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update T_BASE_MESSAGE set ");
            strSql.Append("MESS_CONTENT=:MESS_CONTENT,");
            strSql.Append("MESS_STATUS=:MESS_STATUS,");
            strSql.Append("MENU_FORM=:MENU_FORM");
            strSql.Append(" where ");
            strSql.Append("MESS_CONTENT=:MESS_CONTENT ");
            strSql.Append("and MESS_STATUS=:MESS_STATUS ");
            strSql.Append("and MENU_FORM=:MENU_FORM ");
            OracleParameter[] parameters = {
					new OracleParameter(":MESS_CONTENT",OracleDbType.Varchar2),
					new OracleParameter(":MESS_STATUS",OracleDbType.Varchar2),
                   new OracleParameter(":MENU_FORM",OracleDbType.Varchar2),
                   new OracleParameter(":MESS_CONTENT",OracleDbType.Varchar2),
					new OracleParameter(":MESS_STATUS",OracleDbType.Varchar2),
                   new OracleParameter(":MENU_FORM",OracleDbType.Varchar2)};

            parameters[0].Value = model.MESS_CONTENT;
            parameters[1].Value = '1';
            parameters[2].Value = model.MENU_FORM;
            parameters[3].Value = model.MESS_CONTENT;
            parameters[4].Value = model.MESS_STATUS;
            parameters[5].Value = model.MENU_FORM;


            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据时间删除 2天前的数据
        /// </summary>
        public void DeleteByDateTime()
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_BASE_MESSAGE ");
            strSql.Append(" where 1 =1  ");
            strSql.Append(" and   MESS_DATE between convert(varchar(100),'" + DateTime.Now.AddDays(-20).ToString("yyyy-MM-dd") + "',23) and convert(varchar(100),'" + DateTime.Now.AddDays(-2).ToString("yyyy-MM-dd") + "',23)");

            SystemDataObject.Instance.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 获取流水号
        /// </summary>
        /// <returns></returns>
        public string GetSerialNum()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select NEXT VALUE FOR SEQ_BASE_MESSAGE");
            DataSet ds = SystemDataObject.Instance.GetDataSet(strSql.ToString());
            return ds.Tables[0].Rows[0][0].ToString();
        }


        /// <summary>
        /// 根据编号删除相应的消息
        /// </summary>
        /// <param name="strID"></param>
        public void DeleteMessByRelativeNumber(string strID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_BASE_MESSAGE ");
            strSql.AppendLine(" where RELATIVE_NUMBER=:RELATIVE_NUMBER ");
            OracleParameter[] parameterxs = {
					new OracleParameter(":RELATIVE_NUMBER",OracleDbType.Varchar2)			};
            parameterxs[0].Value = strID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameterxs); 
        }

    }
}