using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using WX.CRM.Common;
using WX.CRM.Model.Entity;
using WX.CRM.Model.MAP;

namespace WX.CRM.DAL.Wx
{
    public class WX_RCONTACT_DAL
    {
        public DataTable CountRcontactByWxJobUserName()
        {
            try
            {
                var sql = "select a.jobwxusername,b.alias,b.nickname,a.countnum from (select jobwxusername,count(1) as countnum from wx_rcontact where  type in(1,3,5,7,257,259,67,65539) or type>11 or type is null group by jobwxusername)  a left join wx_workaccount b on a.jobwxusername = b.username order by a.countnum desc";
                var param = new List<OracleParameter>();
                DataSet dest = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
                return dest.Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }
        public void UpdateRcontactResId(string username, string resid, decimal inneruserid)
        {
            try
            {
                var sql = "update wx_rcontact t  set t.resid =:resid,bindrestime=:ctime where t.username=:username and t.jobwxusername in(select username from wx_workaccount x where x.inneruserid=:innseruserid)";
                var param = new List<OracleParameter>();
                param.Add(new OracleParameter() { ParameterName = ":resid", OracleType = OracleType.VarChar, Value = resid });
                param.Add(new OracleParameter() { ParameterName = ":ctime", OracleType = OracleType.DateTime, Value = DateTime.Now });
                param.Add(new OracleParameter() { ParameterName = ":username", OracleType = OracleType.VarChar, Value = username });
                param.Add(new OracleParameter() { ParameterName = ":innseruserid", OracleType = OracleType.Number, Value = inneruserid });
                OracleHelper.ExecuteNonQuery(CommandType.Text, sql, param.ToArray());
            }
            catch { throw; }
        }
        public DataTable GetRcontactList(string resid)
        {
            try
            {
                var param = new List<OracleParameter>();
                param.Add(new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resid });
                param.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_RCONTACT.GetRcontactByResId", param.ToArray());
                return dest.Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }
        /// <summary>
        /// 导出微信号
        /// </summary>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <param name="inneruserid"></param>
        /// <returns></returns>

        public DataSet ExportWeiXinResource(DateTime beginTime, DateTime endTime, decimal? inneruserid)
        {

            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (OracleTransaction trans = conn.BeginTransaction())
                {
                    OracleParameter[] p = new[] {

                    new OracleParameter() { ParameterName = "p_beginTime", OracleType = OracleType.DateTime, Value = beginTime },
                    new OracleParameter() { ParameterName = "p_endTime", OracleType = OracleType.DateTime, Value = endTime },
                    inneruserid.HasValue?new OracleParameter() { ParameterName = "p_userid", OracleType = OracleType.Number, Value = inneruserid }:
                    new OracleParameter() { ParameterName = "p_userid", OracleType = OracleType.Number, Value = DBNull.Value },
                    new OracleParameter() { ParameterName = "p_tuiguangdata", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
                    new OracleParameter() { ParameterName = "p_nottuiguangdata", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
                    new OracleParameter() { ParameterName = "p_rcontactdata", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                };
                    var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.RcontactExport", p);
                    trans.Commit();
                    return ds;
                }
            }
            catch { throw; }
            finally { conn.Close(); }
        }
        public DataTable GetChatUserByResId(string resid)
        {
            try
            {
                var param = new List<OracleParameter>();
                param.Add(new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resid });
                param.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_RCONTACT.GetChatUserByResId", param.ToArray());
                return dest.Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }

        public DataTable GetChatQwByResId(string resid)
        {
            try
            {
                var param = new List<OracleParameter>();
                param.Add(new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resid });
                param.Add(new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output });
                DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_RCONTACT.GetChatQwByResId", param.ToArray());
                return dest.Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }

        public DataTable GetChatUser(string resid, string username, string alias, string nickname, string conremark, string jobusername, string workalias, decimal? eid)
        {
            try
            {
                var param = new List<OracleParameter>
                {
                    string.IsNullOrEmpty(resid) ? new OracleParameter() {ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resid},
                    string.IsNullOrEmpty(username) ? new OracleParameter() {ParameterName = "v_username", OracleType = OracleType.VarChar, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_username", OracleType = OracleType.VarChar, Value = username},
                    string.IsNullOrEmpty(alias) ? new OracleParameter() {ParameterName = "v_alias", OracleType = OracleType.VarChar, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_alias", OracleType = OracleType.VarChar, Value = alias},
                    string.IsNullOrEmpty(nickname) ? new OracleParameter() {ParameterName = "v_nickname", OracleType = OracleType.NVarChar, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_nickname", OracleType = OracleType.NVarChar, Value = nickname},
                    string.IsNullOrEmpty(conremark) ? new OracleParameter() {ParameterName = "v_conremark", OracleType = OracleType.NVarChar, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_conremark", OracleType = OracleType.NVarChar, Value = conremark},
                    string.IsNullOrEmpty(jobusername) ? new OracleParameter() {ParameterName = "v_jobusername", OracleType = OracleType.VarChar, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_jobusername", OracleType = OracleType.VarChar, Value = jobusername},
                    string.IsNullOrEmpty(workalias) ? new OracleParameter() {ParameterName = "v_workalias", OracleType = OracleType.VarChar, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_workalias", OracleType = OracleType.VarChar, Value = workalias},
                    eid.HasValue ? new OracleParameter() {ParameterName = "v_eid", OracleType = OracleType.Number, Value = DBNull.Value} : new OracleParameter() {ParameterName = "v_eid", OracleType = OracleType.Number, Value = DBNull.Value},
                    new OracleParameter() {ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output}
                };
                DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_WX_RCONTACT.GetChatUser", param.ToArray());
                return dest.Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }
        public DataTable CountRcontactByInnerUserId()
        {
            try
            {
                var sql = "select c.inneruserid,d.eid,d.truename,c.countnum from (select b.inneruserid,count(1) as countnum from wx_rcontact a left join wx_workaccount b on a.jobwxusername = b.username  group by b.inneruserid ) c left join bas_inneruser d on c.inneruserid = d.pkid order by c.countnum desc";
                var param = new List<OracleParameter>();
                DataSet dest = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
                return dest.Tables[0];
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }

        public DataTable CountRcontactByUserGroup()
        {
            try
            {
                var sql = "select d.gid,e.gname,d.countnum from  (select c.gid,count(1) as countnum  from wx_friendsrelation a left join wx_workaccount b on a.jobusername = b.username left join bas_innerusergroup c on b.inneruserid = c.inneruserid where a.createtime>to_date('2016-12-1 00:00:01','yyyy-mm-dd hh24:mi:ss') and a.isinneruser=0  group by c.gid) d left join bas_innergroup e on d.gid=e.gid order by d.countnum desc";
                var param = new List<OracleParameter>();
                DataSet ds = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0];
                }
                return new DataTable();
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }

        public DataTable CountSaleUserCommission()
        {
            try
            {
                var sql = " select (case when t2.saleuserid is null then t1.inneruserid else t2.saleuserid end) as saleuserid," +
               " sum(1 * ((case when t2.commissionratio is null then 100 else t2.commissionratio end)/ 100)) as totalcommission " +
          " from wx_szzyorder t1 left join wx_commissionrule t2 on t1.orderid = t2.orderid" +
          " where t1.isopen = 1 " +
          " group by (case when t2.saleuserid is null then t1.inneruserid else t2.saleuserid end) ";
                var param = new List<OracleParameter>();
                DataSet ds = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0];
                }
                return new DataTable();
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }

        public DataTable CountSaleGroupCommission()
        {
            try
            {
                var sql = "select sum(1 * ((case when t2.commissionratio is null then 100 else t2.commissionratio end)/ 100)) as totalcommission, " +
               " (case " +
               "      when t5.oldgroupid is not null and t1.arrivaltime >= to_date('2016-1-1', 'yyyy-mm-dd') and t1.arrivaltime < t5.indate then t5.oldgroupid " +
               "       when t5.oldgroupid is not null and t1.arrivaltime >= t5.indate and t1.arrivaltime < t5.outdate then t5.newgroupid " +
               "      else (case when t3.gid is null then 0 else t3.gid end) " +
               "     end) gid " +
               " from wx_szzyorder t1 " +
               " left join wx_commissionrule t2 on t1.orderid = t2.orderid " +
               " left join bas_innerusergroup t3 on(case when t2.saleuserid is null then t1.inneruserid else t2.saleuserid end) = t3.inneruserid " +
               " left join (select * from bas_usergroupchangelog l where l.indate >= to_date('2016-1-1', 'yyyy-mm-dd') and l.oldgroupid is not null) t5 on t3.inneruserid = t5.salesid " +
               " where t1.isopen = 1 " +
               " group by (case " +
               "      when t5.oldgroupid is not null and t1.arrivaltime >= to_date('2016-1-1', 'yyyy-mm-dd') and t1.arrivaltime < t5.indate then t5.oldgroupid " +
               "      when t5.oldgroupid is not null and t1.arrivaltime >= t5.indate and t1.arrivaltime < t5.outdate then t5.newgroupid  " +
               "      else (case when t3.gid is null then 0 else t3.gid end) " +
               "     end)";
                var param = new List<OracleParameter>();
                DataSet ds = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0];
                }
                return new DataTable();
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }

        /// <summary>
        /// 推送数据到推广
        /// </summary>
        /// <param name="v_username">用户名</param>
        /// <param name="v_jobusername">工作微信username</param>
        /// <returns>返回PICI</returns>
        public decimal pushRcontactToTG(string v_username, string v_jobusername)
        {
            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            decimal pici = 0;
            DataSet ds = null;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (OracleTransaction trans = conn.BeginTransaction())
                {
                    OracleParameter[] p =
                {
                    new OracleParameter("v_username",OracleType.VarChar),
                    new OracleParameter("v_jobusername",OracleType.VarChar),
                    new OracleParameter(){ParameterName="v_data",OracleType=OracleType.Cursor,Direction = ParameterDirection.Output},
                };
                    p[0].Value = v_username;
                    p[1].Value = v_jobusername;
                    ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX.pushRcontactToTG", p);
                    trans.Commit();
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                        pici = Convert.ToDecimal(ds.Tables[0].Rows[0]["pici"]);
                }
            }
            catch (Exception ex) { LogHelper.Error(ex); }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

            return pici;
        }

        /// <summary>
        /// 从message表中提取好友关系被加数据
        /// </summary>
        /// <param name="v_username">好友username</param>
        /// <param name="v_jobusername">工作微信</param>
        /// <param name="v_msgSvrId">msgSvrId</param>
        /// <param name="v_content">内容</param>
        /// <param name="v_createTime">时间</param>
        /// <returns></returns>
        public decimal extractToBeFriendsDataFromMsg(decimal type, string v_username, string v_jobusername, DateTime v_createTime, ref bool ret)
        {
            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            decimal pici = 0;
            DataSet ds = null;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (OracleTransaction trans = conn.BeginTransaction())
                {
                    OracleParameter[] p =
                    {
                        new OracleParameter("v_type", OracleType.Number),
                        new OracleParameter("v_username", OracleType.VarChar),
                        new OracleParameter("v_jobusername", OracleType.VarChar),
                        new OracleParameter("v_createTime", OracleType.DateTime),
                        new OracleParameter() {ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output},
                    };
                    p[0].Value = type;
                    p[1].Value = v_username;
                    p[2].Value = v_jobusername;
                    p[3].Value = v_createTime;
                    ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WXMSG.extractToBeFriendsDataFromMsg2", p);
                    trans.Commit();
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                        pici = Convert.ToDecimal(ds.Tables[0].Rows[0]["pici"]);
                }
            }
            catch (Exception ex)
            {
                ret = false;
                LogHelper.Error(ex.ToString());
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return pici;
        }

        public DataTable RcontactByDayReport(DateTime? beginTime, DateTime? endTime, decimal? type)
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                        type.HasValue ? new OracleParameter() { ParameterName = "p_type", OracleType = OracleType.Number, Value = type.Value } : new OracleParameter() { ParameterName = "p_type", OracleType = OracleType.Number, Value = 0 },
                        beginTime.HasValue ? new OracleParameter() { ParameterName = "p_beginTime", OracleType = OracleType.DateTime, Value = beginTime.Value } : new OracleParameter() { ParameterName = "p_beginTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
                        endTime.HasValue ? new OracleParameter() { ParameterName = "p_endTime", OracleType = OracleType.DateTime, Value = endTime.Value } : new OracleParameter() { ParameterName = "p_endTime", OracleType = OracleType.DateTime, Value = DBNull.Value },
                        new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.RcontactByDay", parms.ToArray());
                    trans.Commit();
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet GetWebChatResourceList(DateTime? beginTime, DateTime? endTime, decimal? type)
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                        type.HasValue ? new OracleParameter() { ParameterName = "v_type", OracleType = OracleType.Number, Value = type.Value } : new OracleParameter() { ParameterName = "v_type", OracleType = OracleType.Number, Value = 0 },
                        beginTime.HasValue ? new OracleParameter() { ParameterName = "v_stime", OracleType = OracleType.DateTime, Value = beginTime.Value } : new OracleParameter() { ParameterName = "v_stime", OracleType = OracleType.DateTime, Value = DBNull.Value },
                        endTime.HasValue ? new OracleParameter() { ParameterName = "v_etime", OracleType = OracleType.DateTime, Value = endTime.Value } : new OracleParameter() { ParameterName = "v_etime", OracleType = OracleType.DateTime, Value = DBNull.Value },
                        new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.GetWebChatResourceList", parms.ToArray());
                    trans.Commit();
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet GetWebChatResourceDetail(DateTime? beginTime, DateTime? endTime, string jobUsername)
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                        beginTime.HasValue ? new OracleParameter() { ParameterName = "v_stime", OracleType = OracleType.DateTime, Value = beginTime.Value } : new OracleParameter() { ParameterName = "v_stime", OracleType = OracleType.DateTime, Value = DBNull.Value },
                        endTime.HasValue ? new OracleParameter() { ParameterName = "v_etime", OracleType = OracleType.DateTime, Value = endTime.Value } : new OracleParameter() { ParameterName = "v_etime", OracleType = OracleType.DateTime, Value = DBNull.Value },
                        new OracleParameter() { ParameterName = "v_jobusername", OracleType = OracleType.VarChar, Value=jobUsername },
                        new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.GetWebChatResourceDetail", parms.ToArray());
                    trans.Commit();
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable RcontactByUserAndDay(DateTime beginTime, DateTime endTime, decimal? type)
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                       type.HasValue ? new OracleParameter() { ParameterName = "p_type", OracleType = OracleType.Number, Value = type.Value } : new OracleParameter() { ParameterName = "p_type", OracleType = OracleType.Number, Value = 0 },
                       new OracleParameter() { ParameterName = "p_beginTime", OracleType = OracleType.DateTime, Value = beginTime },
                       new OracleParameter() { ParameterName = "p_endTime", OracleType = OracleType.DateTime, Value = endTime },
                       new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.RcontactByUserAndDay", parms.ToArray());
                    trans.Commit();
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        public DataTable RcontactByTag(DateTime beginTime, DateTime endTime)
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                       new OracleParameter() { ParameterName = "p_beginTime", OracleType = OracleType.DateTime, Value = beginTime },
                       new OracleParameter() { ParameterName = "p_endTime", OracleType = OracleType.DateTime, Value = endTime },
                       new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.RcontactByTag", parms.ToArray());
                    trans.Commit();
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        public int ResourceByTagDistinctCount(DateTime beginTime, string tag)
        {
            int count = 0;
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                       new OracleParameter() { ParameterName = "v_date", OracleType = OracleType.DateTime, Value = beginTime },
                       string.IsNullOrEmpty(tag)? new OracleParameter() { ParameterName = "v_tag", OracleType = OracleType.VarChar, Value = DBNull.Value }:
                        new OracleParameter() { ParameterName = "v_tag", OracleType = OracleType.VarChar, Value = tag },
                       new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.ResourceByTagDistinctCount", parms.ToArray());
                    trans.Commit();
                    count = Convert.ToInt32(ds.Tables[0].Rows[0]["ncount"]);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
            }
            finally
            {
                conn.Close();
            }
            return count;
        }
        public int ResourceByNameDistinctCount(DateTime beginTime, string tag, decimal? groupid)
        {
            int count = 0;
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                       new OracleParameter() { ParameterName = "v_date", OracleType = OracleType.DateTime, Value = beginTime },
                       string.IsNullOrEmpty(tag)? new OracleParameter() { ParameterName = "v_tag", OracleType = OracleType.VarChar, Value = DBNull.Value }:
                                                  new OracleParameter() { ParameterName = "v_tag", OracleType = OracleType.VarChar, Value = tag },
                       !groupid.HasValue?  new OracleParameter() { ParameterName = "v_groupid", OracleType = OracleType.Number, Value = DBNull.Value }:
                                           new OracleParameter() { ParameterName = "v_groupid", OracleType = OracleType.Number, Value = groupid },
                       new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX_REPORT.ResourceByNameDistinctCount", parms.ToArray());
                    trans.Commit();
                    count = Convert.ToInt32(ds.Tables[0].Rows[0]["ncount"]);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
            }
            finally
            {
                conn.Close();
            }
            return count;
        }
        public Wx_MsgConfig GetMsgConfig(string username)
        {
            Wx_MsgConfig config = new Wx_MsgConfig();
            if (username.IndexOf("@@") > -1)
                return GetDbUpMsgConfig(username);
            try
            {
                config.fileCreateTime = 0;
                config.createTime = 0;
                var sql = "select max(createtime)createtime from wx_friendsrelation  where jobusername=:jobusername";
                var param = new List<OracleParameter>();
                param.Add(new OracleParameter() { ParameterName = ":jobusername", OracleType = OracleType.VarChar, Value = username });
                DataTable tab = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray()).Tables[0];

                if (tab.Rows.Count > 0)
                {
                    if (!(tab.Rows[0]["createtime"] is DBNull))
                        config.friendCreateTime = Convert.ToDateTime(tab.Rows[0]["createtime"]);
                }

                sql = @"select 
                    (
                        select max(createtime) from dbo.wx_gen_message t where  t.type in(10000,1) and  t.username='" + username + @"'
                    ) createTime,
                    (
                        select max(clientid) from dbo.wx_gen_msqunfa t where   t.jobusername='" + username + @"' 
                    )fileCreateTime ";
                DataTable tab2 = SqlHelper.Query(SqlHelper.DatabaseType.AYCRM, sql).Tables[0];

                if (tab2.Rows.Count > 0)
                {
                    if (!(tab2.Rows[0]["createTime"] is DBNull))
                        config.createTime = Convert.ToInt64(tab2.Rows[0]["createTime"]);
                    if (!(tab2.Rows[0]["fileCreateTime"] is DBNull))
                        config.fileCreateTime = Convert.ToInt64(tab2.Rows[0]["fileCreateTime"]);
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            return config;
        }
        private Wx_MsgConfig GetDbUpMsgConfig(string username)
        {
            username = username.Replace("@@", "");
            Wx_MsgConfig config = new Wx_MsgConfig();
            try
            {
                config.createTime = 0;
                config.fileCreateTime = 0;
                var sql = "select CREATETIME,QUNFACLIENTID from WX_UPLOADMAXCREATETIME  where USERNAME=:jobusername";
                var param = new List<OracleParameter>();
                param.Add(new OracleParameter() { ParameterName = ":jobusername", OracleType = OracleType.VarChar, Value = username });
                DataTable tab = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray()).Tables[0];
                if (tab.Rows.Count > 0)
                {
                    if (!(tab.Rows[0]["createtime"] is DBNull))
                        config.createTime = Convert.ToInt64(tab.Rows[0]["createtime"]);
                    if (!(tab.Rows[0]["QUNFACLIENTID"] is DBNull))
                        config.fileCreateTime = Convert.ToInt64(tab.Rows[0]["QUNFACLIENTID"]);
                }
            }
            catch (Exception xx) { }
            return config;
        }

        public bool WxDbUploadLog(WX_DBUPLOADLOG model, long createTime, DateTime createTimewin, string qunfaclientid)
        {
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_username", OracleType = OracleType.VarChar, Value = model.USERNAME });
                para.Add(new OracleParameter() { ParameterName = "v_password", OracleType = OracleType.VarChar, Value = model.PASSWORD });
                para.Add(new OracleParameter() { ParameterName = "v_filepath", OracleType = OracleType.VarChar, Value = model.DBFILE });
                para.Add(new OracleParameter() { ParameterName = "v_copytime", OracleType = OracleType.DateTime, Value = model.COPYTIME });
                para.Add(new OracleParameter() { ParameterName = "v_msgCreatetime", OracleType = OracleType.Number, Value = createTime });
                para.Add(new OracleParameter() { ParameterName = "v_msgCreatetimewin", OracleType = OracleType.DateTime, Value = createTimewin });
                para.Add(new OracleParameter() { ParameterName = "v_qunfaclientid", OracleType = OracleType.VarChar, Value = qunfaclientid });
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX_DB.WxDbUploadLog", para.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
                return false;
            }
        }


        /// <summary>
        /// 获取
        /// </summary>
        /// <returns></returns>
        public DataSet GetJobWeiXinLastFriendList()
        {
            try
            {
                var sql = "select JobUserName,CreateTime from (select rank() over(partition by jobusername order by createtime desc) r,a.* from wx_friendsrelation a) where r=1";
                var param = new List<OracleParameter>();
                return OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }


        public decimal GetDistinctWeiXinFriendCount(string day, decimal? type, decimal? saleDeptId)
        {
            try
            {
                if (!type.HasValue)
                {
                    type = 0;
                }
                //默认读取全部
                var sql = "select count(distinct username)  from wx_friendsrelation t where t.type in(1,2) and t.isworkacount=1 and t.istuiguang=1 and t.isinneruser=0 and  to_char(t.createtime,'yyyy-mm-dd')=:day and username not in(select username from wx_friendsrelation where createtime<to_date(:day,'yyyy-mm-dd'))";
                if (type == 1)
                {
                    sql = "select count(distinct t.username)  from wx_friendsrelation t " +
                        "left join wx_workaccount w on t.jobusername=w.username " +
                        "left join bas_innerusergroup ug on w.inneruserid = ug.inneruserid " +
                        "left join bas_innergroup gg on ug.gid = gg.gid " +
                        "where t.type in(1,2) and t.isworkacount=1 and t.istuiguang=1 and t.isinneruser=0 and  to_char(t.createtime,'yyyy-mm-dd')=:day and t.username not in(select username from wx_friendsrelation where createtime<to_date(:day,'yyyy-mm-dd')) " +
                        "and gg.saledeptid<>" + saleDeptId;

                }
                else if (type == 3)
                {
                    sql = "select count(distinct t.username)  from wx_friendsrelation t " +
                        "left join wx_workaccount w on t.jobusername=w.username " +
                        "left join bas_innerusergroup ug on w.inneruserid = ug.inneruserid " +
                        "left join bas_innergroup gg on ug.gid = gg.gid " +
                        "where t.type in(1,2) and t.isworkacount=1 and t.istuiguang=1 and t.isinneruser=0 and  to_char(t.createtime,'yyyy-mm-dd')=:day and t.username not in(select username from wx_friendsrelation where createtime<to_date(:day,'yyyy-mm-dd')) " +
                        "and gg.saledeptid=" + saleDeptId;
                }

                var param = new List<OracleParameter>()
                {
                    new OracleParameter() {ParameterName="day",OracleType=OracleType.VarChar,Value=day }
                };
                DataSet ds = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    return Convert.ToDecimal(ds.Tables[0].Rows[0][0]);
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }

        public DataSet GetWeixinFriendCount(decimal type)
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                        new OracleParameter() {ParameterName="p_type",OracleType=OracleType.Number,Value=type },
                        new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WX.getWeixinFriendCount2", parms.ToArray());
                    trans.Commit();
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public void UpadteUserPoint(decimal eid, decimal piont)
        {
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "v_eid", OracleType = OracleType.Number, Value = eid });
                para.Add(new OracleParameter() { ParameterName = "v_point", OracleType = OracleType.Number, Value = piont });
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX.UpdaePoint", para.ToArray());
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
                throw;
            }
        }
        public void ComputeToPushData(string eids, decimal type)
        {
            try
            {
                var para = new List<OracleParameter>();
                para.Add(new OracleParameter() { ParameterName = "p_type", OracleType = OracleType.Number, Value = type });
                para.Add(new OracleParameter() { ParameterName = "v_eids", OracleType = OracleType.VarChar, Value = eids });
                OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_WX.ComputeToPushData", para.ToArray());
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.ToString());
                throw;
            }
        }

        public DataSet GetRcontactUserNameCount(string stime, string etime, string usernameList)
        {
            try
            {
                var sql = "select username,count(1) as num from wx_rcontact where username in(" + usernameList + ") and ctime >to_date(:stime,'yyyy-mm-dd hh24:mi:ss') and ctime <to_date(:etime,'yyyy-mm-dd hh24:mi:ss') and (type in(1,3,5,7,257,259,67,65539) or type>11)  group by username";
                var param = new List<OracleParameter>()
                {
                    new OracleParameter() {ParameterName="stime",OracleType=OracleType.VarChar,Value=stime },
                    new OracleParameter() {ParameterName="etime",OracleType=OracleType.VarChar,Value=etime }
                };
                DataSet ds = OracleHelper.DataQueray(CommandType.Text, sql, param.ToArray());
                return ds;
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw;
            }
        }
        /// <summary>
        /// 获取工作微信的订单客户
        /// </summary>
        /// <param name="jobusername"></param>
        /// <returns></returns>
        public DataTable GetOrderUserName(string jobusername)
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var parms = new List<OracleParameter>()
                    {
                        new OracleParameter() {ParameterName="v_username",OracleType=OracleType.VarChar,Value=jobusername },
                        new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output }
                    };
                    DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_WXMSG.GetOrderUserName", parms.ToArray());
                    trans.Commit();
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}