public async Task AddPictureResourcesAsync(string productId, List <ProductPicture> pictures, string userId)
{
if (pictures != null)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
await AddAsync(productId, pictures, userId, db, tran);
}
}
public async Task AddAsync(BasicData entity, string userId)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
StringBuilder sql = new StringBuilder();
sql.Append($" INSERT INTO {_TABLE} ");
sql.Append(" (Id, Code, Name, ParentId, SortOrder, Enabled, CreatedAt, CreatedById) ");
sql.Append($" VALUES ");
sql.Append(" (nextval('DT'), @Code, @Name, @ParentId, @SortOrder, @Enabled, @CreatedAt, @CreatedById) ");
await db.ExecuteAsync(
sql.ToString(),
new
{
entity.Code,
entity.Name,
entity.ParentId,
entity.SortOrder,
entity.Enabled,
entity.CreatedAt,
CreatedById = userId,
},
tran);
}
public async Task AddAsync(ProducType entity, string userId)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
StringBuilder sql = new StringBuilder();
sql.Append($" INSERT INTO {_TABLE} ");
sql.Append(" (Id, ParentId, LevelCode, TypeName, TypeCode, SortOrder, CreatedById, CreatedAt) ");
sql.Append($" VALUES ");
sql.Append(" (nextval('PT'), @ParentId, @LevelCode, @TypeName, @TypeCode, @SortOrder, @CreatedById, NOW()) ");
await db.ExecuteAsync(sql.ToString(), new { entity.ParentId, entity.LevelCode, entity.TypeCode, entity.TypeName, entity.SortOrder, CreatedById = userId }, tran);
}
public async Task AddAsync(List <Trade> tradeinfos)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
StringBuilder sql = new StringBuilder();
sql.Append($@" INSERT INTO {_TABLE}
(Id, ProdMaterId, TradeId, TradeName, CreatedAt)
VALUES
(nextval('TI'), @ProdMaterId, @TradeId, @TradeName ,NOW())");
var result = await db.ExecuteAsync(sql.ToString(), tradeinfos, tran);
}
public async Task AddAsync(List <ProductVehicle> entities)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
StringBuilder sql = new StringBuilder();
sql.Append($" INSERT INTO {_TABLE} ");
sql.Append(@" ( Id, VehicleId, ProductId, BrandId, BrandName, ChildBrandId, ChildBrandName,
SeriesId, SeriesName, ModelId, ModelName, Year, Displacement, DisplacementText,
Horsepower, IntakeType, GearboxName, CreatedAt) ");
sql.Append($" VALUES ");
sql.Append(@" ( nextval('PV'), @VehicleId, @ProductId, @BrandId, @BrandName, @ChildBrandId, @ChildBrandName,
@SeriesId, @SeriesName, @ModelId, @ModelName, @Year, @Displacement, @DisplacementText,
@Horsepower, @IntakeType, @GearboxName, NOW()) ");
await db.ExecuteAsync(sql.ToString(), entities, tran);
}
public async Task <Material> FindAsync(string id)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
Material material = null;
var sql = @"SELECT m.*,p.*,t.* FROM tb_materialinfo m
LEFT JOIN tb_materialproperties p ON p.MaterialId = m.Id
LEFT JOIN tb_tradeinfo t ON t.ProdMaterId = m.Id
WHERE m.Id =@Id AND m.Deleted = 0";
await db.QueryAsync <Material, MaterialProperty, Trade, Material>(
sql,
(m, p, t) =>
{
if (material == null)
{
material = m;
}
if (p != null && (!material.Properties.Any(x => x.Id == p.Id)))
{
material.Properties.Add(p);
}
if (t != null && (!material.Trades.Any(x => x.Id == t.Id)))
{
material.Trades.Add(t);
}
return(m);
},
new
{
Id = id,
});
return(material);
}
public async Task AddAsync(Property entity, string userId)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
StringBuilder sql = new StringBuilder();
sql.Append($" INSERT INTO {_TABLE} ");
sql.Append(" ( Id, PropName, DisplayName, IsDisable, CreatedById, CreatedAt) ");
sql.Append($" VALUES ");
sql.Append(" ( nextval('PR'), @PropName, @DisplayName, @IsDisable, @CreatedById, NOW()) ");
await db.ExecuteAsync(
sql.ToString(),
new
{
entity.PropName,
entity.DisplayName,
entity.IsDisable,
CreatedById = userId,
}, tran);
}
public async Task AddAsync(ProductDetail entity, string userId)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
StringBuilder sql = new StringBuilder();
sql.Append($@" INSERT INTO {_TABLE}
(Id, ProductId, MaterialId, Quantity, CreatedById, CreatedAt)
VALUES
(nextval('PD'), @ProductId, @MaterialId, @Quantity, @CreatedById, NOW()) ");
var result = await db.ExecuteAsync(
sql.ToString(),
new
{
entity.ProductId,
entity.MaterialId,
entity.Quantity,
CreatedById = userId,
},
tran);
}
public bool DoCheck()
{
bool isOK = false;
try
{
using (var scope = _databaseScopeFactory.CreateWithTransaction())
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
isOK = db.State == System.Data.ConnectionState.Open;
}
}
catch (Exception e)
{
Message = $"fatil to check db,msg is = {e.Message},stacktrace = {e.StackTrace}";
_logger.LogCritical(Message);
}
return(isOK);
}
/// <summary>
/// 同级移动
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="moveAfterPosition">移动之后的位置</param>
/// <param name="moveId">当前Id</param>
/// <param name="parentId">移动后的ParentId</param>
/// <param name="tbName">表名</param>
/// <returns>返回值</returns>
public async Task <int> MovePosition <T>(int moveAfterPosition, string moveId, string parentId, string tbName)
where T : MoveBaseModel
{
int result = -1;
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
var atInfo = await db.QueryMultipleAsync(
$"SELECT SortOrder FROM {tbName} " +
"WHERE Id = @Id AND Deleted = 0",
new
{
Id = moveId,
},
tran);
int?atSortOrder = atInfo.Read <T>().First().SortOrder;
result = await db.ExecuteAsync(
$"UPDATE {tbName} SET " +
"SortOrder = @SortOrder WHERE Id = @Id AND Deleted = 0",
new
{
SortOrder = moveAfterPosition,
Id = moveId,
},
tran);
string symbol = string.Empty;
int initSort = -1;
if (moveAfterPosition < atSortOrder)
{
symbol = ">=";
}
else
{
symbol = "<=";
}
string sql = $"SELECT Id,SortOrder FROM {tbName} ";
if (parentId == null || parentId == string.Empty || parentId == "-1")
{
sql += $"WHERE ParentId IS NULL AND Deleted = 0 AND SortOrder {symbol} @SortOrder ";
}
else
{
sql += $"WHERE ParentId = @ParentId AND Deleted = 0 AND SortOrder {symbol} @SortOrder ";
}
var updateInfo = await db.QueryMultipleAsync(
sql +
"ORDER BY SortOrder ASC ",
new
{
ParentId = parentId,
SortOrder = moveAfterPosition,
},
tran);
if (symbol == "<=")
{
moveAfterPosition = initSort;
}
foreach (var item in updateInfo.Read <T>())
{
if (item.Id != moveId)
{
moveAfterPosition += 1;
var resInfo = await db.ExecuteAsync(
$"UPDATE {tbName} SET " +
"SortOrder = @SortOrder WHERE Id = @Id AND Deleted = 0",
new
{
SortOrder = moveAfterPosition,
Id = item.Id,
},
tran);
}
}
return(result);
}
public async Task <MaterialType> FindAsync(string id)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
return(await db.QueryFirstOrDefaultAsync <MaterialType>(
"SELECT Id, ParentId, TypeCode, TypeName, LevelCode, SortOrder, Deleted FROM tb_materialtype WHERE Deleted=0 AND Id=@Id",
new { Id = id }));
}
public async Task AddAsync(ProductInfo entity, string userId)
{
var db = _databaseLocator.GetProductCenterManagementDatabase();
var tran = _databaseLocator.GetDbTransaction(db);
StringBuilder sql = new StringBuilder();
sql.Append($" INSERT INTO {_TABLE} ");
sql.Append(" ( Id, ProdCode, ProdName, ProdFullName, ProdTypeId,");
sql.Append(" ProdTypeCode, ProdTypeName, AuditState, CreatedById, CreatedAt ) ");
sql.Append(" VALUES ");
sql.Append(" ( nextval('PI'), @ProdCode, @ProdName, @ProdFullName, @ProdTypeId,");
sql.Append(" @ProdTypeCode, @ProdTypeName, 0, @CreatedById, NOW() ); ");
var result = await db.ExecuteAsync(
sql.ToString(),
new
{
entity.ProdCode,
entity.ProdName,
entity.ProdFullName,
entity.ProdTypeId,
entity.ProdTypeCode,
entity.ProdTypeName,
entity.AuditState,
CreatedById = userId,
},
tran);
}