Good afternoon, I'm trying to import an XML file from my site (PHP). I have some research on this, and I found it passing data to a stored procedure , but for C# . So, is it possible to send it?
here is my stored procedure
USE [sales_web] GO /****** Object: StoredProcedure [dbo].[InsertSALESPOS] Script Date: 11/30/2016 09:19:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertSALESPOS] @HeaderTemp HeaderTemp readonly, @DetailTemp DetailTemp readonly, @PaymentTemp PaymentTemp readonly, @VoucherTemp VoucherTemp readonly, @LedgerTemp LedgerTemp readonly AS BEGIN set nocount on; begin try begin transaction select ID, TransactionNo, TransactionDate, TotalTransaction, LocationID, UserID, CustomerCode, SendStatus into #HeaderTemp from @HeaderTemp where TransactionNo not in(SELECT TransactionNo from TransactionHeader with(nolock)) INSERT INTO dbo.SalesOrderHeader (TransactionNo, DocumentNo, TransDate, ETADate, ExternalDocument, Reference, RefTransNo, Category, CustomerCode, Remark, Remark2, TermOfPayment, Currency, Rate, UseVAT, SubTotal, DiscPercent, DiscAmount, GrandTotal, TotalReceivable, FromCustomerCode, PostingStatus, PostingBy, PostingDate, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, CustomerReceivable, LocationCode, VatPercent, VatAmount, FinalReleasedDate_Outlet) select h.TransactionNo, h.TransactionNo as DocumentNo, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as TransDate, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as ETADate, '' as ExternalDocument, '' as Reference, '' as RefTransNo, 'POS' as Category, h.CustomerCode, '' as Remark, '' as Remark2, Cast('' as Datetime) as TermOfPayment, 'IDR' as Currency, Cast(1 as Float) as Rate, Cast(0 as Bit) as UseVAT, Cast(SUM(d.TotalAmountAfterDisc) as Float) as SubTotal, Cast(0 as Float) as DiscPercent, Cast(0 as Float) as DiscAmount, Cast(SUM(d.TotalAmountAfterDisc) as Float) as GrandTotal, Cast(SUM(d.TotalAmountAfterDisc) as Float) as TotalReceivable, h.CustomerCode as FromCustomerCode, Cast(0 as Bit) as PostingStatus, '' as PostingBy, Cast('' as Datetime) as PostingDate, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as CreatedDate, h.UserID as CreatedBy, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as ModifiedDate, h.UserID as ModifiedBy, h.CustomerCode as CustomerReceivable, h.CustomerCode as LocationCode, Cast(0 as Float) as VatPercent, Cast(0 as Float) as VatAmount, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as FinalReleasedDate_Outlet from @HeaderTemp h Inner Join @DetailTemp d on h.id = d.TransactionID and h.CustomerCode = d.CustomerCode Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) Group By h.TransactionNo, TransactionDate , h.CustomerCode, h.UserID Insert Into dbo.SalesOrderDetail SELECT h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo, h.TransactionNo as SalesOrderTransNo, h.TransactionNo as RefTransNo, (ROW_NUMBER() Over(Order By h.TransactionNo))*1000 as [LineNo], isnull(d.ItemCode,''), d.Quantity, isnull(d.MainUOMCode,'') as UOM, d.UnitPrice, 0 as DiscountPercent, 0 as VatPercent, (d.Quantity*d.UnitPrice) as AmountPrice, ((d.Quantity*d.UnitPrice)-d.TotalAmountAfterDisc) as AmountDiscount, 0 as AmountVat, d.TotalAmountAfterDisc as Total, d.TotalAmountAfterDisc as Receivable, d.UnitPrice as UnitPriceAfterReceivable, '' as ExtraRemark, '' as ExtraRemark2, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as CreatedDate, h.UserID as CreatedBy, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as ModifiedDate, h.UserID as ModifiedBy from @HeaderTemp h Inner Join @DetailTemp d on h.id = d.TransactionID and h.CustomerCode = d.CustomerCode Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) Insert Into dbo.SalesOrderPayment Select h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo, h.TransactionNo as SalesOrderTransNo, p.PaymentMethodCode, p.TotalDue, p.TotalTendered From @HeaderTemp h Left Join @PaymentTemp p on h.id = p.TransactionID and h.CustomerCode = p.CustomerCode Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) --cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) Insert Into dbo.SalesOrderVoucher Select h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo, h.TransactionNo as SalesOrderTransNo, v.VoucherVendor as Voucher, Case When v.VoucherType = '1' Then 'NOMINAL' Else 'PERCENT' End as VoucherType, v.VoucherValue, v.VoucherAmount From @HeaderTemp h Left Join @VoucherTemp v On h.CustomerCode = v.CustomerCode And h.ID = v.TransactionID Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock)) Insert Into dbo.ItemLedgerEntry(TransactionNo, SequenceNo, DocumentNo, EntryType, CustomerCode, ItemCode, PostingDate, Quantity, UomCode, CreatedDate, CreatedBy) Select TransactionNo, SequenceNo, DocumentNo, EntryType, CustomerCode, ItemCode, cast(left(PostingDate,len(PostingDate)-6) as datetime) as PostingDate, Cast(Quantity as Float) as Quantity, UomCode, cast(left(CreatedDate,len(CreatedDate)-6) as datetime) as CreatedDate, CreatedBy From @LedgerTemp Where DocumentNo in (select Transactionno from #HeaderTemp with(nolock)) drop table #HeaderTemp Select 'Succes' as [Status] commit transaction end try begin catch rollback insert into XMLLOGERROR values(getdate(), 'SALESPOS', ERROR_MESSAGE()) end catch end
I tried this way
$xml=simplexml_load_file($file); $HeaderTemp = array(); $DetailTemp = array(); $PaymentTemp = array(); $LedgerTemp = array(); $VoucherTemp = array(); $tambahan = array('session_id' => date('d:h:i:s')); $login = str_replace(' ', '', $this->input->cookie('cookie_webstore_user')); $session = $login.$tambahan['session_id']; foreach ($xml->HeaderTemp as $HeaderTempnya) { $HeaderTemp[] = $HeaderTempnya; } foreach ($xml->DetailTemp as $DetailTempnya) { $DetailTemp[] = $DetailTempnya; } foreach($xml->PaymentTemp as $PaymentTempnya) { $PaymentTemp[] = $PaymentTempnya; } foreach($xml->LedgerTemp as $LedgerTempnya) { $LedgerTemp[] = $LedgerTempnya; } foreach($xml->VoucherTemp as $VoucherTempnya) { $VoucherTemp[] = $VoucherTempnya; } if($VoucherTempnya != ""){ $this->modelmodel->showsingle("[InsertSALESPOS] '{$HeaderTemp}','{$DetailTemp}','{$PaymentTemp}','{$VoucherTemp}','{$LedgerTemp}'"); }else{ $this->modelmodel->showsingle("[InsertSALESPOS] '{$HeaderTemp}','{$DetailTemp}','{$PaymentTemp}','','{$LedgerTemp}'"); }
With my script above, I get this error
Error Number: 22018/206
[Microsoft] [SQL Server Native Client 10.0] [SQL Server] Operand type behavior: varchar is not compatible with HeaderTemp
[InsertSALESPOS] 'Array', 'Array', 'Array', '', 'Array'
File Name: D: /xampp/htdocs/new_store/system/database/DB_driver.php
Line Number: 691
Sorry for my bad english.
Ok I solved the problem above using another method. I'm just wondering if this is not possible, explain why.