307 lines
10 KiB
C#
307 lines
10 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using dccdc.Models;
|
|
using Dapper;
|
|
using System.Reflection;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class InfectionRedDal
|
|
{
|
|
|
|
public bool GetSendRedMoneyFlagById(string id)
|
|
{
|
|
string CheckCountSql = string.Format("select COUNT(1) from infection_check t where t.user_id = {0} and t.state = '1'", id);
|
|
string DaysSql = string.Format("select days from infection_red_money t where t.user_id = {0}", 1);
|
|
|
|
int CheckCount = 0;
|
|
int Days = 0;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
CheckCount = conn.ExecuteScalar<int>(CheckCountSql);
|
|
Days = conn.ExecuteScalar<int>(DaysSql);
|
|
}
|
|
|
|
if (CheckCount >= Days)
|
|
{
|
|
return true;
|
|
}
|
|
else {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public int UpdateCheckCountBy(string id, string days)
|
|
{
|
|
string UpdateSql = string.Format("update infection_check set state = '2' where user_id = {0} and state = '1' and id in ", id);
|
|
UpdateSql += string.Format("(select TOP {0} id from infection_check where user_id = {1}' and state = '1' order by check_date)", days, id);
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Execute(UpdateSql);
|
|
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 获取红包信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public List<InfectionRedModel> GetRedList()
|
|
{
|
|
try
|
|
{
|
|
string sql = "select r.*, u.openid,u.nickname, u.sex from infection_red r left join infection_open_user u on u.id = r.user_id";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionRedModel>(sql).ToList();
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return null;
|
|
}
|
|
|
|
}
|
|
/// <summary>
|
|
/// 添加签到红包
|
|
/// </summary>
|
|
/// <param name="model"></param>
|
|
/// <returns></returns>
|
|
public int InsertRed(InfectionRedModel model)
|
|
{
|
|
try
|
|
{
|
|
string sql = "insert infection_red (openid,phone,sum,state,create_time,type,mch_billno) values(@openid,@phone,@sum,@state,@create_time,@type,@mch_billno) Select @@Identity";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, model);
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
}
|
|
/// <summary>
|
|
/// 修改提现状态
|
|
/// </summary>
|
|
/// <param name="id"></param>
|
|
/// <returns></returns>
|
|
public int UpdateState(string id,string send_listid)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = string.Format("update infection_red set state = 1 ,mch_billno={0} where id ={1} ", send_listid, id);
|
|
log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType).Info("sql=====================" + sql);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Execute(sql);
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据时间和类型分页查询数据
|
|
/// </summary>
|
|
/// <param name="page"></param>
|
|
/// <param name="pagesize"></param>
|
|
/// <param name="start"></param>
|
|
/// <param name="end"></param>
|
|
/// <param name="type"></param>
|
|
/// <returns></returns>
|
|
public List<InfectionRedModel> GetDataByTypeTime(int page, int pagesize, string start, string end, string type,string status)
|
|
{
|
|
string sql = " Select oui.name,oui.ident,ou.nickname,r.type,r.sum,r.state,r.status,r.create_time,r.phone,r.receive_date,Row_number() Over(Order By r.create_time Desc) As rownum From infection_red r Left Join infection_open_user ou on r.openid=ou.openid Left Join infection_open_user_info oui on ou.id = oui.user_id Where 1=1 ";
|
|
if (!string.IsNullOrEmpty(start))
|
|
{
|
|
sql += " And r.create_time >= '" + start + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(end))
|
|
{
|
|
sql += " And r.create_time <= '" + end + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(type))
|
|
{
|
|
sql += " And r.type = " + type;
|
|
}
|
|
if (!string.IsNullOrEmpty(status))
|
|
{
|
|
sql += " And r.status = " + status;
|
|
}
|
|
sql = "Select * From (" + sql + ") t Where t.rownum>(" + page + "-1)*" + pagesize + " And t.rownum<=" + page + "*" + pagesize;
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionRedModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据时间和类型获取数据总数
|
|
/// </summary>
|
|
/// <param name="start"></param>
|
|
/// <param name="end"></param>
|
|
/// <param name="type"></param>
|
|
/// <returns></returns>
|
|
public int GetDataCount(string start, string end, string type,string status)
|
|
{
|
|
string sql = "Select count(1) From infection_red Where 1=1 ";
|
|
if (!string.IsNullOrEmpty(start))
|
|
{
|
|
sql += " And create_time >= '" + start + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(end))
|
|
{
|
|
sql += " And create_time <= '" + end + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(type))
|
|
{
|
|
sql += " And type = " + type;
|
|
}
|
|
if (!string.IsNullOrEmpty(status))
|
|
{
|
|
sql += " And status = " + status;
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取红包信息
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public List<InfectionRedModel> GetUnreceived()
|
|
{
|
|
try
|
|
{
|
|
string sql = "select * from infection_red where state=1 and datediff(day,create_time,getdate())<= 3 and datediff(day,create_time,getdate())>= 0 ";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionRedModel>(sql).ToList();
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return null;
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改领取状态
|
|
/// </summary>
|
|
/// <param name="id"></param>
|
|
/// <returns></returns>
|
|
public int UpdateStatus(int id, int status,string receive_date)
|
|
{
|
|
try
|
|
{
|
|
string sql = "";
|
|
if (string.IsNullOrEmpty(receive_date))
|
|
{
|
|
sql = string.Format("update infection_red set status = {0} where id = {1} ", status, id);
|
|
}
|
|
else {
|
|
sql = string.Format("update infection_red set status = {0} , receive_date= '{1}' where id = {2} ", status, receive_date, id);
|
|
}
|
|
log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType).Info("sql================="+sql);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Execute(sql);
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
}
|
|
|
|
//问卷调查
|
|
public int getMonthOne()
|
|
{
|
|
string sql = "select count(*) from infection_red where type=2 ";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
//签到
|
|
public int getMonthTwo()
|
|
{
|
|
string sql = "select count(*) from infection_red where type=1 ";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
//总数
|
|
public int getAllOne()
|
|
{
|
|
string sql = "select count(*) from infection_red ";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 检查今天红包
|
|
/// </summary>
|
|
/// <param name="model"></param>
|
|
/// <returns></returns>
|
|
public int redToday(string openid)
|
|
{
|
|
try
|
|
{
|
|
string sql = "select count(*) from infection_red where CONVERT(varchar(10),create_time,120)=CONVERT(varchar(10),GETDATE(),120) and type=1 and openid='" + openid+"'";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 检查所有今天红包
|
|
/// </summary>
|
|
/// <param name="model"></param>
|
|
/// <returns></returns>
|
|
public double redAllToday()
|
|
{
|
|
try
|
|
{
|
|
string sql = "select isnull(sum(sum),0) from infection_red where type=1 and CONVERT(varchar(10),create_time,120)=CONVERT(varchar(10),GETDATE(),120) ";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
}
|