public IList <ExperienceVoucher> GetExperienceVoucherList()
{
DynamicParameters paras = new DynamicParameters();
IList <ExperienceVoucher> list = DapperSqlHelper.FindToList <ExperienceVoucher>(@"select * from [dbo].[ExperienceVoucher] where VersionEndTime is null ", null, false);
return(list);
}
public IList <RecordsOfConsumption> GetRecordsOfConsumptionList()
{
DynamicParameters paras = new DynamicParameters();
IList <RecordsOfConsumption> list = DapperSqlHelper.FindToList <RecordsOfConsumption>(@"select * from [dbo].[RecordsOfConsumption]", null, false);
return(list);
}
public IList <CouponType> GetCouponTypeList()
{
DynamicParameters paras = new DynamicParameters();
IList <CouponType> list = DapperSqlHelper.FindToList <CouponType>(@"select * from [dbo].[CouponType] where Status=1", null, false);
return(list);
}
public IList <Task> GetTaskList()
{
DynamicParameters paras = new DynamicParameters();
IList <Task> list = DapperSqlHelper.FindToList <Task>(@"select * from [dbo].[Task] where VersionEndTime is null ", null, false);
return(list);
}
public IList <RechargeRecord> GetRechargeRecordList()
{
DynamicParameters paras = new DynamicParameters();
IList <RechargeRecord> list = DapperSqlHelper.FindToList <RechargeRecord>(@"select * from [dbo].[RechargeRecord]", null, false);
return(list);
}
public IList <UserTaskshow> GetUserTaskList(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <UserTaskshow> list = DapperSqlHelper.FindToList <UserTaskshow>(@"select u.* ,t.TasKName,t.TaskTarget,t.Reward,t.Integral,t.TaskCode from [dbo].[UserTask] u join [dbo].[Task] t on t.TaskCode=u.TaskCode where u.userCode=@userCode", paras, false);
return(list);
}
public IList <ProductList> GetProductList(string listCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@listCode", listCode, System.Data.DbType.String);
IList <ProductList> list = DapperSqlHelper.FindToList <ProductList>("select * from [dbo].[ProductList] where ListCode=@listCode and Status =1", paras, false);
return(list);
}
public IList <TopupOrder> GetTopupOrderbyuserCode(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <TopupOrder> userone = DapperSqlHelper.FindToList <TopupOrder>("select * from [dbo].[TopupOrder] where userCode=@userCode", paras, false);
return(userone);
}
public IList <Coupon> GetCouponList(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@UserCode", userCode, System.Data.DbType.String);
IList <Coupon> list = DapperSqlHelper.FindToList <Coupon>(@"select a.*,b.CouponTypeName from [dbo].[Coupon] a,[dbo].[CouponType] b where a.UserCode=@userCode and a.CouponTypeCode=b.CouponTypeCode", paras, false);
return(list);
}
public IList <MembershipScore> GetMembershipScoreListByUserCode(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <MembershipScore> list = DapperSqlHelper.FindToList <MembershipScore>(@"select * from [dbo].[MembershipScore] where UserCode=@userCode", paras, false);
return(list);
}
public IList <CouponShow> GetUserCoupon(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <CouponShow> takeEffectList = DapperSqlHelper.FindToList <CouponShow>("select * from [dbo].[Coupon] a,[dbo].[CouponType] b where a.UserCode=@userCode and a.CouponTypeCode=b.CouponTypeCode and isnull(a.IsUsed,0)=0 and isnull(a.VersionEndTime,'9999-9-9')>=getdate()", paras, false);
return(takeEffectList);
}
public IList <IntegralRecord> GetIntegralRecordByUserCode(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@UserCode", userCode, System.Data.DbType.String);
IList <IntegralRecord> list = DapperSqlHelper.FindToList <IntegralRecord>("select * from [dbo].[IntegralRecord] where UserCode=@UserCode", paras, false);
return(list);
}
public ListType GameCategoryShow(string code)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@productCode", code, System.Data.DbType.String);
string sql = @"SELECT b.ListTypeCode,b.ListTypeName FROM ProductList a, ListType b WHERE a.ListCode= b.ListTypeCode AND a.ProductCode= @productCode";
IList <ListType> list = DapperSqlHelper.FindToList <ListType>(sql, paras, false);
return(list.FirstOrDefault());
}
public List <string> GetTypeShow(string code)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@productCode", code, System.Data.DbType.String);
string sql = @"SELECT b.ProductTypeName FROM ProductTypeList a, ProductType b WHERE a.ProductTypeCode= b.ProductTypeCode AND a.ProductCode= @productCode";
IList <ProductType> list = DapperSqlHelper.FindToList <ProductType>(sql, paras, false);
return(list.Select(p => p.ProductTypeName).ToList());
}
public IList <RecordsOfConsumption> GetRecordsOfConsumptionListByUserCode(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@UserCode", userCode, System.Data.DbType.String);
IList <RecordsOfConsumption> list = DapperSqlHelper.FindToList <RecordsOfConsumption>(@"select * from [dbo].[RecordsOfConsumption]
where UserCode=@UserCode AND ISNULL(IsRecharging,0)=0 ", paras, false);
return(list);
}
public IList <ProductInfo> GetProductInfoList()
{
IList <ProductInfo> list = DapperSqlHelper.FindToList <ProductInfo>(@"SELECT a.ListTypeCode,a.ListTypeName,b.*
FROM [dbo].[ListType] a, [dbo].[ProductInfo] b,[dbo].[ProductList] c
WHERE a.ListTypeCode = c.ListCode AND c.ProductCode = b.ProductCode and isnull(b.VersionEndTime, '9999-09-09') > getdate() AND a.Status = 1 order by c.CreateTime desc ", null, false);
foreach (var p in list)
{
p.TypeShowList = GetTypeShow(p.ProductCode);
}
return(list);
}
public IList <Order> GetNOPayOrderList(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <Order> list = DapperSqlHelper.FindToList <Order>(@"select a.*,b.ProductCode,b.ProductName,b.Imageurl,b.videourl,
0 OrderState
from [dbo].[Order] a,[dbo].[ProductInfo] b where a.UserCode=@userCode
and b.ProductCode = a.ProductCode and isnull(a.PayTime,'') ='' ", paras, false);
return(list);
}
public IList <UseWallet> GetUseWallet(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <UseWallet> list = DapperSqlHelper.FindToList <UseWallet>("select * from [dbo].[UseWallet] where UserCode=@userCode and Status=1 order by Sort", paras, false);
foreach (var w in list)
{
w.TotalAmount = w.AccountPrincipal + w.DonationAmount;
}
return(list);
}
public IList <ProductInfo> GetProductInfoListByListCode(string listCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@listCode", listCode, System.Data.DbType.String);
IList <ProductInfo> list = DapperSqlHelper.FindToList <ProductInfo>(@"select a.ProductCode,a.ProductName,a.ExperiencePrice,a.Imageurl,a.TransverseImageUrl from [dbo].[ProductInfo] a ,[dbo].[ProductList] b where a.ProductCode=b.ProductCode and b.Status=1
and b.ListCode=@listCode and isnull(a.VersionEndTime,'9999-09-09')>getdate()", paras, false);
foreach (var p in list)
{
p.TypeShowList = GetTypeShow(p.ProductCode);
}
return(list);
}
public bool IsWriteOffUser(string phone)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@phone", phone, System.Data.DbType.String);
string sql = @"select ID as Code from [dbo].[TDF_SYSTEM_USER] where PHONE=@phone";
var list = DapperSqlHelper.FindToList <User>(sql, paras, false);
if (list.Count > 0)
{
return(true);
}
return(false);
}
public IList <Order> GetOrderList(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <Order> list = DapperSqlHelper.FindToList <Order>(@"select a.*,b.ProductCode,b.ProductName,b.Imageurl,b.videourl,
case when ISNULL(a.PayTime,'')='' then 0
WHEN ISNULL(a.PayTime,'')!='' AND
isnull(a.IsWriteOff,0)=1 THEN 1
WHEN ISNULL(a.PayTime,'')!='' AND
ISNULL(a.IsWriteOff,0)=0 THEN 2 end OrderState
from [dbo].[Order] a,[dbo].[ProductInfo] b where a.UserCode=@userCode
and b.ProductCode = a.ProductCode", paras, false);
return(list);
}
public object GetCoupon(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
//IList<Coupon> isUserList = DapperSqlHelper.FindToList<Coupon>("select * from [dbo].[Coupon] a,[dbo].[CouponType] b where a.UserCode=@userCode and a.CouponTypeCode=b.CouponTypeCode and a.IsUsed=1 ", paras, false);
IList <CouponShow> lapseList = DapperSqlHelper.FindToList <CouponShow>("select * from [dbo].[Coupon] a,[dbo].[CouponType] b where a.UserCode=@userCode and a.CouponTypeCode=b.CouponTypeCode and (a.IsUsed=1 or (isnull(a.VersionEndTime,'9999-9-9')<getdate()))", paras, false);
IList <CouponShow> takeEffectList = DapperSqlHelper.FindToList <CouponShow>("select * from [dbo].[Coupon] a,[dbo].[CouponType] b where a.UserCode=@userCode and a.CouponTypeCode=b.CouponTypeCode and isnull(a.IsUsed,0)=0 and isnull(a.VersionEndTime,'9999-9-9')>=getdate()", paras, false);
return(new
{
// isUserList= isUserList,
lapseList = lapseList,
takeEffectList = takeEffectList
});
}
public IList <RechargeTypeshow> GetRechargeRecordListByUserCode(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
IList <RechargeTypeshow> list = DapperSqlHelper.FindToList <RechargeTypeshow>(@" SELECT
CASE WHEN a.RechargeTypeCode='0' THEN '0' ELSE b.RechargeTypeCode END RechargeTypeCode,
CASE WHEN a.RechargeTypeCode='0' THEN '充值' ELSE b.RechargeTypeName END RechargeTypeName,
CASE WHEN a.RechargeTypeCode='0' THEN a.RecordsMoney ELSE b.Money END Money,
CASE WHEN a.RechargeTypeCode='0' THEN 0 ELSE b.DonationAmount END DonationAmount
,a.CreateTime
from [dbo].[RecordsOfConsumption] a LEFT JOIN [dbo].[RechargeType] b
ON a.RechargeTypeCode=b.RechargeTypeCode
AND b.Status=1 WHERE a.IsRecharging=1
AND a.UserCode=@userCode", paras, false);
return(list);
}
public bool checkCoupon(string userCode, string taskCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
paras.Add("@taskCode", taskCode, System.Data.DbType.String);
IList <CouponShow> lapseList = DapperSqlHelper.FindToList <CouponShow>(@"select * from [dbo].[Coupon] a
INNER JOIN dbo.CouponType ct ON ct.CouponTypeCode = a.CouponTypeCode
where a.UserCode = @userCode and IsGiveed = 1 AND ct.TaskType = @taskCode", paras, false);
if (lapseList.Count >= 1)
{
return(false);
}
else
{
return(true);
}
}
public decimal?GetUseAccountPrincipalByUserCode(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
decimal? totalAmount = 0;
decimal? accountPrincipal = 0;
decimal? donationAmount = 0;
IList <UseWallet> list = DapperSqlHelper.FindToList <UseWallet>("select * from [dbo].[UseWallet] where UserCode=@userCode and Status=1 order by Sort", paras, false);
foreach (var w in list)
{
accountPrincipal = w.AccountPrincipal + accountPrincipal;
donationAmount = donationAmount + w.DonationAmount;
w.TotalAmount = w.AccountPrincipal + w.DonationAmount;
totalAmount = totalAmount + w.TotalAmount;
}
return(accountPrincipal);
}
public UseWallet GetUseWalletCountMoneyWf(string userCode)
{
DynamicParameters paras = new DynamicParameters();
paras.Add("@userCode", userCode, System.Data.DbType.String);
decimal? totalAmount = 0;
decimal? accountPrincipal = 0;
decimal? donationAmount = 0;
string Ratio = "1";
IList <UseWallet> list = DapperSqlHelper.FindToList <UseWallet>("select * from [dbo].[UseWallet] where UserCode=@userCode and Status=1 order by Sort", paras, false);
foreach (var w in list)
{
accountPrincipal = w.AccountPrincipal + accountPrincipal;
donationAmount = donationAmount + w.DonationAmount;
w.TotalAmount = w.AccountPrincipal + w.DonationAmount;
totalAmount = totalAmount + w.TotalAmount;
}
decimal?totalAmounttemp = 0;
foreach (var x in list)
{
totalAmounttemp = x.AccountPrincipal + x.DonationAmount;
if (totalAmounttemp != 0)
{
Ratio = x.Ratio;
break;
}
}
return(new UseWallet()
{
AccountPrincipal = accountPrincipal,
DonationAmount = donationAmount,
TotalAmount = totalAmount,
Ratio = Ratio
});
}
public IList <ListType> GetListType()
{
IList <ListType> list = DapperSqlHelper.FindToList <ListType>("select top 5 * from [dbo].[ListType] where Status=1", null, false);
return(list);
}
public IList <CustomerService> GetCustomerServiceList()
{
IList <CustomerService> list = DapperSqlHelper.FindToList <CustomerService>("select * from [dbo].[CustomerService] where Status =1", null, false);
return(list);
}
public IList <UseWallet> GetUseWalletList()
{
IList <UseWallet> list = DapperSqlHelper.FindToList <UseWallet>("select * from [dbo].[UseWallet] where Status=1", null, false);
return(list);
}
public IList <Propaganda> GetPropaganda()
{
IList <Propaganda> list = DapperSqlHelper.FindToList <Propaganda>("select * from [dbo].[Propaganda] where Status=1", null, false);
return(list);
}