Sunday, March 17, 2013

PIVOT IN SQL SERVER EXAMPLE


DECLARE @startDate DATETIME;
SET @startDate = '20130301';
DECLARE @Keywords VARCHAR(8000)
DECLARE @Kwyword VARCHAR(255)

DECLARE cKeyword CURSOR FOR

WITH N(n) AS
(SELECT 0 UNION ALL SELECT n+1 FROM N WHERE n < 30)
SELECT
CONVERT(VARCHAR(10), DATEADD(dd,n,@startDate), 120)

FROM
N as t
WHERE
MONTH(DATEADD(dd,n,@startDate)) = MONTH(@startDate);



OPEN cKeyword
FETCH NEXT FROM cKeyword INTO @Kwyword
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Keywords = ISNULL( +@Keywords + ',', '') +'['+ @Kwyword +']'
FETCH NEXT FROM cKeyword INTO @Kwyword
END

CLOSE cKeyword
DEALLOCATE cKeyword

SELECT @Keywords AS Keywords

execute('select M.employee_v_email,* from (select M03_employee_i_employeeid,max(ESSL_AttendanceDate) as ESSL_AttendanceDate
,''In -''+CONVERT(VARCHAR(8), attendance_dt_intime, 108)  + '' Out-''+ CONVERT(VARCHAR(8), attendance_dt_outtime, 108) attendance_dt_intime
 from TRANS.T03_tblAttendance group by M03_employee_i_employeeid,attendance_dt_intime,
 attendance_dt_outtime) tab
pivot (max(attendance_dt_intime) for ESSL_AttendanceDate in ('+@Keywords+')) as pv
inner join MTR.M03_tblemployee as M on pv.M03_employee_i_employeeid = M.employee_i_employeeid
order by pv.M03_employee_i_employeeid')

Thursday, January 3, 2013

GET DIRECTION IN GOOLE MAP

saddr=source address
daddr=destination address
http://maps.google.com/maps?saddr=dumdum,IN&daddr=Ballygunge,%20Kolkata,%20IN&hl=en

Tuesday, November 27, 2012

Upload and Crop Images with jQuery, JCrop and ASP.NET

Owner of this article is http://www.mikesdotnetting.com

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UploadAndCrop.aspx.cs" Inherits="UploadAndCrop" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>

</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:Panel ID="pnlUpload" runat="server">
      <asp:FileUpload ID="Upload" runat="server" />
      <br />
      <asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" />
      <asp:Label ID="lblError" runat="server" Visible="false" />
    </asp:Panel>
    <asp:Panel ID="pnlCrop" runat="server" Visible="false">
      <asp:Image ID="imgCrop" runat="server" />
      <br />
      <asp:HiddenField ID="X" runat="server" />
      <asp:HiddenField ID="Y" runat="server" />
      <asp:HiddenField ID="W" runat="server" />
      <asp:HiddenField ID="H" runat="server" />
      <asp:Button ID="btnCrop" runat="server" Text="Crop" OnClick="btnCrop_Click" />
    </asp:Panel>
    <asp:Panel ID="pnlCropped" runat="server" Visible="false">
      <asp:Image ID="imgCropped" runat="server" />
    </asp:Panel>
  </div>
  </form>
</body>
</html>

Some Javascript is required. If you were starting this kind of application from scratch, it would need an awful lot of javascript. However, jQuery's real power is illustrated by just how little javascript this page actually needs. To make use of both jQuery and JCrop, they need to be linked to in the head section of the page, along with the css file that comes as part of the JCrop download. The link to jQuery makes use of the copy available from the Google Ajax API Library for better caching and faster downloading:

<link href="css/jquery.Jcrop.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"></script>
<script type="text/javascript" src="script/jquery.Jcrop.pack.js"></script>

All that's now needed to activate JCrop is a handful of lines of Javascript, which go within <script> that's in the head of the document below the previous lines:

<script type="text/javascript">
  jQuery(document).ready(function() {
    jQuery('#imgCrop').Jcrop({
      onSelect: storeCoords
    });
  });

  function storeCoords(c) {
    jQuery('#X').val(c.x);
    jQuery('#Y').val(c.y);
    jQuery('#W').val(c.w);
    jQuery('#H').val(c.h);
  };

</script>

It really is that simple. Jcrop has been applied to the image that has the id of imgCrop (the one in pnlCrop), and an event handler has been added to the select event of the cropper. This will happen when the user has completed selecting the area of the image they want to keep. The handler makes use of the storeCoords function, which sets the values of the HiddenFields, passing them the x and y coordinates of the top left of the selection relative to the image, and the width and height of the selection. That's all ASP.NET needs to know in order to process the image on the server. Now on to the server-side code.
There are 4 namespaces that need to be referenced in additional to the default ones that a new Web Form brings in:

using System.IO;
using SD = System.Drawing;
using System.Drawing.Imaging;
using System.Drawing.Drawing2D;

Since we will be working with instances of System.Drawing.Image as well as System.Web.UI.WebControls, which also has an Image class, I have aliased System.Drawing.Image, which will permit me to use a shorthand to reference the System.Drawing.Image classes. The first four lines of the code-behind simply set a variable to point to the file system path of the directory in which uploaded images will be stored, and show an empty Page_Load method:

String path = HttpContext.Current.Request.PhysicalApplicationPath + "images\\";

protected void Page_Load(object sender, EventArgs e)
{}

The next section covers the event handler for the Upload button click:


protected void btnUpload_Click(object sender, EventArgs e)
{
  Boolean FileOK = false;
  Boolean FileSaved = false;

  if (Upload.HasFile)
  {
    Session["WorkingImage"] = Upload.FileName;
    String FileExtension = Path.GetExtension(Session["WorkingImage"].ToString()).ToLower();
    String[] allowedExtensions = { ".png", ".jpeg", ".jpg", ".gif" };
    for (int i = 0; i < allowedExtensions.Length; i++)
    {
      if (FileExtension == allowedExtensions[i])
      {
        FileOK = true;
      }
    }
  }

  if (FileOK)
  {
    try
    {
      Upload.PostedFile.SaveAs(path + Session["WorkingImage"]);
      FileSaved = true;
    }
    catch (Exception ex)
    {
      lblError.Text = "File could not be uploaded." + ex.Message.ToString();
      lblError.Visible = true;
      FileSaved = false;
    }
  }
  else
  {
    lblError.Text = "Cannot accept files of this type.";
    lblError.Visible = true;
  }

  if (FileSaved)
  {
    pnlUpload.Visible = false;
    pnlCrop.Visible = true;
    imgCrop.ImageUrl = "images/" + Session["WorkingImage"].ToString();
  }
}

This code should be pretty familiar to anyone who has worked with the FileUpload control before. It simply checks to see if a file has been submitted, and ensures that it is of the right type. Then it saves the file to disk and the name of the file to a Session variable. Once the image has been saved, it is set as the ImageUrl of the Image control which has been targeted by JCrop and makes the containing Panel visible.

The next section covers the event handler for the Crop button:

protected void btnCrop_Click(object sender, EventArgs e)
{
  string ImageName = Session["WorkingImage"].ToString();
  int w = Convert.ToInt32(W.Value);
  int h = Convert.ToInt32(H.Value);
  int x = Convert.ToInt32(X.Value);
  int y = Convert.ToInt32(Y.Value);

  byte[] CropImage = Crop(path + ImageName, w, h, x, y);
  using (MemoryStream ms = new MemoryStream(CropImage, 0, CropImage.Length))
  {
    ms.Write(CropImage, 0, CropImage.Length);
    using(SD.Image CroppedImage = SD.Image.FromStream(ms, true))
    {
      string SaveTo = path + "crop" + ImageName;
      CroppedImage.Save(SaveTo, CroppedImage.RawFormat);
      pnlCrop.Visible = false;
      pnlCropped.Visible = true;
      imgCropped.ImageUrl = "images/crop" + ImageName;
    }
  }
}

This section references the image name from the Session variable, and then declares a number of integers that get their values from the HiddenFields that JCrop wrote to when the user selected the area they wanted to keep.

It then calls a method called Crop() to perform the actual cropping of the image (more of which soon). Crop() returns a byte array, which is written to a MemoryStream so that it can be used and converted back to an Image. This is then prefixed with the word "crop" in its name before being saved to disk and displayed to the user.

The Crop() method is below:

static byte[] Crop(string Img, int Width, int Height, int X, int Y)
{
  try
  {
    using (SD.Image OriginalImage = SD.Image.FromFile(Img))
    {
      using (SD.Bitmap bmp = new SD.Bitmap(Width, Height))
      {
        bmp.SetResolution(OriginalImage.HorizontalResolution, OriginalImage.VerticalResolution);
        using (SD.Graphics Graphic = SD.Graphics.FromImage(bmp))
        {
          Graphic.SmoothingMode = SmoothingMode.AntiAlias;
          Graphic.InterpolationMode = InterpolationMode.HighQualityBicubic;
          Graphic.PixelOffsetMode = PixelOffsetMode.HighQuality;
          Graphic.DrawImage(OriginalImage, new SD.Rectangle(0, 0, Width, Height), X, Y, Width, Height, SD.GraphicsUnit.Pixel);
          MemoryStream ms = new MemoryStream();
          bmp.Save(ms, OriginalImage.RawFormat);
          return ms.GetBuffer();
        }
      }
    }
  }
  catch (Exception Ex)
  {
    throw (Ex);
  }
}

This might at first glance look a little daunting to beginners, but most of the code just sets properties that affect the quality and appearance of the resulting image. What it does is to simply use the original image as a base from which a new image is drawn, and then save it to a MemoryStream object, which is returned as a byte array to be consumed by the calling code above. You should take note of the using blocks that are employed in this method. They ensure that Image, Bitmap and Graphics objects are all disposed of when the method is done. There's nothing worse than finding that your busy web app has slowed to a crawl through unreleased resources.
Oh, and thanks go to my cat, Alfie, for kindly agreeing to model for this exercise.





Note-Original Source From :-http://www.mikesdotnetting.com/Article/95/Upload-and-Crop-Images-with-jQuery-JCrop-and-ASP.NET

Thursday, October 4, 2012

Re -write url in asp.net

Re -write url in asp.net



First Create two hyperlinks in default.aspx page and paste the following
-------------------------------------------------------------------------------------------------------------------------------------------------
<asp:HyperLink  Text="ASP.net" runat="server" ID="link1" NavigateUrl=" "> </asp:HyperLink>
<asp:HyperLink  Text="PHP" runat="server" ID="HyperLink1" NavigateUrl=" "> </asp:HyperLink>


Then create a global.asax file and paste the same under <script>
-------------------------------------------------------------------------------------------------------------------------------------------------
void Application_Start(object sender, EventArgs e) 
    {
        RegisterRoutes(System.Web.Routing.RouteTable.Routes);
    }

    public static void RegisterRoutes(System.Web.Routing.RouteCollection routeCollection)
    {
        routeCollection.MapPageRoute("RouteForCustomer", "soumen/{training}", "~/soumen.aspx");
    }


Now since because you are passing like a value "training" you just need to make the url of same kind , so that the global.asax page can track it.

Now paste the below code under default.aspx file
-------------------------------------------------------------------------------------------------------------------------------------------------

link1.NavigateUrl =  Page.GetRouteUrl("RouteForCustomer", new { training = "ASP.net"});
HyperLink1.NavigateUrl = Page.GetRouteUrl("RouteForCustomer", new { training = "PHP" });


Now add a page soumen.aspx here i have added one. and paste the underlying lines
--------------------------------------------------------------------------------------------------------------------------------------------------
string category = Page.RouteData.Values["training"] as string;
lbl.Text = category;


thats it.

Wednesday, September 19, 2012

C# DateTime Format

Many DateTime formats are available. C# programs use the ToString method on the DateTime type. ToString receives many useful formats. These formats have confusing syntax forms. Correct formatting of dates and times is essential to many programs.

Format string

To start, we see an example of how you can use a specific formatting string with DateTime and ToString to obtain a special DateTime string. This is useful when interacting with other systems, or when you require a precise format.

Program that uses DateTime format [C#]  using System;  class Program
{static void Main()
{ DateTime time = DateTime.Now;// Use current time
string format = "MMM ddd d HH:mm yyyy"; // Use this format
Console.WriteLine(time.ToString(format)); // Write to console } }
Output Feb Fri 27 11:41 2009 Format string pattern MMM
three-letter month ddd display three-letter day of the WEEK d
display day of the MONTH HH display two-digit hours on 24-hour scale mm
display two-digit minutes yyyy display four-digit year

Note: The letters in the format string above specify the output you want to display. The final comment shows what the MMM, ddd, d, HH, mm, and yyyy will do.

Modify format


Continuing on, we see how you can modify the DateTime format string in the above example to get different output with ToString. We change some of the fields so the resulting value is shorter.

Program that uses different format [C#]
using System;
class Program {static void Main()
{ DateTime time = DateTime.Now; // Use current time string format = "M d h:mm yy";
// Use this format Console.WriteLine(time.ToString(format)); // Write to console } }
Output 2 27 11:48 09 Format string pattern M display one-digit month number
[changed] d display one-digit day of the MONTH [changed] h
display one-digit hour on 12-hour scale [changed] mm
display two-digit minutes yy display two-digit year
[changed]

Format string usages. You will also need to specify a format string when using DateTime.ParseExact and DateTime.ParseExact. This is because those methods require a custom pattern to parse.



Next you can use a single character with ToString or DateTime.ParseExact to specify a preset format available in the framework. These are standard formats and useful in many programs. They can eliminate typos in the custom format strings.

Program that tests formats [C#]
using System;
class Program {
static void Main()
{ DateTime now = DateTime.Now;
Console.WriteLine(now.ToString("d"));
Console.WriteLine(now.ToString("D"));
Console.WriteLine(now.ToString("f"));
Console.WriteLine(now.ToString("F"));
Console.WriteLine(now.ToString("g"));
Console.WriteLine(now.ToString("G"));
Console.WriteLine(now.ToString("m"));
Console.WriteLine(now.ToString("M"));
Console.WriteLine(now.ToString("o"));
Console.WriteLine(now.ToString("O"));
Console.WriteLine(now.ToString("s"));
Console.WriteLine(now.ToString("t"));
Console.WriteLine(now.ToString("T"));
Console.WriteLine(now.ToString("u"));
Console.WriteLine(now.ToString("U"));
Console.WriteLine(now.ToString("y"));
Console.WriteLine(now.ToString("Y"));} }
Output
d 2/27/2009 D
Friday, February 27, 2009 f
Friday, February 27, 2009 12:11 PM F
Friday, February 27, 2009 12:12:22 PM g
2/27/2009 12:12 PM G 2/27/2009 12:12:22 PM m
February 27 M February 27 o 2009-02-27T12:12:22.1020000-08:00 O
2009-02-27T12:12:22.1020000-08:00 s 2009-02-27T12:12:22 t
12:12 PM T 12:12:22 PM u 2009-02-27 12:12:22Z U
Friday, February 27, 2009 8:12:22 PM y February, 2009 Y February, 2009

Date strings

Here we see the ToLongDateString, ToLongTimeString, ToShortDateString, and ToShortTimeString methods on DateTime. These methods are equivalent to the lowercase and uppercase D and T methods shown in the example above.

Program that uses ToString methods [C#]
using System; class Progra{ static void Main()
{ DateTime now = DateTime.Now; Console.WriteLine(now.ToLongDateString());
// Equivalent to D Console.WriteLine(now.ToLongTimeString());
// Equivalent to T Console.WriteLine(now.ToShortDateString());
// Equivalent to d Console.WriteLine(now.ToShortTimeString());
// Equivalent to t
Console.WriteLine(now.ToString()); } }
Output ToLongDateString
Friday, February 27, 2009 ToLongTimeString
12:16:59 PM ToShortDateString 2/27/2009 ToShortTimeString
12:16 PM ToString 2/27/2009 12:16:59 PM

SOURCE-http://www.dotnetperls.com/datetime-format

Tuesday, September 11, 2012

Add fck editor in php

include "fckeditor/fckeditor.php"; //Top of the page


where you need the edior

$FCKeditor = new FCKeditor('FCKeditor1');
$FCKeditor->BasePath = 'fckeditor/';
$FCKeditor->Value = $content;
$FCKeditor->Height = '400px';
$FCKeditor->Create();
?>

Monday, September 10, 2012

To find the second highest salary from a table 

select top 1 name,id from tbltest where name not in (select top 1 name from tbltest order by name desc) order by name desc

Same if you want to select the nth highest salary from a table 

select top 1 name,id from tbltest where name not in (select top n name from tbltest order by name desc) order by name desc