public static int Update(SqlDbHelper dbHelper, TirePatternModel model)
{
return(dbHelper.ExecuteNonQuery(@"UPDATE Tuhu_productcatalog.dbo.PatternArticle
SET Image = @Image,
Title = @Title,
Describe = @Describe,
Author = @Author,
Date = @Date,
ArticleLink = @ArticleLink,
IsShow = @IsShow,
IsActive=@IsActive,
Brand = @Brand,
Pattern = @Pattern,
UpdateTime = GETDATE()
WHERE PKID = @PKID;
", CommandType.Text, new SqlParameter[] {
new SqlParameter("@Image", model.Image),
new SqlParameter("@Title", model.Title),
new SqlParameter("@Describe", model.Describe),
new SqlParameter("@Author", model.Author),
new SqlParameter("@Date", model.Date.Value),
new SqlParameter("@ArticleLink", model.ArticleLink),
new SqlParameter("@IsShow", model.IsShow ?? false),
new SqlParameter("@Brand", model.Brand),
new SqlParameter("@Pattern", model.Pattern),
new SqlParameter("@PKID", model.PKID),
new SqlParameter("@IsActive", model.IsActive)
}));
}
public static int SaveAddMany(TirePatternModel model)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
dbHelper.BeginTransaction();
var result = -99;
foreach (var brand in model.Patterns)
{
foreach (var pattern in brand.Patterns.Split(','))
{
TirePatternModel tempModel = new TirePatternModel()
{
Image = model.Image,
Title = model.Title,
Describe = model.Describe,
Author = model.Author,
Date = model.Date,
ArticleLink = model.ArticleLink,
IsShow = false,
IsActive = model.IsActive,
Brand = brand.Brand,
Pattern = pattern
};
result = Insert(dbHelper, tempModel);
if (result <= 0)
{
dbHelper.Rollback();
}
}
}
dbHelper.Commit();
return(result);
}
}
public static int GetListCount(SqlDbHelper dbHelper, TirePatternModel model)
{
var OBJ = dbHelper.ExecuteScalar(@"SELECT COUNT(1)
FROM ( SELECT DISTINCT
VP.CP_Brand AS Brand,
VP.CP_Tire_Pattern AS Pattern
FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK )
WHERE VP.PID LIKE 'TR-%'
AND ISNULL(VP.CP_Brand, '') <> ''
AND ISNULL(VP.CP_Tire_Pattern, '') <> ''
AND VP.OnSale=1
AND VP.stockout=0
) AS T
LEFT JOIN Tuhu_productcatalog.dbo.PatternArticle AS PA WITH ( NOLOCK )
ON PA.Brand = T.Brand
AND PA.Pattern = T.Pattern
WHERE (
T.Brand = @Brand
OR @Brand IS NULL )
AND (
T.Pattern = @Pattern
OR @Pattern IS NULL )
AND (
PA.Title = @Title
OR @Title IS NULL )
AND (
PA.Author = @Author
OR @Author IS NULL )
AND (
@Count = 0
OR @Count = 1
AND PA.PKID > 0
OR @Count = 2
AND PA.PKID IS NULL )
AND (
@ShowCount = 0
OR @ShowCount = 1
AND T.Pattern IN ( SELECT PAA.Pattern
FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK )
WHERE PAA.IsShow = 1 )
OR @ShowCount = 2
AND T.Pattern NOT IN ( SELECT PAA.Pattern
FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK )
WHERE PAA.IsShow = 1 )
AND PA.PKID > 0 )" ,
CommandType.Text,
new SqlParameter[] {
new SqlParameter("@Brand", String.IsNullOrWhiteSpace(model.Brand)?null:model.Brand),
new SqlParameter("@Pattern", String.IsNullOrWhiteSpace(model.Pattern)?null:model.Pattern),
new SqlParameter("@Title", String.IsNullOrWhiteSpace(model.Title)?null:model.Title),
new SqlParameter("@Author", String.IsNullOrWhiteSpace(model.Author)?null:model.Author),
new SqlParameter("@Count", model.Count),
new SqlParameter("@ShowCount", model.ShowCount),
});
return(Convert.ToInt32(OBJ));
}
public static bool CanShow(TirePatternModel model)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
var OBJ = dbHelper.ExecuteScalar(@"SELECT COUNT(1)
FROM Tuhu_productcatalog.dbo.PatternArticle AS PA WITH ( NOLOCK )
WHERE PA.Pattern = @Pattern
AND PA.Brand = @Brand
AND PKID<>@PKID
AND PA.IsShow = 1" , CommandType.Text, new SqlParameter[] { new SqlParameter("@Brand", model.Brand), new SqlParameter("@Pattern", model.Pattern), new SqlParameter("@PKID", model.PKID) });
return(Convert.ToInt32(OBJ) > 0);
}
}
public static int SaveUpdateOrAdd(TirePatternModel model)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
var result = -99;
if (model.PKID > 0)
{
result = Update(dbHelper, model);
}
else
{
result = Insert(dbHelper, model);
}
return(result);
}
}
public static int Insert(SqlDbHelper dbHelper, TirePatternModel model)
{
return(dbHelper.ExecuteNonQuery(@"INSERT INTO Tuhu_productcatalog.dbo.PatternArticle
(
Image,
Title,
Describe,
Author,
Date,
ArticleLink,
IsShow,
Brand,
Pattern,
IsActive)
VALUES (
@Image,
@Title,
@Describe,
@Author,
@Date,
@ArticleLink,
@IsShow,
@Brand,
@Pattern,
@IsActive)", CommandType.Text, new SqlParameter[] {
new SqlParameter("@Image", model.Image),
new SqlParameter("@Title", model.Title),
new SqlParameter("@Describe", model.Describe),
new SqlParameter("@Author", model.Author),
new SqlParameter("@Date", model.Date.Value),
new SqlParameter("@ArticleLink", model.ArticleLink),
new SqlParameter("@IsShow", model.IsShow ?? false),
new SqlParameter("@Brand", model.Brand),
new SqlParameter("@Pattern", model.Pattern),
new SqlParameter("@IsActive", model.IsActive)
}));
}
public static bool CanShow(TirePatternModel model) => DalPattern.CanShow(model);
public static int SaveUpdateOrAdd(TirePatternModel model) => DalPattern.SaveUpdateOrAdd(model);
public static int SaveAddMany(TirePatternModel model) => DalPattern.SaveAddMany(model);
public static IEnumerable <TirePatternModel> SelectList(TirePatternModel model, PagerModel pager) => DalPattern.SelectList(model, pager);
public static IEnumerable <TirePatternModel> SelectList(TirePatternModel model, PagerModel pager)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
pager.TotalItem = GetListCount(dbHelper, model);
return(dbHelper.ExecuteDataTable(@"SELECT T.Brand,
T.Pattern,
PA.Image,
PA.Title,
PA.Describe,
PA.Author,
PA.Date,
PA.ArticleLink,
PA.IsShow,
PA.IsActive,
PA.PKID
FROM ( SELECT DISTINCT
VP.CP_Brand AS Brand,
VP.CP_Tire_Pattern AS Pattern
FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK )
WHERE VP.PID LIKE 'TR-%'
AND ISNULL(VP.CP_Brand, '') <> ''
AND ISNULL(VP.CP_Tire_Pattern, '') <> ''
AND VP.OnSale=1
AND VP.stockout=0
) AS T
LEFT JOIN Tuhu_productcatalog.dbo.PatternArticle AS PA WITH ( NOLOCK )
ON PA.Brand = T.Brand
AND PA.Pattern = T.Pattern
WHERE (
T.Brand = @Brand
OR @Brand IS NULL )
AND (
T.Pattern = @Pattern
OR @Pattern IS NULL )
AND (
PA.Title = @Title
OR @Title IS NULL )
AND (
PA.Author = @Author
OR @Author IS NULL )
AND (
@Count = 0
OR @Count = 1
AND PA.PKID > 0
OR @Count = 2
AND PA.PKID IS NULL )
AND (
@ShowCount = 0
OR @ShowCount = 1
AND T.Pattern IN ( SELECT PAA.Pattern
FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK )
WHERE PAA.IsShow = 1 )
OR @ShowCount = 2
AND T.Pattern NOT IN ( SELECT PAA.Pattern
FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK )
WHERE PAA.IsShow = 1 )
AND PA.PKID > 0 )
ORDER BY T.Pattern
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;", CommandType.Text,
new SqlParameter[] {
new SqlParameter("@Brand", String.IsNullOrWhiteSpace(model.Brand)?null:model.Brand),
new SqlParameter("@Pattern", String.IsNullOrWhiteSpace(model.Pattern)?null:model.Pattern),
new SqlParameter("@Title", String.IsNullOrWhiteSpace(model.Title)?null:model.Title),
new SqlParameter("@Author", String.IsNullOrWhiteSpace(model.Author)?null:model.Author),
new SqlParameter("@Count", model.Count),
new SqlParameter("@ShowCount", model.ShowCount),
new SqlParameter("@PageIndex", pager.CurrentPage),
new SqlParameter("@PageSize", pager.PageSize),
}).ConvertTo <TirePatternModel>());
}
}