/// <summary>
/// delete ralated product
/// </summary>
/// <param name="productId"></param>
/// <param name="relatedProductId"></param>
/// <param name="relatedType"></param>
public static void DeleteRelatedProduct(int productId, int relatedProductId, RelatedType relatedType)
{
SQLDataAccess.ExecuteNonQuery("[Catalog].[sp_DeleteRelatedProduct]", CommandType.StoredProcedure,
new SqlParameter("@ProductID", productId),
new SqlParameter("@RelatedProductID", relatedProductId),
new SqlParameter("@RelatedType", (int)relatedType));
}
public async Task <IActionResult> UsersWithRelatedPersons(RelatedType RelatedTypeId)
{
var route = Request.Path.Value;
var result = await _userService.UsersWithRelatedPersons(RelatedTypeId);
var reportPath = ReportUsersWithRelatedPerson.ReportUsers(_web, _uriService.GetBaseUrl(), result);
return(Ok(new GenericResponseWithDataList <UserResponseModel>(result, true, "ReportPdfPathView: " + reportPath)));
}
public Task <List <UserWithRelatedPerson> > UsersWithRelatedPersons(RelatedType id)
{
var Users = from U in _dbContext.Users.AsEnumerable()
join UR in _dbContext.UseRelateds.AsEnumerable() on U.Id equals UR.UserId into U_UR
from UR in U_UR.DefaultIfEmpty()
where UR?.RelatedType == id
group new { UR, U } by new { UR.UserId } into URg
select new UserWithRelatedPerson(URg.FirstOrDefault().U, URg.Select(urg => urg.UR));
return(Task.FromResult(Users.Take(10).ToList()));
}
public UseRelated(UseRelated useRelated, User user, City city, City_Translation city_Translation, int userId)
{
Id = useRelated.Id;
UserId = userId;
RelatedUserId = useRelated.RelatedUserId;
RelatedUser = new User(user, city);
RelatedType = useRelated.RelatedType;
if (city_Translation != null)
{
RelatedUser.City.Name = city_Translation.NameTranslate;
}
}
public async Task <List <UserResponseModel> > UsersWithRelatedPersons(RelatedType relatedTypeId)
{
var usersModel = new List <UserResponseModel>();
var users = await _userRepository.UsersWithRelatedPersons(relatedTypeId);
if (users.Count == 0)
{
throw new AppException("Not Exist Relate Type Users");
}
users.ForEach(x =>
{
var user = _mapper.Map <UserResponseModel>(x);
user.userRelateds = _mapper.Map <List <UserRelatedResponseModel> >(x.useRelateds);
usersModel.Add(user);
});
return(usersModel);
}
public async Task <IActionResult> ChangeDetail(string word, RelatedType detail_type, string oldone, string newone)
{
using (var ctx = new WordContext())
{
switch (detail_type)
{
case RelatedType.Example:
return(new EnumResult(await WordManipulator.ChangeRelatedString(ctx, RelatedType.Example, word, oldone, newone)));
case RelatedType.Synonym:
return(new EnumResult(await WordManipulator.ChangeRelatedWord(ctx, RelatedType.Synonym, word, oldone, newone)));
case RelatedType.Antonym:
return(new EnumResult(await WordManipulator.ChangeRelatedWord(ctx, RelatedType.Antonym, word, oldone, newone)));
default:
return(new StatusCodeResult(StatusCodes.Status400BadRequest));
}
}
}
/// <summary>
/// Get related products
/// </summary>
/// <param name="productId"></param>
/// <param name="relatedType"></param>
/// <returns></returns>
public static List <Product> GetRelatedProducts(int productId, RelatedType relatedType)
{
List <Product> res = SQLDataAccess.ExecuteReadList <Product>("[Catalog].[sp_GetRelatedProducts]", CommandType.StoredProcedure,
reader => new Product
{
ProductId = SQLDataHelper.GetInt(reader, "ProductId"),
ArtNo = SQLDataHelper.GetString(reader, "ArtNo"),
Photo = SQLDataHelper.GetString(reader, "Photo"),
PhotoDesc = SQLDataHelper.GetString(reader, "PhotoDesc"),
Name = SQLDataHelper.GetString(reader, "Name"),
BriefDescription = SQLDataHelper.GetString(reader, "BriefDescription"),
Discount = SQLDataHelper.GetDecimal(reader, "Discount"),
UrlPath = SQLDataHelper.GetString(reader, "UrlPath"),
},
new SqlParameter("@ProductID", productId),
new SqlParameter("@RelatedType", (int)relatedType),
new SqlParameter("@Type", PhotoType.Product.ToString())
);
return(res);
}
public async Task <IActionResult> GetDetail(string word, RelatedType detail_type)
{
using (var ctx = new WordContext())
using (var t = await ctx.Database.BeginTransactionAsync())
{
switch (detail_type)
{
case RelatedType.Example:
return(new JsonResult(await WordManipulator.GetRelatedStrings(ctx, RelatedType.Example, word)));
case RelatedType.Synonym:
return(new JsonResult(await WordManipulator.GetRelatedWords(ctx, RelatedType.Synonym, word)));
case RelatedType.Antonym:
return(new JsonResult(await WordManipulator.GetRelatedWords(ctx, RelatedType.Antonym, word)));
default:
return(new StatusCodeResult(StatusCodes.Status400BadRequest));
}
}
}
public static async Task <List <string> > GetRelatedStrings(WordContext ctx, RelatedType type, string word)
{
if (string.IsNullOrWhiteSpace(word))
{
throw new ArgumentNullException(nameof(word));
}
if (!Enum.IsDefined(typeof(RelatedType), type) || type == RelatedType.None)
{
throw new ArgumentException(nameof(type));
}
var word_data = await ctx.Words
.Where(w => w.Content == word)
.Include(w => w.RelatedStrings)
.ToAsyncEnumerable()
.SingleOrDefault();
return(await word_data.RelatedStrings
.Where(rs => rs.Type == type)
.Select(rs => rs.Content)
.ToAsyncEnumerable()
.ToList());
}
/// <summary>
/// Get related products
/// </summary>
/// <param name="productId"></param>
/// <param name="relatedType"></param>
/// <returns></returns>
public static List<Product> GetRelatedProducts(int productId, RelatedType relatedType)
{
List<Product> res = SQLDataAccess.ExecuteReadList<Product>("[Catalog].[sp_GetRelatedProducts]", CommandType.StoredProcedure,
reader => new Product
{
ProductId = SQLDataHelper.GetInt(reader, "ProductId"),
ArtNo = SQLDataHelper.GetString(reader, "ArtNo"),
Photo = SQLDataHelper.GetString(reader, "Photo"),
PhotoDesc = SQLDataHelper.GetString(reader, "PhotoDesc"),
Name = SQLDataHelper.GetString(reader, "Name"),
BriefDescription = SQLDataHelper.GetString(reader, "BriefDescription"),
Discount = SQLDataHelper.GetFloat(reader, "Discount"),
UrlPath = SQLDataHelper.GetString(reader, "UrlPath"),
},
new SqlParameter("@ProductID", productId),
new SqlParameter("@RelatedType", (int)relatedType),
new SqlParameter("@Type", PhotoType.Product.ToString())
);
return res;
}
public static void DeleteRelatedProducts(int productId, RelatedType relatedType)
{
SQLDataAccess.ExecuteNonQuery("Delete from catalog.RelatedProducts Where ProductId=@ProductID Or LinkedProductID=@ProductID and RelatedType=@RelatedType",
CommandType.Text,
new SqlParameter("@ProductID", productId),
new SqlParameter("@RelatedType", (int)relatedType));
}
/// <summary>
/// delete ralated product
/// </summary>
/// <param name="productId"></param>
/// <param name="relatedProductId"></param>
/// <param name="relatedType"></param>
public static void DeleteRelatedProduct(int productId, int relatedProductId, RelatedType relatedType)
{
SQLDataAccess.ExecuteNonQuery("[Catalog].[sp_DeleteRelatedProduct]", CommandType.StoredProcedure,
new SqlParameter("@ProductID", productId),
new SqlParameter("@RelatedProductID", relatedProductId),
new SqlParameter("@RelatedType", (int)relatedType));
}
public RelatedSolutionItem(RelatedType type, long entry)
{
Entry = entry;
Type = type;
}
/// <summary>
/// 获得相关联的区间
/// </summary>
/// <param name="pn"></param>
/// <param name="st"></param>
/// <param name="et"></param>
/// <param name="rt"></param>
/// <returns></returns>
public DataSet GetRelatedSpans(string pn, DateTime st, DateTime et, RelatedType rt)
{
try
{
StringBuilder sb = new StringBuilder();
Database db = DatabaseFactory.CreateDatabase("dbconn");
//cst<=hst and cet>=het
if (rt == RelatedType.OutSide)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime <= '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime >= '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//cst<=hst and (cet<het and cet>hst)
else if (rt == RelatedType.ForeIntersect)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime <= '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//(cst>hst and cst<het) and cet>=het
else if (rt == RelatedType.BackIntersect)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.starttime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime >= '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//cst>hst and cet<het
else if (rt == RelatedType.Inside)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
//return null;
}
catch (Exception ex)
{
return null;
}
}
private async Task <List <int> > AddNotValidsAsync(List <int> idsRelatedValids, RelatedType relatedType, int matchId)
{
var idsNotValids = new List <int>();
foreach (var idRelated in idsRelatedValids)
{
var notValid = new NotValidBeforeStart();
notValid.RelatedId = idRelated;
notValid.RelatedType = relatedType;
notValid.MatchId = matchId;
await this.notValidRepository.AddAsync(notValid);
await this.notValidRepository.SaveAsync();
idsNotValids.Add(notValid.Id);
}
return(idsNotValids);
}
public static void LinkedProductFromString(int productId, string linkproducts, RelatedType type)
{
ClearRelatedProducts(productId, type);
if (!string.IsNullOrEmpty(linkproducts))
{
var arrArt = linkproducts.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries);
foreach (string t in arrArt)
{
var artNo = t.Trim();
if (string.IsNullOrWhiteSpace(artNo)) continue;
int linkProductId = GetProductId(artNo);
if (linkProductId != 0)
AddRelatedProduct(productId, linkProductId, type);
}
}
}
public static string LinkedProductToString(int productId, RelatedType related)
{
var temp = SQLDataAccess.ExecuteReadList("Select ArtNo from Catalog.Product inner join Catalog.RelatedProducts on " +
"RelatedProducts.LinkedProductID=Product.ProductId where RelatedType=@type and RelatedProducts.ProductID=@productId",
CommandType.Text,
reader => SQLDataHelper.GetString(reader, "ArtNo"),
new SqlParameter("@productId", productId),
new SqlParameter("@type", (int)related));
return temp.AggregateString(',');
}
/// <summary>
/// 获得相关联的区间
/// </summary>
/// <param name="pn"></param>
/// <param name="st"></param>
/// <param name="et"></param>
/// <param name="rt"></param>
/// <returns></returns>
public DataSet GetRelatedSpans(string pn, DateTime st, DateTime et, RelatedType rt)
{
try
{
StringBuilder sb = new StringBuilder();
Database db = DatabaseFactory.CreateDatabase("dbconn");
//cst<=hst and cet>=het
if (rt == RelatedType.OutSide)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime <= '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime >= '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//cst<=hst and (cet<het and cet>hst)
else if (rt == RelatedType.ForeIntersect)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime <= '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//(cst>hst and cst<het) and cet>=het
else if (rt == RelatedType.BackIntersect)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.starttime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime >= '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//cst>hst and cet<het
else if (rt == RelatedType.Inside)
{
sb.Append("select * from CalibRecords t where ");
sb.Append("t.pointid = '" + pn + "' and ");
sb.Append("t.starttime > '" + st.ToString("yyyy-MM-dd HH:mm:ss") + "' and ");
sb.Append("t.endtime < '" + et.ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
System.Data.Common.DbCommand dbc = db.GetSqlStringCommand(sb.ToString());
return db.ExecuteDataSet(dbc);
//return null;
}
catch (Exception ex)
{
if (Biz.publicliblogenabled)
{
PublicLib.ExceptionBody eb = new PublicLib.ExceptionBody() { et = PublicLib.ExceptionType.Message, info = "GetRelatedSpans?" + ex.ToString(), ts = DateTime.Now };
(new PublicLib.Log()).AddExceptionLog(eb, PublicLib.logtype.console);
}
return null;
}
}
