Friday, May 14, 2010

SET NOCOUNT ON;
GO
USE master;
IF EXISTS (SELECT name FROM sys.tables
WHERE name = 'wages')
DROP TABLE wages;
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
);
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
select * from wages
--drop
SELECT emp_id, COALESCE(hourly_wage, salary, commission)
FROM wages;
select * from wages
SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS money) AS 'Total Salary'
FROM wages;
GO

Labels: COALESCE

Tuesday, December 26, 2006

How does XMLHTTP work

-----------------------

var http;
function getPage()
{
http = new ActiveXObject("Microsoft.XMLHTTP");
var myurl = "http://localhost/WebApplication_Soum/WebForm2.aspx";

http.open("GET", myurl , true);
http.onreadystatechange = useHttpResponse;
http.send(null);

}

function useHttpResponse()
{
if (http.readyState == 4) {
//var textout = http.responseText;
//document.write.textout;
alert(http.responseText);
document.getElementById("div2").innerHTML = "Response is " + http.responseText;
}
else
{
document.getElementById("div2").innerText = "HTTP Ready State is " + http.readyState;
}
}

Monday, November 27, 2006

Excel

--if you are running this sp without any parameters then it will perform the export by default for last one month.
--SELECT 'Cost Centre','Dept','Activity Code','JC Analysis Code','Employee Number','Basic Hours/Calls','Basic Amount','Bonus Hours/Calls','Bonus Amount','Holiday Hours/Calls','Holiday Amount','Mileage Value','Expenses Value' UNION ALL SELECT Cost_Centre 'Cost Centre',Dept 'Dept',Activity_Code 'Activity Code',JC_Analysis_Code 'JC Analysis Code',Employee_Number 'Employee Number',Basic_Hours_Calls 'Basic Hours/Calls',Basic_Amount 'Basic Amount',Bonus_Hours_Calls 'Bonus Hours/Calls',Bonus_Amount 'Bonus Amount',Holiday_Hours_Calls 'Holiday Hours/Calls',Holiday_Amount 'Holiday Amount',Mileage_Value 'Mileage Value',Expenses_Value 'Expenses Value' FROM ##TempResults
--DROP TABLE ##TempResults
CREATE PROCEDURE dbo.stpFEPaymentGetList_Excel_ALL_ACTIVITY
(
@fromDate datetime=NULL,
@toDate datetime=NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(8000),
@fileName varchar(50),
@command varchar(8000),
@Script_file_name varchar(150),
@FTP_site varchar(100),
@FTP_user_id varchar(50),
@FTP_password varchar(100),
@FTP_destination varchar(100),
@Source_folder varchar(100),
@Files_to_upload varchar(100),
@finalEmailBody varchar(8000),
@emailServer varchar(100),
@FromEmailID varchar(100),
@ToEmailID varchar(100),
@CcEmailID varchar(100)
CREATE TABLE ##TempResults
(
Cost_Centre varchar(1000),
Dept varchar(1000),
Activity_Code varchar(1000),
JC_Analysis_Code varchar(3),
Employee_Number varchar(1000),
Basic_Hours_Calls varchar(25),
Basic_Amount varchar(25),
Bonus_Hours_Calls varchar(25),
Bonus_Amount varchar(25),
Holiday_Hours_Calls varchar(25),
Holiday_Amount varchar(25),
Mileage_Value varchar(25),
Expenses_Value varchar(25)
)
IF @fromDate IS NULL OR @toDate IS NULL
BEGIN
SET @toDate=GETDATE()
SET @fromDate=DATEADD(Month,-1,@toDate)
END
SELECT @fileName='ActXl'+CAST(DATEPART(millisecond,GETDATE()) AS varchar(4))+'.xls'
SET @Files_to_upload=@fileName
SET @fileName='D:\Websites\Raptor\General\'+@fileName
SET @sql='SELECT CLC.strClientCategory as ''Cost Centre'''
SET @sql=@sql+' ,CLC.strClientCategory as ''Dept'''
SET @sql=@sql+' ,AC.strActivityCode as ''Activity Code'''
SET @sql=@sql+' ,611 as ''JC Analysis Code'''
SET @sql=@sql+' ,tblRPTUser.strUserPMICode AS ''Employee Number'''
SET @sql=@sql+' ,(AC.dcBasicRate-(AC.dcHolidayRate+AC.dcBonus)) as ''Basic Hours/Calls'''
SET @sql=@sql+' ,((AC.dcBasicRate-(AC.dcHolidayRate+AC.dcBonus))* FA.intCompletedCalls) as ''Basic Amount'''
SET @sql=@sql+' ,AC.dcBonus as ''Bonus Hours/Calls'''
SET @sql=@sql+' ,(AC.dcBonus * FA.intCompletedCalls) as ''Bonus Amount'''
SET @sql=@sql+' ,AC.dcHolidayRate as ''Holiday Hours/Calls'''
SET @sql=@sql+' ,(AC.dcHolidayRate * FA.intCompletedCalls) as ''Holiday Amount'''
SET @sql=@sql+' ,cast(round(isnull((FA.dcMiles*AC.dcMilageRate),0),2) as decimal(10,2)) as ''Mileage Value'''
SET @sql=@sql+' ,FA.dcExpenses as ''Expenses Value'''
SET @sql=@sql+' FROM tblrptActivity AC'
SET @sql=@sql+' inner join tblrptcampaign CMP '
SET @sql=@sql+' ON AC.FK_intCampaignID=CMP.PK_intCampaignID '
SET @sql=@sql+' inner JOIN tblrptclient CL'
SET @sql=@sql+' ON CMP.FK_intClientID=CL.PK_intClientID '
SET @sql=@sql+' inner JOIN tblrptmasterclientcategory CLC'
SET @sql=@sql+' ON CL.FK_intClientCategoryID=CLC.PK_intClientCategoryID '
SET @sql=@sql+' INNER JOIN tblRPTFEActivityAllocation FA'
SET @sql=@sql+' on FA.fK_intactivityid = pk_intactivityid'
SET @sql=@sql+' Inner JOIN tblRPTUser'
SET @sql=@sql+' ON tblRPTUser.PK_intUserID = FA.fk_INTFEID'
SET @sql=@sql+' where btClaimApproved=1 AND FA.dtDateOfApproval BETWEEN '''+ CAST(@fromDate AS varchar(12))+''' AND ''' + CAST(@toDate AS varchar(12))+''''
INSERT INTO ##TempResults EXEC(@sql)
SET @sql=''
SET @sql='SELECT ''Cost Centre'',''Dept'',''Activity Code'',''JC Analysis Code'',''Employee Number'',''Basic Hours/Calls'',''Basic Amount'',''Bonus Hours/Calls'',''Bonus Amount'',''Holiday Hours/Calls'',''Holiday Amount'',''Mileage Value'',''Expenses Value'' UNION ALL '
SET @sql=@sql+'SELECT Cost_Centre ''Cost Centre'',Dept ''Dept'',Activity_Code ''Activity Code'',JC_Analysis_Code ''JC Analysis Code'',Employee_Number ''Employee Number'',Basic_Hours_Calls ''Basic Hours/Calls'',Basic_Amount ''Basic Amount'',Bonus_Hours_Calls ''Bonus Hours/Calls'',Bonus_Amount ''Bonus Amount'',Holiday_Hours_Calls ''Holiday Hours/Calls'',Holiday_Amount ''Holiday Amount'',Mileage_Value ''Mileage Value'',Expenses_Value ''Expenses Value'' FROM ##TempResults'
SET @sql='bcp "'+@sql+'" queryout "'+@fileName+'" -UWebAppUser -PPlA123magic -c -CRAW'
SET @sql=@sql+' -S'+@@SERVERNAME
EXEC master..xp_cmdshell @sql
DROP TABLE ##TempResults
--Initializing FTP command variables
SET @Script_file_name='D:\\Websites\Raptor\unilever\FTPScripts\ScriptaCTIVITY.txt'
SET @FTP_site='84.252.241.203'
SET @FTP_user_id='Greenland'
SET @FTP_password='Land12345'
SET @FTP_destination='/webspiders/raptor/Wagesxls'
SET @Source_folder='D:\Websites\Raptor\General'
SET @command='echo OPEN '+@FTP_site+'> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo '+@FTP_user_id+'>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo '+@FTP_password+'>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo cd "'+@FTP_destination+'">> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo lcd "'+@Source_folder+'">> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo binary>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo put '+@Files_to_upload+'>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='echo quit>> "'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
SET @command=''
SET @command='ftp.exe -i -s:"'+@Script_file_name + '"'
EXEC master..xp_cmdshell @command
end
--Send email, notifying that export has been completed
SET @emailServer='192.168.16.12'
SET @FromEmailID='wagesxls@pmifm.co.uk'
SET @ToEmailID='nitin@webspiders.com'
SET @CcEmailID='ritwik.sanyal@webspiders.com;swaty.dalmia@webspiders.com'
SET @finalEmailBody='Dear Administrator,

Activities are exported to excel, please visit the following link to get the file: -
Please click here to get the file'
SET @finalEmailBody=@finalEmailBody+'

Regards,
PMIFM Raptor'
EXEC SendEmail 'Activities exported to excel: Please get it by visiting the following link',@finalEmailBody,
@emailServer,@FromEmailID,@ToEmailID,@CcEmailID

GO

Monday, October 30, 2006

Making a File Open and asking the user to Save

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
byte[] fileRD = br.ReadBytes((int)fs.Length);
br.Close();
fs.Close();

Response.AddHeader("Content-Disposition", "attachment;filename=\"" + strfilename + "\"");
Response.BinaryWrite( fileRD );
Response.End();

Monday, May 22, 2006

CALLING WEB-SERVICE FROM SP

----------------------------
create procedure http_get( @sUrl varchar(200), @response varchar(8000) out)
As

Declare @obj int ,@hr int ,@status int ,@msg varchar(255)

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr < 0
begin
Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1)
return
end
else
select @obj

exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
if @hr <0
begin
set @msg = 'sp_OAMethod Open failed'
goto eh
end
else
select @hr, @obj, @surl

exec @hr = sp_OAMethod @obj, 'send'
if @hr <0
begin
set @msg = 'sp_OAMethod Send failed'
goto eh
end
else
select @hr, @obj

exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <0
begin
set @msg = 'sp_OAMethod read status failed'
goto eh
end
else
select 'sp_OAGetProperty of '+ convert(varchar, @obj) +' executed with status='+ convert(varchar, @status) + ' '

/*
if @status <> 200
begin set @msg = 'sp_OAMethod http status ' + str(@status)
goto eh
end
*/
exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <0
begin
set @msg = 'sp_OAMethod read response failed'
goto eh
end
else
select @response

exec @hr = sp_OADestroy @obj

return

--Error handling code
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
go

/*
then run this
declare @resp varchar(8000)
exec http_get 'http://localhost/WebServiceToBeCalledFromSP/HelloWorld.asmx?op=CallMe',@resp out
print @resp
*/

Monday, September 12, 2005

A Simple and Generic sorting technique for your business object collection
Stop writing IComparer classes to sort your custom collections! This article discusses sorting of a user defined collection object based on any of the properties of the business entity. This sorting technique is generic for all the collection objects. You can sort your collection based on their properties.

Solution
How it works?
SortableCollectionBase class uses "GenericComparer" class for sorting. "GenericComparer" class implements IComparer interface and compares the objects based on the public property (Sort Column) of the class type dynamically irrespective of the collection.

GenericComparer class///

/// This class is used to compare any
/// type(property) of a class.
/// This class automatically fetches the
/// type of the property and compares.
///

public sealed class GenericComparer:IGenericComparer
{
///

/// Sorting order
///

public enum SortOrder
{
Ascending = 0,
Descending = 1
}
Type objectType;
///

/// Type of the object to be compared.
///

public Type ObjectType
{
get{return objectType;}set{objectType = value;}
}
string sortcolumn = "";
///

/// Column(public property of the class) to be sorted.
///

public string SortColumn
{
get{return sortcolumn;}set{sortcolumn = value;}
}
int sortingOrder = 0;
///

/// Sorting order.
///

public int SortingOrder
{
get{return sortingOrder;}set{sortingOrder = value;}
}
///

/// Compare interface implementation
///

/// Object 1
/// Object 2
/// Result of comparison
public int Compare(object x, object y)
{
//Dynamically get the protery info
//based on the protery name
PropertyInfo propertyInfo =
ObjectType.GetProperty(sortcolumn);
//Get the value of the instance
IComparable obj1 =
(IComparable)propertyInfo.GetValue(x,null) ;
IComparable obj2 =
(IComparable)propertyInfo.GetValue(y,null) ;
//Compare based on the sorting order.
if(sortingOrder == 0)
return ( obj1.CompareTo(obj2) );
else
return ( obj2.CompareTo(obj1) );
}
}

SortableCollectionBase class
///

/// Abstract implementation of Sortable collection.
///

public abstract class SortableCollectionBase :
CollectionBase,ISortable
{
string sortcolumn="";
public string SortColumn
{
get{return sortcolumn;}
set{sortcolumn = value;}
}
GenericComparer.SortOrder sortingOrder =
GenericComparer.SortOrder.Ascending;
public GenericComparer.SortOrder SortingOrder
{
get{return sortingOrder;}set{sortingOrder = value;}
}
Type sortObjectType;
public Type SortObjectType
{
get{return sortObjectType;} set{sortObjectType = value;}
}
public virtual void Sort()
{
if(sortcolumn == "")
throw new Exception("Sort column required.");
if(SortObjectType == null)
throw new Exception("Sort object type required.");
IGenericComparer sorter = new GenericComparer();
sorter.ObjectType = sortObjectType;
sorter.SortColumn = sortcolumn;
sorter.SortingOrder = (int)sortingOrder;
InnerList.Sort(sorter);
}
}
How to use SortableCollectionBase class?
Using SortableCollectionBase is simple and effortless. Just inherit your custom collection class from SortableCollectionBase class and in the constructor set the SortableObjectType property. Now your class becomes sortable.
For example///

/// Note : This student collection
/// inherhits SortableCollectionBase
/// In the constructor set the
/// SortObjectType for sorting.
///

public class StudentCollection :
SortableCollectionBase
{
public StudentCollection()
{
//In your collection class
//constructor add this line.
//set the SortObjectType for sorting.
base.SortObjectType = typeof(Student);
}
public Student this[ int index ]
{
get
{
return( (Student) List[index] );
}
set
{
List[index] = value;
}
}
public int Add( Student value )
{
return( List.Add( value ) );
}
public int IndexOf( Student value )
{
return( List.IndexOf( value ) );
}
public void Insert( int index, Student value )
{
List.Insert( index, value );
}
//......
}
How to sort?
To sort your custom collection call the "Sort()" method. Note: Make sure that you have set the "SortColumn" property before calling the "Sort()" method. "SortColumn" is the property of the business entity based on which the collection will be sorted. In this case SortColumn can be "name", "id" or "Dob".StudentCollection Students = new StudentCollection();
Students.Add(new Student("Sai",5,new DateTime(1914,10,4)));
Students.Add(new Student("Sree",1,new DateTime(1980,10,4)));
Students.Add(new Student("Sow",3,new DateTime(2000,4,1)));
Students.Add(new Student("Zaheer",2,new DateTime(1978,1,27)));
Students.SortColumn = "Name";
Students.SortingOrder =
GenericComparer.SortOrder.Ascending;
Students.Sort();
dataGrid1.DataSource = Students;

Tuesday, August 30, 2005

USING FOR XML EXPLICIT IN SQL SERVER 2000

select 1 as Tag,
NULL as Parent,
ur.role_id as [ur!1!role_id],
null as [rs!2!service_id],
null as [rs!3!element_lvl_id]
from tum_user u, tum_user_role ur
where u.user_id = 1
and u.user_id = ur.user_id
union
select 2 as Tag, 1 as Parent,
ur.role_id ,
rs.service_id as [rs!2!service_id],
null as [rs!3!element_lvl_id]
from tum_user u, tum_user_role ur, tad_role_service rs
where u.user_id = 1
and u.user_id = ur.user_id
and ur.role_id = rs.role_id
union
select 3 as tag, 2 as Parent,
ur.role_id,
rs.service_id as [rs!2!service_id],
rs.element_lvl_id as [rs!3!element_lvl_id]
from tum_user u, tum_user_role ur, tad_role_service rs
where u.user_id =1
and u.user_id = ur.user_id
and ur.role_id = rs.role_id
order by [ur!1!role_id], [rs!2!SERVICE_ID], [rs!3!element_lvl_id]
for xml explicit

the output
============
<ur role_id="1">
<rs service_id="73">
<rs element_lvl_id="0"/>
<rs element_lvl_id="2"/>
<rs element_lvl_id="3"/>
</rs>
<rs service_id="122">
<rs element_lvl_id="0"/>
<rs element_lvl_id="1"/>
</rs>
<rs service_id="123">
<rs element_lvl_id="-1"/>
</rs>
</ur>

soum-net