private string GetVipListSql(VipSearchEntity vipSearchInfo)
{
PublicService pService = new PublicService();
string sql = string.Empty;
sql += " select a.*, DisplayIndex=row_number() over(order by a.VipName desc ) into #tmp from ( ";
sql += "SELECT a.* "
+ " ,(SELECT x.VipSourceName FROM dbo.SysVipSource x WHERE x.VipSourceID = a.VipSourceId) VipSourceName "
+ " ,CASE WHEN a.Status = '1' THEN '潜在会员' ELSE '正式会员' END StatusDesc "
+ " ,'' LastUnit "
+ " ,CASE WHEN a.VipLevel = '1' THEN '基本' ELSE '高级' END VipLevelDesc "
+ " ,(select sum(Integral) from VipIntegralDetail where IsDelete='0' "
+ " and fromVipId=a.vipId and vipId='" + vipSearchInfo.HigherVipId + "') IntegralForHightUser "
+ " ,CASE WHEN a.Gender = '1' THEN '男' ELSE '女' END GenderInfo "
+ " from vip a "
+ " WHERE a.IsDelete = '0') a where 1=1 ";
if (vipSearchInfo.VipInfo != null && !vipSearchInfo.VipInfo.Equals(""))
{
sql += " and (a.VipCode like '%" + vipSearchInfo.VipInfo + "%' or a.VipName like '%" + vipSearchInfo.VipInfo + "%' ) ";
}
sql = pService.GetLinkSql(sql, "a.Phone", vipSearchInfo.Phone, "%");
return(sql);
}
public int WebGetListCountAdd(VipSearchEntity vipSearchInfo)
{
string sql = WebGetListSqlAdd(vipSearchInfo, -1, -1);
//sql = sql + " select count(*) as icount From #tmp; ";
return(Convert.ToInt32(this.SQLHelper.ExecuteScalar(sql)));
}
/// <summary>
/// 获取VIP列表
/// </summary>
public string GetVipListData()
{
var service = new VipCardBLL(CurrentUserInfo);
string content = string.Empty;
int pageIndex = Utils.GetIntVal(FormatParamValue(Request("page")));
pageIndex = pageIndex < 1 ? 1 : pageIndex;
var queryEntity = new VipSearchEntity();
queryEntity.Page = pageIndex;
queryEntity.PageSize = PageSize;
if (Request("vipName") != "")
{
queryEntity.VipInfo = FormatParamValue(Request("vipName"));
}
if (Request("phone") != "")
{
queryEntity.Phone = FormatParamValue(Request("phone"));
}
var data = service.GetVipList(queryEntity);
var dataTotalCount = data.ICount;
content = string.Format("{{\"totalCount\":{1},\"topics\":{0}}}",
data.vipInfoList.ToJSON(),
dataTotalCount);
return(content);
}
/// <summary>
/// 获取查询会员的数量
/// </summary>
/// <param name="vipSearchInfo"></param>
/// <returns></returns>
public int GetVipListCount(VipSearchEntity vipSearchInfo)
{
string sql = GetVipListSql(vipSearchInfo);
sql = sql + " select count(*) as icount From #tmp; ";
return(Convert.ToInt32(this.SQLHelper.ExecuteScalar(sql)));
}
/// <summary>
///
/// </summary>
public string GetIntegralByVip()
{
var service = new VipBLL(CurrentUserInfo);
string content = string.Empty;
string key = string.Empty;
if (Request("vip_id") != null && Request("vip_id") != string.Empty)
{
key = Request("vip_id").ToString().Trim();
}
int pageIndex = Utils.GetIntVal(FormatParamValue(Request("page")));
var queryEntity = new VipSearchEntity();
queryEntity.HigherVipId = key;
queryEntity.Page = pageIndex;
queryEntity.PageSize = PageSize;
var data = service.GetIntegralByVip(queryEntity);
content = string.Format("{{\"totalCount\":{1},\"topics\":{0}}}",
data.vipInfoList.ToJSON(),
data.ICount);
return(content);
}
/// <summary>
/// 查询会员
/// </summary>
public string GetVipListData()
{
var form = Request("form").DeserializeJSONTo <VipQueryEntity>();
var vipBLL = new VipBLL(CurrentUserInfo);
VipEntity vipList;
string content = string.Empty;
int pageIndex = Utils.GetIntVal(FormatParamValue(Request("page")));
VipSearchEntity queryEntity = new VipSearchEntity();
queryEntity.VipInfo = FormatParamValue(form.VipName);
queryEntity.Phone = FormatParamValue(form.Phone);
queryEntity.Page = pageIndex;
queryEntity.PageSize = PageSize;
vipList = vipBLL.SearchVipInfo(queryEntity);
var dataTotalCount = vipList.ICount;
var jsonData = new JsonData();
jsonData.totalCount = dataTotalCount.ToString();
jsonData.data = vipList.vipInfoList;
content = string.Format("{{\"totalCount\":{1},\"topics\":{0}}}",
jsonData.data.ToJSON(),
jsonData.totalCount);
return(content);
}
private string WebGetListSql(VipSearchEntity vipSearchInfo)
{
PublicService pService = new PublicService();
string sql = string.Empty;
//处理循环处理标签
sql = SetTagsSql(vipSearchInfo);
#region
sql += " select a.*,DisplayIndex=row_number() over(order by a.LastUpdateTime desc ) into #tmp from ( ";
sql += "SELECT a.*, b.EventId "
+ " ,(SELECT x.VipSourceName FROM dbo.SysVipSource x WHERE x.VipSourceID = a.VipSourceId) VipSourceName "
+ " ,CASE WHEN a.Status = '1' THEN '潜在会员' ELSE '正式会员' END StatusDesc "
+ " ,'' LastUnit "
+ " ,CASE WHEN a.VipLevel = '1' THEN '基本' ELSE '高级' END VipLevelDesc "
+ " "
+ " ,(select sum(Integral) from VipIntegralDetail where IsDelete='0' "
+ " and fromVipId=a.vipId and vipId='" + vipSearchInfo.HigherVipId + "') IntegralForHightUser "
+ " ,CASE WHEN a.Gender = '1' THEN '男' ELSE '女' END GenderInfo "
+ " ,(SELECT AnswerID FROM MarketQuesAnswer x WHERE x.MarketEventID='4' AND QuestionID = '41' AND x.OpenID = a.WeiXinUserId) UserName "
+ " ,(SELECT AnswerID FROM MarketQuesAnswer x WHERE x.MarketEventID='4' AND QuestionID = '42' AND x.OpenID = a.WeiXinUserId) Enterprice "
+ " ,(SELECT y.OptionsId FROM MarketQuesAnswer x INNER JOIN dbo.QuesOption y "
+ " ON(x.AnswerID =y.OptionsID) WHERE x.MarketEventID='4' AND x.QuestionID = '43' AND x.OpenID = a.WeiXinUserId) IsChainStoresId "
+ " ,(SELECT y.OptionsText FROM MarketQuesAnswer x INNER JOIN dbo.QuesOption y "
+ " ON(x.AnswerID =y.OptionsID) WHERE x.MarketEventID='4' AND x.QuestionID = '43' AND x.OpenID = a.WeiXinUserId) IsChainStores "
+ " ,(SELECT y.OptionsText FROM MarketQuesAnswer x INNER JOIN dbo.QuesOption y "
+ " ON(x.AnswerID =y.OptionsID) WHERE x.MarketEventID='4' AND x.QuestionID = '45' AND x.OpenID = a.WeiXinUserId) IsWeiXinMarketing "
+ " from #vip a "
+ " left join MarketSignIn b on (b.openId=a.weiXinUserId and a.isDelete='0') "
+ " WHERE a.IsDelete = '0') a where 1=1 and ClientID = '" + this.CurrentUserInfo.CurrentUser.customer_id + "' ";
if (vipSearchInfo.Gender != null && vipSearchInfo.Gender.Trim().Length > 0)
{
sql = pService.GetLinkSql(sql, "a.Gender ", vipSearchInfo.Gender.Trim(), "=");
}
if (vipSearchInfo.UserName != null && vipSearchInfo.UserName.Trim().Length > 0)
{
sql += " and a.VIPName like '%" + vipSearchInfo.UserName + "%' ";
}
if (vipSearchInfo.Enterprice != null && vipSearchInfo.Enterprice.Trim().Length > 0)
{
sql += " and a.Enterprice like '%" + vipSearchInfo.Enterprice + "%' ";
}
if (vipSearchInfo.IsChainStores != null && vipSearchInfo.IsChainStores.Trim().Length > 0)
{
sql += " and a.IsChainStoresId = '" + vipSearchInfo.IsChainStores + "' ";
}
if (vipSearchInfo.IsWeiXinMarketing != null && vipSearchInfo.IsWeiXinMarketing.Trim().Length > 0)
{
sql += " and a.IsWeiXinMarketing = '" + vipSearchInfo.IsWeiXinMarketing + "' ";
}
//if (vipSearchInfo.EventId != null && vipSearchInfo.EventId.Trim().Length > 0)
//{
// sql += " and (a.EventID = '" + vipSearchInfo.EventId + "' or a.EventID = '4') ";
//}
//sql += " order by a.LastUpdatetime desc";
#endregion
return(sql);
}
public DataSet WebGetListAdd(VipSearchEntity vipSearchInfo)
{
int beginSize = (vipSearchInfo.Page - 1) * vipSearchInfo.PageSize;
int endSize = (vipSearchInfo.Page - 1) * vipSearchInfo.PageSize + vipSearchInfo.PageSize;
string sql = WebGetListSqlAdd(vipSearchInfo, beginSize, endSize);
//sql = sql + "select * From #tmp a where 1=1 and a.DisplayIndex between '" + beginSize + "' and '" + endSize + "' order by a.displayindex";
DataSet ds = this.SQLHelper.ExecuteDataset(sql);
return(ds);
}
/// <summary>
/// 处理循环处理标签
/// </summary>
/// <param name="vipSearchInfo"></param>
/// <returns></returns>
public string SetTagsSqlAdd(VipSearchEntity vipSearchInfo)
{
string sql = string.Empty;
sql = " select * into #vip From ( select VIPID,VipCode,VipLevel,VipName,Phone,WeiXin,Integration,LastUpdateTime,PurchaseAmount,PurchaseCount,weiXinUserId,isDelete,VipSourceId,Status,Gender,ClientID From vip ";
sql += " WHERE IsDelete = '0' and ClientID = '" + this.CurrentUserInfo.CurrentUser.customer_id + "' ) x ";
if (vipSearchInfo == null || vipSearchInfo.Tags == null || vipSearchInfo.Tags.Length < 5)
{
}
else
{
string[] tagsArr = vipSearchInfo.Tags.Split(';');
foreach (string tag in tagsArr)
{
if (!tag.Trim().Equals(""))
{
string[] tagArr = tag.Split(',');
string tagId = tagArr[0];
string strlinkType = tagArr[1];
if (!tagId.Equals(""))
{
JIT.CPOS.BS.DataAccess.TagsDAO server = new TagsDAO(this.CurrentUserInfo);
JIT.CPOS.BS.Entity.TagsEntity tagsInfo = new TagsEntity();
tagsInfo = server.GetByID(tagId);
if (tagsInfo != null && tagsInfo.TagsFormula != null && !tagsInfo.TagsFormula.Equals(""))
{
switch (strlinkType)
{
case "1":
sql += " and " + tagsInfo.TagsFormula.ToString();
break;
case "2":
sql += " or " + tagsInfo.TagsFormula.ToString();
break;
case "3":
sql += " where " + tagsInfo.TagsFormula.ToString();
break;
default:
break;
}
}
}
}
}
}
//sql += " order by LastUpdateTime desc ";
sql += " ;";
return(sql);
}
/// <summary>
/// 查询
/// </summary>
public string GetVipListData()
{
var form = Request("form").DeserializeJSONTo <VipQueryEntity>();
var service = new VipBLL(CurrentUserInfo);
VipEntity listObj = null;
string content = string.Empty;
VipSearchEntity queryEntity = new VipSearchEntity();
queryEntity.Page = Utils.GetIntVal(Request("page"));
queryEntity.PageSize = PageSize;
queryEntity.VipInfo = FormatParamValue(form.VipInfo);
queryEntity.Phone = FormatParamValue(form.Phone);
queryEntity.UnitId = FormatParamValue(Request("UnitId"));
queryEntity.MembershipShopId = FormatParamValue(Request("MembershipShopId"));
queryEntity.VipSourceId = FormatParamValue(form.VipSourceId);
if (form.VipSourceId == null)
{
queryEntity.VipSourceId = FormatParamValue(Request("VipSourceId"));
}
queryEntity.Status = Utils.GetIntVal(FormatParamValue(form.Status));
queryEntity.VipLevel = Utils.GetIntVal(FormatParamValue(form.VipLevel));
queryEntity.RegistrationDateBegin = FormatParamValue(form.RegistrationDateBegin);
queryEntity.RegistrationDateEnd = FormatParamValue(form.RegistrationDateEnd);
queryEntity.RecentlySalesDateBegin = FormatParamValue(form.RecentlySalesDateBegin);
queryEntity.RecentlySalesDateEnd = FormatParamValue(form.RecentlySalesDateEnd);
queryEntity.IntegrationBegin = Utils.GetIntVal(FormatParamValue(form.IntegrationBegin));
queryEntity.IntegrationEnd = Utils.GetIntVal(FormatParamValue(form.IntegrationEnd));
queryEntity.UserId = CurrentUserInfo.CurrentUser.User_Id;
queryEntity.CustomerId = CurrentUserInfo.CurrentUser.customer_id;
//Jermyn20130801
if (Request("tags") != "") // 标签及组合关系
{
queryEntity.Tags = FormatParamValue(Request("tags"));
}
listObj = service.SearchVipInfo(queryEntity);
var jsonData = new JsonData();
jsonData.totalCount = listObj.ICount.ToString();
jsonData.data = listObj.vipInfoList;
content = string.Format("{{\"totalCount\":{1},\"topics\":{0}}}",
jsonData.data.ToJSON(),
jsonData.totalCount);
return(content);
}
/// <summary>
/// 获取查询会员的信息
/// </summary>
/// <param name="vipSearchInfo"></param>
/// <returns></returns>
public DataSet WebGetList(VipSearchEntity vipSearchInfo)
{
int beginSize = (vipSearchInfo.Page - 1) * vipSearchInfo.PageSize;
int endSize = (vipSearchInfo.Page - 1) * vipSearchInfo.PageSize + vipSearchInfo.PageSize;
string sql = WebGetListSql(vipSearchInfo);
//JIT.Utility.Log.Loggers.Debug(new JIT.Utility.Log.DebugLogInfo() { Message = "活动sql:" + sql.ToString().Trim() });
sql = sql + "select * From #tmp a where 1=1 and a.DisplayIndex between '" + beginSize + "' and '" + endSize + "' order by a.displayindex";
DataSet ds = this.SQLHelper.ExecuteDataset(sql);
return(ds);
}
/// <summary>
/// 处理循环处理标签
/// </summary>
/// <param name="vipSearchInfo"></param>
/// <returns></returns>
public string SetTagsSql(VipSearchEntity vipSearchInfo)
{
string sql = string.Empty;
sql = " select * into #vip From vip ";
if (vipSearchInfo == null || vipSearchInfo.Tags == null || vipSearchInfo.Tags.Length < 5)
{
}
else
{
string[] tagsArr = vipSearchInfo.Tags.Split(';');
foreach (string tag in tagsArr)
{
if (!tag.Trim().Equals(""))
{
string[] tagArr = tag.Split(',');
string tagId = tagArr[0];
string strlinkType = tagArr[1];
if (!tagId.Equals(""))
{
JIT.CPOS.BS.DataAccess.TagsDAO server = new TagsDAO(this.CurrentUserInfo);
JIT.CPOS.BS.Entity.TagsEntity tagsInfo = new TagsEntity();
tagsInfo = server.GetByID(tagId);
if (tagsInfo != null && tagsInfo.TagsFormula != null && !tagsInfo.TagsFormula.Equals(""))
{
switch (strlinkType)
{
case "1":
sql += " and " + tagsInfo.TagsFormula.ToString();
break;
case "2":
sql += " or " + tagsInfo.TagsFormula.ToString();
break;
case "3":
sql += " where " + tagsInfo.TagsFormula.ToString();
break;
default:
break;
}
}
}
}
}
}
sql += " ;";
return(sql);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="vipSearchInfo"></param>
/// <returns></returns>
public VipEntity WebGetListAdd(VipSearchEntity vipSearchInfo)
{
try
{
VipEntity vipInfo = new VipEntity();
//int iCount = _currentDAO.WebGetListCount(vipSearchInfo);
int iCount = _currentDAO.WebGetListCountAdd(vipSearchInfo);
IList <VipEntity> vipInfoList = new List <VipEntity>();
DataSet ds = _currentDAO.WebGetListAdd(vipSearchInfo);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
//vipInfoList = DataTableToObject.ConvertToList<VipEntity>(ds.Tables[0]);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var vipObj = new VipEntity();
vipObj.VIPID = dr["VIPID"] != DBNull.Value ? dr["VIPID"].ToString() : "";
vipObj.VipCode = dr["VipCode"] != DBNull.Value ? dr["VipCode"].ToString() : "";
vipObj.VipLevel = dr["VipLevel"] != DBNull.Value ? Convert.ToInt32(dr["VipLevel"].ToString()) : 0;
vipObj.VipName = dr["VipName"] != DBNull.Value ? dr["VipName"].ToString() : "";
vipObj.Phone = dr["Phone"] != DBNull.Value ? dr["Phone"].ToString() : "";
vipObj.WeiXin = dr["WeiXin"] != DBNull.Value ? dr["WeiXin"].ToString() : "";
vipObj.Integration = dr["Integration"] != DBNull.Value ? Convert.ToDecimal(dr["Integration"].ToString()) : 0;
vipObj.LastUpdateTime = dr["LastUpdateTime"] != DBNull.Value ? Convert.ToDateTime(dr["LastUpdateTime"].ToString()) : DateTime.Now;
vipObj.PurchaseAmount = dr["PurchaseAmount"] != DBNull.Value ? Convert.ToDecimal(dr["PurchaseAmount"].ToString()) : 0;
vipObj.PurchaseCount = dr["PurchaseCount"] != DBNull.Value ? Convert.ToInt32(dr["PurchaseCount"].ToString()) : 0;
vipInfoList.Add(vipObj);
}
}
vipInfo.ICount = iCount;
vipInfo.vipInfoList = vipInfoList;
return(vipInfo);
}
catch (Exception ex)
{
throw (ex);
}
}
private void SearchVipInfo()
{
VipSearchEntity vipSearchInfo = new VipSearchEntity();
vipSearchInfo.Page = 1;
vipSearchInfo.PageSize = 15;
LoggingSessionInfo loggingSessionInfo = new LoggingSessionInfo();
loggingSessionInfo = new CLoggingSessionService().GetLoggingSessionInfo("29E11BDC6DAC439896958CC6866FF64E", "7d4cda48970b4ed0aa697d8c2c2e4af3");
VipBLL vipServer = new VipBLL(loggingSessionInfo);
VipEntity vipInfo = new VipEntity();
vipInfo = vipServer.SearchVipInfo(vipSearchInfo);
if (vipInfo != null)
{
this.lb1.Text = vipInfo.ICount.ToString();
if (vipInfo.ICount > 0)
{
this.GridView1.DataSource = vipInfo.vipInfoList;
this.GridView1.DataBind();
}
}
}
/// <summary>
/// 会员查询
/// </summary>
/// <param name="vipSearchInfo"></param>
/// <returns></returns>
public VipEntity GetVipList(VipSearchEntity vipSearchInfo)
{
try
{
VipEntity vipInfo = new VipEntity();
int iCount = _currentDAO.GetVipListCount(vipSearchInfo);
IList <VipEntity> vipInfoList = new List <VipEntity>();
DataSet ds = _currentDAO.GetVipList(vipSearchInfo);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
vipInfoList = DataTableToObject.ConvertToList <VipEntity>(ds.Tables[0]);
}
vipInfo.ICount = iCount;
vipInfo.vipInfoList = vipInfoList;
return(vipInfo);
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 获取VIP列表
/// </summary>
public string GetVipListData()
{
var service = new VipBLL(CurrentUserInfo);
var marketSignInBLL = new MarketSignInBLL(CurrentUserInfo);
string content = string.Empty;
string key = string.Empty;
if (Request("VIPID") != null && Request("VIPID") != string.Empty)
{
key = Request("VIPID").ToString().Trim();
}
int pageIndex = Utils.GetIntVal(FormatParamValue(Request("page")));
pageIndex = pageIndex < 1 ? 1 : pageIndex;
var queryEntity = new VipSearchEntity();
//queryEntity.VIPID = key;
queryEntity.Page = pageIndex;
queryEntity.PageSize = PageSize;
if (Request("Gender") != "" && Request("Gender") != "--请选择--")
{
queryEntity.Gender = FormatParamValue(Request("Gender"));
}
if (Request("UserName") != "")
{
queryEntity.UserName = FormatParamValue(Request("UserName"));
}
if (Request("Enterprice") != "")
{
queryEntity.Enterprice = FormatParamValue(Request("Enterprice"));
}
if (Request("IsChainStores") != "")
{
queryEntity.IsChainStores = FormatParamValue(Request("IsChainStores"));
}
if (Request("IsWeiXinMarketing") != "" && Request("IsWeiXinMarketing") != "--请选择--")
{
queryEntity.IsWeiXinMarketing = FormatParamValue(Request("IsWeiXinMarketing"));
}
if (Request("EventId") != "")
{
queryEntity.EventId = FormatParamValue(Request("EventId"));
}
if (Request("tags") != "") // 标签及组合关系
{
queryEntity.Tags = FormatParamValue(Request("tags"));
}
//var data = service.SearchVipInfo(queryEntity);
var data = marketSignInBLL.WebGetListAdd(queryEntity);
var dataTotalCount = data.ICount;
content = string.Format("{{\"totalCount\":{1},\"topics\":{0}}}",
data.vipInfoList.ToJSON(),
dataTotalCount);
return(content);
}
