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 static bool CheckTable(string tableName)
{
string sql = SQL_SELECT_TABLE_EXISTS.Replace("@tableName", DBHelper.GetRealTable("TalkContent", tableName));
int drow = ServiceDBHelper.GetScaleSql(sql);
return(drow == 0 ? false : true);
}
/// <summary>
/// 修改客户服务信息的方法
/// </summary>
/// <param name="CustServiceM">服务信息</param>
/// <returns>bool值</returns>
public static bool UpdateService(CustServiceModel CustServiceM)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
LogInfoModel logModel = new LogInfoModel(); //操作日志
bool isSucc = false; //定义返回变量
#region 设置操作日志内容
//设置公司代码
logModel.CompanyCD = userInfo.CompanyCD;
//设置登陆用户ID
logModel.UserID = userInfo.UserID;
//设置模块ID 模块ID在ConstUtil中定义，以便维护
logModel.ModuleID = ConstUtil.MODULE_ID_CUST_SERVICE_LIST;
//操作单据编号 客户服务单编号
logModel.ObjectID = CustServiceM.ServeNO;
//操作对象 操作的表信息
logModel.ObjectName = ConstUtil.TABLE_NAME_SERVICE;
//涉及关键元素 涉及其他业务、表关系
logModel.Element = string.Empty;
//备注 操作类型
logModel.Remark = ConstUtil.LOG_PROCESS_UPDATE;
#endregion
try
{
isSucc = ServiceDBHelper.UpdateService(CustServiceM);
}
catch (Exception ex)
{
#region 操作失败时记录日志到文件
//定义变量
LogInfo logSys = new LogInfo();
//设置日志类型 需要指定为系统日志
logSys.Type = LogInfo.LogType.SYSTEM;
//指定系统日志类型 出错信息
logSys.SystemKind = LogInfo.SystemLogKind.SYSTEM_ERROR;
//指定登陆用户信息
logSys.UserInfo = userInfo;
//设定模块ID
logSys.ModuleID = ConstUtil.MODULE_ID_CUST_SERVICE_LIST;
//描述
logSys.Description = ex.ToString();
//输出日志
LogUtil.WriteLog(logSys);
#endregion
}
if (isSucc)//操作成功
{
logModel.Remark += "成功";
}
else//操作失败
{
logModel.Remark += "失败";
}
//记录日志
LogDBHelper.InsertLog(logModel);
return(isSucc);
}
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 int InsertContent(TalkContent tc, string nick)
{
SqlParameter[] param = CreateParameter(tc);
string sql = SQL_INSERT.Replace("@tableName", DBHelper.GetRealTable("TalkContent", DataHelper.Encrypt(nick)));
return(ServiceDBHelper.ExcuteSql(sql, param));
}
/// <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 <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);
}
public static bool CheckTable(string tableName)
{
string sql = SQL_SELECT_EXISTSTABLE.Replace("@tableName", tableName);
int drow = ServiceDBHelper.ExcuteSql(sql);
return(drow == 0 ? false : true);
}
public int UpdateNickShop(string nick, string shopId)
{
SqlParameter[] param = new[]
{
new SqlParameter("@ShopId", shopId),
new SqlParameter("@nick", nick)
};
return(ServiceDBHelper.ExcuteSql(SQL_UPDATE_SHOPID, param));
}
public void DropTable(string tableName)
{
if (CheckTable(tableName))
{
string sql = SQL_DROP_TABLE.Replace("@tableName", tableName);
ServiceDBHelper.ExcuteSql(sql);
}
ServiceDBHelper.ExcuteSql(SQL_CREATE_TABLE);
}
public static DataTable GetServiceByDaysPrint(string Days, string CompanyCD, string ord)
{
try
{
return(ServiceDBHelper.GetServiceByDaysPrint(Days, CompanyCD, ord));
}
catch
{
return(null);
}
}
public static DataTable GetServiceByManPrint(string Executant, string CompanyCD, string LinkDateBegin, string LinkDateEnd, string ord)
{
try
{
return(ServiceDBHelper.GetServiceByManPrint(Executant, CompanyCD, LinkDateBegin, LinkDateEnd, ord));
}
catch
{
return(null);
}
}
/// <summary>
/// 按服务执行人统计_报表
/// </summary>
/// <param name="TypeId">执行人ID</param>
/// <param name="CompanyCD">公司ID</param>
/// <param name="LinkDateBegin">开始时间</param>
/// <param name="LinkDateEnd">结束时间</param>
/// <returns>记录集</returns>
public static DataTable GetServiceByMan(string Executant, string CompanyCD, string LinkDateBegin, string LinkDateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
return(ServiceDBHelper.GetServiceByMan(Executant, CompanyCD, LinkDateBegin, LinkDateEnd, pageIndex, pageCount, ord, ref TotalCount));
}
catch
{
return(null);
}
}
public static DataTable GetServiceInfoBycondition(string CanUserID, string CustName, CustServiceModel CustServiceM, string ServiceDateBegin, string ServiceDateEnd, string Executant, string CustLinkMan, int pageIndex, int pageCount, string ord, ref int totalCount)
{
try
{
return(ServiceDBHelper.GetServiceInfoBycondition(CanUserID, CustName, CustServiceM, ServiceDateBegin, ServiceDateEnd, Executant, CustLinkMan, pageIndex, pageCount, ord, ref totalCount));
}
catch
{
return(null);
}
}
public static DataTable GetServiceByTypePrint(string ServeType, string CompanyCD, string LinkDateBegin, string LinkDateEnd, string ord)
{
try
{
return(ServiceDBHelper.GetCustServiceByTypePrint(ServeType, CompanyCD, LinkDateBegin, LinkDateEnd, ord));
}
catch
{
return(null);
}
}
/// <summary>
/// 零服务客户统计_报表
/// </summary>
/// <param name="Days">天数</param>
/// <param name="CompanyCD">公司ID</param>
/// <returns>记录集</returns>
public static DataTable GetServiceByDays(string Days, string CompanyCD, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
return(ServiceDBHelper.GetServiceByDays(Days, CompanyCD, pageIndex, pageCount, ord, ref TotalCount));
}
catch
{
return(null);
}
}
/// <summary>
/// 服务次数统计_报表
/// </summary>
/// <param name="CustName">客户名称</param>
/// <param name="TypeId">服务分类</param>
/// <param name="CompanyCD">公司ID</param>
/// <param name="LinkDateBegin">开始时间</param>
/// <param name="LinkDateEnd">结束时间</param>
/// <returns>记录集</returns>
public static DataTable GetCustServiceCount(string CustName, string ServeType, string CompanyCD, string LinkDateBegin, string LinkDateEnd, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
try
{
return(ServiceDBHelper.GetCustServiceCount(CustName, ServeType, CompanyCD, LinkDateBegin, LinkDateEnd, pageIndex, pageCount, ord, ref TotalCount));
}
catch
{
return(null);
}
}
/// <summary>
/// 获取产品销售记录的方法
/// </summary>
/// <param name="CompanyCD">公司代码</param>
/// <returns>查询结果</returns>
public static DataTable GetSellAnnal(string CompanyCD, string CustID, string ProductID, string DateBegin, string DateEnd)
{
try
{
return(ServiceDBHelper.GetSellAnnal(CompanyCD, CustID, ProductID, DateBegin, DateEnd));
}
catch
{
return(null);
}
}
/// <summary>
/// 根据服务ID获取此条客户服务信息
/// </summary>
/// <param name="CompanyCD">公司代码</param>
/// <param name="serviceid">服务信息ID</param>
/// <returns>返回一条结果记录</returns>
public static DataTable GetServiceInfoByID(string CompanyCD, int serviceid)
{
try
{
return(ServiceDBHelper.GetServiceInfoByID(CompanyCD, serviceid));
}
catch
{
return(null);
}
}
/// <summary>
/// 用户订购获取代码时生成一张表
/// </summary>
/// <param name="nickNo">加密后的</param>
public void CreateTable(string nickNo)
{
string sql = SQL_SELECT_TABLE_EXISTS.Replace("@tableName", DBHelper.GetRealTable("TalkContent", nickNo));
int drow = ServiceDBHelper.GetScaleSql(sql);
if (drow == 0)
{
sql = SQL_CREATE_TABLE.Replace("@tableName", DBHelper.GetRealTable("TalkContent", nickNo)).Replace("@pk", "PK_TalkContent_" + nickNo);
ServiceDBHelper.ExcuteSql(sql);
}
}
public int UpdateGoodsInfo(GoodsInfo info)
{
SqlParameter[] param = new[]
{
new SqlParameter("@GoodsId", info.num_iid),
new SqlParameter("@CId", info.cid),
new SqlParameter("@GoodsName", info.title),
new SqlParameter("@Pic_Url", info.pic_url),
new SqlParameter("@GoodsPrice", info.price)
};
return(ServiceDBHelper.ExcuteSql(SQL_UPDATE, param));
}
public int InsertGoods(GoodsInfo info, string nick)
{
SqlParameter[] param = new[]
{
new SqlParameter("@GoodsId", info.num_iid),
new SqlParameter("@CId", info.cid),
new SqlParameter("@GoodsName", info.title),
new SqlParameter("@Pic_Url", string.IsNullOrEmpty(info.pic_url)?"": info.pic_url),
new SqlParameter("@GoodsPrice", info.price),
new SqlParameter("@NickNo", nick)
};
return(ServiceDBHelper.ExcuteSql(SQL_INSERT, param));
}
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);
}
/// <summary>
/// 导出客户服务信息
/// </summary>
/// <param name="CustName"></param>
/// <param name="CustServiceM"></param>
/// <param name="ServiceDateBegin"></param>
/// <param name="ServiceDateEnd"></param>
/// <param name="Executant"></param>
/// <param name="CustLinkMan"></param>
/// <param name="ord"></param>
/// <returns></returns>
public static DataTable ExportServiceInfo(string CanUserID, string CustID, CustServiceModel CustServiceM, string ServiceDateBegin, string ServiceDateEnd, string Executant, string CustLinkMan, string ord)
{
return(ServiceDBHelper.ExportServiceInfo(CanUserID, CustID, CustServiceM, ServiceDateBegin, ServiceDateEnd, Executant, CustLinkMan, ord));
}
/// <summary>
/// 客户服务信息打印
/// </summary>
/// <param name="CompanyCD"></param>
/// <param name="serviceid"></param>
/// <returns></returns>
public static DataTable PrintService(string CompanyCD, string serviceid)
{
return(ServiceDBHelper.PrintService(CompanyCD, serviceid));
}
/// <summary>
/// 获取产品销售记录列表（分页）
/// </summary>
/// <param name="userinfo">用户session信息</param>
/// <param name="CustID">检索条件：客户</param>
/// <param name="ProductID">检索条件：物品</param>
/// <param name="DateBegin">检索条件：开始时间</param>
/// <param name="DateEnd">检索条件：结束时间</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="OrderBy"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetSellAnnalList(XBase.Common.UserInfoUtil userinfo, string CustID, string ProductID, string DateBegin, string DateEnd, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
return(ServiceDBHelper.GetSellAnnalList(userinfo, CustID, ProductID, DateBegin, DateEnd, pageIndex, pageCount, OrderBy, ref totalCount));
}
