public void Write()
{
var array = new string[] { };
var name = array.GetType().Name;
var helper = new DefaultExcelBulkEditHelper();
var model = new ExcelDataModel <CategroyExcelBulkRow, ProductExcelBulkRow>();
using (var stream = new FileStream($"{DateTime.Now.ToUnixStampDateTime()}.xlsx", FileMode.Create)) {
using (var database = factory.GenerateDatabase()) {
model.Data1 = database.SqlQuery <CategroyExcelBulkRow>("SELECT * FROM [dbo].[Category] (NOLOCK)").ToArray();
model.Data2 = database.SqlQuery <ProductExcelBulkRow>("SELECT * FROM [dbo].[Product] (NOLOCK)").ToArray();
model.Data2.ToList().ForEach(ctx => {
var settings = ctx.Settings.DeserializeToObject <ProductSettings>();
ctx.Options = settings?.Specifications.SerializeToJson();
ctx.Banners = string.Join(",",
(settings?.Banners.Select(x => x.Replace("https://www.yourc.club/images/products/", string.Empty))) ??
new string[] { ctx.ImageUrl.Replace("https://www.yourc.club/images/products/", string.Empty) });
ctx.Size = settings?.Specifications[0].SerializeToJson();
ctx.Temperature = settings?.Specifications[1].SerializeToJson();
ctx.ImageUrl = ctx.ImageUrl?.Replace("https://www.yourc.club/images/products/", string.Empty);
ctx.Banners = string.IsNullOrEmpty(ctx.Banners) ? ctx.ImageUrl : ctx.Banners;
});
}
model.DataMark = new ExcelDataMark()
{
DateTime = DateTime.Now.ToUnixStampDateTime(),
Additional = new Dictionary <string, string>(),
Version = "1.0"
};
helper.Write(stream, model);
stream.Flush();
}
}
public void ImportProducts()
{
using (var stream = new FileStream("Products.xlsx", FileMode.Open, FileAccess.Read)) {
var helper = new DefaultExcelBulkEditHelper();
var categories = helper.Read <CategroyExcelBulkRow>(stream);
var products = helper.Read <ProductExcelBulkRow>(stream);
var queryString = @"
SET IDENTITY_INSERT [dbo].[Category] ON
MERGE INTO [dbo].[Category] [target]
USING(
VALUES
{0}
)[source]([Id],[MerchantId],[Name],[Enabled],[Description],[JsonString],[CreatedDateTime],[LastUpdatedDateTime],[CreatedBy],[LastUpdatedBy])
ON [target].[Id] = [source].[Id] AND [target].[MerchantId] = [source].[MerchantId]
WHEN MATCHED THEN UPDATE SET
[target].[Name] = [source].[Name],
[target].[Enabled] = [source].[Enabled],
[target].[Description] = [source].[Description],
[target].[JsonString] = [source].[JsonString],
[target].[LastUpdatedDateTime] = [source].[LastUpdatedDateTime],
[target].[LastUpdatedBy] = [source].[LastUpdatedBy]
WHEN NOT MATCHED THEN INSERT ([Id],[MerchantId],[Name],[Enabled],[Description],[JsonString],[CreatedDateTime],[CreatedBy])
VALUES([source].[Id],[source].[MerchantId],[source].[Name],[source].[Enabled],[source].[Description],[source].[JsonString],[source].[CreatedDateTime],[source].[CreatedBy]);
SET IDENTITY_INSERT [dbo].[Category] OFF
";
using (var database = factory.GenerateDatabase()) {
queryString = string.Format(queryString, string.Join(",", categories.Data.Where(x => x.Id != 0).Select(ctx => {
return($"\r\n({ctx.Id},{ctx.MerchantId},'{ctx.Name}',1,'','[]',DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()),DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()),'Initialization','Initialization')");
})));
database.Execute(queryString);
}
queryString = @"SET IDENTITY_INSERT [dbo].[Product] ON
MERGE INTO [dbo].[Product] AS [target]
USING(
VALUES
{0}
) AS[source]([Id],[MerchantId],[CategoryId],[Name],[Price],[SalesVol],[SortNo],[Enabled],[Description],[ImageUrl],[Settings],[CreatedBy],[CreatedDateTime],[LastUpdatedBy],[LastUpdatedDateTime])
ON[target].[Id] = [source].[Id] AND[target].[MerchantId] = [source].[MerchantId]
WHEN MATCHED THEN UPDATE SET
[target].[CategoryId] = [source].[CategoryId]
,[target].[Name] = [source].[Name]
,[target].[Price] = [source].[Price]
,[target].[SalesVol] = [source].[SalesVol]
,[target].[SortNo] = [source].[SortNo]
,[target].[Enabled] = [source].[Enabled]
,[target].[Description] = [source].[Description]
,[target].[ImageUrl] = [source].[ImageUrl]
,[target].[Settings] = [source].[Settings]
,[target].[LastUpdatedBy]=[source].[LastUpdatedBy]
,[target].[LastUpdatedDateTime] =[source].[LastUpdatedDateTime]
WHEN NOT MATCHED THEN INSERT
([Id], [MerchantId], [CategoryId], [Name], [Price], [SalesVol], [SortNo], [Enabled], [Description], [ImageUrl], [CreatedBy], [CreatedDateTime])
VALUES([source].[Id] , [source].[MerchantId] , [source].[CategoryId] , [source].[Name] , [source].[Price] , [source].[SalesVol] , [source].[SortNo] , [source].[Enabled] , [source].[Description] , [source].[ImageUrl] , [source].[CreatedBy] , [source].[CreatedDateTime] );
SET IDENTITY_INSERT[dbo].[Product] OFF
";
using (var database = factory.GenerateDatabase()) {
queryString = string.Format(queryString, string.Join(",", products.Data.Where(x => x.Id != 0).Select(ctx => {
var settings = new ProductSettings();
settings.Banners = ctx.Banners.Split(",")
?.Select(x => $"https://www.yourc.club/images/products/{x}")
.ToArray();
settings.Specifications = new List <Specification>();
var size = ctx.Size.DeserializeToObject <Specification>();
if (size != null)
{
settings.Specifications.Add(size);
}
var tempet = ctx.Temperature.DeserializeToObject <Specification>();
if (tempet != null)
{
settings.Specifications.Add(tempet);
}
ctx.ImageUrl = string.IsNullOrEmpty(ctx.ImageUrl) ? string.Empty : $"https://www.yourc.club/images/products/{ctx.ImageUrl}";
return($"\r\n({ctx.Id},{ctx.MerchantId},{ctx.CategoryId},'{ctx.Name}',{ctx.Price},{ctx.SalesVol},{ctx.SortNo},1,'{ctx.Description}','{ctx.ImageUrl}','{settings.SerializeToJson()}','Initialization',DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()),'Initialization',DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()))");
})));
database.Execute(queryString);
}
}
}
