Import Selco Data

gistfile1.txt

#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;;