TG.WXCRM.V4/DAL/TS/WX_TSADDFRIEND_DAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using WX.CRM.Common;

namespace WX.CRM.DAL.TS
{
    public class WX_TSADDFRIEND_DAL
    {
        /// <summary>
        /// 获取可以推送的工作微信号
        /// </summary>
        /// <param name="v_userid">用户ID</param>
        /// <returns></returns>
        public DataTable GetCanSendWorkAlias(decimal v_userid)
        {
            DataTable table = null;
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_userid", OracleType = OracleType.Number, Value = v_userid });
                para.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                table = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.GetCanSendWorkAlias", para.ToArray()).Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
            }
            return table;
        }

        /// <summary>
        /// 获取资源
        /// </summary>
        /// <param name="pici">批次号</param>
        /// <returns></returns>
        public DataTable GetTsAddFriendRes(decimal pici)
        {
            DataTable table = null;
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_pici", OracleType = OracleType.Number, Value = pici });
                para.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                table = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.GetTsSendResByPici", para.ToArray()).Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
            }
            return table;
        }
        /// <summary>
        /// 获取批次信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetTsSendPici()
        {
            DataTable table = null;
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                table = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.GetTsSendPici", para.ToArray()).Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
            }
            return table;
        }

        public bool UpdateSendResStutas(string v_alias, decimal v_pici, DateTime v_senddate, decimal v_stutas, string v_msg)
        {
            bool resut = false;
            try
            {
                var param1 = new List<OracleParameter>()
                {
                      new OracleParameter() { ParameterName = "v_alias",  OracleType = OracleType.VarChar, Value =v_alias },
                      new OracleParameter() { ParameterName = "v_pici",  OracleType = OracleType.Number, Value =v_pici },
                      new OracleParameter() { ParameterName = "v_senddate",  OracleType = OracleType.DateTime, Value =v_senddate },
                      new OracleParameter() { ParameterName = "v_stutas",  OracleType = OracleType.Number, Value =v_stutas },
                      new OracleParameter() { ParameterName = "v_msg",  OracleType = OracleType.VarChar, Value =v_msg }
                };
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.UpdateSendResStutas", param1.ToArray());
                resut = true;
            }
            catch (Exception ex)
            {
                LogHelper.Error("UpdateSendResStutas:" + ex.ToString());
            }
            return resut;
        }

        public bool AddFriendRelation(string v_jobusername, string v_username, string v_resid, DateTime v_createtime)
        {
            bool resut = false;
            try
            {
                var param1 = new List<OracleParameter>()
                {
                      new OracleParameter() { ParameterName = "v_jobusername",  OracleType = OracleType.VarChar, Value =v_jobusername },
                      new OracleParameter() { ParameterName = "v_username",  OracleType = OracleType.VarChar, Value =v_username },
                      new OracleParameter() { ParameterName = "v_resid",  OracleType = OracleType.VarChar, Value =v_resid },
                      new OracleParameter() { ParameterName = "v_createtime",  OracleType = OracleType.DateTime, Value =v_createtime },
                };
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.AddFriendRelation", param1.ToArray());
                resut = true;
            }
            catch (Exception ex)
            {
                LogHelper.Error("AddFriendRelation=》v_jobusername:" + v_jobusername + ",username:" + v_username + ex.ToString());
            }
            return resut;
        }


        /// <summary>
        /// 获取资源by cookie
        /// </summary>
        /// <returns></returns>
        public DataTable GetResByCookiResid(string resids)
        {
            DataTable table = null;
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resids });
                para.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                table = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.GetResByCookiResid", para.ToArray()).Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
            }
            return table;
        }

        /// <summary>
        /// 获取验证的不通过的客户ID
        /// </summary>
        /// <returns></returns>
        public DataTable GetCheckErroRes(string resids)
        {
            DataTable table = null;
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resids });
                para.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                table = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.GetCheckErroRes", para.ToArray()).Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
            }
            return table;
        }
        /// <summary>
        /// 获取最新的触动精灵脚本
        /// </summary>
        /// <returns></returns>
        public DataTable GetNewestScriptVersion()
        {
            DataTable table = null;
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                table = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.GetNewestScriptVersion", para.ToArray()).Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
            }
            return table;
        }
        public bool CreatePici(string v_resid, string v_alias, string v_helloMsg, int v_timeType, string v_exe_date, string v_exe_time, ref ValidationErrors errors)
        {
            bool resut = false;
            try
            {
                var param1 = new List<OracleParameter>()
                {
                      new OracleParameter() { ParameterName = "v_resid",  OracleType = OracleType.VarChar, Value =v_resid },
                      new OracleParameter() { ParameterName = "v_alias",  OracleType = OracleType.VarChar, Value =v_alias },
                      new OracleParameter() { ParameterName = "v_helloMsg",  OracleType = OracleType.VarChar, Value =v_helloMsg },
                      new OracleParameter() { ParameterName = "v_timeType",  OracleType = OracleType.Number, Value =v_timeType },
                      new OracleParameter() { ParameterName = "v_exe_date",  OracleType = OracleType.VarChar, Value =v_exe_date },
                      new OracleParameter() { ParameterName = "v_exe_time",  OracleType = OracleType.VarChar, Value =v_exe_time },
                };
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.CreatePici", param1.ToArray());
                resut = true;
            }
            catch (Exception ex)
            {
                errors.Add(ex.ToString());
                LogHelper.Error("AddFriendRelation:" + ex.ToString());
            }
            return resut;
        }
        /// <summary>
        /// 上传并修 执行状态
        /// </summary>
        /// <param name="v_pici"></param>
        /// <param name="v_isStart"></param>
        /// <param name="v_startTime"></param>
        /// <param name="v_isEnd"></param>
        /// <param name="v_endTime"></param>
        /// <param name="errors"></param>
        /// <returns></returns>
        public bool UpdatePiciExecStatus(long v_pici, int? v_isStart, DateTime? v_startTime, int? v_isEnd, DateTime? v_endTime, string v_erromsg, ref ValidationErrors errors)
        {
            bool resut = false;
            try
            {
                var param1 = new List<OracleParameter>()
                {
                      new OracleParameter() { ParameterName = "v_pici",  OracleType = OracleType.Number, Value =v_pici },
                      new OracleParameter() { ParameterName = "v_isStart",  OracleType = OracleType.Number, Value =v_isStart },
                      new OracleParameter() { ParameterName = "v_startTime",  OracleType = OracleType.DateTime, Value =v_startTime },
                      new OracleParameter() { ParameterName = "v_isEnd",  OracleType = OracleType.Number, Value =v_isEnd },
                      new OracleParameter() { ParameterName = "v_endTime",  OracleType = OracleType.DateTime, Value =v_endTime },
                      string.IsNullOrEmpty(v_erromsg)?new OracleParameter() { ParameterName = "v_erromsg",  OracleType = OracleType.VarChar, Value =DBNull.Value }:
                      new OracleParameter() { ParameterName = "v_erromsg",  OracleType = OracleType.VarChar, Value =v_erromsg }
                };
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.UpdatePiciExecStatus", param1.ToArray());
                resut = true;
            }
            catch (Exception ex)
            {
                errors.Add(ex.ToString());
                LogHelper.Error("UpdatePiciExecStatus:" + ex.ToString());
            }
            return resut;
        }

        public bool AddFriendPiciLog(string v_alias, string v_resid, decimal v_eid, decimal v_pici, decimal v_state, string v_remark, long v_zitime, DateTime v_zdtime)
        {
            var param1 = new List<OracleParameter>()
            {
                new OracleParameter() {ParameterName = "v_alias", OracleType = OracleType.VarChar, Value = v_alias},
                new OracleParameter() {ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = v_resid},
                new OracleParameter() {ParameterName = "v_eid", OracleType = OracleType.Number, Value = v_eid},
                new OracleParameter() {ParameterName = "v_pici", OracleType = OracleType.Number, Value = v_pici},
                new OracleParameter() {ParameterName = "v_state", OracleType = OracleType.Number, Value = v_state},
                string.IsNullOrEmpty(v_remark)? new OracleParameter() {ParameterName = "v_remark", OracleType = OracleType.VarChar, Value = DBNull.Value}: new OracleParameter() {ParameterName = "v_remark", OracleType = OracleType.VarChar, Value = v_remark},
                new OracleParameter() {ParameterName = "v_zitime", OracleType = OracleType.Number, Value = v_zitime},
                new OracleParameter() {ParameterName = "v_zdtime", OracleType = OracleType.DateTime, Value = v_zdtime}
            };
            OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.AddFriendPiciLog", param1.ToArray());
            return true;
        }
        /// <summary>
        /// 白板资源申请
        /// </summary>
        /// <param name="v_alias"></param>
        /// <param name="v_helloMsg"></param>
        /// <param name="v_timeType"></param>
        /// <param name="v_exe_date"></param>
        /// <param name="v_exe_time"></param>
        /// <param name="v_rescount"></param>
        /// <param name="v_eid"></param>
        /// <param name="errors"></param>
        /// <returns></returns>
        public bool WhiteResApply(string v_alias, string v_helloMsg, int v_timeType, string v_exe_date, string v_exe_time, int v_rescount, decimal v_eid, int dept, ref ValidationErrors errors)
        {
            bool resut = false;
            try
            {
                var param1 = new List<OracleParameter>()
                {
                      new OracleParameter() { ParameterName = "v_alias",  OracleType = OracleType.VarChar, Value =v_alias },
                      new OracleParameter() { ParameterName = "v_helloMsg",  OracleType = OracleType.VarChar, Value =v_helloMsg },
                      new OracleParameter() { ParameterName = "v_timeType",  OracleType = OracleType.Number, Value =v_timeType },
                      new OracleParameter() { ParameterName = "v_exe_date",  OracleType = OracleType.VarChar, Value =v_exe_date },
                      new OracleParameter() { ParameterName = "v_exe_time",  OracleType = OracleType.VarChar, Value =v_exe_time },
                      new OracleParameter() { ParameterName = "v_rescount",  OracleType = OracleType.Number, Value =v_rescount },
                      new OracleParameter() { ParameterName = "v_eid",  OracleType = OracleType.Number, Value =v_eid },
                      new OracleParameter(){ ParameterName="v_message",OracleType=OracleType.VarChar,Size=4000,Direction = ParameterDirection.Output},
                      new OracleParameter() { ParameterName = "v_dept",  OracleType = OracleType.Number, Value =dept }
                };
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.WhiteResApply", param1.ToArray());
                string str = param1.Single(m => m.ParameterName == "v_message").Value.ToString();
                LogHelper.Info(str);
                if (!string.IsNullOrEmpty(str))
                {
                    errors.Add(str);
                    resut = false;
                }
                else
                {
                    resut = true;
                }
            }
            catch (Exception ex)
            {
                errors.Add(ex.ToString());
                LogHelper.Error("WhiteResApply:" + ex.ToString());
            }
            return resut;
        }


        /// <summary>
        /// 加人日志查询
        /// </summary>
        /// <param name="v_eid">工号</param>
        /// <param name="v_alias">微信号</param>
        /// <param name="pager">分页信息</param>
        /// <returns></returns>
        public DataTable ResLogReport(Decimal v_eid, string v_alias, ref Pager pager)
        {
            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            try
            {

                OracleParameter[] p2 =
                {
                        new OracleParameter(){ ParameterName = "v_eid", OracleType = OracleType.Number, Value = v_eid},
                        string.IsNullOrEmpty(v_alias)?
                        new OracleParameter(){ ParameterName = "v_alias", OracleType = OracleType.VarChar, Value = DBNull.Value}:
                        new OracleParameter(){ ParameterName = "v_alias", OracleType = OracleType.VarChar, Value = v_alias},
                        new OracleParameter(){ ParameterName = "v_pageIndex",  OracleType = OracleType.Number, Value = pager.page},
                        new OracleParameter(){ ParameterName = "v_pageSize",  OracleType = OracleType.Number, Value = pager.rows},
                        new OracleParameter(){ ParameterName="v_total",OracleType=OracleType.Number,Direction = ParameterDirection.Output},
                        new OracleParameter(){ ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_TSSERVICE.ResLogReport", p2).Tables[0];
                pager.totalRows = int.Parse(string.Format("{0}", p2.Single(m => m.ParameterName == "v_total").Value));
                trans.Commit();
                return ds;
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 加人日志查询
        /// </summary>
        /// <param name="v_eid">工号</param>
        /// <param name="v_alias">微信号</param>
        /// <param name="pager">分页信息</param>
        /// <returns></returns>
        public DataTable ResLogReport_Manager(Decimal v_eid, string v_alias, ref Pager pager)
        {
            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            try
            {

                OracleParameter[] p2 =
                {
                        new OracleParameter(){ ParameterName = "v_eid", OracleType = OracleType.Number, Value = v_eid},
                        string.IsNullOrEmpty(v_alias)?
                        new OracleParameter(){ ParameterName = "v_alias", OracleType = OracleType.VarChar, Value = DBNull.Value}:
                        new OracleParameter(){ ParameterName = "v_alias", OracleType = OracleType.VarChar, Value = v_alias},
                        new OracleParameter(){ ParameterName = "v_pageIndex",  OracleType = OracleType.Number, Value = pager.page},
                        new OracleParameter(){ ParameterName = "v_pageSize",  OracleType = OracleType.Number, Value = pager.rows},
                        new OracleParameter(){ ParameterName="v_total",OracleType=OracleType.Number,Direction = ParameterDirection.Output},
                        new OracleParameter(){ ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_TSSERVICE.ResLogReport_Manager", p2).Tables[0];
                pager.totalRows = int.Parse(string.Format("{0}", p2.Single(m => m.ParameterName == "v_total").Value));
                trans.Commit();
                return ds;
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 加人日志查询
        /// </summary>
        /// <param name="v_eid">工号</param>
        /// <returns></returns>
        public DataTable ResLogReport_Detial(Decimal v_pici)
        {
            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            try
            {

                OracleParameter[] p2 =
                {
                        new OracleParameter(){ ParameterName = "v_pici",  OracleType = OracleType.Number, Value = v_pici},
                        new OracleParameter(){ ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_TSSERVICE.ResLogReport_Detial", p2).Tables[0];
                trans.Commit();
                return ds;
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 是否可以作为推送
        /// </summary>
        /// <param name="v_resid"></param>
        /// <returns></returns>
        public bool IsCanSend(string v_resid)
        {
            bool isOk = true;
            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {

                OracleParameter[] p2 =
                {
                        new OracleParameter(){ ParameterName = "v_resid",  OracleType = OracleType.VarChar, Value = v_resid},
                        new OracleParameter(){ ParameterName ="v_isok",OracleType=OracleType.Number,Direction = ParameterDirection.Output},
                };
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_TSADDFRIEND.IsCanSend", p2);
                int orderCount = int.Parse(string.Format("{0}", p2.Single(m => m.ParameterName == "v_isok").Value));
                if (orderCount > 0)
                    isOk = false;
            }
            catch (Exception e)
            {
                LogHelper.Error(e);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return isOk;
        }
    }
}