public bool SetDefault(int userId, int id)
{
var sql = @"UPDATE UserAddress SET IsDefault=0 WHERE UserId=@userId AND IsDefault=1;
UPDATE UserAddress SET IsDefault=1 WHERE UserId=@userId AND Id=@id;";
return(DbManage.Execute(sql, new { userId, id }) > 0);
}
public int InsertOrderItem(OrderItemDto dto)
{
//string sql = @" INSERT INTO [dbo].[OrderItem]( [OrderId], [ProductId], [FeatureName],[FeatureValue], [Num], [Name], [TotalFee], [Price], [Sort], [Status], [Creator], [CreateTime], [IsDeleted] ) Select @OrderId, s.ProductId, f.Name , v.Value,@Num,p.Name,@TotalFee,@Price, 0, 1, NULL, @date, 0 from dbo.[Product] p join dbo.[ProductSku] s on p.id = s.Productid JOIN dbo.[FeatureValue] v on s.FeatureValueId = v.Id JOIN dbo.[Feature] f ON f.id = v.FeatureId Where p.Status =1 and Isdeleted = 0 ";
string sql = @" INSERT INTO [dbo].[OrderItem]( [OrderId], [ProductId], [FeatureName],[FeatureValue], [Num], [Name], [TotalFee], [ImgUrl], [Price], [Sort], [Status], [Creator], [CreateTime], [IsDeleted] ) select @OrderId, @ProductId, @FeatureName , @FeatureValue,@Num, @Name,@TotalFee, p.CoverUrl, @Price, 0, 1, NULL, @date, 0 from dbo.[Product] p where p.Id=@ProductId;";
return(DbManage.Execute(sql, new { OrderId = dto.OrderId, ProductId = dto.ProductId, FeatureName = dto.FeatureName, FeatureValue = dto.FeatureValue, Num = dto.Num, Name = dto.Name, TotalFee = dto.TotalFee, Price = dto.Price, date = DateTime.Now }));
}
public IEnumerable <OrderItemDto> GetProductsByTradeNo(string tradeNo)
{
var sql = @"SELECT oi.ProductId as ProductId, oi.Num FROM OrderItem oi
INNER JOIN [Order] o on oi.orderId=o.id
WHERE o.tradeNo = @tradeNo;";
return(DbManage.Query <OrderItemDto>(sql, new { tradeNo }));
}
public IEnumerable <UserAddressDto> List(int userId)
{
var sql = @"SELECT ua.Id,ua.UserId, ua.Receiver, ua.Mobile, ua.Province, ua.City, ua.District, ua.Address, ua.PostCode, ua.IsDefault
FROM UserAddress ua
WHERE ua.IsDeleted=0 AND ua.Status = 1 AND ua.UserId=@userId;";
return(DbManage.Query <UserAddressDto>(sql, new { userId }));
}
public UserAddressDto GetDetail(int userId, int id)
{
var sql = @"SELECT ua.Id,ua.UserId, ua.Receiver, ua.Mobile, ua.Province, ua.City, ua.District, ua.Address, ua.PostCode, ua.IsDefault
FROM UserAddress ua
WHERE ua.Id=@id AND ua.UserId=@userId";
return(DbManage.Query <UserAddressDto>(sql, new { userId, id }).FirstOrDefault());
}
public int InsertOrderShipping(OrderShipping model)
{
string sql = @" INSERT INTO [dbo].[OrderShipping](OrderId, Name, Mobile, Province, City, District, Address,
PostCode, Sort, Status, Creator, CreateTime, IsDeleted) values( @OrderId, @Receiver, @Mobile,
@Province, @City, @District, @Address, @PostCode, 0, 1, 'admin', GETDATE(), 0) " ;
return(DbManage.Execute(sql, new { OrderId = model.OrderId, Receiver = model.Name, Mobile = model.Mobile, Province = model.Province, City = model.City, District = model.District, Address = model.Address, PostCode = model.PostCode }));
}
private void btnGenerate_Click(object sender, EventArgs e)
{
DbManage manage = new DbManage();
var catalog = manage.GetCataLogByName(txtNovel.Text);
var epub = new EPubDocument();
epub.AddTitle(txtNovel.Text);
epub.AddLanguage("zh-cn");
String css = CrawelNovel.Properties.Resources.style;
css += "\nbody { font-family: LiberationSerif; }\n";
css += "@font-face { font-family : LiberationSerif; font-weight : normal; font-style: normal; src : url(LiberationSerif-Regular.ttf); }\n";
css += "@font-face { font-family : LiberationSerif; font-weight : normal; font-style: italic; src : url(LiberationSerif-Italic.ttf); }\n";
css += "@font-face { font-family : LiberationSerif; font-weight : bold; font-style: normal; src : url(LiberationSerif-Bold.ttf); }\n";
css += "@font-face { font-family : LiberationSerif; font-weight : bold; font-style: italic; src : url(LiberationSerif-BoldItalic.ttf); }\n";
epub.AddData("LiberationSerif-Regular.ttf", CrawelNovel.Properties.Resources.LiberationSerif_Regular, "application/octet-stream");
epub.AddData("LiberationSerif-Bold.ttf", CrawelNovel.Properties.Resources.LiberationSerif_Bold, "application/octet-stream");
epub.AddData("LiberationSerif-Italic.ttf", CrawelNovel.Properties.Resources.LiberationSerif_Italic, "application/octet-stream");
epub.AddData("LiberationSerif-BoldItalic.ttf", CrawelNovel.Properties.Resources.LiberationSerif_BoldItalic, "application/octet-stream");
epub.AddStylesheetData("style.css", css);
String coverId = epub.AddImageData("cover.png", catalog.Img);
epub.AddMetaItem("cover", coverId);
String page_template = Encoding.UTF8.GetString(CrawelNovel.Properties.Resources.page);
int navCounter = 1;
int pageCounter = 1;
var chaps = manage.GetChapterList(catalog.Id);
foreach (var chap in chaps)
{
String page = page_template.Replace("%%CONTENT%%", chap.ChapterName + "<br />" + chap.ChapterContent.Replace("\r\n", "<br />")).Replace("<br /><br />", "");
String pageName = String.Format("page{0}.xhtml", pageCounter);
epub.AddXhtmlData(pageName, page);
epub.AddNavPoint(chap.ChapterName, pageName, navCounter++);
pageCounter++;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "epub files (*.epub)|*.epub|All files (*.*)|*.*";
saveFileDialog.FilterIndex = 1;
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
epub.Generate(saveFileDialog.FileName);
}
}
public IEnumerable <TEntity> GetPagerList <TEntity>(string columns, string table, string where, string orderby, out int rowCount, int pageIndex = 1, int pageSize = 10, object param = null)
{
using (var cxt = DbContext(DbOperation.Read))
{
var list = new DbManage(cxt).GetPagerList <TEntity>(columns, table, where, orderby, out rowCount, pageIndex, pageSize, param);
return(list);
}
}
public PayOrderDto GetPayOrderInfoByTradeNo(string tradeNo)
{
string sql = @"SELECT o.TradeNo, o.Payment as TotalFee, u.OpenId
from [Order] o
LEFT JOIN [dbo].[User] u on u.Id=o.UserId
WHERE o.TradeNo=@tradeNo;";
return(DbManage.Query <PayOrderDto>(sql, new { tradeNo }).FirstOrDefault());
}
public UserAddressDto GetDefault(int userId)
{
var sql = @"SELECT TOP 1 ua.Id,ua.UserId, ua.Receiver, ua.Mobile, ua.Province, ua.City, ua.District, ua.Address, ua.PostCode, ua.IsDefault
FROM UserAddress ua
WHERE ua.IsDefault=1 AND ua.UserId=@userId
ORDER BY UpdateTime DESC;";
return(DbManage.Query <UserAddressDto>(sql, new { userId }).FirstOrDefault());
}
public OrderStatViewDto GetStatCountInfo(int userId)
{
string sql = @"SELECT (SELECT COUNT(1) FROM [Order] WHERE UserId=2 AND IsDeleted=0 AND Status=0) AS NotPayCount,
(SELECT COUNT(1) FROM [Order] WHERE UserId=@userId AND IsDeleted=0 AND Status=1) AS NotDeliverCount,
(SELECT COUNT(1) FROM [Order] WHERE UserId=@userId AND IsDeleted=0 AND Status=2) AS DeliveredCount,
(SELECT COUNT(1) FROM [Order] WHERE UserId=@userId AND IsDeleted=0 AND Status=3) AS FinishedCount" ;
return(DbManage.Query <OrderStatViewDto>(sql, new { userId }).FirstOrDefault());
}
public bool AddToCart(int userId, int productId, int featureValueId, int num)
{
var sql = @"DECLARE @@skuId int
SELECT top 1 @@skuId = id from ProductSku where ProductId =@productId and FeatureValueId = @featureValueId
INSERT INTO ShoppingCart(ProductSkuId, num, Status, CreateTime, UpdateTime, IsDeleted, UserId)
values(@@skuId, @num, 1, GETDATE(), GETDATE(), 0, @userId);";
return(DbManage.Execute(sql, new { userId, productId, featureValueId, num }) > 0);
}
public CouponDto GetDetail(int id, int userId)
{
var sql = @"SELECT TOP 1 c.Id, c.Title, c.StartTime, c.EndTime, c.MinFee, c.RebateFee, c.MaxOwnLimit, c.Status,
CASE WHEN DATEDIFF(d, GETDATE(), c.EndTime) >=0 THEN 1 ELSE 0 END as CanUse,
(SELECT count(1) FROM UserCoupon uc WHERE uc.CouponId=c.Id AND uc.Status=1 AND IsDeleted=0 AND IsUsed=0 AND uc.UserId=@userId ) as OwnCount
FROM Coupon c
WHERE c.Id =@id;";
return(DbManage.Query <CouponDto>(sql, new { id, userId }).FirstOrDefault());
}
public IEnumerable <ProductSkuDto> GetProductSku(int productId)
{
string sql = @"SELECT f.Id as FeatureId,v.Id as ValueId,f.Name as FeatureName,v.[Value] as FeatureValue, sku.Num , ISNULL(sku.Price, p.Price) AS Price
from dbo.ProductSku sku JOIN dbo.Product p ON sku.ProductId = p.Id
JOIN dbo.FeatureValue v ON sku.FeatureValueId = v.Id
JOIN dbo.Feature f ON v.FeatureId = f.Id
WHERE sku.ProductId = @productId AND sku.IsDeleted=0 AND sku.Status =1 AND v.IsDeleted=0 AND v.Status=1
AND f.IsDeleted=0 AND f.Status =1 AND p.Status=1 AND p.IsDeleted=0 " ;
return(DbManage.Query <ProductSkuDto>(sql, new { productId }));
}
public IEnumerable <ProductFileDto> GetProductFiles(int productId, int fileType)
{
string sql = "select Url,Type from dbo.[File] where IsDeleted = 0 and Status = 1 and ProductId = @productId ";
if (fileType > 0)
{
sql += "and Type = @fileType";
}
return(DbManage.Query <ProductFileDto>(sql, new { productId, fileType }));
}
public OrderDetailViewDto GetDetail(int userId, int orderId)
{
string sql = @"SELECT o.Id, o.TradeNo, o.TotalFee, o.PostFee, o.Payment, o.Status, o.UserMessage, o.UserCouponId, c.Title as CouponTitle, c.RebateFee as CouponFee,
os.Name as Receiver, os.Mobile, os.Province, os.City, os.District, os.Address, os.PostCode
from [Order] o
LEFT JOIN OrderShipping os on os.OrderId=o.Id
LEFT JOIN UserCoupon uc on uc.Id=o.UserCouponId
LEFT JOIN Coupon c on c.Id=uc.CouponId
WHERE o.UserId=@userId AND o.Id = @orderId;";
return(DbManage.Query <OrderDetailViewDto>(sql, new { userId, orderId }).FirstOrDefault());
}
public IEnumerable <FeatureWithValueDto> GetProductFeatureWithValues(int productId)
{
string sql = @"SELECT p.Id as ProductId, f.Id as FeatureId, f.Name, f.DisplayName as DisplayName,
fv.Id as ValueId, fv.[Value] as [Value]
FROM FeatureValue fv
INNER JOIN Feature f on f.Id = fv.FeatureId
INNER JOIN ProductSku sku on sku.FeatureValueId = fv.Id
INNER JOIN Product p on p.Id = sku.ProductId
WHERE p.Id = @productId and p.Status = 1";
return(DbManage.Query <FeatureWithValueDto>(sql, new { productId }));
}
public UserCouponDto GetDefault(int userId, int totalFee)
{
var sql = @"SELECT TOP 1 uc.Id, u.Id as UserId, c.Id as CouponId, c.Title, c.StartTime, c.EndTime, c.MinFee, c.RebateFee, c.MaxOwnLimit, c.Status
FROM UserCoupon uc
INNER JOIN Coupon c on c.Id=uc.CouponId
INNER JOIN [User] u on u.Id=uc.UserId
WHERE uc.isUsed=0 AND uc.IsDeleted=0
and u.Id = @userId AND c.MinFee<= @totalFee AND DATEDIFF(d, GETDATE(), c.EndTime) >=0
ORDER BY c.RebateFee DESC, uc.CreateTime ASC;";
return(DbManage.Query <UserCouponDto>(sql, new { userId, totalFee }).FirstOrDefault());
}
public IEnumerable <ShoppingCartDetailDto> GetItemsByCartIds(int[] cartIdArr)
{
string sql = @" SELECT c.id as cartid, p.Id as ProductId, p.CoverUrl, p.Name,c.Num, ISNULL(s.Price, p.Price) AS Price, f.[Name] as FeatureName,s.FeatureValueId,v.[value] as FeatureValue
FROM dbo.ShoppingCart c
JOIN dbo.ProductSku s on c.ProductSkuId = s.Id
JOIN dbo.Product p ON s.ProductId = p.Id
JOIN dbo.FeatureValue v ON s.FeatureValueId = v.Id
JOIN dbo.Feature f ON v.FeatureId = f.Id
WHERE c.IsDeleted=0 AND c.id in @cartIdArr ORDER BY c.Sort,c.CreateTime DESC" ;
return(DbManage.Query <ShoppingCartDetailDto>(sql, new { cartIdArr }));
}
public IEnumerable <UserCouponDto> GetNoUseList(int userId, int totalFee)
{
var sql = @"
SELECT uc.Id, u.Id as UserId, c.Id as CouponId, c.Title, c.StartTime, c.EndTime, c.MinFee, c.RebateFee, c.MaxOwnLimit, '未使用' as status,
(case WHEN c.MinFee <= @totalFee THEN 1 ELSE 0 END) as CanUse
FROM UserCoupon uc
INNER JOIN Coupon c on c.Id=uc.CouponId AND uc.Status=1
INNER JOIN [User] u on u.Id=uc.UserId
WHERE uc.Status=1 AND uc.IsDeleted=0
AND u.Id = @userId and uc.isUsed = 0 and DATEDIFF(d, GETDATE(), c.EndTime) >=0
ORDER BY uc.CreateTime ASC;";
return(DbManage.Query <UserCouponDto>(sql, new { userId, totalFee }));
}
public int CheckParamsValid(GenerateOrderDto dto)
{
var procParams = new DynamicParameters();
procParams.Add("@UserId", dto.UserId);
procParams.Add("@TotalFee", dto.TotalFee);
procParams.Add("@Payment", dto.Payment);
procParams.Add("@UserCouponId", dto.UserCouponId);
procParams.Add("@PostFee", dto.PostFee);
procParams.Add("@ROut", dbType: DbType.Int32, direction: ParameterDirection.Output);
DbManage.Execute("[dbo].[Order_ParamsCheck]", procParams, CommandType.StoredProcedure);
return(procParams.Get <int>("@ROut"));
}
public IEnumerable <OrderItemDto> GetOrderItems(int orderId)
{
string sql = @" SELECT Name,FeatureName,FeatureValue,Num,Price,TotalFee,ImgUrl from dbo.OrderItem WHERE orderid = @orderId ";
return(DbManage.Query <OrderItemDto>(sql, new { orderId }));
}
public IEnumerable <OrderItemViewDto> GetAllByOrderId(int orderId)
{
string sql = @"SELECT * FROM [OrderItem] WHERE OrderId=@orderId ORDER BY Id DESC";
return(DbManage.Query <OrderItemViewDto>(sql, new { orderId }));
}
public bool UpdateToken(string token, string newToken, DateTime expireTime)
{
var sql = @"update dbo.[User] set token=@newToken, tokenExpireTime=@expireTime where token=@token;";
return(DbManage.Execute(sql, new { token, newToken, expireTime }) > 0);
}
public UserDto GetUserByToken(string token)
{
var sql = @"select id,nickname,avatarurl,token from dbo.[User] where token=@token;";
return(DbManage.Query <UserDto>(sql, new { token }).FirstOrDefault());
}
public UserDto GetUserByOpenId(string openId)
{
var sql = @"select id,nickname,avatarurl,token from dbo.[User] where openid=@openId";
return(DbManage.Query <UserDto>(sql, new { openId }).FirstOrDefault());
}
/// <summary>
/// true 未过期，false 过期
/// </summary>
public bool CheckToken(string token)
{
var sql = @"select count(1) from dbo.[User] where token=@token and DATEDIFF(s, tokenExpireTime , getdate()) < 0;";
return(DbManage.ExecuteScalar <int>(sql, new { token }) > 0);
}
public IEnumerable <FeatureWithValueDto> GetFeatureWithValues()
{
string sql = @" SELECT f.Id as FeatureId,f.Name,v.Id AS ValueId,v.[value] FROM dbo.feature f JOIN dbo.FeatureValue v ON f.Id = v.FeatureId WHERE f.status =1 AND f.isdeleted=0 AND v.status =1 AND v.isdeleted=0";
return(DbManage.Query <FeatureWithValueDto>(sql));
}
public bool UpdateNum(int userId, int cartId, int num)
{
var sql = @"UPDATE ShoppingCart SET Num=@num WHERE Id=@cartId AND UserId=@userId;";
return(DbManage.Execute(sql, new { userId, cartId, num }) > 0);
}
public int GetCartCount(int userId)
{
var sql = @"Select count(1) from [dbo].[ShoppingCart] where UserId=@userId AND Status=1 AND IsDeleted=0;";
return(DbManage.ExecuteScalar <int>(sql, new { userId }));
}
