aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Rehau.Sku.Assist-AddIn-packed.xllbin653312 -> 0 bytes
-rw-r--r--Rehau.Sku.Assist.csproj22
-rw-r--r--Source/Assistant/HttpClientUtil.cs74
-rw-r--r--Source/Assistant/IProduct.cs (renamed from src/Assistant/IProduct.cs)4
-rw-r--r--Source/Assistant/SkuAssist.cs102
-rw-r--r--Source/Assistant/StoreResponse.cs21
-rw-r--r--Source/ExcelDNA/AddIn.cs (renamed from src/ExcelDNA/AddIn.cs)4
-rw-r--r--Source/ExcelDNA/Functions.cs19
-rw-r--r--Tests/SkuAssistTests.cs15
-rw-r--r--packages.config3
-rw-r--r--src/Assistant/Product.cs21
-rw-r--r--src/Assistant/SkuAssist.cs45
-rw-r--r--src/ExcelDNA/Functions.cs21
13 files changed, 257 insertions, 94 deletions
diff --git a/Rehau.Sku.Assist-AddIn-packed.xll b/Rehau.Sku.Assist-AddIn-packed.xll
deleted file mode 100644
index aea355f..0000000
--- a/Rehau.Sku.Assist-AddIn-packed.xll
+++ /dev/null
Binary files differ
diff --git a/Rehau.Sku.Assist.csproj b/Rehau.Sku.Assist.csproj
index 5b7438a..87bd1e0 100644
--- a/Rehau.Sku.Assist.csproj
+++ b/Rehau.Sku.Assist.csproj
@@ -1,5 +1,7 @@
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
+ <Import Project="packages\NUnit3TestAdapter.4.1.0\build\net35\NUnit3TestAdapter.props" Condition="Exists('packages\NUnit3TestAdapter.4.1.0\build\net35\NUnit3TestAdapter.props')" />
+ <Import Project="packages\NUnit.3.13.2\build\NUnit.props" Condition="Exists('packages\NUnit.3.13.2\build\NUnit.props')" />
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
<PropertyGroup>
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
@@ -46,6 +48,12 @@
<HintPath>packages\ExcelDna.Registration.1.5.0\lib\net452\ExcelDna.Registration.dll</HintPath>
<Private>True</Private>
</Reference>
+ <Reference Include="Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed, processorArchitecture=MSIL">
+ <HintPath>packages\Newtonsoft.Json.13.0.1\lib\net45\Newtonsoft.Json.dll</HintPath>
+ </Reference>
+ <Reference Include="nunit.framework, Version=3.13.2.0, Culture=neutral, PublicKeyToken=2638cd05610744eb, processorArchitecture=MSIL">
+ <HintPath>packages\NUnit.3.13.2\lib\net45\nunit.framework.dll</HintPath>
+ </Reference>
<Reference Include="System" />
<Reference Include="System.Buffers, Version=4.0.3.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51, processorArchitecture=MSIL">
<HintPath>packages\System.Buffers.4.5.1\lib\net461\System.Buffers.dll</HintPath>
@@ -80,12 +88,14 @@
<Reference Include="WindowsBase" />
</ItemGroup>
<ItemGroup>
- <Compile Include="src\ExcelDNA\AddIn.cs" />
- <Compile Include="src\Assistant\IProduct.cs" />
- <Compile Include="src\Assistant\Product.cs" />
- <Compile Include="src\ExcelDNA\Functions.cs" />
+ <Compile Include="Source\Assistant\HttpClientUtil.cs" />
+ <Compile Include="Source\Assistant\StoreResponse.cs" />
+ <Compile Include="Source\ExcelDNA\AddIn.cs" />
+ <Compile Include="Source\Assistant\IProduct.cs" />
+ <Compile Include="Source\ExcelDNA\Functions.cs" />
<Compile Include="Properties\AssemblyInfo.cs" />
- <Compile Include="src\Assistant\SkuAssist.cs" />
+ <Compile Include="Source\Assistant\SkuAssist.cs" />
+ <Compile Include="Tests\SkuAssistTests.cs" />
</ItemGroup>
<ItemGroup>
<None Include="app.config" />
@@ -100,5 +110,7 @@
<ErrorText>This project references NuGet package(s) that are missing on this computer. Use NuGet Package Restore to download them. For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.</ErrorText>
</PropertyGroup>
<Error Condition="!Exists('packages\ExcelDna.AddIn.1.5.0\build\ExcelDna.AddIn.targets')" Text="$([System.String]::Format('$(ErrorText)', 'packages\ExcelDna.AddIn.1.5.0\build\ExcelDna.AddIn.targets'))" />
+ <Error Condition="!Exists('packages\NUnit.3.13.2\build\NUnit.props')" Text="$([System.String]::Format('$(ErrorText)', 'packages\NUnit.3.13.2\build\NUnit.props'))" />
+ <Error Condition="!Exists('packages\NUnit3TestAdapter.4.1.0\build\net35\NUnit3TestAdapter.props')" Text="$([System.String]::Format('$(ErrorText)', 'packages\NUnit3TestAdapter.4.1.0\build\net35\NUnit3TestAdapter.props'))" />
</Target>
</Project> \ No newline at end of file
diff --git a/Source/Assistant/HttpClientUtil.cs b/Source/Assistant/HttpClientUtil.cs
new file mode 100644
index 0000000..f9c144b
--- /dev/null
+++ b/Source/Assistant/HttpClientUtil.cs
@@ -0,0 +1,74 @@
+using AngleSharp;
+using AngleSharp.Dom;
+using System;
+using System.Net;
+using System.Net.Http;
+using System.Threading.Tasks;
+using System.Text;
+
+namespace Rehau.Sku.Assist
+{
+ static class HttpClientUtil
+ {
+ private static HttpClient _httpClient = AddIn.httpClient;
+
+ public async static Task<string> GetContentByUriAsync(Uri uri)
+ {
+ ServicePointManager.SecurityProtocol =
+ SecurityProtocolType.Tls12 |
+ SecurityProtocolType.Tls11 |
+ SecurityProtocolType.Tls;
+
+ return await _httpClient.GetStringAsync(uri);
+ }
+
+ public async static Task<IDocument> ContentToDocAsync(Task<string> content)
+ {
+ IConfiguration config = Configuration.Default;
+ IBrowsingContext context = BrowsingContext.New(config);
+
+ return await context.OpenAsync(req => req.Content(content.Result));
+ }
+
+ public static Uri ConvertToUri(this string request, ResponseOrder order)
+ {
+ UriBuilder baseUri = new UriBuilder("https", "shop-rehau.ru");
+
+ baseUri.Path = "/catalogsearch/result/index/";
+ string cleanedRequest = request._CleanRequest();
+
+ switch (order)
+ {
+ case ResponseOrder.Relevance:
+ baseUri.Query = "dir=asc&order=relevance&q=" + cleanedRequest;
+ break;
+ case ResponseOrder.Name:
+ baseUri.Query = "dir=asc&order=name&q=" + cleanedRequest;
+ break;
+ case ResponseOrder.Price:
+ baseUri.Query = "dir=asc&order=price&q=" + cleanedRequest;
+ break;
+ case ResponseOrder.Series:
+ baseUri.Query = "dir=asc&order=sch_product_series&q=" + cleanedRequest;
+ break;
+ case ResponseOrder.NoSettings:
+ baseUri.Query = "q=" + cleanedRequest;
+ break;
+ default:
+ throw new ArgumentException();
+ }
+
+ return baseUri.Uri;
+ }
+
+ private static string _CleanRequest(this string input)
+ {
+ return new StringBuilder(input)
+ .Replace("+", " plus ")
+ .Replace("РХ", "")
+ .Replace("º", " ")
+ .Replace(".", " ")
+ .ToString();
+ }
+ }
+} \ No newline at end of file
diff --git a/src/Assistant/IProduct.cs b/Source/Assistant/IProduct.cs
index aca3ff5..d5db286 100644
--- a/src/Assistant/IProduct.cs
+++ b/Source/Assistant/IProduct.cs
@@ -2,8 +2,8 @@
{
interface IProduct
{
- string Sku { get; }
+ string Id { get; }
string Name { get; }
- string Uri { get; }
+ string Price { get; }
}
}
diff --git a/Source/Assistant/SkuAssist.cs b/Source/Assistant/SkuAssist.cs
new file mode 100644
index 0000000..69c1a81
--- /dev/null
+++ b/Source/Assistant/SkuAssist.cs
@@ -0,0 +1,102 @@
+using AngleSharp.Dom;
+using ExcelDna.Integration;
+using Newtonsoft.Json;
+using System;
+using System.Linq;
+using System.Runtime.Caching;
+using System.Text.RegularExpressions;
+using System.Threading.Tasks;
+
+namespace Rehau.Sku.Assist
+{
+ public enum ResponseOrder
+ {
+ NoSettings,
+ Relevance,
+ Name,
+ Price,
+ Series
+ }
+
+ public enum ProductField
+ {
+ Name,
+ Id,
+ Price
+ }
+
+ static class SkuAssist
+ {
+ public static async Task<IProduct> GetProduct(string request)
+ {
+ Uri uri = request.ConvertToUri(ResponseOrder.NoSettings);
+
+ Task<string> contentTask = Task.Run(() => HttpClientUtil.GetContentByUriAsync(uri));
+ Task<IDocument> documentTask = await contentTask.ContinueWith(content => HttpClientUtil.ContentToDocAsync(content));
+
+ return GetProduct(documentTask.Result);
+ }
+
+ public static IProduct GetProduct(IDocument d)
+ {
+ string script = d.Scripts
+ .Where(s => s.InnerHtml.Contains("dataLayer"))
+ .First()
+ .InnerHtml;
+
+ string json = script
+ .Substring(script.IndexOf("push(") + 5)
+ .TrimEnd(new[] { ')', ';', '\n', ' ' });
+
+ StoreResponce storeResponse = JsonConvert.DeserializeObject<StoreResponce>(json);
+ IProduct product = storeResponse
+ .Ecommerce
+ .Impressions
+ .Where(p => Regex.IsMatch(p.Id, @"\d{11}", RegexOptions.None))
+ .FirstOrDefault();
+
+ return product;
+ }
+
+ public static object GetProduct(string request, ProductField field)
+ {
+ IProduct product;
+
+ if (MemoryCache.Default.Contains(request))
+ {
+ product = MemoryCache.Default[request] as IProduct;
+ }
+
+ else
+ {
+ object result = ExcelAsyncUtil.Run("RauName", new[] { request },
+ delegate
+ {
+ Task<IProduct> p = Task.Run(() => GetProduct(request));
+ return p.Result;
+ });
+
+ if (result == null)
+ return "Не найдено";
+
+ if (result.Equals(ExcelError.ExcelErrorNA))
+ return "Загрузка...";
+
+ product = result as IProduct;
+ MemoryCache.Default.Add(request, product, DateTime.Now.AddMinutes(10));
+ }
+
+ switch (field)
+ {
+ case ProductField.Name:
+ return product.Name;
+ case ProductField.Id:
+ return product.Id;
+ case ProductField.Price:
+ return product.Price;
+ default:
+ return ExcelError.ExcelErrorValue;
+ }
+ }
+ }
+} \ No newline at end of file
diff --git a/Source/Assistant/StoreResponse.cs b/Source/Assistant/StoreResponse.cs
new file mode 100644
index 0000000..78fe846
--- /dev/null
+++ b/Source/Assistant/StoreResponse.cs
@@ -0,0 +1,21 @@
+using System.Collections.Generic;
+
+namespace Rehau.Sku.Assist
+{
+ public class StoreResponce
+ {
+ public Ecommerce Ecommerce { get; set; }
+ }
+
+ public class Ecommerce
+ {
+ public List<Product> Impressions { get; set; }
+ }
+
+ public class Product : IProduct
+ {
+ public string Id { get; set; }
+ public string Name { get; set; }
+ public string Price { get; set; }
+ }
+} \ No newline at end of file
diff --git a/src/ExcelDNA/AddIn.cs b/Source/ExcelDNA/AddIn.cs
index dd99667..0505e5b 100644
--- a/src/ExcelDNA/AddIn.cs
+++ b/Source/ExcelDNA/AddIn.cs
@@ -1,13 +1,17 @@
using ExcelDna.Integration;
using ExcelDna.Registration;
+using System.Net.Http;
namespace Rehau.Sku.Assist
{
public class AddIn : IExcelAddIn
{
+ public static HttpClient httpClient;
+
public void AutoOpen()
{
RegisterFunctions();
+ httpClient = new HttpClient();
}
public void AutoClose()
diff --git a/Source/ExcelDNA/Functions.cs b/Source/ExcelDNA/Functions.cs
new file mode 100644
index 0000000..a9bdfca
--- /dev/null
+++ b/Source/ExcelDNA/Functions.cs
@@ -0,0 +1,19 @@
+using ExcelDna.Integration;
+
+namespace Rehau.Sku.Assist
+{
+ public class Functions
+ {
+ [ExcelFunction]
+ public static object RAUNAME(string request)
+ => SkuAssist.GetProduct(request, ProductField.Name);
+
+ [ExcelFunction]
+ public static object RAUSKU(string request)
+ => SkuAssist.GetProduct(request, ProductField.Id);
+
+ [ExcelFunction]
+ public static object RAUPRICE(string request)
+ => SkuAssist.GetProduct(request, ProductField.Price);
+ }
+} \ No newline at end of file
diff --git a/Tests/SkuAssistTests.cs b/Tests/SkuAssistTests.cs
new file mode 100644
index 0000000..19f8a0c
--- /dev/null
+++ b/Tests/SkuAssistTests.cs
@@ -0,0 +1,15 @@
+using NUnit.Framework;
+
+namespace Rehau.Sku.Assist.Tests
+{
+ [TestFixture]
+ public class SkuAssistTests
+ {
+ [Test]
+ public static void BaseTest()
+ {
+ var result = Functions.RAUNAME("160001");
+ Assert.AreEqual("Надвижная гильза REHAU RAUTITAN РХ (11600011001)", result);
+ }
+ }
+}
diff --git a/packages.config b/packages.config
index cc383d8..b42dcc6 100644
--- a/packages.config
+++ b/packages.config
@@ -4,6 +4,9 @@
<package id="ExcelDna.AddIn" version="1.5.0" targetFramework="net480" />
<package id="ExcelDna.Integration" version="1.5.0" targetFramework="net480" />
<package id="ExcelDna.Registration" version="1.5.0" targetFramework="net480" />
+ <package id="Newtonsoft.Json" version="13.0.1" targetFramework="net48" />
+ <package id="NUnit" version="3.13.2" targetFramework="net48" />
+ <package id="NUnit3TestAdapter" version="4.1.0" targetFramework="net48" />
<package id="System.Buffers" version="4.5.1" targetFramework="net48" />
<package id="System.Memory" version="4.5.4" targetFramework="net48" />
<package id="System.Numerics.Vectors" version="4.5.0" targetFramework="net48" />
diff --git a/src/Assistant/Product.cs b/src/Assistant/Product.cs
deleted file mode 100644
index 17a7065..0000000
--- a/src/Assistant/Product.cs
+++ /dev/null
@@ -1,21 +0,0 @@
-namespace Rehau.Sku.Assist
-{
- public class Product : IProduct
- {
- public string Sku { get; }
- public string Name { get; }
-
- public string Uri => throw new System.NotImplementedException();
-
- public Product(string sku, string name)
- {
- Sku = sku;
- Name = name;
- }
-
- public override string ToString()
- {
- return $"{this.Name} ({this.Sku})";
- }
- }
-} \ No newline at end of file
diff --git a/src/Assistant/SkuAssist.cs b/src/Assistant/SkuAssist.cs
deleted file mode 100644
index dc36dc0..0000000
--- a/src/Assistant/SkuAssist.cs
+++ /dev/null
@@ -1,45 +0,0 @@
-using AngleSharp;
-using AngleSharp.Dom;
-using System.Linq;
-using System.Net;
-using System.Net.Http;
-using System.Text.RegularExpressions;
-using System.Threading.Tasks;
-
-namespace Rehau.Sku.Assist
-{
- static class SkuAssist
- {
- public async static Task<string> GetContent(string request, HttpClient httpClient)
- {
- string uri = "https://shop-rehau.ru/catalogsearch/result/?q=" + request._CleanRequest();
- ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;
-
- return await httpClient.GetStringAsync(uri);
- }
-
- public async static Task<IDocument> GetDocument(Task<string> source)
- {
- IConfiguration config = Configuration.Default;
- IBrowsingContext context = BrowsingContext.New(config);
-
- return await context.OpenAsync(req => req.Content(source.Result));
- }
-
- public static IProduct GetProductFromDocument(IDocument document)
- {
- return document
- .All
- .Where(e => e.ClassName == "product-item__desc-top")
- .Select(e => new Product(e.Children[0].TextContent, e.Children[1].TextContent.Trim(new[] { '\n', ' ' })))
- .FirstOrDefault();
- }
-
- private static string _CleanRequest(this string input)
- {
- return input.Replace("+", " plus ");
- }
- }
-}
-
-
diff --git a/src/ExcelDNA/Functions.cs b/src/ExcelDNA/Functions.cs
deleted file mode 100644
index ec9c607..0000000
--- a/src/ExcelDNA/Functions.cs
+++ /dev/null
@@ -1,21 +0,0 @@
-using AngleSharp.Dom;
-using ExcelDna.Integration;
-using System.Net.Http;
-using System.Threading.Tasks;
-
-namespace Rehau.Sku.Assist
-{
- public class Functions
- {
- private static HttpClient _httpClient = new HttpClient();
-
- [ExcelFunction]
- public static async Task<string> RAUNAME(string request)
- {
- Task<string> contentTask = Task.Run(() => SkuAssist.GetContent(request, _httpClient));
- Task<IDocument> documentTask = await contentTask.ContinueWith(content => SkuAssist.GetDocument(content));
- IProduct product = await documentTask.ContinueWith(doc => SkuAssist.GetProductFromDocument(doc.Result));
- return product == null ? ExcelError.ExcelErrorNull.ToString() : product.ToString();
- }
- }
-} \ No newline at end of file
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-20 08:27:28 +0000