public List <TalkContent> GetAllContent(DateTime start, DateTime end, string nick)
{
List <TalkContent> list = new List <TalkContent>();
string sql = SQL_SELECT_ALL_CONTENT_BYDATE.Replace("@tableName", DBHelper.GetRealTable("TalkContent", DataHelper.Encrypt(nick)));
SqlParameter[] param = new[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end)
};
using (SqlDataReader dr = ServiceDBHelper.CreateReader(sql, param))
{
if (dr != null)
{
while (dr.Read())
{
TalkContent info = new TalkContent();
info.FromNick = dr["FromId"].ToString();
info.direction = dr["direction"] == DBNull.Value ? 0 : int.Parse(dr["direction"].ToString());
info.ToNick = dr["ToId"].ToString();
info.time = dr["TalkTime"].ToString();
list.Add(info);
}
}
}
return(list);
}
public List <CustomerInfo> GetReceiveList(string nick, DateTime start, DateTime end)
{
string sql = SQL_SELECT_RECEIVE_COUNT.Replace("@tableName", DBHelper.GetRealTable("TalkContent", DataHelper.Encrypt(nick)));
List <CustomerInfo> list = new List <CustomerInfo>();
SqlParameter[] param = new[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
new SqlParameter("@nick", nick)
};
using (SqlDataReader dr = ServiceDBHelper.CreateReader(sql, param))
{
if (dr != null)
{
while (dr.Read())
{
CustomerInfo info = new CustomerInfo();
info.FromNick = dr["fromid"].ToString();
info.CustomerNick = dr["cuscount"].ToString(); //用客服ID装下客户总数
info.TalkCount = int.Parse(dr["tccount"].ToString());
list.Add(info);
}
}
}
return(list);
}
/// <summary>
/// 获取客服及客服未回复客户的数量
/// </summary>
/// <param name="nick"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public List <TopKefuTotalInfo> GetUnTalkCustomerList(string nick, DateTime start, DateTime end)
{
SqlParameter[] param = new[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
new SqlParameter("@nick", nick)
};
List <TopKefuTotalInfo> list = new List <TopKefuTotalInfo>();
string sql = SQL_SELECT_UNTALKCUSTOMER.Replace("@tableName", DBHelper.GetRealTable("TalkContent", DataHelper.Encrypt(nick)));
using (SqlDataReader dr = ServiceDBHelper.CreateReader(sql, param))
{
if (dr != null)
{
if (dr.Read())
{
TopKefuTotalInfo info = new TopKefuTotalInfo();
info.Nick = dr["FromId"].ToString();
info.UnTalkCustomerCount = dr["untalkcount"] == DBNull.Value ? 0 : int.Parse(dr["untalkcount"].ToString());
list.Add(info);
}
}
}
return(list);
}
public List <GoodsInfo> GetAllGoods(string nick)
{
SqlParameter param = new SqlParameter("@nickno", nick);
List <GoodsInfo> list = new List <GoodsInfo>();
using (SqlDataReader dr = ServiceDBHelper.CreateReader(SQL_SELECT_ALLGOODS_NICK, param))
{
if (dr != null)
{
while (dr.Read())
{
GoodsInfo info = new GoodsInfo();
info.num_iid = dr["GoodsId"].ToString();
info.pic_url = dr["Pic_Url"].ToString();
info.cid = dr["CId"].ToString();
info.price = decimal.Parse(dr["GoodsPrice"].ToString());
info.title = dr["GoodsName"].ToString();
list.Add(info);
}
}
}
return(list);
}
public DateTime GetMaxTime(string nick)
{
string sql = SQL_SELECT_MAXTIME.Replace("@tableName", DBHelper.GetRealTable("TalkContent", DataHelper.Encrypt(nick)));
using (SqlDataReader dr = ServiceDBHelper.CreateReader(sql))
{
if (dr != null)
{
if (dr.Read())
{
return(dr[0] == DBNull.Value ? DateTime.Now.AddDays(-1) : dr.GetDateTime(0));
}
}
}
return(DateTime.Parse(DateTime.Now.AddDays(-1).ToShortDateString()));
}
public IList <string> GetGoodsIds(string nickNo)
{
IList <string> list = new List <string>();
SqlParameter param = new SqlParameter("@nickno", nickNo);
using (SqlDataReader dr = ServiceDBHelper.CreateReader(SQL_SELECT_GOODS_BYNICK, param))
{
if (dr != null)
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
}
return(list);
}
public IList <CustomerInfo> GetCustomerList(DateTime start, DateTime end, string nick)
{
string sql = SQL_SELECT_CUSTOMEN_LIST_SERVICE.Replace("@tableName", DBHelper.GetRealTable("TalkContent", DataHelper.Encrypt(nick)));
IList <CustomerInfo> list = new List <CustomerInfo>();
SqlParameter[] param = new[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
new SqlParameter("@nick", nick)
};
using (SqlDataReader dr = ServiceDBHelper.CreateReader(sql, param))
{
if (dr != null)
{
while (dr.Read())
{
CustomerInfo info = new CustomerInfo();
info.StartTime = DateTime.Parse(dr["start"].ToString());
info.EndTime = DateTime.Parse(dr["endt"].ToString());
info.FromNick = dr["fromid"].ToString();
info.CustomerNick = dr["toid"].ToString();
info.TalkCount = dr["talkcount"] == DBNull.Value ? 0 : int.Parse(dr["talkcount"].ToString());
IList <CustomerInfo> owerList = list.Where(o => o.CustomerNick == info.CustomerNick).ToList();
if (owerList.Count > 0)
{
//按聊天次数最多的算
if (owerList[0].TalkCount > info.TalkCount)
{
continue;
}
list.Remove(owerList[0]);
}
list.Add(info);
}
}
}
return(list);
}
/// <summary>
/// 搜索订购指定服务的用户信息
/// </summary>
/// <param name="serviceId"></param>
/// <returns></returns>
public IList <TopNickSessionInfo> GetAllNickSession(TopTaoBaoService serviceId)
{
SqlParameter param = new SqlParameter("@ServiceID", (int)serviceId);
IList <TopNickSessionInfo> list = new List <TopNickSessionInfo>();
using (SqlDataReader dr = ServiceDBHelper.CreateReader(SQL_SELECT_NICKNO, param))
{
if (dr != null)
{
while (dr.Read())
{
TopNickSessionInfo info = new TopNickSessionInfo();
info.Nick = dr["nick"].ToString();
info.Session = dr["session"].ToString();
info.NickState = (bool)dr["NickState"];
info.LastGetOrderTime = dr["LastGetOrderTime"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dr["LastGetOrderTime"].ToString());
info.ShopId = dr["ShopId"].ToString();
list.Add(info);
}
}
}
return(list);
}