public string GetCaiPinCountBySecondId(string SecondId)
{
string sqlwhere = "";
if (!string.IsNullOrEmpty(SecondId))
{
sqlwhere = string.Format(" and c.SecondId IN ({0})", SecondId);
}
string sql = string.Format(@"SELECT COUNT(1)
FROM dbo.CpkCaiPinBasicInfo a
JOIN dbo.CpkCaiPinCategory b ON b.CaiPinId = a.CaiPinId
JOIN dbo.CpkSecondCategory c ON c.SecondId = b.SecondCategoryId
WHERE a.IsEnable =1 AND a.IsPublish =1 AND c.IsEnable = 1 AND c.IsPublish =1 {0} ", sqlwhere);
return(SqlHelper2.ExecuteScalar(CommandType.Text, sql).ToString());
}
/// <summary>
/// 验证抽奖规则
/// </summary>
/// <param name="openid"></param>
/// <param name="scode"></param>
/// <returns></returns>
private string CheckRule(string openid, string scode)
{
string msg = "fail,";
string sql = "";
//验证scode码是否可用
sql = string.Format(@"SELECT State FROM dbo.RedPackCode WHERE Code = '{0}' ", scode);
var a = SqlHelper2.ExecuteScalar(CommandType.Text, sql);
string nowday = DateTime.Now.ToString("yyyy-MM-dd");
//验证当天扫描是否超过10次
sql = string.Format("SELECT COUNT(1) FROM dboRedPackScanRecord WHERE OpenId ='{0}' AND ScanDate LIKE'%{1}%' ", openid, nowday);
int nowdayscan = SqlHelper2.GetCount(CommandType.Text, sql);
if (nowdayscan >= 10)
{
msg = "fail,当天扫描是已超过10次";
return(msg);
//r.message = "当天扫描是已超过10次";
//return JsonConvert.SerializeObject(r);
}
return("");
}
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));
}