Thanks . for replying ..
please find the data-config
On Thu, Jun 11, 2015 at 6:06 AM, Chris Hostetter <hossman_luc...@fucit.org>
wrote:
>
> : The guys was using delta import anyway, so maybe the problem is
> : different and not related to the clean.
>
> that's not what the logs say.
>
> Here's what i see...
>
> Log begins with server startup @ "Jun 10, 2015 11:14:56 AM"
>
> The DeletionPolicy for the "shopclue_prod" core is initialized at "Jun
> 10, 2015 11:15:04 AM" and we see a few interesting things here we note
> for the future as we keep reading...
>
> 1) There is currently "commits:num=1" commits on disk
> 2) the current index dir in use is "index.20150311161021822"
> 3) the current segment & generation are "segFN=segments_1a,generation=46"
>
> Immediately after this, we see some searcher warming using a searcher with
> this same segments file, and then this searcher is registered ("Jun 10,
> 2015 11:15:05 AM") and the core is registered.
>
> Next we see some replication polling, and we see what look like some
> simple monitoring requests for "q=*" which return hits=85898 being
> repeated over and over.
>
> At "Jun 10, 2015 11:16:30 AM" we see some requests for /dataimport that
> look like they are coming from the UI. and then at "Jun 10, 2015 11:17:01
> AM" we see a request for a full import started.
>
> We have no idea what the data import configuration file looks like, so we
> have no idea if clean=false is being used or not. it's certianly not
> specified in the URL.
>
> We see some more monitoring URLs returning hits=85898 and some more
> /repliation status calls, and then @ "Jun 10, 2015 11:18:02 AM" we see the
> first commit executed since hte server started up.
>
> there's no indication that this commit came from an external request (eg
> "/update") so probably was made by some internal request. One
> possiblility is that it came from DIH finishing -- but i doubt it, i'm
> fairly sure that would have involved more logging then this. A more
> probably scenerio is that it came from an autoCommit setting -- the fact
> that it is almost exactly 60 seconds after DIH started -- and almost
> exactly 60 seconds after DIH may have done a deleteAll query due to
> clean=true -- makes it seem very likely that this was a 1 minute
> autoCommit)
>
> (but since we don't have either hte data import config, or the
> solrconfig.xml, we have no way of knowing -- it's all just guess work.)
>
> Very importantly, note that this commit is not opening a new searcher...
>
> Jun 10, 2015 11:18:02 AM org.apache.solr.update.DirectUpdateHandler2 commit
> INFO: start
> commit{,optimize=false,openSearcher=false,waitSearcher=true,expungeDeletes=false,softCommit=false,prepareCommit=false}
>
> Here are some other interesting things to note from the logging
> that comes from the DeletionPolicy when this commit happens...
>
> 1) it now notes that there are commits:num=2 on disk
> 2) the current index dir hasn't changed (index.20150311161021822) so
> some weird replication command didn't swap the world out from under us
> 3) the newest segment/generation are "segFN=segments_1b,generation=47"
> 4) the newest commit has no other files in it besides the segments file.
>
> this means, with out a doubt, there are no documents in this commits view
> of the index. they have all been deleted by something.
>
>
> At this point the *old* searcher (for commit generation 46) is still in
> use however -- nothing has done an openSearcher=true.
>
> we see more /dataimport status requests, and other requests that appear to
> come from the Solr UI, and more monitoring queries that still return
> hits=85898 because the same searcher is in use.
>
> At "Jun 10, 2015 11:27:04 AM" we see another commit happen -- again, no
> indication that this came from an outside /update request, so it might be
> from DIH, or it might be from an autoCommit setting. the fact that it is
> nearly exactly 10 minutes after DIH started (and probably did a clean=true
> deleteAll query) makes it seem extremely likely this is an autoSoftCommit
> setting kicking in.
>
> Very importantly, note that this softCommit *does* open a new searcher...
>
> Jun 10, 2015 11:27:04 AM org.apache.solr.update.DirectUpdateHandler2 commit
> INFO: start
>
> commit{,optimize=false,openSearcher=true,waitSearcher=true,expungeDeletes=false,softCommit=true,prepareCommit=false}
>
>
> In less then a second, this new searcher is warmed up and the next time we
> see a q=* monitoring query get logged, it returns hits=0.
>
> Note that at no point in the logs, after the DataImporter is started, do
> we see it log anything other then that it has initiated the request to
> MySQL -- we do see some logs starting ~ "Jun 10, 2015 11:41:19 AM"
> indicating that someone was using the Web UI to look at the dataimport
> handler's status report. it would be really nice to know what that person
> saw at that point -- because my guess is DIH was still running and was
> staled waiting for MySql, and hadn't even started adding docs to Solr (if
> it had, i'm certian there would have been some log of it).
>
> So instead, the combination of a (probable) DIH clean=true option and a
> (near certainty) autoCommit=60sec and autoSoftCommit=10min ment that a new
> commit was created after the clean, and that commit was opened as a new
> searcher before the DIH ever finished.
>
> Or at least ... that's my best guess based solely on the Solr logs, w/o
> any concrete info on what the configs look like, or what the User observed
> at the time when looking at the DIH status.
>
>
>
> -Hoss
> http://www.lucidworks.com/
>
<dataConfig>
<script><![CDATA[
function addfield(row){
if(row.get('COLcolor_col')=='By Mega Pixel'){ var value= row.get('COLcolor_col_val');row.put('by_mega_pixel',value);}
if(row.get('COLcolor_col')=='By Facility'){ var value= row.get('COLcolor_col_val');row.put('by_facility',value);}
if(row.get('COLcolor_col')=='By Genre'){ var value= row.get('COLcolor_col_val');row.put('by_genre',value);}
if(row.get('COLcolor_col')=='By Processor'){ var value= row.get('COLcolor_col_val');row.put('by_processor',value);}
if(row.get('COLcolor_col')=='Saree Colour'){ var value= row.get('COLcolor_col_val');row.put('saree_colour',value);}
if(row.get('COLcolor_col')=='By Material'){ var value= row.get('COLcolor_col_val');row.put('by_material',value);}
if(row.get('COLcolor_col')=='By Suitable For'){ var value= row.get('COLcolor_col_val');row.put('by_suitable_for',value);}
if(row.get('COLcolor_col')=='By Compatibility'){ var value= row.get('COLcolor_col_val');row.put('by_compatibility',value);}
if(row.get('COLcolor_col')=='By Capacity'){ var value= row.get('COLcolor_col_val');row.put('by_capacity',value);}
if(row.get('COLcolor_col')=='By Optical Zoom'){ var value= row.get('COLcolor_col_val');row.put('by_optical_zoom',value);}
if(row.get('COLcolor_col')=='By Drive Supported'){ var value= row.get('COLcolor_col_val');row.put('by_drive_supported',value);}
if(row.get('COLcolor_col')=='By Headphone Type'){ var value= row.get('COLcolor_col_val');row.put('by_headphone_type',value);}
if(row.get('COLcolor_col')=='Wired or Wireless'){ var value= row.get('COLcolor_col_val');row.put('wired_or_wireless',value);}
if(row.get('COLcolor_col')=='By Weight (in Kg)'){ var value= row.get('COLcolor_col_val');row.put('by_weight_in_kg',value);}
if(row.get('COLcolor_col')=='By Energy Rating'){ var value= row.get('COLcolor_col_val');row.put('by_energy_rating',value);}
if(row.get('COLcolor_col')=='By Publisher'){ var value= row.get('COLcolor_col_val');row.put('by_publisher',value);}
if(row.get('COLcolor_col')=='By Spray Type'){ var value= row.get('COLcolor_col_val');row.put('by_spray_type',value);}
if(row.get('COLcolor_col')=='By Memory Capacity'){ var value= row.get('COLcolor_col_val');row.put('by_memory_capacity',value);}
if(row.get('COLcolor_col')=='By Device'){ var value= row.get('COLcolor_col_val');row.put('by_device',value);}
if(row.get('COLcolor_col')=='By Colour & Finish'){ var value= row.get('COLcolor_col_val');row.put('by_colour_and_finish',value);}
if(row.get('COLcolor_col')=='By System Memory'){ var value= row.get('COLcolor_col_val');row.put('by_system_memory',value);}
if(row.get('COLcolor_col')=='By Number of PC'){ var value= row.get('COLcolor_col_val');row.put('by_number_of_pc',value);}
if(row.get('COLcolor_col')=='By Function Type'){ var value= row.get('COLcolor_col_val');row.put('by_function_type',value);}
if(row.get('COLcolor_col')=='By Language'){ var value= row.get('COLcolor_col_val');row.put('by_language',value);}
if(row.get('COLcolor_col')=='By Display Size'){ var value= row.get('COLcolor_col_val');row.put('by_display_size',value);}
if(row.get('COLcolor_col')=='By Subscription Validity'){ var value= row.get('COLcolor_col_val');row.put('by_subscription_validity',value);}
if(row.get('COLcolor_col')=='By Loading Type'){ var value= row.get('COLcolor_col_val');row.put('by_loading_type',value);}
if(row.get('COLcolor_col')=='By Screen Size'){ var value= row.get('COLcolor_col_val');row.put('by_screen_size',value);}
if(row.get('COLcolor_col')=='By Function'){ var value= row.get('COLcolor_col_val');row.put('by_function',value);}
if(row.get('COLcolor_col')=='By Platform'){ var value= row.get('COLcolor_col_val');row.put('by_platform',value);}
if(row.get('COLcolor_col')=='By Color'){ var value= row.get('COLcolor_col_val');row.put('by_color',value);}
if(row.get('COLcolor_col')=='By Strap Material'){ var value= row.get('COLcolor_col_val');row.put('by_strap_material',value);}
if(row.get('COLcolor_col')=='By Compatible Laptop Size'){ var value= row.get('COLcolor_col_val');row.put('by_compatible_laptop_size',value);}
if(row.get('COLcolor_col')=='By Service Count'){ var value= row.get('COLcolor_col_val');row.put('by_service_count',value);}
if(row.get('COLcolor_col')=='By Shape'){ var value= row.get('COLcolor_col_val');row.put('by_shape',value);}
if(row.get('COLcolor_col')=='By Ink Color'){ var value= row.get('COLcolor_col_val');row.put('by_ink_color',value);}
if(row.get('COLcolor_col')=='By Motherboard Supported'){ var value= row.get('COLcolor_col_val');row.put('by_motherboard_supported',value);}
if(row.get('COLcolor_col')=='By Interface'){ var value= row.get('COLcolor_col_val');row.put('by_interface',value);}
if(row.get('COLcolor_col')=='By Placement'){ var value= row.get('COLcolor_col_val');row.put('by_placement',value);}
if(row.get('COLcolor_col')=='By Star Rating'){ var value= row.get('COLcolor_col_val');row.put('by_star_rating',value);}
if(row.get('COLcolor_col')=='By Purifier Type'){ var value= row.get('COLcolor_col_val');row.put('by_purifier_type',value);}
if(row.get('COLcolor_col')=='By Design'){ var value= row.get('COLcolor_col_val');row.put('by_design',value);}
if(row.get('COLcolor_col')=='By Age'){ var value= row.get('COLcolor_col_val');row.put('by_age',value);}
if(row.get('COLcolor_col')=='By Purity'){ var value= row.get('COLcolor_col_val');row.put('by_purity',value);}
if(row.get('COLcolor_col')=='By Operating System'){ var value= row.get('COLcolor_col_val');row.put('by_operating_system',value);}
if(row.get('COLcolor_col')=='By Type'){ var value= row.get('COLcolor_col_val');row.put('by_type',value);}
if(row.get('COLcolor_col')=='By Neck Lines'){ var value= row.get('COLcolor_col_val');row.put('by_neck_lines',value);}
if(row.get('COLcolor_col')=='By Sleeves'){ var value= row.get('COLcolor_col_val');row.put('by_sleeves',value);}
if(row.get('COLcolor_col')=='By Strap Colour'){ var value= row.get('COLcolor_col_val');row.put('by_strap_colour',value);}
if(row.get('COLcolor_col')=='By Colour'){ var value= row.get('COLcolor_col_val');row.put('by_colour',value);}
if(row.get('COLcolor_col')=='By Primary Camera'){ var value= row.get('COLcolor_col_val');row.put('by_primary_camera',value);}
if(row.get('COLcolor_col')=='By Door Type'){ var value= row.get('COLcolor_col_val');row.put('by_door_type',value);}
if(row.get('COLcolor_col')=='By PEGI Rating'){ var value= row.get('COLcolor_col_val');row.put('by_pegi_rating',value);}
if(row.get('COLcolor_col')=='By Character'){ var value= row.get('COLcolor_col_val');row.put('by_character',value);}
if(row.get('COLcolor_col')=='By Weight (in gm)'){ var value= row.get('COLcolor_col_val');row.put('by_weight_in_gm',value);}
if(row.get('COLcolor_col')=='By Occasion'){ var value= row.get('COLcolor_col_val');row.put('by_occasion',value);}
if(row.get('COLcolor_col')=='By Dial Shape'){ var value= row.get('COLcolor_col_val');row.put('by_dial_shape',value);}
if(row.get('COLcolor_col')=='By Features'){ var value= row.get('COLcolor_col_val');row.put('by_features',value);}
if(row.get('COLcolor_col')=='By Purification Method'){ var value= row.get('COLcolor_col_val');row.put('by_purification_method',value);}
if(row.get('COLcolor_col')=='By Playing Mode'){ var value= row.get('COLcolor_col_val');row.put('by_playing_mode',value);}
if(row.get('COLcolor_col')=='By Class'){ var value= row.get('COLcolor_col_val');row.put('by_class',value);}
if(row.get('COLcolor_col')=='By Dial Color'){ var value= row.get('COLcolor_col_val');row.put('by_dial_color',value);}
if(row.get('COLcolor_col')=='By Hard Drive Capacity'){ var value= row.get('COLcolor_col_val');row.put('by_hard_drive_capacity',value);}
if(row.get('COLcolor_col')=='By Chocolates Type'){ var value= row.get('COLcolor_col_val');row.put('by_chocolates_type',value);}
if(row.get('COLcolor_col')=='By Graphic Memory'){ var value= row.get('COLcolor_col_val');row.put('by_graphic_memory',value);}
if(row.get('COLcolor_col')=='By Hard Drive'){ var value= row.get('COLcolor_col_val');row.put('by_hard_drive',value);}
if(row.get('COLcolor_col')=='By Binding'){ var value= row.get('COLcolor_col_val');row.put('by_binding',value);}
if(row.get('COLcolor_col')=='By Headset Type'){ var value= row.get('COLcolor_col_val');row.put('by_headset_type',value);}
if(row.get('COLcolor_col')=='By Wired/Wireless'){ var value= row.get('COLcolor_col_val');row.put('by_wired_or_wireless',value);}
if(row.get('COLcolor_col')=='Suitable For'){ var value= row.get('COLcolor_col_val');row.put('suitable_for',value);}
if(row.get('COLcolor_col')=='Speaker Configuration'){ var value= row.get('COLcolor_col_val');row.put('speaker_configuration',value);}
if(row.get('COLcolor_col')=='Touchscreen'){ var value= row.get('COLcolor_col_val');row.put('touchscreen',value);}
if(row.get('COLcolor_col')=='Laptop Type'){ var value= row.get('COLcolor_col_val');row.put('laptop_type',value);}
if(row.get('COLcolor_col')=='By Logo'){ var value= row.get('COLcolor_col_val');row.put('by_logo',value);}
if(row.get('COLcolor_col')=='Bags Material'){ var value= row.get('COLcolor_col_val');row.put('bags_material',value);}
if(row.get('COLcolor_col')=='Burner Type'){ var value= row.get('COLcolor_col_val');row.put('burner_type',value);}
if(row.get('COLcolor_col')=='HD'){ var value= row.get('COLcolor_col_val');row.put('hd',value);}
if(row.get('COLcolor_col')=='Built In Microphone'){ var value= row.get('COLcolor_col_val');row.put('built_in_microphone',value);}
if(row.get('COLcolor_col')=='Webcam Mega Pixels'){ var value= row.get('COLcolor_col_val');row.put('webcam_mega_pixels',value);}
if(row.get('COLcolor_col')=='By Occassion'){ var value= row.get('COLcolor_col_val');row.put('by_occassion',value);}
if(row.get('COLcolor_col')=='Hub & Card Reader Type'){ var value= row.get('COLcolor_col_val');row.put('hub_and_card_reader_type',value);}
if(row.get('COLcolor_col')=='RAMs Capacity'){ var value= row.get('COLcolor_col_val');row.put('rams_capacity',value);}
if(row.get('COLcolor_col')=='TV Tuner Placement'){ var value= row.get('COLcolor_col_val');row.put('tv_tuner_placement',value);}
if(row.get('COLcolor_col')=='Graphic Card Memory'){ var value= row.get('COLcolor_col_val');row.put('graphic_card_memory',value);}
if(row.get('COLcolor_col')=='With Recording'){ var value= row.get('COLcolor_col_val');row.put('with_recording',value);}
if(row.get('COLcolor_col')=='Landline Type'){ var value= row.get('COLcolor_col_val');row.put('landline_type',value);}
if(row.get('COLcolor_col')=='Ideal For'){ var value= row.get('COLcolor_col_val');row.put('ideal_for',value);}
if(row.get('COLcolor_col')=='Candles Type'){ var value= row.get('COLcolor_col_val');row.put('candles_type',value);}
if(row.get('COLcolor_col')=='Lamps Type'){ var value= row.get('COLcolor_col_val');row.put('lamps_type',value);}
if(row.get('COLcolor_col')=='By Warranty'){ var value= row.get('COLcolor_col_val');row.put('by_warranty',value);}
if(row.get('COLcolor_col')=='Bulb Type'){ var value= row.get('COLcolor_col_val');row.put('bulb_type',value);}
if(row.get('COLcolor_col')=='Wall Lights Type'){ var value= row.get('COLcolor_col_val');row.put('wall_lights_type',value);}
if(row.get('COLcolor_col')=='By Fabric'){ var value= row.get('COLcolor_col_val');row.put('by_fabric',value);}
if(row.get('COLcolor_col')=='By Style'){ var value= row.get('COLcolor_col_val');row.put('by_style',value);}
if(row.get('COLcolor_col')=='By Length'){ var value= row.get('COLcolor_col_val');row.put('by_length',value);}
if(row.get('COLcolor_col')=='By Collars & Neck'){ var value= row.get('COLcolor_col_val');row.put('by_collars_and_neck',value);}
if(row.get('COLcolor_col')=='By Work'){ var value= row.get('COLcolor_col_val');row.put('by_work',value);}
if(row.get('COLcolor_col')=='3D'){ var value= row.get('COLcolor_col_val');row.put('3d',value);}
if(row.get('COLcolor_col')=='By fit'){ var value= row.get('COLcolor_col_val');row.put('by_fit',value);}
if(row.get('COLcolor_col')=='By pattern'){ var value= row.get('COLcolor_col_val');row.put('by_pattern',value);}
if(row.get('COLcolor_col')=='By collar'){ var value= row.get('COLcolor_col_val');row.put('by_collar',value);}
if(row.get('COLcolor_col')=='By Theme'){ var value= row.get('COLcolor_col_val');row.put('by_theme',value);}
if(row.get('COLcolor_col')=='With Frame'){ var value= row.get('COLcolor_col_val');row.put('with_frame',value);}
if(row.get('COLcolor_col')=='Frames Type'){ var value= row.get('COLcolor_col_val');row.put('frames_type',value);}
if(row.get('COLcolor_col')=='Product Type'){ var value= row.get('COLcolor_col_val');row.put('product_type',value);}
if(row.get('COLcolor_col')=='Heater Type'){ var value= row.get('COLcolor_col_val');row.put('heater_type',value);}
if(row.get('COLcolor_col')=='Flavor'){ var value= row.get('COLcolor_col_val');row.put('flavor',value);}
if(row.get('COLcolor_col')=='Compatible Mobiles'){ var value= row.get('COLcolor_col_val');row.put('compatible_mobiles',value);}
if(row.get('COLcolor_col')=='Bags Type'){ var value= row.get('COLcolor_col_val');row.put('bags_type',value);}
if(row.get('COLcolor_col')=='Impression Types'){ var value= row.get('COLcolor_col_val');row.put('impression_types',value);}
if(row.get('COLcolor_col')=='Instrument Type'){ var value= row.get('COLcolor_col_val');row.put('instrument_type',value);}
if(row.get('COLcolor_col')=='Capacity'){ var value= row.get('COLcolor_col_val');row.put('capacity',value);}
if(row.get('COLcolor_col')=='Stones Type'){ var value= row.get('COLcolor_col_val');row.put('stones_type',value);}
if(row.get('COLcolor_col')=='Guitar Type'){ var value= row.get('COLcolor_col_val');row.put('guitar_type',value);}
if(row.get('COLcolor_col')=='Grooming Type'){ var value= row.get('COLcolor_col_val');row.put('grooming_type',value);}
if(row.get('COLcolor_col')=='By Animals Type'){ var value= row.get('COLcolor_col_val');row.put('by_animals_type',value);}
if(row.get('COLcolor_col')=='By Impression'){ var value= row.get('COLcolor_col_val');row.put('by_impression',value);}
if(row.get('COLcolor_col')=='Battery Type'){ var value= row.get('COLcolor_col_val');row.put('battery_type',value);}
if(row.get('COLcolor_col')=='By Concern'){ var value= row.get('COLcolor_col_val');row.put('by_concern',value);}
if(row.get('COLcolor_col')=='Display Resolution'){ var value= row.get('COLcolor_col_val');row.put('display_resolution',value);}
if(row.get('COLcolor_col')=='Processor Brand'){ var value= row.get('COLcolor_col_val');row.put('processor_brand',value);}
if(row.get('COLcolor_col')=='Skin Type'){ var value= row.get('COLcolor_col_val');row.put('skin_type',value);}
if(row.get('COLcolor_col')=='By Size'){ var value= row.get('COLcolor_col_val');row.put('by_size',value);}
if(row.get('COLcolor_col')=='By Certification'){ var value= row.get('COLcolor_col_val');row.put('by_certification',value);}
if(row.get('COLcolor_col')=='By Rings Design'){ var value= row.get('COLcolor_col_val');row.put('by_rings_design',value);}
if(row.get('COLcolor_col')=='By Earring Design'){ var value= row.get('COLcolor_col_val');row.put('by_earring_design',value);}
if(row.get('COLcolor_col')=='By Wearability'){ var value= row.get('COLcolor_col_val');row.put('by_wearability',value);}
if(row.get('COLcolor_col')=='By Pendants & Lockets Type'){ var value= row.get('COLcolor_col_val');row.put('by_pendants_and_lockets_type',value);}
if(row.get('COLcolor_col')=='By Polish Color'){ var value= row.get('COLcolor_col_val');row.put('by_polish_color',value);}
if(row.get('COLcolor_col')=='By No of Diamonds'){ var value= row.get('COLcolor_col_val');row.put('by_no_of_diamonds',value);}
if(row.get('COLcolor_col')=='By Gold Weight'){ var value= row.get('COLcolor_col_val');row.put('by_gold_weight',value);}
if(row.get('COLcolor_col')=='By Diamond Shape'){ var value= row.get('COLcolor_col_val');row.put('by_diamond_shape',value);}
if(row.get('COLcolor_col')=='By Diamond Color'){ var value= row.get('COLcolor_col_val');row.put('by_diamond_color',value);}
if(row.get('COLcolor_col')=='By Diamond Clarity'){ var value= row.get('COLcolor_col_val');row.put('by_diamond_clarity',value);}
if(row.get('COLcolor_col')=='By Stone Name'){ var value= row.get('COLcolor_col_val');row.put('by_stone_name',value);}
if(row.get('COLcolor_col')=='By Ratti'){ var value= row.get('COLcolor_col_val');row.put('by_ratti',value);}
if(row.get('COLcolor_col')=='By Gemstone Cut'){ var value= row.get('COLcolor_col_val');row.put('by_gemstone_cut',value);}
if(row.get('COLcolor_col')=='By Gemstone Color'){ var value= row.get('COLcolor_col_val');row.put('by_gemstone_color',value);}
if(row.get('COLcolor_col')=='By Classification'){ var value= row.get('COLcolor_col_val');row.put('by_classification',value);}
if(row.get('COLcolor_col')=='By Gold Purity'){ var value= row.get('COLcolor_col_val');row.put('by_gold_purity',value);}
if(row.get('COLcolor_col')=='Desktop Type'){ var value= row.get('COLcolor_col_val');row.put('desktop_type',value);}
if(row.get('COLcolor_col')=='By Warranty Duration in Months'){ var value= row.get('COLcolor_col_val');row.put('by_warranty_duration_in_months',value);}
if(row.get('COLcolor_col')=='By Warranty Available'){ var value= row.get('COLcolor_col_val');row.put('by_warranty_available',value);}
if(row.get('COLcolor_col')=='By Seller Warranty in Months'){ var value= row.get('COLcolor_col_val');row.put('by_seller_warranty_in_months',value);}
if(row.get('COLcolor_col')=='By Device Warranty'){ var value= row.get('COLcolor_col_val');row.put('by_device_warranty',value);}
if(row.get('COLcolor_col')=='Cartridge Type'){ var value= row.get('COLcolor_col_val');row.put('cartridge_type',value);}
if(row.get('COLcolor_col')=='By Expiry date'){ var value= row.get('COLcolor_col_val');row.put('by_expiry_date',value);}
if(row.get('COLcolor_col')=='By Fashion Material'){ var value= row.get('COLcolor_col_val');row.put('by_fashion_material',value);}
if(row.get('COLcolor_col')=='By Precious Material'){ var value= row.get('COLcolor_col_val');row.put('by_precious_material',value);}
if(row.get('COLcolor_col')=='By Quantity'){ var value= row.get('COLcolor_col_val');row.put('by_quantity',value);}
if(row.get('COLcolor_col')=='Usage'){ var value= row.get('COLcolor_col_val');row.put('usage',value);}
if(row.get('COLcolor_col')=='Trimmer Types'){ var value= row.get('COLcolor_col_val');row.put('trimmer_types',value);}
if(row.get('COLcolor_col')=='Type'){ var value= row.get('COLcolor_col_val');row.put('type',value);}
if(row.get('COLcolor_col')=='Size'){ var value= row.get('COLcolor_col_val');row.put('size',value);}
if(row.get('COLcolor_col')=='By Seller Warranty Available'){ var value= row.get('COLcolor_col_val');row.put('by_seller_warranty_available',value);}
if(row.get('COLcolor_col')=='Shaving Need'){ var value= row.get('COLcolor_col_val');row.put('shaving_need_type',value);}
if(row.get('COLcolor_col')=='By Cream Type'){ var value= row.get('COLcolor_col_val');row.put('by_cream_type',value);}
if(row.get('COLcolor_col')=='By Skin Care'){ var value= row.get('COLcolor_col_val');row.put('by_skin_care_type',value);}
if(row.get('COLcolor_col')=='Hair Care Type'){ var value= row.get('COLcolor_col_val');row.put('hair_care_type',value);}
if(row.get('COLcolor_col')=='Bath & Body Type'){ var value= row.get('COLcolor_col_val');row.put('bath_and_body_type',value);}
if(row.get('COLcolor_col')=='By Warranty Duration'){ var value= row.get('COLcolor_col_val');row.put('by_warranty_duration',value);}
if(row.get('COLcolor_col')=='By Warranty Type'){ var value= row.get('COLcolor_col_val');row.put('by_warranty_type',value);}
if(row.get('COLcolor_col')=='By Output Type'){ var value= row.get('COLcolor_col_val');row.put('by_output_type',value);}
if(row.get('COLcolor_col')=='By Shaver Types'){ var value= row.get('COLcolor_col_val');row.put('by_shaver_types',value);}
if(row.get('COLcolor_col')=='By Shaving Types'){ var value= row.get('COLcolor_col_val');row.put('by_shaving_types',value);}
if(row.get('COLcolor_col')=='By Razor & Cartridge Type'){ var value= row.get('COLcolor_col_val');row.put('by_razor_and_cartridge_type',value);}
if(row.get('COLcolor_col')=='By Cartrdges : No. of units'){ var value= row.get('COLcolor_col_val');row.put('by_cartrdges_no_of_units',value);}
if(row.get('COLcolor_col')=='By Shaving Type'){ var value= row.get('COLcolor_col_val');row.put('by_shaving_type',value);}
if(row.get('COLcolor_col')=='o_belts_waist_size'){ var value= row.get('COLcolor_col_val');row.put('o_belts_waist_size',value);}
if(row.get('COLcolor_col')=='o_blouse_size'){ var value= row.get('COLcolor_col_val');row.put('o_blouse_size',value);}
if(row.get('COLcolor_col')=='o_color'){ var value= row.get('COLcolor_col_val');row.put('o_color',value);}
if(row.get('COLcolor_col')=='o_color_option'){ var value= row.get('COLcolor_col_val');row.put('o_color_option',value);}
if(row.get('COLcolor_col')=='o_colors'){ var value= row.get('COLcolor_col_val');row.put('o_colors',value);}
if(row.get('COLcolor_col')=='o_flavour'){ var value= row.get('COLcolor_col_val');row.put('o_flavour',value);}
if(row.get('COLcolor_col')=='o_footwear_size'){ var value= row.get('COLcolor_col_val');row.put('o_footwear_size',value);}
if(row.get('COLcolor_col')=='o_handset_model'){ var value= row.get('COLcolor_col_val');row.put('o_handset_model',value);}
if(row.get('COLcolor_col')=='o_keypad_gaurd_size'){ var value= row.get('COLcolor_col_val');row.put('o_keypad_gaurd_size',value);}
if(row.get('COLcolor_col')=='o_legging_size'){ var value= row.get('COLcolor_col_val');row.put('o_legging_size',value);}
if(row.get('COLcolor_col')=='o_lip_gloss'){ var value= row.get('COLcolor_col_val');row.put('o_lip_gloss',value);}
if(row.get('COLcolor_col')=='o_loungewear_size'){ var value= row.get('COLcolor_col_val');row.put('o_loungewear_size',value);}
if(row.get('COLcolor_col')=='o_shirt_size'){ var value= row.get('COLcolor_col_val');row.put('o_shirt_size',value);}
if(row.get('COLcolor_col')=='o_shoe_size'){ var value= row.get('COLcolor_col_val');row.put('o_shoe_size',value);}
if(row.get('COLcolor_col')=='o_shoes_size'){ var value= row.get('COLcolor_col_val');row.put('o_shoes_size',value);}
if(row.get('COLcolor_col')=='o_shorts_or_capri_size'){ var value= row.get('COLcolor_col_val');row.put('o_shorts_or_capri_size',value);}
if(row.get('COLcolor_col')=='o_size'){ var value= row.get('COLcolor_col_val');row.put('o_size',value);}
if(row.get('COLcolor_col')=='o_size_(jeans_&_shorts)'){ var value= row.get('COLcolor_col_val');row.put('o_size_jeans_and_shorts)',value);}
if(row.get('COLcolor_col')=='o_size_kids'){ var value= row.get('COLcolor_col_val');row.put('o_size_kids',value);}
if(row.get('COLcolor_col')=='o_size-men-belt-inches'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-belt-inches',value);}
if(row.get('COLcolor_col')=='o_size-men-belts'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-belts',value);}
if(row.get('COLcolor_col')=='o_size-men-briefs'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-briefs',value);}
if(row.get('COLcolor_col')=='o_size-men-shirt'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-shirt',value);}
if(row.get('COLcolor_col')=='o_size-men-shirts'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-shirts',value);}
if(row.get('COLcolor_col')=='o_size-men-trousers'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-trousers',value);}
if(row.get('COLcolor_col')=='o_size-men-tshirt'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-tshirt',value);}
if(row.get('COLcolor_col')=='o_size-men-vests'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-vests',value);}
if(row.get('COLcolor_col')=='o_size-women-bottomwear-alphasize'){ var value= row.get('COLcolor_col_val');row.put('o_size-women-bottomwear-alphasize',value);}
if(row.get('COLcolor_col')=='o_size-women-bottomwear-inches'){ var value= row.get('COLcolor_col_val');row.put('o_size-women-bottomwear-inches',value);}
if(row.get('COLcolor_col')=='o_size-women-churidar_&_salwar'){ var value= row.get('COLcolor_col_val');row.put('o_size-women-churidar_and_salwar',value);}
if(row.get('COLcolor_col')=='o_size-men-kurta'){ var value= row.get('COLcolor_col_val');row.put('o_size-men-kurta',value);}
if(row.get('COLcolor_col')=='o_womens_footwear'){ var value= row.get('COLcolor_col_val');row.put('o_womens_footwear',value);}
if(row.get('COLcolor_col')=='o_mens_footwear'){ var value= row.get('COLcolor_col_val');row.put('o_mens_footwear',value);}
if(row.get('COLcolor_col')=='o_baby_age_group'){ var value= row.get('COLcolor_col_val');row.put('o_baby_age_group',value);}
if(row.get('COLcolor_col')=='o_bangles_color'){ var value= row.get('COLcolor_col_val');row.put('o_bangles_color',value);}
if(row.get('COLcolor_col')=='o_bag_color'){ var value= row.get('COLcolor_col_val');row.put('o_bag_color',value);}
if(row.get('COLcolor_col')=='o_size-women-upper'){ var value= row.get('COLcolor_col_val');row.put('o_size-women-upper',value);}
if(row.get('COLcolor_col')=='o_size-men_lower'){ var value= row.get('COLcolor_col_val');row.put('o_size-men_lower',value);}
if(row.get('COLcolor_col')=='o_size-men'){ var value= row.get('COLcolor_col_val');row.put('o_size-men',value);}
if(row.get('COLcolor_col')=='o_women_ring_size'){ var value= row.get('COLcolor_col_val');row.put('o_women_ring_size',value);}
if(row.get('COLcolor_col')=='o_men_briefs'){ var value= row.get('COLcolor_col_val');row.put('o_men_briefs',value);}
if(row.get('COLcolor_col')=='o_size-women-churidar-salwar-legging'){ var value= row.get('COLcolor_col_val');row.put('o_size-women-churidar-salwar-legging',value);}
if(row.get('COLcolor_col')=='o_size-women-bottomwear-inches'){ var value= row.get('COLcolor_col_val');row.put('o_size-women-bottomwear-inches',value);}
if(row.get('COLcolor_col')=='o_mobile-cases-covers'){ var value= row.get('COLcolor_col_val');row.put('o_mobile-cases-covers',value);}
if(row.get('COLcolor_col')=='o_bracelet_size'){ var value= row.get('COLcolor_col_val');row.put('o_bracelet_size',value);}
if(row.get('COLcolor_col')=='o_bangles_-_size'){ var value= row.get('COLcolor_col_val');row.put('o_bangles_-_size',value);}
if(row.get('COLcolor_col')=='o_tracksuit-sports_wear'){ var value= row.get('COLcolor_col_val');row.put('o_tracksuit-sports_wear',value);}
if(row.get('COLcolor_col')=='o_design'){ var value= row.get('COLcolor_col_val');row.put('o_design',value);}
if(row.get('COLcolor_col')=='o_fashion_color'){ var value= row.get('COLcolor_col_val');row.put('o_fashion_color',value);}
return row;
}
function stringtoarray(row) {
var value=row.get('category_ids');
if(value !="" && value !=null) {
cat_arr=value.split("/");
row.put('cat_ids',cat_arr);
return row;
}
}
]]>
</script>
<dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://###########/######"
user="######" password="#######" tinyInt1isBit="false"/>
<document>
<entity name="products" pk="id" transformer="RegexTransformer" query = "SELECT p.product_id as id, REPLACE(REPLACE(REPLACE(pd.product, '\n', ''), '', ''), '\r','') as product, pd.meta_keywords, pd.search_words, pd.page_title, p.product_id, p.company_id, p.list_price as list_price, min(pp.price) as price,
p.third_price,
pp.retail_price,
IF(p.third_price != '0.00', ROUND(((p.list_price - p.third_price) / p.list_price) * 100), ROUND(((p.list_price - pp.price) / p.list_price) * 100 )) as discount_percentage,
IF( p.third_price != '0.00', p.third_price, IF( pp.price != '0.00', pp.price, p.list_price ) ) AS sort_price,
REPLACE(REPLACE(ps.name, '\n', ''), '\r', '') as seo_name, pe.name as brand_url,
CONCAT('images/thumbnails/',floor(if(i.image_id!=0, i.image_id,il.image_id)/1000),'/320/320/',REPLACE(REPLACE(image_path, '\n', ''), '\r', '')) as image_url,
cat.id_path as id_path,
cpfv.variant_id as brand_id,
REPLACE(REPLACE(REPLACE(cpfvd.variant, '\n', ''), '', ''), '\r','') as brand,
REPLACE(REPLACE(REPLACE(CONCAT(cpfv.variant_id,'_',cpfvd.variant), '\n', ''), '', ''), '\r','') as show_brand,
CONCAT(cpfv.variant_id,'_',c1.category_id) as suggest_brand,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as category_ids,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as cat_ids,
GROUP_CONCAT(distinct(c1.status) SEPARATOR '/') as category_status,
max(IF(pc.link_type = 'M', cd2.category_id, null)) as category_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd2.category, '\n', ''), '', ''), '\r',''), null)) as category,
max(if(pc.link_type = 'M', c1.category_id, null)) as metacategory_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd1.category, '\n', ''), '', ''), '\r',''), null)) as metacategory,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category), '\n', ''), '', ''), '\r','') , null)) as show_category,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category,'_',cd1.category), '\n', ''), '', ''), '\r',''), null)) as show_metacategory,
max(IF(pc.link_type = 'M', cat.status, null)) as maincategory_status,
max(IF(pc.link_type = 'M', c1.status, null)) as metacategory_status,
max(IF(pc.link_type = 'M', REPLACE(cd2.category_path,'///','>'), null)) as category_path,
c.company as merchant,
CAST(SUBSTRING_INDEX(c.zipcode, '-', -1) AS UNSIGNED) as zipcode,
REPLACE(REPLACE(REPLACE(CONCAT(c.company_id,'_',c.company), '\n', ''), '', ''), '\r','') as show_merchant,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cd1.category_id,'_',cd1.category), '\n', ''), '', ''), '\r',''), null)) as show_meta_id,
max(IF(pc.link_type = 'M', c1.include_in_search, null)) as include_in_search,
c.is_ngo,
if(p.tracking='O',IF(sum(inv.amount)=0 OR sum(inv.amount) IS NULL,0,1),IF(p.amount=0,0,1)) as product_amount_available,
c.sdeep_rating as merchant_rating,
if(p.product_rating IS NULL,'0.00',p.product_rating) as product_rating,
popularity.total as popularity,
popularity.bought as bestsellers,
promo.promotion_id,
if(promo.to_date >= UNIX_TIMESTAMP(),if(promo.status = 'A',CONCAT(promo.promotion_id,'_',REPLACE(REPLACE(promodesc.internal_name,'_',' '),'#',''),'#',promo.promotion_type_id,'-',promo.status,'-',promo.to_date),''),'') as show_promotion,
cm.market_id,
REPLACE(REPLACE(REPLACE(CONCAT(cm.market_id,'_',cm.seo_name), '\n', ''), '', ''), '\r','') as show_market,
p.free_shipping,
p.shipping_freight,
if(p.tracking='O', sum(inv.amount), p.amount) as amount,
p.deals_index as deal_index,
p.deals_index as deals_index,
p.feature_index,
p.boost_index,
p.is_cod,
p.price_see_inside,
p.deal_inside_badge,
p.special_offer_badge,
p.freebee_inside,
p.last_update,
p.timestamp,
p.status,
p.manufacturer_reference_number,
p.is_wholesale_product,
p.wholesale_type,
p.min_qty,
p.why_buy_reason_1,
p.why_buy_reason_2,
p.master_id,
p.is_master,
p.anniversary_tag,
p.marketplace_product_visibility,
p.mobile_boost_index,
p.is_factory_outlet_product,
p.outlet_brand_id as outlet_brand_id,
cob.brand_name as outlet_brand_name,
REPLACE(REPLACE(REPLACE(CONCAT(p.outlet_brand_id,'_',cob.brand_name), '\n', ''), '', ''), '\r','') as show_outlet_brand,
outlet.outlet_status as is_outlet_status,
cob.status as is_cob_status,
UNIX_TIMESTAMP() as solr_update,
c.status as company_status,
c.company,
c.is_trm,
cspr.sort_1,
cspr.sort_2,
if(p.special_offer_text IS NULL, '', p.special_offer_text) as special_offer_text,
REPLACE(REPLACE(REPLACE(pd.clean_full_description, '\n', ''), '', ''), '\r','') as full_description,
REPLACE(REPLACE(REPLACE(pd.clean_short_description, '\n', ''), '', ''), '\r','') as short_description
FROM
cscart_products p
INNER JOIN cscart_product_descriptions as pd ON pd.product_id = p.product_id and pd.lang_code = 'EN'
left join clues_search_promotion as cspr on cspr.product_id=p.product_id
LEFT JOIN cscart_product_prices as pp ON pp.product_id = p.product_id
LEFT JOIN cscart_companies AS c ON c.company_id = p.company_id
INNER JOIN cscart_products_categories as pc ON pc.product_id = p.product_id
INNER JOIN cscart_categories as cat ON cat.category_id = pc.category_id AND cat.status IN ('A', 'H')
LEFT JOIN cscart_seo_names as ps ON ps.object_id = p.product_id AND ps.type = 'p'
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = p.product_id
LEFT JOIN cscart_product_options_inventory as inv on inv.product_id=p.product_id
left JOIN cscart_category_descriptions cd1 on cd1.category_id=SUBSTRING_INDEX(SUBSTRING_INDEX(id_path, '/',2), '/',1)
left JOIN cscart_category_descriptions cd2 ON cd2.category_id = pc.category_id
left JOIN cscart_categories c1 on c1.category_id = cd1.category_id
left JOIN cscart_images_links il ON il.object_id = p.product_id and il.object_type = 'product' and il.type = 'M'
left JOIN cscart_images i ON il.detailed_id = i.image_id
left join cscart_product_features_values cpfv on cpfv.product_id=p.product_id and cpfv.feature_id=53
left join cscart_product_feature_variant_descriptions cpfvd on cpfvd.variant_id=cpfv.variant_id
left JOIN cscart_seo_names pe ON cpfv.variant_id = pe.object_id and pe.type = 'e'
left join clues_factory_outlet_apply outlet on outlet.company_id=c.company_id
LEFT join clues_outlet_brands cob on cob.brand_id=p.outlet_brand_id
LEFT join cscart_promotions promo on promo.promotion_id = p.promotion_id
LEFT join cscart_promotion_descriptions promodesc on promodesc.promotion_id = promo.promotion_id
LEFT join clues_markets cm ON POSITION(c.zipcode IN cm.pincode) != 0
WHERE 1
AND p.status = 'A'
AND (c.status = 'A' OR p.company_id = 0)
group by p.product_id"
deltaImportQuery="SELECT p.product_id as id, REPLACE(REPLACE(REPLACE(pd.product, '\n', ''), '', ''), '\r','') as product, pd.meta_keywords, pd.search_words, pd.page_title, p.product_id, p.company_id, p.list_price as list_price,
min(pp.price) as price,
p.third_price,
pp.retail_price,
IF(p.third_price != '0.00', ROUND(((p.list_price - p.third_price) / p.list_price) * 100), ROUND(((p.list_price - pp.price) / p.list_price) * 100 )) as discount_percentage,
IF( p.third_price != '0.00', p.third_price, IF( pp.price != '0.00', pp.price, p.list_price ) ) AS sort_price,
REPLACE(REPLACE(ps.name, '\n', ''), '\r', '') as seo_name, pe.name as brand_url,
CONCAT('images/thumbnails/',floor(if(i.image_id!=0, i.image_id,il.image_id)/1000),'/320/320/',REPLACE(REPLACE(image_path, '\n', ''), '\r', '')) as image_url,
cat.id_path as id_path,
cpfv.variant_id as brand_id,
REPLACE(REPLACE(REPLACE(cpfvd.variant, '\n', ''), '', ''), '\r','') as brand,
REPLACE(REPLACE(REPLACE(CONCAT(cpfv.variant_id,'_',cpfvd.variant), '\n', ''), '', ''), '\r','') as show_brand,
CONCAT(cpfv.variant_id,'_',c1.category_id) as suggest_brand,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as category_ids,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as cat_ids,
GROUP_CONCAT(distinct(c1.status) SEPARATOR '/') as category_status,
max(IF(pc.link_type = 'M', cd2.category_id, null)) as category_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd2.category, '\n', ''), '', ''), '\r',''), null)) as category,
max(if(pc.link_type = 'M', c1.category_id, null)) as metacategory_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd1.category, '\n', ''), '', ''), '\r',''), null)) as metacategory,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category), '\n', ''), '', ''), '\r','') , null)) as show_category,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category,'_',cd1.category), '\n', ''), '', ''), '\r',''), null)) as show_metacategory,
max(IF(pc.link_type = 'M', cat.status, null)) as maincategory_status,
max(IF(pc.link_type = 'M', c1.status, null)) as metacategory_status,
max(IF(pc.link_type = 'M', REPLACE(cd2.category_path,'///','>'), null)) as category_path,
c.company as merchant,
CAST(SUBSTRING_INDEX(c.zipcode, '-', -1) AS UNSIGNED) as zipcode,
REPLACE(REPLACE(REPLACE(CONCAT(c.company_id,'_',c.company), '\n', ''), '', ''), '\r','') as show_merchant,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cd1.category_id,'_',cd1.category), '\n', ''), '', ''), '\r',''), null)) as show_meta_id,
max(IF(pc.link_type = 'M', c1.include_in_search, null)) as include_in_search,
c.is_ngo,
if(p.tracking='O',IF(sum(inv.amount)=0 OR sum(inv.amount) IS NULL,0,1),IF(p.amount=0,0,1)) as product_amount_available,
c.sdeep_rating as merchant_rating,
if(p.product_rating IS NULL,'0.00',p.product_rating) as product_rating,
popularity.total as popularity,
popularity.bought as bestsellers,
promo.promotion_id,
if(promo.to_date >= UNIX_TIMESTAMP(),if(promo.status = 'A',CONCAT(promo.promotion_id,'_',REPLACE(REPLACE(promodesc.internal_name,'_',' '),'#',''),'#',promo.promotion_type_id,'-',promo.status,'-',promo.to_date),''),'') as show_promotion,
cm.market_id,
REPLACE(REPLACE(REPLACE(CONCAT(cm.market_id,'_',cm.seo_name), '\n', ''), '', ''), '\r','') as show_market,
p.free_shipping,
p.shipping_freight,
if(p.tracking='O', sum(inv.amount), p.amount) as amount,
p.deals_index as deal_index,
p.deals_index as deals_index,
p.feature_index,
p.boost_index,
p.is_cod,
p.price_see_inside,
p.deal_inside_badge,
p.special_offer_badge,
p.freebee_inside,
p.last_update,
p.timestamp,
p.status,
p.manufacturer_reference_number,
p.is_wholesale_product,
p.wholesale_type,
p.min_qty,
p.why_buy_reason_1,
p.why_buy_reason_2,
p.master_id,
p.is_master,
p.anniversary_tag,
p.marketplace_product_visibility,
p.mobile_boost_index,
p.is_factory_outlet_product,
p.outlet_brand_id as outlet_brand_id,
cob.brand_name as outlet_brand_name,
REPLACE(REPLACE(REPLACE(CONCAT(p.outlet_brand_id,'_',cob.brand_name), '\n', ''), '', ''), '\r','') as show_outlet_brand,
outlet.outlet_status as is_outlet_status,
cob.status as is_cob_status,
UNIX_TIMESTAMP() as solr_update,
c.status as company_status,
c.company,
c.is_trm,
cspr.sort_1,
cspr.sort_2,
if(p.special_offer_text IS NULL, '', p.special_offer_text) as special_offer_text,
REPLACE(REPLACE(REPLACE(pd.clean_full_description, '\n', ''), '', ''), '\r','') as full_description,
REPLACE(REPLACE(REPLACE(pd.clean_short_description, '\n', ''), '', ''), '\r','') as short_description
FROM
cscart_products p
INNER JOIN cscart_product_descriptions as pd ON pd.product_id = p.product_id and pd.lang_code = 'EN'
left join clues_search_promotion as cspr on cspr.product_id=p.product_id
LEFT JOIN cscart_product_prices as pp ON pp.product_id = p.product_id
LEFT JOIN cscart_companies AS c ON c.company_id = p.company_id
INNER JOIN cscart_products_categories as pc ON pc.product_id = p.product_id
INNER JOIN cscart_categories as cat ON cat.category_id = pc.category_id AND cat.status IN ('A', 'H')
LEFT JOIN cscart_seo_names as ps ON ps.object_id = p.product_id AND ps.type = 'p'
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = p.product_id
LEFT JOIN cscart_product_options_inventory as inv on inv.product_id=p.product_id
left JOIN cscart_category_descriptions cd1 on cd1.category_id=SUBSTRING_INDEX(SUBSTRING_INDEX(id_path, '/',2), '/',1)
left JOIN cscart_category_descriptions cd2 ON cd2.category_id = pc.category_id
left JOIN cscart_categories c1 on c1.category_id = cd1.category_id
left JOIN cscart_images_links il ON il.object_id = p.product_id and il.object_type = 'product' and il.type = 'M'
left JOIN cscart_images i ON il.detailed_id = i.image_id
left join cscart_product_features_values cpfv on cpfv.product_id=p.product_id and cpfv.feature_id=53
left join cscart_product_feature_variant_descriptions cpfvd on cpfvd.variant_id=cpfv.variant_id
left JOIN cscart_seo_names pe ON cpfv.variant_id = pe.object_id and pe.type = 'e'
left join clues_factory_outlet_apply outlet on outlet.company_id=c.company_id
LEFT join clues_outlet_brands cob on cob.brand_id=p.outlet_brand_id
LEFT join cscart_promotions promo on promo.promotion_id = p.promotion_id
LEFT join cscart_promotion_descriptions promodesc on promodesc.promotion_id = promo.promotion_id
LEFT join clues_markets cm ON POSITION(c.zipcode IN cm.pincode) != 0
WHERE 1
AND p.status = 'A'
AND (c.status = 'A' OR p.company_id = 0)
and p.product_id = '${dataimporter.delta.id}'
group by p.product_id"
deltaQuery="SELECT p.product_id as id, REPLACE(REPLACE(REPLACE(pd.product, '\n', ''), '', ''), '\r','') as product, pd.meta_keywords, pd.search_words, pd.page_title, p.product_id, p.company_id, p.list_price as list_price,
min(pp.price) as price,
p.third_price,
pp.retail_price,
IF(p.third_price != '0.00', ROUND(((p.list_price - p.third_price) / p.list_price) * 100), ROUND(((p.list_price - pp.price) / p.list_price) * 100 )) as discount_percentage,
IF( p.third_price != '0.00', p.third_price, IF( pp.price != '0.00', pp.price, p.list_price ) ) AS sort_price,
REPLACE(REPLACE(ps.name, '\n', ''), '\r', '') as seo_name, pe.name as brand_url,
CONCAT('images/thumbnails/',floor(if(i.image_id!=0, i.image_id,il.image_id)/1000),'/320/320/',REPLACE(REPLACE(image_path, '\n', ''), '\r', '')) as image_url,
cat.id_path as id_path,
cpfv.variant_id as brand_id,
REPLACE(REPLACE(REPLACE(cpfvd.variant, '\n', ''), '', ''), '\r','') as brand,
REPLACE(REPLACE(REPLACE(CONCAT(cpfv.variant_id,'_',cpfvd.variant), '\n', ''), '', ''), '\r','') as show_brand,
CONCAT(cpfv.variant_id,'_',c1.category_id) as suggest_brand,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as category_ids,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as cat_ids,
GROUP_CONCAT(distinct(c1.status) SEPARATOR '/') as category_status,
max(IF(pc.link_type = 'M', cd2.category_id, null)) as category_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd2.category, '\n', ''), '', ''), '\r',''), null)) as category,
max(if(pc.link_type = 'M', c1.category_id, null)) as metacategory_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd1.category, '\n', ''), '', ''), '\r',''), null)) as metacategory,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category), '\n', ''), '', ''), '\r','') , null)) as show_category,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category,'_',cd1.category), '\n', ''), '', ''), '\r',''), null)) as show_metacategory,
max(IF(pc.link_type = 'M', cat.status, null)) as maincategory_status,
max(IF(pc.link_type = 'M', c1.status, null)) as metacategory_status,
max(IF(pc.link_type = 'M', REPLACE(cd2.category_path,'///','>'), null)) as category_path,
c.company as merchant,
CAST(SUBSTRING_INDEX(c.zipcode, '-', -1) AS UNSIGNED) as zipcode,
REPLACE(REPLACE(REPLACE(CONCAT(c.company_id,'_',c.company), '\n', ''), '', ''), '\r','') as show_merchant,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cd1.category_id,'_',cd1.category), '\n', ''), '', ''), '\r',''), null)) as show_meta_id,
max(IF(pc.link_type = 'M', c1.include_in_search, null)) as include_in_search,
c.is_ngo,
if(p.tracking='O',IF(sum(inv.amount)=0 OR sum(inv.amount) IS NULL,0,1),IF(p.amount=0,0,1)) as product_amount_available,
c.sdeep_rating as merchant_rating,
if(p.product_rating IS NULL,'0.00',p.product_rating) as product_rating,
popularity.total as popularity,
popularity.bought as bestsellers,
promo.promotion_id,
if(promo.to_date >= UNIX_TIMESTAMP(),if(promo.status = 'A',CONCAT(promo.promotion_id,'_',REPLACE(REPLACE(promodesc.internal_name,'_',' '),'#',''),'#',promo.promotion_type_id,'-',promo.status,'-',promo.to_date),''),'') as show_promotion,
cm.market_id,
REPLACE(REPLACE(REPLACE(CONCAT(cm.market_id,'_',cm.seo_name), '\n', ''), '', ''), '\r','') as show_market,
p.free_shipping,
p.shipping_freight,
if(p.tracking='O', sum(inv.amount), p.amount) as amount,
p.deals_index as deal_index,
p.deals_index as deals_index,
p.feature_index,
p.boost_index,
p.is_cod,
p.price_see_inside,
p.deal_inside_badge,
p.special_offer_badge,
p.freebee_inside,
p.last_update,
p.timestamp,
p.status,
p.manufacturer_reference_number,
p.is_wholesale_product,
p.wholesale_type,
p.min_qty,
p.why_buy_reason_1,
p.why_buy_reason_2,
p.master_id,
p.is_master,
p.anniversary_tag,
p.marketplace_product_visibility,
p.mobile_boost_index,
p.is_factory_outlet_product,
p.outlet_brand_id as outlet_brand_id,
cob.brand_name as outlet_brand_name,
REPLACE(REPLACE(REPLACE(CONCAT(p.outlet_brand_id,'_',cob.brand_name), '\n', ''), '', ''), '\r','') as show_outlet_brand,
outlet.outlet_status as is_outlet_status,
cob.status as is_cob_status,
UNIX_TIMESTAMP() as solr_update,
c.status as company_status,
c.company,
c.is_trm,
cspr.sort_1,
cspr.sort_2,
if(p.special_offer_text IS NULL, '', p.special_offer_text) as special_offer_text,
REPLACE(REPLACE(REPLACE(pd.clean_full_description, '\n', ''), '', ''), '\r','') as full_description,
REPLACE(REPLACE(REPLACE(pd.clean_short_description, '\n', ''), '', ''), '\r','') as short_description
FROM
cscart_products p
INNER JOIN cscart_product_descriptions as pd ON pd.product_id = p.product_id and pd.lang_code = 'EN'
left join clues_search_promotion as cspr on cspr.product_id=p.product_id
LEFT JOIN cscart_product_prices as pp ON pp.product_id = p.product_id
LEFT JOIN cscart_companies AS c ON c.company_id = p.company_id
INNER JOIN cscart_products_categories as pc ON pc.product_id = p.product_id
INNER JOIN cscart_categories as cat ON cat.category_id = pc.category_id AND cat.status IN ('A', 'H')
LEFT JOIN cscart_seo_names as ps ON ps.object_id = p.product_id AND ps.type = 'p'
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = p.product_id
LEFT JOIN cscart_product_options_inventory as inv on inv.product_id=p.product_id
left JOIN cscart_category_descriptions cd1 on cd1.category_id=SUBSTRING_INDEX(SUBSTRING_INDEX(id_path, '/',2), '/',1)
left JOIN cscart_category_descriptions cd2 ON cd2.category_id = pc.category_id
left JOIN cscart_categories c1 on c1.category_id = cd1.category_id
left JOIN cscart_images_links il ON il.object_id = p.product_id and il.object_type = 'product' and il.type = 'M'
left JOIN cscart_images i ON il.detailed_id = i.image_id
left join cscart_product_features_values cpfv on cpfv.product_id=p.product_id and cpfv.feature_id=53
left join cscart_product_feature_variant_descriptions cpfvd on cpfvd.variant_id=cpfv.variant_id
left JOIN cscart_seo_names pe ON cpfv.variant_id = pe.object_id and pe.type = 'e'
left join clues_factory_outlet_apply outlet on outlet.company_id=c.company_id
LEFT join clues_outlet_brands cob on cob.brand_id=p.outlet_brand_id
LEFT join cscart_promotions promo on promo.promotion_id = p.promotion_id
LEFT join cscart_promotion_descriptions promodesc on promodesc.promotion_id = promo.promotion_id
LEFT join clues_markets cm ON POSITION(c.zipcode IN cm.pincode) != 0
WHERE 1
AND p.status = 'A'
AND (c.status = 'A' OR p.company_id = 0)
AND ( p.last_update >= '${dih.last_index_time}' OR c.trigger_update >= '${dih.last_index_time}' OR cat.trigger_update >= '${dih.last_index_time}' OR inv.last_update >= '${dih.last_index_time}')
group by p.product_id"
deletedPkQuery="SELECT p.product_id as id FROM cscart_products p LEFT JOIN cscart_products_categories pc ON p.product_id = pc.product_id and link_type = 'M' LEFT JOIN cscart_companies c ON c.company_id = p.company_id LEFT JOIN cscart_categories cat ON cat.category_id = pc.category_id WHERE ( p.status != 'A' OR c.status != 'A' ) AND ( p.last_update >= '${dataimporter.last_index_time}' OR c.updated_at >= '${dataimporter.last_index_time}') "
>
<entity name="feature" transformer="script:addfield" query="SELECT pf.feature_id, REPLACE(REPLACE(REPLACE(pfd.filter, '\n', ''), '', ''), '\r','') as COLcolor_col,
pfv.variant_id, CONCAT(pfv.variant_id,'_',(REPLACE(REPLACE(REPLACE(fvd.variant, '\n', ''), '', ''), '\r',''))) as COLcolor_col_val
FROM cscart_product_filters pf
inner join cscart_product_filter_descriptions pfd on pfd.filter_id = pf.filter_id
inner join cscart_categories c on find_in_set (c.category_id, pf.categories_path)
inner join cscart_products_categories pc on pc.category_id = c.category_id
left join cscart_product_features_values pfv on pfv.feature_id = pf.feature_id and pfv.product_id = pc.product_id
left join cscart_product_feature_variant_descriptions fvd on fvd.variant_id = pfv.variant_id
where pf.feature_id != 53
and pc.product_id = '${products.id}'
and pf.status = 'A'
order by pf.position asc">
</entity>
<entity name="option" transformer="script:addfield" query="select distinct ov.variant_id, REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('o_',LOWER(trim(od.option_name))), ' ','_'), '\'',''), '.',''), '/', '_or_') AS COLcolor_col, CONCAT( od.option_id,'-',ov.variant_id,'_',(REPLACE(REPLACE(REPLACE(vd.variant_name, '\n', ''), '', ''), '\r','')) ) AS COLcolor_col_val
FROM cscart_product_global_option_links g
INNER JOIN cscart_product_option_variants ov ON ov.option_id = g.option_id
INNER JOIN cscart_product_option_variants_descriptions vd ON vd.variant_id = ov.variant_id
INNER JOIN cscart_product_options_descriptions od ON od.option_id = ov.option_id
INNER JOIN cscart_product_options_inventory inv ON g.product_id = inv.product_id
where inv.product_id=${products.id} AND inv.amount > 0
AND ov.variant_id IN (SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',2),'_',-1), SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',4),'_',-1), SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',6),'_',-1), SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',8),'_',-1))">
</entity>
<entity name="combination" query="select product_id,CONCAT(REPLACE(combination,'_','-'),'_',amount) as combination_inv from cscart_product_options_inventory where product_id = ${products.id} AND amount > 0">
</entity>
<entity name="geolocation" query="select CONCAT(pl.lat,',',pl.lng) as geolocation, pl.district, pl.state from cscart_products cp LEFT JOIN cscart_companies cc ON cp.company_id=cc.company_id LEFT JOIN clues_pincode_latlng pl ON cc.zipcode=pl.pincode WHERE cp.product_id = ${products.id}">
</entity>
<field column="cat_ids" splitBy="/" sourceColName="cat_ids"/>
<field column="marketplace_product_visibility" splitBy="/" sourceColName="marketplace_product_visibility"/>
</entity>
</document>
</dataConfig>
/dataimport
