eConnect to Load the data from RMS to GP with Payments

Last post 05-28-2008 10:00 AM by Alwani. 0 replies.

Page 1 of 1 (1 items)
	Sort Posts: Previous Next

05-28-2008 10:00 AM

Alwani
Joined on 05-28-2008
Member

eConnect to Load the data from RMS to GP with Payments

Reply Contact

I am developing the applicaiton using the econnect but as I am just beginner I have followed the Console application to send the data: My problem is that how do I load the data in bulk (50 to 100 rows). You can see below in my code that I have done the exit do for my rsHDR recordset after reading the first row because I am not sure where and how to change the code. Is there any other easy way where I can create my own web service and send the data fastly. Can I schedule my web service etc. I know I have asked too much but have a look at the code. I am ready to send the whole project upon your request. I found steve is really excellent person in support.

Public Sub SerializeSalesOrderObject(ByVal filename As String)

Try

Dim query1 As String

Dim query2 As String

Dim query3 As String

Dim query4 As String

Dim ln As Integer

Dim RecPayment As Boolean

Dim salesOrder As New SOPTransactionType

Dim TotalPayment As Double

'Dim salesLine As New taSopLineIvcInsert_ItemsTaSopLineIvcInsert

Dim SalesHdr As New taSopHdrIvcInsert

Dim LineItems(1) As taSopLineIvcInsert_ItemsTaSopLineIvcInsert

Dim LinePayment(1) As taCreateSopPaymentInsertRecord_ItemsTaCreateSopPaymentInsertRecord

query1 = "SELECT [Transaction].StoreID,[Transaction].TransactionNumber,[Time] As TransactionDate,CustomerID,Cashier.Number As CashierNo,Total, " _

& "Customer.AccountNumber,Tw_Doc_No,TW_Flag," _

& "'R-'+convert(varchar,[Transaction].StoreID)+'-'+TW_Doc_No+'-'+convert(varchar,[Transaction].TransactionNumber) GPInvoice," _

& "Sum((FullPrice-Price)*Quantity) TotalDiscount,Sum(FullPrice*Quantity) TotalAmount " _

& "FROM [Transaction] INNER JOIN TransactionEntry ON TransactionEntry.TransactionNumber=[Transaction].TransactionNumber " _

& "LEFT OUTER JOIN Customer on [Transaction].CustomerID=Customer.ID " _

& "INNER JOIN Cashier ON [Transaction].CashierID=Cashier.ID " _

& "WHERE TW_Flag='W' or TW_Flag='WT'" _

& " GROUP BY [Transaction].StoreID,[Transaction].TransactionNumber,[Time],CustomerID,Cashier.Number,Total,Tw_Doc_No,TW_Flag,Customer.AccountNumber" _

& " ORDER By 2"

'query1 = "SELECT * from vw_Header Order by 2"

Dim Lineno As Integer = -1

Dim lastsoptype As Long = 0

Dim currsoptype As Long = 0If Not RMSCn.State = ConnectionState.Open Then RMSCn.Open()

rsHdr.Open(query1, RMSCn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

Glb_Batch = ""

TotalPayment = 0

If rsHdr.EOF Then

rsHdr.Close() : rsHdr = Nothing

Else

Glb_Location = "STORE " & rsHdr("StoreID").Value

lblMessage.Text = "Processing RMS Transaction " & rsHdr("GPInvoice").Value

Do While Not rsHdr.EOF

'Get the Customer Number

'Glb_Batch = "RMS-" & Glb_StoreID & "-" & Format(rsHdr("TransactionDate").Value, "mmddyyyy") & "-" & rsHdr("TransactionNumber").Value

Glb_Batch = "eConnect"

Glb_CustNo = rsHdr("CustomerID").Value

Glb_Invoice = rsHdr("GPInvoice").Value

Glb_Date = Format(rsHdr("TransactionDate").Value, "Short Date")

Glb_SubTotal = rsHdr("Total").Value

Glb_DocAmt = rsHdr("TotalAmount").Value

getCustomerInfo()

query2 = "SELECT TransactionEntry.[ID],TransactionNumber,TransactionEntry.ItemID,FullPrice,TransactionEntry.Price," _

& "(Case Sign(TransactionEntry.Quantity) WHEN 1 then 3 ELSE 4 END) Soptype, " _

& "TransactionEntry.Quantity,TransactionEntry.Cost,(TransactionEntry.FullPrice-TransactionEntry.Price) Discount,ItemLookupCode,[Description],SalesRepID,Alias " _

& " From TransactionEntry INNER JOIN Item on ItemID=Item.ID LEFT OUTER JOIN Alias ON Item.ID=Alias.ItemID " _

& " WHERE TransactionNumber = " & rsHdr("TransactionNumber").Value & " Order by 2,6"

query3 = "SELECT StoreID,TransactionNumber,Tender.Description,AdditionalDetailType,Tender.Code," _

& "CreditCardExpiration, CreditCardNumber, CreditCardApprovalCode, Amount " _

& " FROM TenderEntry INNER JOIN Tender on TenderEntry.TenderID=Tender.ID " _

& " WHERE TenderEntry.TransactionNumber= " & rsHdr("TransactionNumber").Value

query4 = "SELECT Count(Tender.Code) PayCount,Sum(TenderEntry.Amount) PayAmount " _

& " FROM TenderEntry INNER JOIN Tender on TenderEntry.TenderID=Tender.ID " _

& " WHERE TenderEntry.TransactionNumber= " & rsHdr("TransactionNumber").Value _

& " GROUP by TransactionNumber"

rsDtl.Open(query2, RMSCn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic)

rsPayHDR.Open(query3, RMSCn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic)

rsPayDTL.Open(query3, RMSCn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic)

Lineno = 0

Do While Not rsDtl.EOF

currsoptype = rsDtl("Soptype").Value

Do While Not rsDtl.EOF And rsDtl("soptype").Value = currsoptype

Dim salesLine As New taSopLineIvcInsert_ItemsTaSopLineIvcInsert

With salesLine

.SOPTYPE = currsoptype

.SOPNUMBE = Glb_Invoice

.CUSTNMBR = Glb_AccNo

.DOCDATE = Glb_Date

.LOCNCODE = Glb_Location

If currsoptype = 3 Then

.DOCID = "RMSI"

Else

.DOCID = "RMSR"

End If

.ITEMNMBR = rsDtl("Alias").Value

.ITEMDESC = rsDtl("Description").Value

.QUANTITY = rsDtl("Quantity").Value

.UNITPRCE = rsDtl("FullPrice").Value

.MRKDNAMT = rsDtl("Discount").Value

.XTNDPRCE = (rsDtl("Price").Value * rsDtl("Quantity").Value)

End With

LineItems(Lineno) = salesLine

Lineno = Lineno + 1

ReDim Preserve salesOrder.taSopLineIvcInsert_Items(Lineno)

rsDtl.MoveNext()

If rsDtl.EOF Then

Exit Do

End If

Loop

If Not rsDtl.EOF Then

'here record the payment and insert the header info as 2 types of document in one sales invoice

If Not rsPayHDR.EOF And rsPayHDR("PayAmount").Value > 0 Then

'Here the Sales and Record cause the amount to be pay by customer

RecPayment = False

'While Loop for all Payments

TotalPayment = 0

ln = 1

Dim SOPPayment As New taCreateSopPaymentInsertRecord_ItemsTaCreateSopPaymentInsertRecord

Do While Not rsPayDTL.EOF

With SOPPayment

.SOPTYPE = currsoptype

.SOPNUMBE = Glb_Invoice

.CUSTNMBR = Glb_AccNo

.DOCDATE = Glb_Date

.DOCAMNT = rsPayDTL("Amount").ValueTotalPayment = TotalPayment + rsPayDTL("Amount").Value

.Action = 1

If rsPayDTL("AdditionalDetailType").Value = 1 Then

.CHEKBKID = Glb_CashAcc

.PYMTTYPE = 4

Else

.CARDNAME = rsPayDTL("Code").Value

.RCTNCCRD = rsPayDTL("CreditCardNumber").Value

.AUTHCODE = rsPayDTL("CreditCardApprovalCode").Value.EXPNDATE = rsPayDTL("CreditCardExpiration").Value

.PYMTTYPE = 6

End If

End With

rsPayDTL.MoveNext()

LinePayment(ln) = SOPPayment

ln = ln + 1

ReDim Preserve salesOrder.taCreateSopPaymentInsertRecord_Items(ln)

Loop

With SalesHdr

If currsoptype = 3 Then

.DOCID = "RMSI"

Else

.DOCID = "RMSR"

End If

.SOPTYPE = currsoptype

.SOPNUMBE = Glb_Invoice

.DOCDATE = Glb_Date

.CUSTNMBR = Glb_AccNo

.BACHNUMB = Glb_Batch

.LOCNCODE = Glb_Location

.SUBTOTAL = Glb_SubTotal

.DOCAMNT = Glb_DocAmt

.PYMTRCVD = TotalPayment

End With

Else

'Here the Sales and Record cause the amount to be pay back to customer

RecPayment = True

End If

Else

RecPayment = True

End If

Loop

salesOrder.taSopLineIvcInsert_Items = LineItems

If RecPayment = True Then

ln = 1

Dim SOPPayment As New taCreateSopPaymentInsertRecord_ItemsTaCreateSopPaymentInsertRecord

TotalPayment = 0

Do While Not rsPayDTL.EOFWith SOPPayment

.SOPTYPE = currsoptype

.SOPNUMBE = Glb_Invoice

.CUSTNMBR = Glb_AccNo

.DOCDATE = Glb_Date

.DOCAMNT = rsPayDTL("Amount").ValueTotalPayment = TotalPayment + rsPayDTL("Amount").Value

.Action = 1

If rsPayDTL("AdditionalDetailType").Value = 1 Then

.CHEKBKID = Glb_CashAcc

.PYMTTYPE = 4

Else

.CARDNAME = rsPayDTL("Code").Value

.RCTNCCRD = rsPayDTL("CreditCardNumber").Value

.AUTHCODE = rsPayDTL("CreditCardApprovalCode").Value.EXPNDATE = rsPayDTL("CreditCardExpiration").Value

.PYMTTYPE = 6

End If

End With

rsPayDTL.MoveNext()

LinePayment(ln) = SOPPayment

ln = ln + 1

ReDim Preserve salesOrder.taCreateSopPaymentInsertRecord_Items(ln)

Loop

End If

With SalesHdr

If currsoptype = 3 Then

.DOCID = "RMSI"

Else

.DOCID = "RMSR"

End If

.SOPTYPE = currsoptype

.SOPNUMBE = Glb_Invoice

.DOCDATE = Glb_Date

.CUSTNMBR = Glb_AccNo

.BACHNUMB = Glb_Batch

.LOCNCODE = Glb_Location

.SUBTOTAL = Glb_SubTotal

.DOCAMNT = Glb_DocAmt

.PYMTRCVD = TotalPayment

End With

salesOrder.taCreateSopPaymentInsertRecord_Items = LinePayment

Exit Do

Loop

End If

RMSCn.Close()

GPCn.Close()

Dim eConnect As New eConnectType

salesOrder.taSopHdrIvcInsert = SalesHdr

ReDim Preserve eConnect.SOPTransactionType(0)

eConnect.SOPTransactionType(0) = salesOrder

Dim fs As New FileStream(filename, FileMode.Create)

Dim writer As New XmlTextWriter(fs, New UTF8Encoding)

' Serialize using the XmlTextWriter .

Dim serializer As New XmlSerializer(GetType(eConnectType))

serializer.Serialize(writer, eConnect)

writer.Close()

Catch ex As System.Exception

MsgBox(ex.ToString)

End Try

End Sub

MCSD,
Certified Microsoft Retail Specialist
Certified Microsoft Great Plains Specialist
Certified Cognos BI Specialist

88.84.104.99

Page 1 of 1 (1 items)