Jumat, 07 Mei 2010

Split Character di SQL Server

Contoh pemisahan karakter (seperti LEFT, RIght,Mid di Visual Studio):


/****** Object:  StoredProcedure [Reports].[Usp_RetrieveRPTDetailARAPB2B]    Script Date: 05/07/2010 15:39:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================


ALTER PROCEDURE [Reports].[Usp_RetrieveRPTDetailARAPB2B]
    -- Add the parameters for the stored procedure here   
--declare
@Company     varchar(6) ,
@InterDistrib varchar(6),
@CdJournalType  varchar(3),
@Channel varchar(30),
@Directorate varchar(6),
@Intermediary varchar(11)

--set
--@company     ='z'
--set
--@InterDistrib ='ALL'
--set
--@CdJournalType  ='ALL'
--set
--@Channel ='AGEN'
--set
--@Directorate ='ALL'
--set
--@Intermediary ='84938800'

as
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
     
 DECLARE @TmpTABLE TABLE    
(   
 company varchar(6) ,
 InterDistrib varchar(6), 
 Channel varchar(40)
)        

DECLARE 
@company1 varchar(6),
@InterDistrib1 varchar(6),
@Channel1 varchar(40)
         
INSERT INTO @TmpTABLE (company,InterDistrib,Channel)
SELECT  @company1, @InterDistrib1 ,@Channel1
       
SELECT @company1 = company ,@InterDistrib1 =InterDistrib , @Channel1 = Channel FROM @TmpTABLE
 
SELECT @company1 = CASE WHEN @company = 'ALL' THEN  '' ELSE @company END;
SELECT @InterDistrib1 = CASE WHEN @InterDistrib = 'ALL' THEN  '' ELSE @InterDistrib END;
SELECT @Channel1 = CASE WHEN @Channel = 'ALL' THEN  '' ELSE @Channel END;

 --IF @IsReady = 1
    -- Insert statements for procedure here
    SELECT
        ROW_NUMBER() OVER (ORDER BY [Company]) AS NO
        , Reports.Company.CompanyDescription AS Company
        , Reports.RPTDetailNoteData.CdJournalType AS CdJournalType
        , Reports.RPTDetailNoteData.CdSubJournalType AS CdSubJournalType
        , Reports.RPTDetailNoteData.NoteNumber AS NoteNumber
        , Reports.RPTDetailNoteData.PolicyNo AS PolicyNo
        , RPTDetailNoteData.OurRefNo AS [OurRefNo]
        , RPTDetailNoteData.[ClaimNo] AS [ClaimNo]
        , Reports.RPTDetailNoteData.ENDNo AS ENDNo
        , Reports.RPTDetailNoteData.Currency AS Currency
        , Reports.RPTDetailNoteData.InternalDistribution AS InterDistrib
        , Reports.Channel.ChannelDescription AS Channel
        , Reports.RPTDetailNoteData.CdProduct AS CdProduct
        , Reports.RPTDetailNoteData.MOUID AS MOUID
        , Reports.RPTDetailNoteData.Cd2W4W AS Cd2W4W
        , Reports.RPTDetailNoteData.CdSalesman AS CdSalesman
        , Reports.RPTDetailNoteData.ChassisNo AS ChassisNo
        , Reports.RPTDetailNoteData.EngineNo AS EngineNo
        , Reports.RPTDetailNoteData.CdPayer AS CdPayer
        , PayerName.CustomerDescription  AS PayerName
        , Reports.RPTDetailNoteData.CdCustomer AS CdCustomer
        , Reports.Customer.CustomerDescription AS CustomerName
        --, Reports.CustomerGroup.CustomerGroupCode + '-' + Reports.CustomerGroup.CustomerGroupDescription   AS CustomerGroup   
        --, Reports.IntermediaryGroup.IntermediaryGroupCode + '-' +Reports.IntermediaryGroup.IntermediaryGroupDescription AS IntermediaryGroup  
        --, Reports.RPTDetailNoteData.CustStatus + '-' +  CustomerStatus.CustomerStatusDescription AS CustomerStatus   
        , Reports.RPTDetailNoteData.DtStart AS DtStart
        , Reports.RPTDetailNoteData.DtEND AS DtEND
        , Reports.RPTDetailNoteData.DtDue AS DtDue
        , Reports.RPTDetailNoteData.CdCOB AS CdCOB
        , Reports.RPTARAPData.NYDAmount AS NYDAmount
        , Reports.RPTDetailNoteData.NoteAmount AS NoteAmount
        , CASE Reports.RPTARAPData.CdAging 
            WHEN 0 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_1_30'
        , CASE RPTARAPData.CdAging
            WHEN 1 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_31_60'

        ,CASE RPTARAPData.CdAging
            WHEN 2 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_61_90'

        ,CASE RPTARAPData.CdAging
            WHEN 3 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_91_120'

        ,CASE RPTARAPData.CdAging
            WHEN 4 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_121_150'

        ,CASE RPTARAPData.CdAging
            WHEN 5 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_151_180'

        ,CASE RPTARAPData.CdAging
            WHEN 6 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_181_210'

        ,CASE RPTARAPData.CdAging
            WHEN 7 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_211_240'

        ,CASE RPTARAPData.CdAging
            WHEN 8 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_241_270'

        ,CASE RPTARAPData.CdAging
            WHEN 9 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_271_300'

        ,CASE RPTARAPData.CdAging
            WHEN 10 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_301_330'

        ,CASE RPTARAPData.CdAging
            WHEN 11 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_331_360'

        ,CASE RPTARAPData.CdAging
            WHEN 12 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_360'
        , Reports.RPTARAPData.ARAmount AS ARAmount
        , Reports.RPTARAPData.AmountGT30 AS AmountGT30
        , Reports.RPTARAPData.AmountGT60 AS AmountGT60
        , Reports.RPTARAPData.AmountGT120 AS AmountGT120
        , Reports.RPTARAPData.AmountGT180 AS AmountGT180
        , Reports.RPTDetailNoteData.GroupingNo AS GroupingNo
        , Reports.RPTDetailNoteData.ContractNo AS ContractNo
        , Reports.RPTDetailNoteData.ClientNo AS ClientNo
        , Reports.RPTDetailNoteData.PHolder AS PHolder
        , Reports.RPTDetailNoteData.DtProduction AS DtProduction
        , Reports.RPTDetailNoteData.UserEntry AS UserEntry
        , Reports.RPTDetailNoteData.ENDRmk AS ENDRmk
        , RPTLastSOANumberData.SOANo AS SOANo
        , RPTlastResultCategoryData.Modul AS Modul
        , Reports.OrderType.OrderTypeDescription AS OrderTypeName
        , Reports.RPTDetailNoteData.CdDealer AS CdDealer
        , ResultCategory.ResultCategoryDescription AS ResultCat
        , Reports.RPTDetailNoteData.DtDOL AS DtDOL
        , Reports.RPTDetailNoteData.CdIntermediary  AS CdIntermediary
        , Reports.RPTDetailNoteData.CdDirectorate AS CdDirectorate
        , Reports.RPTDetailNoteData.CdUnit + '-' + Unit.UnitDescription    AS CdUnit
        , Reports.RPTDetailNoteData.Partner AS Partners 
        , Reports.RPTARAPData.DtAsAt AS DtAsAt 
       

    FROM        
        Reports.RPTDetailNoteData
        INNER JOIN
             Reports.Company 
        ON Reports.RPTDetailNoteData.Company = Reports.Company.CompanyCode
        INNER JOIN
             Reports.RPTARAPData
        ON Reports.RPTDetailNoteData.FinanceNoteID = Reports.RPTARAPData.FinanceNoteID
        INNER JOIN
             Reports.Unit
        ON Reports.RPTDetailNoteData.CdUnit= Reports.Unit.UnitCode
        INNER JOIN
             Reports.Channel
        ON (SUBSTRING(Reports.RPTDetailNoteData.Channel,1,4)) = Reports.Channel.ChannelCode
        INNER JOIN
             Reports.Customer
        ON Reports.RPTDetailNoteData.CdCustomer = Reports.Customer.CustomerCode
        INNER JOIN
             Reports.Customer AS PayerName
        ON Reports.RPTDetailNoteData.CdPayer = PayerName.CustomerCode
        INNER JOIN
             Reports.OrderType
        ON Reports.RPTDetailNoteData.OrderType = Reports.OrderType.OrderTypeCode
        INNER JOIN
             Reports.Customer AS Intermediary
        ON Reports.RPTDetailNoteData.CdIntermediary = Intermediary.CustomerCode
        INNER JOIN
             Reports.RPTlastResultCategoryData AS RPTlastResultCategoryData
        ON RPTlastResultCategoryData.FinanceNoteID = RPTDetailNoteData.FinanceNoteID
        INNER JOIN
            Reports.RPTLastSOANumberData AS RPTLastSOANumberData
        ON RPTLastSOANumberData.FinanceNoteID = RPTDetailNoteData.FinanceNoteID
        INNER JOIN
            Reports.ResultCategory
        ON  ResultCategory.ResultCategoryCode  = RPTlastResultCategoryData.ResultCategory
        INNER JOIN
            Reports.RPTSettlementData RPTSettlementData
         ON RPTDetailNoteData.FinanceNoteID = RPTSettlementData.FinanceNoteID   
                 INNER JOIN
            Reports.CustomerStatus
         ON RPTDetailNoteData.CustStatus = CustomerStatus.CustomerStatusCode 

    WHERE

        --Reports.RPTDetailNoteData.Company LIKE '%' + @Company + '%' AND   
    (CASE WHEN ISNULL(@Company1, '')= '' THEN '' ELSE Company END)=ISNULL(@Company1, '') AND   
        --Reports.RPTDetailNoteData.InternalDistribution LIKE '%' + @InterDistrib + '%' AND Not(
    (CASE WHEN ISNULL(@InterDistrib1, '')= '' THEN '' ELSE RPTDetailNoteData.InternalDistribution END)=ISNULL(@InterDistrib1, '') AND NOT(
            RPTDetailNoteData.Channel='LEAS' AND
            RPTDetailNoteData.Partner IN ('ACC','TAF','FIF','SAF')) AND
        --Reports.RPTDetailNoteData.CdJournalType LIKE '%' + @CdJournalType + '%' AND  
    (CASE WHEN ISNULL(@CdJournalType, '')= '' THEN '' ELSE RPTDetailNoteData.CdJournalType END)=ISNULL(@CdJournalType, '') AND   
        --Reports.RPTDetailNoteData.Channel LIKE '%' + @Channel + '%'  AND
    (CASE WHEN ISNULL(@Channel1, '')= '' THEN '' ELSE Channel END)=ISNULL(@Channel1, '') AND       
        --Reports.RPTDetailNoteData.CdIntermediary  LIKE '%' + @CdInetermediary + '%' AND
    (CASE WHEN ISNULL(@Intermediary, '')= '' THEN '' ELSE CdIntermediary END)=ISNULL(@Intermediary, '') AND       
        --Reports.RPTDetailNoteData.CdDirectorate LIKE '%' + @CdDirectorate + '%'
    (CASE WHEN ISNULL(@Directorate, '')= '' THEN '' ELSE CdDirectorate END)=ISNULL(@Directorate, '')
    AND RPTDetailNoteData.TpNote = 'B2B' 
   
END





Diposting oleh Sebastian Yuke di 15.48 0 komentar

Kamis, 06 Mei 2010

How Do i Create Attachment from Stream Data


System.Net.Mail FAQ


3.4.2 How do I create an attachment from a stream? Printer Friendly

Something that is new in System.Net.Mail, is the capability to create attachments from streams. With this capability we can create an attachment from text, binary data, or from basically anything in memory. We simply need to make sure it is written to a stream.

The following example creates an attachment from some simple text, but it could have just as easily have come from Sql Server.

[ C# ] 

static void AttachmentFromStream()
{

//create the mail message
 MailMessage mail = new MailMessage();

//set the addresses
mail.From = new MailAddress("me@mycompany.com");
mail.To.Add("you@yourcompany.com");
 
//set the content
mail.Subject = "This is an email";
mail.Body = "this content is in the body";

//Get some binary data
byte[] data = GetData();

//save the data to a memory stream
 MemoryStream ms = new MemoryStream(data);

//create the attachment from a stream. Be sure to name the data with a file and 
//media type that is respective of the data
mail.Attachments.Add( new Attachment( ms, "example.txt", "text/plain" ));
 
//send the message
SmtpClient smtp = new SmtpClient("127.0.0.1");
smtp.Send(mail);
}
static byte[] GetData()
{
//this method just returns some binary data.
//it could come from anywhere, such as Sql Server
 string s = "this is some text";
byte[] data = Encoding.ASCII.GetBytes(s);
return data;
}



[ VB.NET ] 
Sub AttachmentFromStream()

'create the mail message
Dim mail As New MailMessage()
 
'set the addresses
mail.From = New MailAddress("me@mycompany.com")
mail.To.Add("you@yourcompany.com")

 'set the content
mail.Subject = "This is an email"
mail.Body = "this content is in the body"

'Get some binary data
Dim data As Byte() = GetData()

'save the data to a memory stream
 Dim ms As New MemoryStream(data)

'create the attachment from a stream. Be sure to name the data with a file and 
'media type that is respective of the data
mail.Attachments.Add(New Attachment(ms, "example.txt", "text/plain"))
 
'send the message
Dim smtp As New SmtpClient("127.0.0.1")
smtp.Send(mail)
End Sub 'AttachmentFromStream

Function GetData() As Byte()
'this method just returns some binary data.
 'it could come from anywhere, such as Sql Server
Dim s As String = "this is some text"
Dim data As Byte() = Encoding.ASCII.GetBytes(s)
Return data
End Function 'GetData


Diposting oleh Sebastian Yuke di 16.12 0 komentar
Langganan: Postingan (Atom)