T-SQL Snippets

FAO bizTech Team : Add frequently used SQL Snippets here.

 

Get Yesterday’s Date without, any time

-- Add -1 to current time + format without hours
Select DATEADD (day , -1 , CONVERT(varchar(10), getdate(), 101) )

 

T-SQL Get dates on a specific day when you don't care about time

Quite often I have to write queries to return rows where something happened on a specific day. But the values in the table could be anytime within that day.
Previous I would pass in a start date, end date to do this, but the query then needs 2 parameters and it looks a bit cumbersome.
I came across this method which I now employ. Don't know if this has much performance impact, but it certainly is quicker to code and takes less parameters from calling application.
Where
(
DateDiff(day, @ReportStartDate,TerminalSession.StartDate) = 0
--StartDate >= @ReportStartDate
--And
--EndDate >= @ReportEndDate
)

bizTech Software : Development Blog: How to enter Rule fours in Arkle Version 2.7.34 or later

bizTech Software : Development Blog: How to enter Rule fours in Arkle Version 2.7.34 or later

How to enter Rule fours in Arkle Version 2.7.34 or later

Application : Arkle bet POS

Markets | Horse Racing | Edit Early Prices

Enter the race time, in the race time text box, as shown below.

Select the specific race in the Rule four grid shown above

Enter the Rule four and click apply

Configuring Directory Browsing ASP.NET 4.0 Web.Config Snipper

<location path="releases/arkle/arklefeed">
  <system.webServer>
    <directoryBrowse enabled="true" />
  </system.webServer>
</location>

Note: this also enables browsing on any subdirectories.

This code snippet is brough to you by: Web Design by bizTech Software.

Foreign Racing Not Settling Properly

Arkle betPos has recently had an issue with Foreign racing not settling properly.
This is due to no SPs being returned on these races.

Some customers have mentioned that these races settle correctly in other bet settling systems.  The reason they do not settle in Arkle, is that for daily SIS supplied racing, we always waiting until an SP was in before settling any selections.  This was to prevent settling in the case of an SP being missed.  We assume the other bet settling systems use the last supplied price and assume it is the SP.  We are amending Arkle to do this for meetings that SIS don’t supply an SP for, but are continuing with our belt and braces policy for UK, IRE races.

Below is our communication with SIS on this issue.

 

With Arkle Feed 2.2.01 and above you should no longer have an issue for the current meetings with no SP (however any new meetings/tracks added may cause problems, unless SIS provide a permanent solution to let us distinguish between an SP race and a non SP race).

 

Rachel,

Is there anything in the SIS event that distinguishes between a meeting that will supply an SP, and a meeting that will not?

At the moment we are just having to hardcode this by meeting name.

This is currently the header information we receive with a meeting.

[SIS DATA SPEC REMOVED FOR CONFIDENTIALITY REASONS]

Rachel O'Reilly

to me, Aoife

show details 14:57 (52 minutes ago)

Hi

I will pass this on to technical for you!

Regards

Rachel

How to send an error report from within Arkle

• While you have the relevant Bet Slip or Error on screen, Type Control + Print Screen at the same time on your keyboard  (This copies the current screen image to your clipboard)
• Help|Send Error Report
• Click on the Add Image button (This pastes the image that is in your clipboard to the error report) 

• Fill in the details of the error within the error description box. (Please include Bet slip ID's and full details of the error within this.)
• You may also need to send us an image of the race result:
• Right click on the relevant selection
• Result|View Result
• Again Type Control + Print Screen at the same time on your keyboard 
• Click on the Add Image button (This pastes the result image that is in your clipboard to the error report)
• Please include Bet slip ID's that link this result image with relevant error report.

• Click: Send Report button(this automatically sends the error report to support@biztechsoftware.co.uk) 
• At this stage you can amend any bets if a customer is waiting. 
• Notify the support team via a messenging program(Skype, MSN Messenger, etc) or by telephone if it is urgent(Urgent = Customers are being served)

Arkle bet POS

Arkle bet POS, is our replacement for Arkle ePos.
When you login you will notice a huge difference on the Home Screen, and windows management.  This difference is mainly a new skin, and much 99% of the functionality is the same.
Previously we used a multi document interface, which contained all the Windows under one main parent window.
We have now go rid of this outdated windows management method.
Now each window is a separate window of its on.  And you can press F3 to go back to the home window, which will have all your menus, that you are used to.
All of your normal menus should be available by pressing F3 and looking at the menu bars on the home window.

Faster Printing
Press F3, and then select the scanner window, in settings check the WPF printing checkbox

Bet Slip Editor Window
We have also rebuilt the Bet Slip Editor Window, from scratch
Image size
If you find that the default image size is too small(or too big). You can resize the image using the Zoom+ and Zoom- buttons. If you want all the images to have the same zoom level that you have just changed, you just need to select Actions/Save Zoom.

• Re-organised the screen, it will take some getting used to, but the layout is much more compact now, and more information is available on the screen.
• New price shortcuts when editing
• Type name + [Page Up] (instead of name + [enter]), this adds the selection and takes price @ scan time guaranteed (instead of having to ctrl + G, and confirm]
• Type name + [Page Down], this adds the selection and takes price @ scan time not guaranteed (instead of having to ctrl + P and confirm]
• If you want to edit the taken price in this scenario, just do ctrl + P or ctrl + G as before

Arkle ePos : Taking Prices Shortcut

QUICK PRICE ENTRY
When entering a selection by name you normal type part of the name and press [enter]
Instead of pressing [enter], you can press [page up], this adds your selection with scan time price guaranteed.
Or you can press [page down],  this adds your selection with scan time price not guaranteed.
Version 2.7.19 or above

Note this currently is not available on entry by number.

Roulette Videos

Roulette Videos

The videos below are samples of the Roulette software. The actual software have much better quality, the videos have been compressed for a better visualization.

 

Wheel Spinning

All possible bets

WPF Snippets

 

Formatting Binded Data Samples

Numeric 2 Decimal Places
Text="{Binding Path=SlipStake,StringFormat=N2}"

Custom date Format
Text="{Binding Path=DateScanned, StringFormat='{}{0:dd-MMM-yy HH:mm:ss}'}"

Implements INotifyPropertyChanged

Implements INotifyPropertyChanged

Implements INotifyPropertyChanged
   Public Event PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged

   ' call This when you change a property (also remember to call it ' for read only  properties that use that propered Protected
   Sub NotifyPropertyChanged(ByVal name As String)
       RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(name))

   End Sub

 

http://msdn.microsoft.com/en-us/library/system.windows.input.keyboardnavigationmode.aspx

Unable to copy from obj\debug to bin\debug

Arrrrgghgghgh this problem reared its ugly head again today.  Occurs whenever I try to build a solution (winforms) on Visual Studio 2010.

Previously adding this to the build events (My project, compile tab, click build events):
If exist "$(TargetPath).locked" del "$(TargetPath).locked"

But after working for ages I started getting the error again.  Found this online though, and added it and looks like we are good again:

If exist "$(TargetPath).locked" del "$(TargetPath).locked"
if not exist "$(TargetPath).locked" if exist "$(TargetPath)" move "$(TargetPath)" "$(TargetPath).locked"

Microsoft really want to sort this out, as developers typically build a solution more than once, can’t quite get the hang of perfect coding 1st time round yet.

DataGridViewComboBoxColumn "value is not valid" on datagrid combobox column bound to enum

I've been having problems with this error for a while and finally got it solved.

What we have is a form with a number of datagrids which contain combobox columns bound to enums, which was throwing the error when the form is first opened. We discovered that the enum binding must be set before the datagrid bindings are set on the form or else the error will occur. In our case we were setting a property on the form before showing it then trying to bind the enums.

The solution was to bind the enums to the datagridcolumns before setting any other properties on the form. To be sure we also set the enum bindings again on the form_load method.

Another bug in .Net meant that the error was also displayed when we closed the form if the datagrid was not populated, so we set the enum bindings again in the form_closing event, and it all seems to work fine now, thnakfully.

Note this error only seems to appear if we allow the add rows property of the datagrid.

Becoming a better programmer

Recently we have started training one of our support guys, into becoming a junior developer.  It’s the 1st time we’ve trained someone on the team, who hasn’t coded commercially before.  It got me to thinking about what makes a good developer, and in turn how I can become a better developer.

I read a few other good blogs on this issue of being a better developer before starting this one.

• http://jcooney.net/
• http://arcanecode.com/2007/07/13/being-a-better-developer-in-6-months/
• http://hanselman.com/blog/

It also got me to thinking about how I have improved at developing over my career.  Here are some of the things I’m definitely better at now than I was at university.

• Naming of methods and refactoring.  I now use clear concise methods, both in their naming and what they do.  If you need to comment in what the method is doing, rather than that being obvious by the name, I think you are doing something wrong.
• Becoming less likely to jump to a new technology straight away.
• Building user interfaces that really guide the user from start to finish and require little or no introduction. SSW rules to have some great pointers here.
• Dotting the i’s and crossing the t’s in a project, getting it deployed and not having it sit in development or ready for deployment state.
• Searching Google (will it wasn’t there when I was at uni) but I now know when searching which paths it sends me down which are just plain ludicrous and which to follow.  Google can point you in one or two good directions when searching how to solve a coding issue, and thousands of wrong directions.

Then I started thinking, what am I worse at than I was when I came out of university.

• Becoming less likely to learn a new technology quickly (didn’t I mention about that in the positives above) – yeah but there are pluses and minuses to it.
• Deploying projects before they are fully ready.

My main tips to becoming a better developer

• Name your methods and classes will, if they don’t make good sense when you are developing them, you are going to have a lot of trouble maintaining them.
• REFACTOR REFACTOR REFACTOR!!!
• Build user interfaces that don’t need instructions and are in the main self explanatory
• Get involved in teaching others what tips & techniques you have acquired, this will crystallize them in your own mind and help your team.  Teaching will also show you where you are bluffing and don’t understand it, because if you don’t get it you certainly can’t explain it.
• Learn a new technology once per month, and use it in a mini project.
• Don’t let another member of your team become completely specialised into one technology – make sure you all have some awareness of what the other guys are coding.  My colleague is doing a WPF project, and I have no clue how it works, this shouldn’t happen.
• Get to know the users of your software and LISTEN to them, also if possible use your software in the field.
• Above all be passionate about coding and the software you write.

Calibrating the cyberview scanner

 

Calibration helps fix mark sense scanning, allows you to darken images, and can get rid of unwanted lines through text.  Calibration should be done after the scanner is cleaned (for instructions on cleaning your scanner click here).  TIP: It is a good idea to do this about once a week, as blobs of build up on the scanner glass.

• Exit Arkle epos (as you need to run EngBench, which can’t operate when Arkle has the scanner in operation).
• Run EngBench (try running as administrator).  There should be a shortcut or your desktop (otherwise navigate to C:\Program Files\bizTech Software\Arkle – ePos\ where you will find it.

  

• 
  

 

 

 

• 
  
• Click Cal button (to initiate calibration)

 

 

 

 

 

• Insert a blank sheet of A4 into scanner (leave it in scanner, it won’t automatically scan through).
• Click OK (the scanner will now start processing the blank A4)

 

 

 

 

• The scanner will process the blank A4 by repeatedly pulling the paper in and out, this is normal.
• Once this has completed, scan a few test slips and modify the light setting and gamma, until you are happy with scanned image.
• Click OK, to save the new settings, and log back into Arkle and test the scanner there. If settings do not appear to have saved, you may not have run as administrator or have correct permissions to save files to the Arkle ePos folder.
  

Fixes

Fixes in 2.5.41 and above

Monitored Customers

• View detailed monitored customer report, and view all their slips (Reports | Profit Loss | Stake Payout Profit | By Monitored Customer, Sport
• You can now remove a slip from monitored customer, select other slip details tab, and click the delete button beside monitored customer, this sets bet slip back to no monitored customer.
• Hide an old inactive customer, so they don’t show up in monitored customer list.
  In the edit customer Form change their status to “Hidden”
• Bet Slip Actions | Refresh customer list (refreshes added customers, so you no longer have to log out and back in).
• On the F8 screen you can see monitored customer ID column to easily identify which slips are monitored and which are not.

Bet Slip Editor

• Bet Slip Actions | Remove all bets (deletes all bets from slip in one go)

Bug Fixes

• Remove bug allowing re-edit of void bet slips.
• Lotto entry, tab order fixed
• Ctrl + C, fixed bug (was previously copying unselected selections)

A new BETA version is out to selected shops for testing.
As this was a complete rewrite of the internals of Arkle ePos, a lot of functions fell off the radar and were not included.  We are added them back in one by one and doing daily mini-releases to installed shops.

For a list of what is new and improved in the new slip editor click here

Below is a list of the BETA versions and the missing features they fix

• 2.5.33 : Added ctrl + S (soccer), Ctrl + O (golf), Ctrl + D (draw)
• 2.5.33 : Fixed with on editing alert, so you can now easily see whole alert, no matter how large it is.
• 2.5.32 (BETA) | F4 (settle slip) + Add wager to slip, vastly speeded up (by removing event handlers that seemed to be firing all over the place
• 2.5.32 (BETA) | Event Liabilities + Accumulator Liabilities re-added.
• 2.5.32 : Changes to speed up F4 etc... by not firing events
• 2.5.27 (BETA) | Almost Ready For Release to BETA Shops

F5 Check for prices defaults to entering next selection after each price checked
Market Book Summary | View Slips (working again)
F8 | Search Slips By Selection name 

Problem: If you enter a bet by the race time / selection number, it doesn't go straight to the stake screen anymore, it defaults back to the name selection editor. 
2.5.27 BETA changes this behaviour if you enter 2 or more by number it goes to stake, otherwise goes back to selection number (this is by design)

· When entering lotto, It defaults to the draws e.g. lunchtime draw, teatime draw etc. it should default up at the game type then if you press tab it should bring you down to the draws then to 6 or 7 nums and then to enter the numbers.
Shift tab should work for this on 2.5.27 BETA (as most times you are going to be entering consecutive same game types)

Edit after Actual Off Alert, doesn’t include the length of the race.
Should be fixed in 2.5.26 BETA

Arkle Feed 2.1.14, fixes slow settling of losers, which can slow payout if a bet has one loser and one winner on a race just gone

My Fav Blogs

Going to keep a list here of my fav bugs & online posts

My all time favourite (a toss up by SSW rules & How to report bugs effectively)

How to Report Bugs Effectively
by Simon Tatham, professional and free-software programmer



SSW Rules to better



Scott Hanselman : Tools List

Beth Massi's blog (excellent stuff)



Non IT Related

Belfast food reviews

New Selection Editor Tips

None Slip editing enhancements (this update is mainly enhancements to slip editor although a few bugs have been fixed).

• Entry of Irish Lotto Results from SIS (fixed)
• Entry of 49s Results from SIS (fixed)
• Settling of Fav/2nd Fav (fixed)

Enhancements to bet slip editor

• Warnings on no rule4 applied before payout
• Warnings on amended result before payout
• Warnings on Stewarts Enquiries or Objections before payout

See bottom of document for screenshots.

The new results viewer is much easier on the eye

• Dead heats/Co Favs etc... highlighted in yellow

What you will notice 

• Some new shortcut keys (so it may take a bit of getting used too) 
• Switching sports (e.g. Ctrl + L is much faster)
• Filtering when typing selection names is much faster
• Numbers editor defaults to forty nines upon login, and then last edited numbers game, for subsequent ctrl + N calls until you log back out. 

Ctrl + E will return you to text entry textbox at any time (without having to refresh coupon)
Tip: You would use this if you had clicked elsewhere, and wanted to go back to editing another selection

If using the arrow up and down to enter a selection, use "+" key on keyboard to add that particular selection (rather then the enter key)

Entering Dogs/Horses by time & number (of fav)
Tab over to the time filter text box

• You can filter time by entering exact time (e.g. 3.05) or if you just enter 3 (it will show all races between 3pm & 4pm – and you can use up down arrow to select).  If only one race is shown the Market list box will go green. 
• If more than one race matches your search criteria use the up down arrows (or mouse) to select appropriate race.
• Once you are happy with the race click [Space] or [Tab] and enter your numbers of favourites
  • 2 3 (will select numbers 2 & 3)
  • 2 f (will select number 2 & the favourite)
  • 2 2f (will select number 2 & the second favourite)

How was this achieved in code?

• Enhanced reading from database (using stream data instead of database tables)
• Local caching of data, to prevent database calls, if stream data is not updated
• Enhanced filtering during editing using LINQ instead of dataview

Screenshot | 1 (Amended result + no rule 4 warning)

New Results Viewer

DatagridView : Sort Generic List on any property (minimal code)

Had been wrecking my head with this for a few hours but got a good solution (with very little code).
Okay so it using reflection and mightn't have order log n speed, but so what if you are sorting collections with normally less than 50 items.

I handle the Column Header Mouse Click
Get the data property name.
Then use some reflection to sort the generic list.  (before this I had to use select case, and sort by knowing the property name in code, but when I add new properties this was a pain to maintain).


    Private Sub SelectionsCollectionsDataGridView_ColumnHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles SelectionsCollectionsDataGridView.ColumnHeaderMouseClick
        Try
            Dim col = Me.SelectionsCollectionsDataGridView.Columns(e.ColumnIndex)
            Me.selectedMarket.SelectionsCollections.Sort(Function(p1, p2) p1.GetType.GetProperty(col.DataPropertyName).GetValue(p1, Nothing).CompareTo(p2.GetType.GetProperty(col.DataPropertyName).GetValue(p2, Nothing)))
            Me.SelectionsCollectionsBindingSource.CurrencyManager.Refresh()
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Critical)
        End Try
    End Sub

In Running Betting

bizTech Software are pleased to announce in-running betting is now available in arkle bet POS.

• Display in running on arkle betting screens in your shop.
• Edit in running bets on arkle ePos, and view full price change history, in your ePos and online.
• Prices automatically update and suspend in real time.

Sports Available

• Football
• Match betting, next goalscorer, correct score, match handicaps.
• Snooker, Tennis, Golf (and more sports coming soon).

Cleaning Cyberview Scanner

Scanner Cleaning

Perform the following cleaning procedure periodically every week, whenever black lines appear on the scanned image or scanning errors occur.
1. Grab the Scanner Cover by the tips of the input and output guides, or alternatively, by the Front and Back borders on each side.

Grab

2. Either use one or two hands
i.

Grab

Grab

3. Lift-up the Scanner Cover.

Lift-Up

i.

Lift-Up

4. Place the Scanner Cover away from the scanner.

5. Locate the two Feed Roller Lock-Levers as shown (only right side shown).

	Right Side Lock-Lever

Feed Roller

6. Place both thumbs on the Feed Roller Lock-Levers grooves and push-backward to rotate them.

	Rotate Backward

7. Rotate the two Lever-Locks until the Lever-Tail is oriented upward (only right side shown).
i.

	Lock-Tail oriented upward

ii.
8. The Feed-Roller will be released when the 2 Lock-Tails are in the upward position
9. Grab-up both Lock-Tails.
i.

Grab-up

Grab-up

ii.

Grab-up

10. Lift the Feed Roller straight up by holding the two Lock-Tails.
i.

Lift straight up

ii.

Lift-up

11. Take-away the Feed Roller.

Take-away

12. The Glass of the Image Sensor will swing up and be fully exposed.
13. Open a new Cleaning Wipe bag provided and gently swipe the impregnated wipe over the entire glass surface from side to side.
i.

Swipe

Glass

14. Examine closely the surface of the Image Sensor Glass for cleanliness and rub with a fingernail on difficult-to-remove spots.
15. Put-back the Feed Roller by holding the Lock-Levers Handles vertically, then engaging them into their slots. Ensure the toothed gear wheels mesh correctly.

Put-back in

16. Once engaged in their slots, turn the Lever-Locks toward the front.
i.

Turn toward Front

Turn toward Front

ii.
17. Pushdown the two Lever-Tails until they reach the horizontal position.

Push-down

18. The two Lever-Tails should be pushed-down toward the front; otherwise, the Feed Roller will pop off during operation of the scanner.
19. Replace the Scanner Cover.
i.

Push-down

5 with one hand
i.
5 or alternatively, with two hands
20. The scanner is now ready to operate.