Created
January 19, 2022 17:43
-
-
Save HarryMcCarney/2a1ffd514edab2213725eac6cfad0619 to your computer and use it in GitHub Desktop.
Import Selco Data
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#r "nuget: FSharp.Data" | |
#r "nuget: Microsoft.Data.SqlClient" | |
#r "nuget: Dapper" | |
open Microsoft.Data.SqlClient | |
open Dapper | |
open FSharp.Data | |
open System.Threading | |
open System | |
let nullIfNone s = | |
match s with | |
| Some v-> v | |
| None -> null | |
let convertToPence (price: Decimal) : int = | |
(int (price * Decimal 100.00 )) | |
[<Literal>] | |
let CategoriesPath = __SOURCE_DIRECTORY__ + "/CategoriesSample.json" | |
type Categories = JsonProvider<CategoriesPath, InferTypesFromValues=true> | |
[<Literal>] | |
let SubCategoriesPath = __SOURCE_DIRECTORY__ + "/SubCategoriesSample.json" | |
type SubCategories = JsonProvider<SubCategoriesPath, InferTypesFromValues=true> | |
[<Literal>] | |
let ProductsPath = __SOURCE_DIRECTORY__ + "/ProductsSample.json" | |
type Products = JsonProvider<ProductsPath, InferTypesFromValues=true> | |
[<Literal>] | |
let ProductPath = __SOURCE_DIRECTORY__ + "/ProductSample.json" | |
type Product = JsonProvider<ProductPath, InferTypesFromValues=true> | |
let baseUrl = "https://www.selcobw.com" | |
let SQLconnectionString = @"server=hncdev.database.windows.net;Database=tradekart_dev_selco;User ID=someuser;Password=somepwd;Trusted_Connection=False;Encrypt=True;Connection Timeout=300;" | |
let openConnection = new SqlConnection(SQLconnectionString) | |
let cookie = "selco_cookie_consent=true; PHPSESSID=6ad9a1e2ce566a9b16e8e11502a0324c; _sp_ses.3b01=*; _sp_id.3b01=c3a538df0e328ae2.1637595397.6.1638362617.1638270196; PHPSESSID=6ad9a1e2ce566a9b16e8e11502a0324c" | |
let insertVerticalSQL() = | |
let id = Guid.NewGuid() |> string | |
let sql = sprintf "insert Vertical (Id, Name) select '%s', '%s'" id "Decorating" | |
printfn "%s" sql | |
openConnection.Execute(sql) |> ignore | |
id | |
let insertCategory (verticalId: string) (category: Categories.Child) = | |
let id = Guid.NewGuid() |> string | |
let sql = sprintf "insert Category (Id, SourceRef, VerticalId, Name, Image) select '%s', '%s', '%s', '%s', '%s'" id (string category.Id) verticalId category.Name category.Thumbnail | |
printfn "%s" sql | |
openConnection.Execute(sql) |> ignore | |
let insertSubCategory (categoryId: string) (subCategory: SubCategories.Child) = | |
let id = Guid.NewGuid() |> string | |
let sql = sprintf "insert SubCategory | |
(Id, SourceRef, CategoryId, Name, Image) | |
select '%s', '%s', (select id from category where sourceref = '%s'), '%s', '%s'" | |
id (string subCategory.Id) categoryId subCategory.Name (nullIfNone subCategory.Thumbnail) | |
printfn "%s" sql | |
openConnection.Execute(sql) |> ignore | |
let insertProduct (subCategoryId: string) (product: Product.Item) = | |
let id = Guid.NewGuid() |> string | |
let escapedDescription = product.Description.Html.Replace("'", "''") | |
let sql = sprintf "insert Product (Id, SourceRef, SubCategoryId, Name, Image, Description, ManufacturerCode, Price) select '%s', '%s', (select id from subcategory where sourceref = '%s'), '%s', '%s', '%s', '%s',%i" id (string product.Id) subCategoryId product.Name product.SmallImage.Url escapedDescription (string product.ManufacturerCode) (convertToPence product.Price.ExPrice) | |
printfn "%s" sql | |
openConnection.Execute(sql) |> ignore | |
let makeRequest url = | |
let response = Http.Request(url, headers = [ "cookie", cookie ]) | |
match response.Body with | Text x -> x | _ -> failwith "request failed" | |
type ProdDetail = { | |
Detail : ResizeArray<Products.Item> | |
SubCategory: string | |
} | |
let getProductDetails (product : Products.Item)= | |
let url = sprintf "%s/graphql?query=query productDetail($urlKey:String,$sku:String,$id:Int=0) {productDetail:products(filter:{url_key:{eq:$urlKey},sku:{eq:$sku}}){items{sku __typename id stock_status name stock_status special_price campaign_enabled campaign_label campaign_image selco_brand{logo_url name logo_width logo_height __typename}small_image{label url __typename}available_in_branch breadcrumbs{category_id category_level category_name category_url_key __typename}delivery_options media_gallery_entries{large_height large_width large_url medium_url small_url media_type position label __typename}benefits_all certifications_all files_all links_all manufacturer_code specifications{label value __typename}supplier_website price{ex_price inc_price price_per has_valid_special_price __typename}short_description{html __typename}description{html __typename}flag_clearance flag_new flag_while_stock_lasts meta_title meta_keyword meta_description url_key url_path id is_poa}__typename}customAttributeMetadata(attributes:{attribute_code:\"delivery_options\",entity_type:\"4\"}){items{attribute_code entity_type attribute_options{value label __typename}__typename}__typename}yotpoReviewSummary(productId:$id){average_score product_id reviews{author description score __typename}star_distribution{star value __typename}total_reviews __typename}}&operationName=productDetail&variables={\"id\":%i,\"urlKey\":\"%s\"}" baseUrl (product.Id) (product.UrlKey) | |
let body = makeRequest url | |
(Product.Parse(body).Data.ProductDetail.Items.[0]) | |
let requestProductDetails (products: ProdDetail) = | |
products.Detail | |
|> Seq.toArray | |
|> Array.map(fun x -> getProductDetails x) | |
|> Array.map(fun x -> insertProduct products.SubCategory x) | |
|> ignore | |
let requestProductsForSubCategory (subCategory: SubCategories.Child) = | |
let buildProductUrl (subCategoryId: int) (currentPage: int) = | |
sprintf "%s/graphql?query=query categoryProducts($categoryId:String=\"237\",$sortPrice:SortEnum,$sortName:SortEnum,$minPrice:String,$maxPrice:String,$currentPage:Int=1,$pageSize:Int=15,$productFilters:[ProductFilterPairs!]) {categoryProducts:products(currentPage:$currentPage,pageSize:$pageSize,sort:{price:$sortPrice,name:$sortName},filter:{category_id:{eq:$categoryId},price:{from:$minPrice,to:$maxPrice}},productFilters:$productFilters){total_count page_info{current_page page_size total_pages __typename}filters{filter_items{items_count label value_string __typename}filter_items_count name request_var __typename}items{__typename id name sku stock_status special_price url_key flag_clearance flag_new flag_while_stock_lasts campaign_enabled campaign_label campaign_image small_image{label url __typename}breadcrumbs{category_url_key __typename}delivery_options price{ex_price inc_price price_per has_valid_special_price __typename}is_poa}__typename}customAttributeMetadata(attributes:{attribute_code:\"delivery_options\",entity_type:\"4\"}){items{attribute_code entity_type attribute_options{value label __typename}__typename}__typename}}&operationName=categoryProducts&variables={\"categoryId\":%i,\"currentPage\":%i,\"pageSize\":15}" baseUrl subCategoryId currentPage | |
let mutable products = {SubCategory = (string subCategory.Id); Detail = new ResizeArray<Products.Item>()} | |
let firstPageUrl = buildProductUrl subCategory.Id 1 | |
let body = makeRequest firstPageUrl | |
let totalPages = (Products.Parse(body).Data.CategoryProducts.PageInfo.TotalPages) | |
let pageOneItems = Products.Parse(body).Data.CategoryProducts.Items | |
products.Detail.AddRange pageOneItems | |
{2 .. totalPages} | |
|> Seq.map (fun x -> makeRequest (buildProductUrl subCategory.Id x)) | |
|> Seq.map (fun x -> products.Detail.AddRange(Products.Parse(x).Data.CategoryProducts.Items |> Seq.toArray)) | |
|> ignore | |
products | |
let requestSubCategories (category: Categories.Child) : SubCategories.Child array= | |
let url = sprintf "%s/graphql?query=query category($id:Int=7) {category(id:$id){description name url_key url_path breadcrumbs{category_id category_name category_level category_url_key __typename}children_count children{include_in_menu url_path url_key id display_mode image thumbnail children_count product_count name __typename}excerpt meta_description meta_title image thumbnail id __typename}}&operationName=category&variables={\"id\":%i}" baseUrl category.Id | |
let body = makeRequest url | |
let subCateogries = (SubCategories.Parse(body).Data.Category.Children) | |
subCateogries | |
|> Array.map (fun x -> insertSubCategory (string category.Id) x) | |
|> ignore | |
subCateogries | |
let requestCategories = | |
let url = sprintf "%s%s" baseUrl "/graphql?query=query category($id:Int=7) {category(id:$id){description name url_key url_path breadcrumbs{category_id category_name category_level category_url_key __typename}children_count children{include_in_menu url_path url_key id display_mode image thumbnail children_count product_count name __typename}excerpt meta_description meta_title image thumbnail id __typename}}&operationName=category&variables={\"id\":356}" | |
let body = makeRequest url | |
let category = (Categories.Parse(body).Data.Category) | |
let verticalId = insertVerticalSQL() | |
category.Children | |
|> Array.map (fun x -> insertCategory verticalId x) | |
|> ignore | |
category.Children | |
|> Array.map (fun x -> requestSubCategories x) | |
|> Array.concat | |
|> Array.map (fun y -> requestProductsForSubCategory y) | |
|> Array.map(fun x -> requestProductDetails x) | |
requestCategories;; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment