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 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 Create(dynamic requestData)
{
LogHelper.WriteMsgByDay("BanKuaiController-Create-log:" + requestData.ToString());
ReturnJson r = new ReturnJson();
string bankuainame = requestData.BanKuaiName;
if (bankuainame.Length > 0 && bankuainame.Length <= 14)
{
string sql = string.Format(@"SELECT * FROM dbo.CpkBanKuai WHERE BanKuaiName = '{0}'", bankuainame);
int count = SqlHelper2.GetCountByCountSql(sql);
if (count == 0)
{
sql = string.Format(@"INSERT INTO dbo.CpkBanKuai( BanKuaiName )VALUES ('{0}')", bankuainame);
dataContext.ExecuteNonQuery(CommandType.Text, sql);
r.status = "succ";
r.message = "创建成功";
}
else
{
r.message = "该板块已存在";
}
}
else if (bankuainame.Length > 14)
{
r.message = "板块名称长度已超过14";
}
else
{
r.message = "BanKuaiName不能为空";
}
return(JsonConvert.SerializeObject(r));
}
/// <summary>
/// 查询列表(分页)
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="dbParameter">参数</param>
/// <param name="pagination">分页数据</param>
/// <returns></returns>
public DataTable FindTable(string strSql, object dbParameter, Pagination pagination)
{
int total = pagination.records;
pagination.sidx = SqlHelper2.SqlFilters(pagination.sidx);
DataTable data;
if (dbWhere != null)
{
int orderIndex = strSql.ToUpper().IndexOf("ORDER BY");
if (orderIndex > 0)
{
strSql = strSql.Substring(0, orderIndex);
string orderString = strSql.Substring(orderIndex);
strSql = string.Format(" select * From ({0})t Where {1} {2} ", strSql, dbWhere.sql, orderString);
}
else
{
strSql = string.Format(" select * From ({0})t Where {1} ", strSql, dbWhere.sql);
}
DynamicParameters dynamicParameters = SqlHelper.FieldValueParamToParameter(dbWhere.dbParameters);
dynamicParameters.AddDynamicParams(dbParameter);
data = db.FindTable(strSql, dynamicParameters, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total);
}
else
{
data = db.FindTable(strSql, dbParameter, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total);
}
pagination.records = total;
return(data);
}
/// <summary>
/// 分页导航获取总页数
/// </summary>
/// <param name="strwhere">查询条件</param>
/// <param name="tblName">表、视图</param>
/// <returns>Total：字段名（总的记录数）</returns>
public static DataTable GetDataTable(string strwhere, string tblName)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where)
new SqlParameter("@tblName", tblName), //表名
new SqlParameter("@doCount", 1) //返回记录总数,非 0 值则返回
};
return(SqlHelper2.ExecuteTable(CommandType.StoredProcedure, "SP_Pagination", param));
}
public string CreateVisitor(dynamic requestData)
{
RJson r = new RJson();
string telephone = requestData.Phone;
string openid = requestData.OpenId;
string jobType = requestData.JobType;
if (string.IsNullOrEmpty(telephone) || string.IsNullOrEmpty(openid) || string.IsNullOrEmpty(jobType))
{
r.message = "参数不能为空";
return(JsonConvert.SerializeObject(r));
}
try
{
RegistMember member = new RegistMember();
member.MemberTelePhone = telephone;
member.MemberState = 1;
member.RegistDate = DateTime.Now;
member.TotalIntegral = 0;
member.LeaveIntegral = 0;
member.IsEnable = 0;
member.Remark = requestData.Remark;
db.RegistMember.Add(member);
db.SaveChanges();
#region 存入用户OpenId
OpenIdAssociated openbase = new OpenIdAssociated();
openbase.OpenId = requestData.OpenId;
openbase.UserId = member.MemberId;
openbase.UserType = 2;
openbase.Nickname = requestData.Nickname;
openbase.HeadImgUrl = requestData.HeadImgUrl;
openbase.CreateDate = DateTime.Now;
db.OpenIdAssociated.Add(openbase);
db.SaveChanges();
#endregion
//当新增完会员之后 在会员简历表里同步新增一条数据
var sql = string.Format(@"INSERT INTO MemberProfile (MemberId ,JobType) VALUES ({0},'{1}')
", member.MemberId, jobType);
SqlHelper2.ExecuteNonQuery(CommandType.Text, sql);
r.result_status = "succ";
r.data = member.MemberId.ToString();
}
catch (Exception ex)
{
r.message = ex.Message.ToString();
}
return(JsonConvert.SerializeObject(r));
}
public string TestGetRedPack(string openid = "0")
{
//RedPack.CreateRedPack(2,"2018-08") ;
//var a = RedPack.GetMoneys();
//var a = RedPack.GetMoneys2();
string nowday = DateTime.Now.ToString("yyyy-MM-dd");
string nowmonth = DateTime.Now.ToString("yyyy-MM");
if (string.IsNullOrEmpty(openid))
{
for (int s = 51; s <= 52; s++)
{
openid = "xjy" + s.ToString();
for (int i = 1; i <= 10; i++)
{
string 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);
var money = RedPack.GetMoney(nowdayscan, openid, nowmonth);
//增加扫描红包记录
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}') ;
", openid, money, "", nowmonth, nowdayscan == 0 ? 1 : 0);
SqlHelper2.ExecuteNonQuery(CommandType.Text, sql);
}
}
}
else
{
string 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);
var money = RedPack.GetMoney(nowdayscan, openid, nowmonth);
//增加扫描红包记录
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}') ;
", openid, money, "", nowmonth, nowdayscan == 0 ? 1 : 0);
SqlHelper2.ExecuteNonQuery(CommandType.Text, sql);
}
return("");
}
/// <summary>
/// 查询列表(分页)
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="condition">表达式</param>
/// <param name="pagination">分页数据</param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, Pagination pagination) where T : class, new()
{
int total = pagination.records;
pagination.sidx = SqlHelper2.SqlFilters(pagination.sidx);
if (string.IsNullOrEmpty(pagination.sidx))
{
pagination.sidx = "";
pagination.sord = "asc";
}
var data = db.FindList <T>(condition, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total);
pagination.records = total;
return(data);
}
/// <summary>
/// 分页数据库操作函数
/// </summary>
/// <param name="strwhere">查询条件(注意: 不要加where)</param>
/// <param name="tblName">表名</param>
/// <param name="OrderType">设置排序类别,非 0 值则降序</param>
/// <param name="fldName">排序的字段名</param>
/// <param name="strGetFields">需要返回的列</param>
/// <param name="PageSize">页尺寸</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">,[0:返回查询的表数据,非0:值则返回记录总数]</param>
/// <returns>返回一个表</returns>
public static DataTable GetDataTable(string strwhere, string tblName, int OrderType, string fldName, string strGetFields, int PageSize, int PageIndex, int doCount)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where)
new SqlParameter("@tblName", tblName), //表名
new SqlParameter("@OrderType", OrderType), //设置排序类别,非 0 值则降序
new SqlParameter("@fldName", fldName), //排序的字段名
new SqlParameter("@strGetFields", strGetFields), //需要返回的列
new SqlParameter("@PageSize", PageSize), //页尺寸
new SqlParameter("@PageIndex", PageIndex), //页码
new SqlParameter("@doCount", doCount) //返回记录总数,非 0 值则返回
};
return(SqlHelper2.ExecuteTable(CommandType.StoredProcedure, "SP_Pagination", param));
}
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());
}
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));
}
/// <summary>
/// 分页函数,执行SP_Pagination3分页操作
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="strGetFields">需要返回的列</param>
/// <param name="fldName">排序的字段名</param>
/// <param name="PageSize">页尺寸</param>
/// <param name="PageIndex">页码</param>
/// <param name="strwhere">查询条件(注意: 不要加where)</param>
/// <returns></returns>
public static DataTable GetDataTableOfRow_Number(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, string strwhere)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@tblName", tblName), //表名
new SqlParameter("@strGetFields", strGetFields), //需要返回的列
new SqlParameter("@fldName", fldName), //排序的字段名
new SqlParameter("@PageSize", PageSize), //页尺寸
new SqlParameter("@PageIndex", PageIndex), //页码
new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where)
};
//SqlParameter parm1 = new SqlParameter("@WebName", SqlDbType.Image);
//parm1.Value = imageb;
return(SqlHelper2.ExecuteTable(CommandType.StoredProcedure, "SP_Pagination3", param));
}
/// <summary>
/// 把64服务器上byjiang数据库中的教师同步到本地Base_Teacher表中
/// </summary>
/// <returns></returns>
public int SynochroTeacher()
{
SqlHelper2 sh2 = new SqlHelper2();
sh2.Open();
Synchro64DAL sy64 = new Synchro64DAL(sh2);
DataTable dt = sy64.SyncTeacher();
SqlHelper sh = new SqlHelper();
sh.Open();
DeleteOldDAL dlt = new DeleteOldDAL(sh);
dlt.DeleteBase_Teacher();
InsertNewDAL insertNewDAL = new InsertNewDAL(sh);
int res = 0;
foreach (DataRow row in dt.Rows)
{
TeacherModel model = new TeacherModel();
model.Term = row.Value("Term", "");
model.UserID = row.Value("UserID", "");
model.UserName = row.Value("UserName", "");
model.Gender = row.Value("Gender", "");
model.SchoolID = row.Value("SchoolID", "");
model.SchoolName = row.Value("SchoolName", "");
model.SubjectIDs = row.Value("SubjectIDs", "");
model.SubjectNames = row.Value("SubjectNames", "");
//model.TS = row.Value("TS", DateTime.Now);
res = insertNewDAL.InsertTeacher(model);
}
sh2.Close();
sh.Close();
return(res);
}
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 AddLiuYanInfo(dynamic requestData)
{
LogHelper.WriteMsgByDay("WeiXinController-AddLiuYanInfo-log:" + requestData.ToString());
string keyType = requestData.KeyType;
string keyId = requestData.KeyId;
string parentId = requestData.ParentId;
string liuYan = requestData.LiuYan;
string openId = requestData.OpenId;
if (string.IsNullOrEmpty(openId) || string.IsNullOrEmpty(liuYan) ||
string.IsNullOrEmpty(keyId) || string.IsNullOrEmpty(keyType))
{
return("参数不能为空");
}
else
{
string sql = string.Format(@"INSERT INTO dbo.CpkLiuYan
( ParentId ,KeyId ,KeyType ,OpenId , LiuYan )
VALUES ({0},{1},'{2}','{3}','{4}')", parentId, keyId, keyType, openId, liuYan);
SqlHelper2.ExecuteNonQuery(CommandType.Text, sql);
return("succ");
}
}
/// <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 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));
}
/// <summary>
/// 查询列表(分页)
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="strSql">SQL语句</param>
/// <param name="dbParameter">参数</param>
/// <param name="pagination">分页数据</param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(string strSql, object dbParameter, Pagination pagination) where T : class
{
int total = pagination.records;
pagination.sidx = SqlHelper2.SqlFilters(pagination.sidx);
if (string.IsNullOrEmpty(pagination.sidx))
{
pagination.sidx = "";
pagination.sord = "asc";
}
if (dbWhere != null)
{
int orderIndex = strSql.ToUpper().IndexOf("ORDER BY");
if (orderIndex > 0)
{
strSql = strSql.Substring(0, orderIndex);
string orderString = strSql.Substring(orderIndex);
strSql = string.Format(" select * From ({0})t Where {1} {2} ", strSql, dbWhere.sql, orderString);
}
else
{
strSql = string.Format(" select * From ({0})t Where {1} ", strSql, dbWhere.sql);
}
DynamicParameters dynamicParameters = SqlHelper.FieldValueParamToParameter(dbWhere.dbParameters);
dynamicParameters.AddDynamicParams(dbParameter);
var data = db.FindList <T>(strSql, dynamicParameters, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total);
pagination.records = total;
return(data);
}
else
{
var data = db.FindList <T>(strSql, dbParameter, pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total);
pagination.records = total;
return(data);
}
}
//-------------------------使用用户创建的事务
public static int ExecuteNonQuery(SqlTransaction trans, string procName, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteNonQuery(trans, CommandType.StoredProcedure, procName, parms));
}
public static SqlDataReader Select(SqlTransaction trans, string procName, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteReader(trans, CommandType.StoredProcedure, procName, parms));
}
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));
}
public static SqlDataReader Select(SqlTransaction trans, string sql, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteReader(trans, CommandType.Text, sql, parms));
}
//-------------------------使用用户创建的事务
public static int ExecuteNonQuery(SqlTransaction trans, string sql, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteNonQuery(trans, CommandType.Text, sql, parms));
}
public static SqlDataReader Select(SqlConnection conn, string sql, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteReader(conn, CommandType.Text, sql, parms));
}
//-------------------------使用用户创建的connection
public static int ExecuteNonQuery(SqlConnection conn, string sql, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteNonQuery(conn, CommandType.Text, sql, parms));
}
//-------------------------使用用户创建的connection
public static int ExecuteNonQuery(SqlConnection conn, string procName, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteNonQuery(conn, CommandType.StoredProcedure, procName, parms));
}
public Synchro64DAL(SqlHelper2 db) : base(db)
{
}
public string CreateOrEdit(dynamic requestData)
{
LogHelper.WriteMsgByDay("ZhuanTiController-CreateOrEdit-log:" + requestData.ToString());
ReturnJson r = new ReturnJson();
int zhuantiid = requestData.ZhuanTiId; //专题id,新建时为0,编辑时为实际id
string action = requestData.Action; //接口类型 分Create Edit
string zhuantiname = requestData.ZhuanTiName; //专题名称
var contentList = requestData.ContentList; //专题内容列表
if (string.IsNullOrEmpty(zhuantiname))
{
r.message = "专题名称不能为空";
}
else if (zhuantiname.Length > 14)
{
zhuantiname = zhuantiname.Substring(0, 14);
}
else
{
string sql = "";
sql = string.Format("SELECT 1 FROM dbo.CpkZhuanTi WHERE ZhuanTiName = '{0}' ", zhuantiname);
int c = dataContext.GetCount(CommandType.Text, sql);
if (action == "Create")
{
if (c > 0) //判断是否存在
{
r.message = "该专题名称已存在";
}
}
else
{
if (c > 1) //判断是否存在
{
r.message = "该专题名称已存在";
}
}
if (string.IsNullOrEmpty(r.message)) //以上验证都通过时
{
//现将该专题已存在的内容及菜品关系 逻辑删除
sql = string.Format(@"UPDATE dbo.CpkZhuanTiContent SET IsEnable = 0 WHERE ZhuanTiId ={0}", zhuantiid);
SqlHelper2.ExecuteNonQuery(CommandType.Text, sql);
foreach (var item in contentList) //循环内容列表
{
string content = item["Content"].ToString().Replace("\"", "'"); //富文本内容
string caiPinId = item["CaiPinId"]; //内容关联的菜品
//LogHelper.WriteMsgByDay("CaiPinController-content-log:" + content);
if (content.IndexOf("base64") > 0)
{
foreach (string a in ImageHandle.GetHtmlImageUrlList(content))
{
//LogHelper.WriteMsgByDay("CaiPinController-string a-log:" + a);
//LogHelper.WriteMsgByDay("CaiPinController-Common.ImageHandle.DNS-log:" + Common.ImageHandle.DNS);
if (a.IndexOf(Common.ImageHandle.DNS) < 0)
{
string[] asplit = a.Split(',');
//LogHelper.WriteMsgByDay("CaiPinController-asplit-log:" + asplit.ToString());
string imgtype = asplit[0].Substring(11, asplit[0].Length - 18);
//LogHelper.WriteMsgByDay("CaiPinController-imgtype-log:" + imgtype);
string filepath = Common.ImageHandle.Base64StringToImage(asplit[1], imgtype, "/Images");
content = content.Replace(a, filepath);
}
}
}
//新增编辑都是如下逻辑,因为编辑时上面已逻辑删除之前的内容
//新增专题基本信息
CpkZhuanTi m = new CpkZhuanTi();
m.ZhuanTiId = zhuantiid;
m.ZhuanTiName = zhuantiname;
m.UpdateDate = DateTime.Now;
db.CpkZhuanTi.AddOrUpdate(m); //根据zhuantiid新增或者编辑
db.SaveChanges();
zhuantiid = m.ZhuanTiId;
//新增专题内容
CpkZhuanTiContent mContent = new CpkZhuanTiContent();
mContent.ZtContentId = 0;
mContent.ZhuanTiId = zhuantiid;
mContent.Content = content;
db.CpkZhuanTiContent.AddOrUpdate(mContent);
db.SaveChanges();
// sql = string.Format(@"INSERT INTO dbo.CpkZhuanTiContent
// ( ZhuanTiId , Content)
// VALUES ( {0} , '{1}' ); select SCOPE_IDENTITY() ", zhuantiid, content);
// int ztContentId = Convert.ToInt32(SqlHelper2.ExecuteScalar(CommandType.Text, sql));
int ztContentId = mContent.ZtContentId;
//新增专题内容关联的菜品
sql = string.Format(@"INSERT INTO dbo.CpkCpZtContentRelation
( ZtContentId , CaiPinId )
SELECT {0},CaiPinId FROM dbo.CpkCaiPinBasicInfo WHERE CaiPinId IN ({1});
DELETE FROM dbo.CpkZhuanTiContent WHERE ZhuanTiId ={2} AND IsEnable = 0
", ztContentId, caiPinId, zhuantiid);
SqlHelper2.ExecuteNonQuery(CommandType.Text, sql);
}
r.status = "succ";
r.message = action + "成功";
}
}
return(JsonConvert.SerializeObject(r));
}
public static SqlDataReader Select(string procName, params SqlParameter[] parms)
{
return(SqlHelper2.ExecuteReader(connStr, CommandType.StoredProcedure, procName, parms));
}