public cABMdb, cServer, cUserName, cUserPwd, nSQLHandle, cDSNLess, cSQLcmd, frmMyForm, cFile_Output, dFromDate, dToDate, cFromDate, cToDate

set console off
SQLSetProp(0,'DispLogin',3)  && stops the SQL dialog box popping up if the connection details are wrong.

*******************************************
* The following parameters can be changed *
*******************************************
cABMdb = "ABMSample"
cServer = "yoda"
*cServer = "VAIO_RICK\SQLEXPRESS"
cLoginName = "sa"
cLoginPassword = "<<PASSWORD>>"
cFile_Output = "c:\Civilab_Sales.xls"
dFromDate = date()
dToDate = date()

= Dialog_box()
cFromDate = str(year(dFromDate)) + "-" + str(month(dFromDate)) + "-" + str(day(dFromDate)) + " 00:00:00"
cToDate = str(year(dToDate)) + "-" + str(month(dToDate)) + "-" + str(day(dToDate)) + " 00:00:00"

*************************************************************************
* The following is where you can paste SQL code from Enterprise Manager *
*************************************************************************
TEXT TO cSQLcmd && TEXTMERGE begins on the next line.

SELECT CONVERT(char(10),dbo.TRANSHEADERS.AccountingDate,103) as Date, dbo.TRANSHEADERS.AccountingRef, dbo.TRANSDETAILS.Quantity * -1, 
  dbo.TRANSDETAILS.LocalNet * -1, dbo.PRODUCTS.ProductCode, dbo.PRODUCTS.ProductTitle, dbo.CUSTOMERS.CustomerCode, 
  dbo.TRANSDETAILS.AnalysisNo, dbo.CANALYSIS.AnalysisTitle as SalesPerson
FROM dbo.TRANSHEADERS INNER JOIN 
  dbo.CUSTOMERS ON dbo.TRANSHEADERS.AccountID = dbo.CUSTOMERS.UniqueID INNER JOIN 
  dbo.TRANSDETAILS ON dbo.TRANSHEADERS.TransactionID = dbo.TRANSDETAILS.TransactionID INNER JOIN 
  dbo.CANALYSIS ON dbo.TRANSDETAILS.AnalysisNo = dbo.CANALYSIS.AnalysisNo INNER JOIN 
  dbo.PRODUCTS ON dbo.TRANSDETAILS.ItemAcID = dbo.PRODUCTS.UniqueID 
WHERE (dbo.TRANSHEADERS.TransactionType IN ('CI', 'CC', 'CX')) AND (dbo.TRANSHEADERS.AccountingDate BETWEEN CONVERT(DATETIME, 
?cFromDate, 102) AND CONVERT(DATETIME, ?cToDate, 102)) 
ORDER BY dbo.TRANSHEADERS.AccountingDate, dbo.TRANSHEADERS.AccountingRef

ENDTEXT

set textmerge off

*****************************************
* Do not make any changes to code below *
*****************************************

cDSNLess="driver=SQL Server;server=&cServer;database=&cABMdb;uid=&cLoginName;pwd=&cLoginPassword"
*messagebox(cDSNLess,0,"About to open datasource")
nSQLHandle = sqlerrormsg(SQLSTRINGCONNECT(cDSNLess, .T.))

if nSQLHandle > 0
	*messagebox("Successfully connected to:" + chr(13) + ;
			cABMdb,0,"ABM Sales Export")
else
	messagebox("Error connecting to:" + chr(13) + ;
			cDSNLess,0,"ABM Sales Export")
	return
endif


*messagebox(cSQLcmd,0,"About to execute")

nError = doSQL(cSQLcmd, "c_SalesList")

select c_SalesList

copy to &cFile_Output type xls
! &cFile_Output


***************************
* Common Useful Functions *
***************************

Function SQLErrorMsg
Parameters nErrorNo


#Define MB_OKBUTTON	0
#Define MB_STOPSIGNICON	16

If (nErrorNo <= 0)
	Local Array laError[1]
	Aerror(laError)
	Messagebox(laError[2], MB_OKBUTTON + MB_STOPSIGNICON, ;
		"Error " + Transform(laError[5]))
Endif

Return nErrorNo

Endfunc

Function doSQL
* RJP 17-Aug-04 Wrapper over SQL Passthrough to handle errors and simplify syntax
Parameters cSQL, cCursor
If PCOUNT() = 1
	Return SQLErrorMsg(SQLEXEC(nSQLHandle, cSQL))
Else
	Return SQLErrorMsg(SQLEXEC(nSQLHandle, cSQL, cCursor))
Endif
Endfunc

***********************************************************
* The following functions and classes define dialog boxes *
***********************************************************

function Dialog_Box
	frmMyForm = CREATEOBJECT('Form')  && Create a Form
	
	with frmMyForm
		.Closable = .F.  && Disable the Control menu box 
		.Caption = "Date Range"
		.width = 200
		.height = 100
		.autocenter = .t.
	endwith

	* From Label

	frmMyForm.AddObject('lblFrom','label')

	with frmMyForm.lblFrom
		.caption = "From Date"
		.Left = 10
		.Top  = 10
		.Visible = .T.
	endwith

	frmMyForm.AddObject('txtFrom','textbox')

	with frmMyForm.txtFrom
		.value = dFromDate
		.Left = frmMyForm.lblFrom.Left + 80
		.Top  = frmMyForm.lblFrom.Top
		.Visible = .T.
	endwith

	frmMyForm.AddObject('lblTo','label')

	with frmMyForm.lblTo
		.caption = "To Date"
		.Left = frmMyForm.lblFrom.Left
		.Top  = frmMyForm.lblFrom.Top + 20
		.Visible = .T.
	endwith

	frmMyForm.AddObject('txtTo','textbox')

	with frmMyForm.txtTo
		.value = dToDate
		.Left = frmMyForm.txtFrom.Left
		.Top  = frmMyForm.lblTo.Top
		.Visible = .T.
	endwith

	
	frmMyForm.AddObject('cmdAccept','cmdMyAccept')  && Accept button
	frmMyForm.cmdAccept.Visible =.T.  && Up Command button visible
	frmMyForm.SHOW  && Display the form

	READ EVENTS  && Start event processing
endfunc

DEFINE CLASS cmdMyAccept AS COMMANDBUTTON  && Create Accept Command button
	Caption = 'Acce\<pt'  && Caption on the Command button
	Left = 50   && Command button column
	Top = frmMyForm.lblTo.Top + 40  && Command button row
	Height = 25  && Command button height

	PROCEDURE Click 
		dFromDate = frmMyForm.txtFrom.Value
		dToDate = frmMyForm.txtTo.Value

		thisform.release
		clear events  && Stop event processing, close Form
		*messagebox("Accepted",0,"Click Procedure")
ENDDEFINE

*********** useful functions not used in this script **************************
DEFINE CLASS cmdMyCalendar AS COMMANDBUTTON  && Create Calendar Command button
	Picture = 'calendar.ico'  
	Left = 200  && Command button column
	Top = 400  && Command button row
	Height = 25  && Command button height

	PROCEDURE Click
		Create Cursor c_from_date_to_date (FromDate D, ToDate D)
		Insert Into c_from_date_to_date (FromDate, ToDate) ;
			VALUES (thisform.txtFromDate.value, thisform.txtToDate.value)
	

		#DEFINE C_CALCAPTION_LOC	"Data Calendar"
		oCalForm = Create('AXcalform')
		oCalForm.NewObject("AXCalendar","AXCalendar")
		*CalForm.NewObject("AXCalendar",,"c:\develop\abm_matrix\classes\Matrix_Classes.vcx")
		WITH oCalForm.AXCalendar
			.date_column = "c_from_date_to_date.FromDate"
			.RefreshDisplay()
			.BackColor = THISFORM.BackColor
			.Visible = .T.
		ENDWITH

		WITH oCalForm
			*.AutoCenter = .T.
			.BorderStyle = 2
			.Height = oCalForm.AXCalendar.Height
			.Width = oCalForm.AXCalendar.Width
			.Top = thisform.top + thisform.txtFromDate.top + thisform.txtFromDate.Height + 5
			.Left = thisform.left + thisform.txtFromDate.Left + thisform.txtFromDate.width &&- oCalForm.AXCalendar.Width
		ENDWITH

		oCalForm.Show(1)
		THISFORM.Refresh
ENDDEFINE