public string GetLiuYanReplyInfo(dynamic requestData)
{
LogHelper.WriteMsgByDay("WeiXinController-GetLiuYanReplyInfo-log:" + requestData.ToString());
int page = requestData.page;
int pagesize = requestData.pagesize;
string keyType = requestData.KeyType;
string keyId = requestData.KeyId;
string parentId = requestData.ParentId;
if (string.IsNullOrEmpty(keyType) || string.IsNullOrEmpty(keyId) || string.IsNullOrEmpty(parentId))
{
return("参数不能为空");
}
else
{
string sql = string.Format(@"SELECT row_number() over(order BY CreateDate) RowId,t.*
FROM ( SELECT a.Id,a.KeyId,a.OpenId,a.LiuYan, c.Nickname,c.HeadImgUrl,r.HotelName, a.CreateDate,COUNT(b.Id) ReplyCount
FROM CpkLiuYan a
LEFT JOIN CpkLiuYan b ON a.Id=b.ParentId
JOIN dbo.OpenIdAssociated c ON c.OpenId = a.OpenId
JOIN dbo.RegistMember r ON r.MemberId = c.UserId AND c.UserType =2
WHERE a.ParentId={0} AND a.KeyId= {1} AND a.KeyType = '{2}'
GROUP BY a.Id,a.KeyId,a.OpenId,a.LiuYan ,a.CreateDate,c.Nickname,c.HeadImgUrl,r.HotelName
) t", parentId, keyId, keyType);
String sqlpage = PageHelper.GetPagerSql(page, pagesize, sql); //分页sql
var data = SqlHelper2.ExecuteDataTable(sqlpage); //分页的数据
var totalcount = SqlHelper2.GetCountByNormalSql(sql); //总条数
var toatlpage = PageHelper.GetTotalPage(totalcount, pagesize);
return(JsonConvert.SerializeObject(data));
}
}
public string GetGuiGeListByProductId(int ProductId = 0, string OpenId = "")
{
string sql = string.Format(@"SELECT s.SpecificationId, s.ProductId, s.Amount, s.Unit, PKI.ProductName
, s.SpecificationPicUrl, s.VisitCount, PKI.ProductFirstId, PKI.ProductSecondId
, CASE
WHEN L.Id IS NULL THEN 'f'
ELSE 't'
END AS IsPraise, Display.IsDisplay
FROM Specification s
JOIN ProductKuInfo PKI ON s.ProductId = PKI.ProductId
LEFT JOIN (
SELECT Id,SpecificationId
FROM SpecificationPraiseLog
WHERE OpenId = '{0}'
) L ON L.SpecificationId = s.SpecificationId
LEFT JOIN SpecificationConf sConf ON s.SpecificationId = sConf.SpecificationId
LEFT JOIN (
SELECT SpecificationConfId, SUM(IsDisplay) AS IsDisplay
FROM (
SELECT SpecificationConfId
, CASE IsDisplay
WHEN 'true' THEN 1
ELSE 0
END AS IsDisplay
FROM SpecificationConfVC
) A
GROUP BY SpecificationConfId
) Display
ON sConf.SpecificationConfId = Display.SpecificationConfId
WHERE Display.IsDisplay > 0 AND PKi.ProductId ={1}", OpenId, ProductId);
var r = SqlHelper2.ExecuteDataTable(sql);
return(JsonConvert.SerializeObject(r));
}
public string GetHistory(string openid)
{
RHistoryJson r = new RHistoryJson();
try
{
if (string.IsNullOrEmpty(openid))
{
r.message = "参数有误";
}
else
{
string sql = string.Format(@"SELECT * FROM RedPackScanRecord WHERE OpenId = '{0}'", openid);
DataTable dt = SqlHelper2.ExecuteDataTable(sql);
r.haveTxMoney = Convert.ToDouble(dt.Compute("Sum(Money)", "IsPay=1").ToString() == "" ? 0 : dt.Compute("Sum(Money)", "IsPay=1")); //已提现金额
r.kTxMoney = Convert.ToDouble(dt.Compute("Sum(Money)", "IsPay=0").ToString() == "" ? 0 : dt.Compute("Sum(Money)", "IsPay=0")); //还没有进行提现的金额
//获取用户扫描但是未提现的金额总和
// string sql = string.Format(@"SELECT ISNULL(SUM(Money),0) Total
// FROM dbo.RedPackScanRecord WHERE OpenId = '{0}' AND IsPay = 0 ", openid);
// r.kTxMoney = Convert.ToDouble(SqlHelper2.ExecuteScalar(CommandType.Text, sql));
if (r.kTxMoney > 1)
{
r.isKeTiXian = 1;
}
//获取用户扫描 提现记录
sql = string.Format(@"SELECT * FROM (
SELECT Money Money,CONVERT(VARCHAR(30),ScanDate,121) CreateDate,'+' Action FROM dbo.RedPackScanRecord WHERE OpenId = '{0}'
UNION
SELECT PayAmout Money,CONVERT(VARCHAR(30),PayDate,121) CreateDate,'-' Action FROM dbo.RedPackPayRecord WHERE OpenId = '{0}'
) t ORDER BY CreateDate", openid);
var q = SqlHelper2.ExecuteDataTable(sql);
r.data = q; //用户扫描 提现记录
r.isRegist = RedPack.GetIsRegist(openid); //是否注册用户 0未注册 1已注册
r.scanCount = dt.Rows.Count;
r.result_status = "succ"; //返回成功状态
}
}
catch (Exception ex)
{
r.message = "有异常";
RedPack.AddAlertLog(openid, ex.ToString(), "GetHistory-error");
}
return(JsonConvert.SerializeObject(r));
}
public string GetDyrq(string openid = "")
{
string sql = string.Format(@"SELECT TOP 3 a.CaiPinId,a.CaiPinName,a.Images,b.Content Sbly,
ISNULL(k.LlCount,0) LlCount, --浏览数量
CASE g.RecordAction WHEN 'collect' THEN 1 ELSE 0 END IsCollect ,--是否收藏
ISNULL(k.LlCount,0)*0.2+ISNULL(k.ScCount,0)*0.8 RQ ----当月人气计算逻辑 浏览量*0.2 + 收藏量*0.8
FROM
dbo.CpkCaiPinBasicInfo a
LEFT JOIN dbo.CpkCaiPinRichInfo b ON b.CaiPinId = a.CaiPinId AND b.TypeId = 1
LEFT JOIN ViewCpkTongji k ON a.CaiPinId = k.RecordKeyId AND k.RecordKeyType='菜品'
LEFT JOIN dbo.CpkTongJi g ON a.CaiPinId=g.RecordKeyId
AND g.RecordKeyType='菜品' AND g.RecordAction='collect' AND g.OpenId ='{0}'
WHERE CONVERT(VARCHAR(7),a.CreateDate,121) = CONVERT(VARCHAR(7),GETDATE(),121)
ORDER BY RQ DESC", openid);
var r = SqlHelper2.ExecuteDataTable(sql);
return(JsonConvert.SerializeObject(r));
}
public string Edit(dynamic requestData)
{
ReturnJson r = new ReturnJson();
int firstid = requestData.FirstId;
int secondid = requestData.SecondId;
string firstname = requestData.FirstName;
string secondname = requestData.SecondName;
if (!string.IsNullOrEmpty(firstname) && !string.IsNullOrEmpty(secondname))
{
// var sql = string.Format(@"UPDATE dbo.CpkFirstCategory SET FirstName = '{0}' WHERE FirstId = {1} ;
// UPDATE dbo.CpkSecondCategory SET SecondName = '{2}' WHERE SecondId = {3}", firstname, firstid, secondname, secondid);
// dataContext.ExecuteNonQuery(CommandType.Text, sql);
string sql = string.Format(@"SELECT FirstId FROM dbo.CpkFirstCategory WHERE FirstName = '{0}'", firstname);
DataTable dt = SqlHelper2.ExecuteDataTable(sql);
if (dt.Rows.Count > 0) //判断是否有该名称的一级分类
{
//sql = string.Format("SELECT COUNT(1) FROM dbo.CpkSecondCategory WHERE FirstId ={0} AND SecondName = '{1}'", dt.Rows[0]["FirstId"].ToString(), secondname);
//if (SqlHelper2.GetCountByCountSql(sql) > 0) //判断是否该一级分类下已存在该二级分类
//{
// r.message = "已存在改分类";
//}
sql = string.Format(@"UPDATE dbo.CpkSecondCategory SET SecondName = '{0}',FirstId ={1} WHERE SecondId = {2}", secondname, dt.Rows[0]["FirstId"].ToString(), secondid);
dataContext.ExecuteNonQuery(CommandType.Text, sql);
r.message = "更新成功";
r.status = "succ";
}
else
{
r.message = "不存在该名称的一级分类";
}
}
else
{
r.message = "分类名称不能为空";
}
return(JsonConvert.SerializeObject(r));
}
public string Index(string openid = "")
{
string sql = string.Format(@"select BanKuaiId,BanKuaiName,CaiPinId,HotelLongitude,HotelAtitude,CaiPinName,Images,Max(LlCount) LlCount,MAX(Content) Sbly,
VideoImage,VideoUrl,UpdateDate ,IsCollect
FROM (select *,ROW_NUMBER() over(partition by BanKuaiId order by UpdateDate desc) rowNum
from (SELECT a.CaiPinId,a.HotelLongitude,a.HotelAtitude, a.CaiPinName,a.Images,a.VideoImage,a.VideoUrl,a.UpdateDate,c.BanKuaiId,c.BanKuaiName,
ISNULL(k.LlCount,0) LlCount,r.Content, CASE g.RecordAction WHEN 'collect' THEN 1 ELSE 0 END IsCollect ,'' PCaiPuUrl
FROM dbo.CpkCaiPinBasicInfo a
JOIN dbo.CpkCpBkRelation b ON b.CaiPinId = a.CaiPinId AND b.IndexShow = 1
JOIN dbo.CpkBanKuai c ON c.BanKuaiId = b.BanKuaiId
LEFT JOIN dbo.CpkCaiPinRichInfo r ON r.CaiPinId = a.CaiPinId AND r.TypeId = 4 -- TypeId 类别在CpkRichTextType中
LEFT JOIN ViewCpkTongji k ON a.CaiPinId = k.RecordKeyId AND k.RecordKeyType='菜品'
LEFT JOIN dbo.CpkTongJi g ON a.CaiPinId=g.RecordKeyId
AND g.RecordKeyType='菜品' AND g.RecordAction='collect' AND g.OpenId ='{0}'
WHERE a.IsEnable =1 AND a.IsPublish =1 AND c.IsEnable =1 AND c.IsPublish =1 )
t ) as s
WHERE s.rowNum <= 6
GROUP by BanKuaiId,HotelLongitude,HotelAtitude,BanKuaiName,CaiPinId,CaiPinName,Images,VideoImage,VideoUrl,UpdateDate,IsCollect
ORDER by BanKuaiId,UpdateDate desc
", openid);
var r = SqlHelper2.ExecuteDataTable(sql);
return(JsonConvert.SerializeObject(r));
}
public async Task <string> ScanPressureTest(dynamic requestData)
{
RScanJson r = new RScanJson();
string kaiGuan = ConfigurationManager.AppSettings["kaiguan"];
//if (kaiGuan == "ON")
//{
string openid = requestData.OpenId;
string scode = requestData.Scode;
try
{
if (string.IsNullOrEmpty(openid) || string.IsNullOrEmpty(scode))
{
r.message = "参数有误";
}
else
{
#region 1.判断是否注册，是否是队员
int isRegist = RedPack.GetIsRegist(openid);
if (isRegist == 1)
{
r.isRegist = 1;
}
else if (isRegist == -1) //表示是队员 返回
{
r.isRegist = 0;
r.message = "队员不能扫码";
return(JsonConvert.SerializeObject(r));
}
#endregion
#region 2.获取用户扫描但是未提现的金额总和
string sql = "";
sql = string.Format(@"SELECT ISNULL(SUM(Money),0) Total
FROM dbo.RedPackScanRecord WHERE OpenId = '{0}' AND IsPay = 0 ", openid);
r.kTxMoney = Convert.ToDouble(SqlHelper2.ExecuteScalar(CommandType.Text, sql));
if (r.kTxMoney > 1)
{
r.isKeTiXian = 1;
}
#endregion
//当天
string nowDay = DateTime.Now.ToString("yyyy-MM-dd");
//当月
string nowMonth = DateTime.Now.ToString("yyyy-MM");
//string a = CheckRule(openid, scode);
//验证二维码是否有效
sql = string.Format(@"SELECT a.IsScan,b.Money,convert(varchar(19),b.ScanDate,121) ScanDate
FROM dbo.RedPackCode a LEFT JOIN dbo.RedPackScanRecord b ON a.Code=b.ScanCode
WHERE a.Code = '{0}' ", scode);
DataTable dt = SqlHelper2.ExecuteDataTable(sql);
if (dt.Rows.Count == 0)
{
r.code_status = "非活动码";
r.message = "此码不属于本次活动";
RedPack.AddAlertLog(openid, scode + ":" + r.message, "scan");
}
//配合压测 mark掉
//else if (Convert.ToInt16(dt.Rows[0]["IsScan"]) == 1)
//{
// r.code_status = "已被扫";
// r.money = Convert.ToDouble(dt.Rows[0]["Money"]);
// r.scan_date = dt.Rows[0]["ScanDate"].ToString();
// r.message = "该二维码已被扫描";
// RedPack.AddAlertLog(openid, scode + ":" + r.message, "scan");
//}
else
{
//验证当天扫描是否超过10次
sql = string.Format("SELECT COUNT(1) FROM RedPackScanRecord WHERE OpenId ='{0}' AND CONVERT(VARCHAR(10),ScanDate,121) = '{1}' ", openid, nowDay);
int nowDayScan = SqlHelper2.GetCount(CommandType.Text, sql);
if (nowDayScan >= 10)
{
r.message = "当天扫描是已超过10次";
RedPack.AddAlertLog(openid, r.message, "scan");
}
else
{
sql = string.Format("SELECT COUNT(1) FROM RedPackScanRecord WHERE OpenId ='{0}' ", openid);
int allScan = SqlHelper2.GetCount(CommandType.Text, sql);
//去抽红包
double money = RedPack.GetMoney(allScan, openid, nowMonth);
#region 增加扫描红包记录
sql = string.Format(@"INSERT INTO dbo.RedPackScanRecord
( OpenId ,Money ,ScanCode ,IsPay ,PayId,IsFirst,ScanMonth)
VALUES ( '{0}' , -- OpenId - varchar(50)
{1} , -- Money - float
'{2}' , -- ScanCode - varchar(50)
0 , -- IsPay - int
0 , -- PayId - int
{4},'{3}') ;
UPDATE dbo.RedPackCode SET IsScan =1,UseDate = GETDATE() WHERE Code = '{2}';
UPDATE dbo.RedPackConfig SET LeiJiMoney = LeiJiMoney + {1} WHERE Month = '{3}'
", openid, money, scode, nowMonth, nowDayScan == 0 ? 1 : 0);
LogHelper.WriteMsg("sacn增加扫描红包记录-openid:" + openid + ",sql:" + sql);
SqlHelper2.ExecuteNonQuery(CommandType.Text, sql);
#endregion
r.scan_date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
r.money = money; //返回本次中奖金额
//获取用户扫描但是未提现的金额总和
sql = string.Format(@"SELECT ISNULL(SUM(Money),0) Total
FROM dbo.RedPackScanRecord WHERE OpenId = '{0}' AND IsPay = 0 ", openid);
r.kTxMoney = Convert.ToDouble(SqlHelper2.ExecuteScalar(CommandType.Text, sql));
if (r.kTxMoney > 1)
{
r.isKeTiXian = 1;
}
r.result_status = "succ"; //返回成功状态
}
}
}
}
catch (Exception ex)
{
r.message = "有异常";
RedPack.AddAlertLog(openid, ex.ToString(), "scan-error");
}
//}
//else
//{
// r.message = "活动现处于关闭状态,谢谢~";
//}
return(JsonConvert.SerializeObject(r));
}