Friday 17 October 2014

How to use multiple aggregate functions in a single query(without sub query) in oracle/sql server


Here i am using two count functions with different conditions in a single query.


select count(case when department_id = 10 and salary<5000 then 1 end), 
          count(case when department_id = 20 then 1 end) 
from employees;

Output:





If we want the output as the two results in same column,


select count(EMPLOYEE_ID) from employees where DEPARTMENT_ID=30 and salary<5000
union all

select count(EMPLOYEE_ID) from EMPLOYEES where DEPARTMENT_ID=50;

Output:








References: http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query.


Five things to remember to improve the stored procedure performance while create stored procedure in SQL Server

Steps to improve the performance  of Stored procedure in SQL Server

 1) Set NOCOUNT ON;
 2) Don't use input parameters directly. Assign them to Locally  declared variables in stored procedure.
 3) Don't use Count(*) use Count(pk_Id)
 4) Use Exists instead of IN in where condition
 5) Use distinct instead of Group by clause in all possible cases

Example stored procedure
 Here my using group by clause to full file my requirement. Try to avoid the using of group by clause

ALTER PROCEDURE [dbo].[your sp name here] 
@warehouseId char(4)
AS
BEGIN
    SET NOCOUNT ON;
    Declare @Local_warehouseId char(4)=@warehouseId
   
    select oh.OrderNum,case when od.picked is null then 0 else od.picked end as picked,oh.numLines,mem.firstName,mem.lastName,mem.phone,mem.Suburb,oh.memberid as memberID,oh.PickUpPointTimeSlot as EstPickUpTime  from OrderHeader oh join 
members mem on mem.memberID=oh.memberid
join PickupPointTimes pPoint on pPoint.PickupPointID=oh.PickupPointID and oh.PickUpPointTimeSlot=pPoint.TimeSlot and pPoint.PickUpDay=oh.pickupDay
Left outer join (select OrderNum,COUNT(LineItemID) as picked from OrderDetails where PickedQTY is not null group by OrderNum )  as od  on od.OrderNum=oh.OrderNum 
where exists (select * from PickupPoints pp where pp.PickupPointID=oh.PickupPointID and WarehouseID=@Local_warehouseId) and oh.status='RP'
order by pPoint.SortCode,oh.numLines
 
END

Thursday 9 October 2014

Label and Barcode printing using ZPL printer from C#.Net


Printing label and Barcodes using ZPL printer from C#.Net

  Here I am printing Label using PrintLabel Method and printing Barcode  using PrintBarcode method. We can use this code windows,web or any other applications which are using code behide as C#.Net. It's works if ZPL printer is connected via parallel port or via Bluetooth to your system.

Here I am passing ZPL language command  to zpl printer (zpl printer supports EPL and ZPL commands).

Note:Code is written according to my requirements. You can change it according to your requirements. If anyone needs help then you can shoot an email to hareesh434@gmail.com with subject as Share your doubts and answers with IT world. If I can i will help you.

C#.Net  Code

Below class  is for passing the ZPL command to printer

  public class UpcLabel
    {

        private string strFirstName;
        private string strLastName;
        private string strNoOfCopies;
        private string PickUpTime;
        private string Cold;
        private string NewMember;
        public UpcLabel()
        {
        }
        public UpcLabel(string strFirstName, string strLastName, string strNoOfCopies, string PickUpTime, string Cold, string NewMember)
        {
            if (strFirstName == null || strLastName == null || strNoOfCopies == "" || strNoOfCopies=="0")
            {
                throw new ArgumentNullException("strFirstName");
            }

            this.strFirstName = strFirstName;
            this.strLastName = strLastName;
            this.strNoOfCopies = strNoOfCopies;
            this.PickUpTime = PickUpTime;
            this.Cold = Cold;
            this.NewMember = NewMember;
        }
        public void PrintBarcode(string printerName, string pProductName, string pBarcode,string strNumOfCopies)
        {
            if (printerName == null)
            {
                throw new ArgumentNullException("printerName");
            }
            StringBuilder strBldr = new StringBuilder();
            strBldr.AppendLine("^XA");

            strBldr.AppendLine("^FO40,100");
            strBldr.AppendLine("^AQ,50,30");
            // sb1.AppendLine("^FDAnja^FS");
            strBldr.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", pProductName));
            //^FO100,100^BY3
            strBldr.AppendLine("^FO80,200^BY3");
            //^BCN,150,Y,N,Y,N
            strBldr.AppendLine("^BCN,125,Y,N,Y,N");
            strBldr.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", pBarcode));
            strBldr.AppendLine(string.Format(CultureInfo.InvariantCulture, "^PQ{0}", strNumOfCopies));
            strBldr.AppendLine("^XZ");
            RawPrinterHelper.SendStringToPrinter(printerName, strBldr.ToString());



        }
        public void Print(string printerName)
        {
            if (printerName == null)
            {
                throw new ArgumentNullException("printerName");
            }
            StringBuilder sb1=new StringBuilder();
            //^XA=Indicates Starting of Zpl
                sb1.AppendLine("^XA");
                sb1.AppendLine("^LL350");//^FS
                sb1.AppendLine("^PW930");//^FS
                sb1.AppendLine("^FO10,10");
                sb1.AppendLine("^AQ,80,80");
                // sb1.AppendLine("^FDAnja^FS");

                //FOa,b
                //a=Postion from x-axis
                //b=Position from y-axis
                //Aa,b,c
                //a=Font size Like Q,V,R,0
                //b=Font width
                //c=Font Height
             
                sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.strFirstName));
                sb1.AppendLine("^FO10,68");
                sb1.AppendLine("^AQ,80,80");
                sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.strLastName));
       
            sb1.AppendLine("^FO10,150");
            sb1.AppendLine("^AQ,50,50");
            sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.PickUpTime));

            sb1.AppendLine("^FO240,150");
            sb1.AppendLine("^AQ,50,50");
            sb1.AppendLine(string.Format(CultureInfo.InvariantCulture,"^FD{0}^FS",this.Cold));
            //^PQ2= Indicates number of copies to print
            sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^PQ{0}", this.strNoOfCopies));
            //sb1.AppendLine("^PQ2");
            //^XZ=Indicates ending of ZPL page
            sb1.AppendLine("^XZ");
            RawPrinterHelper.SendStringToPrinter(printerName, sb1.ToString());
            //for (int counter = 0; counter <Convert.ToInt32(this.strNoOfCopies); counter++)
            //{
            //    RawPrinterHelper.SendStringToPrinter(printerName, sb1.ToString());
            //    System.Threading.Thread.Sleep(500);
            //}
            if (this.NewMember != "")
            {
                StringBuilder strb= new StringBuilder();
                strb.AppendLine("^XA");
                strb.AppendLine("^LL350");//^FS
                strb.AppendLine("^PW930");//^FS
                strb.AppendLine("^FO20,30");
                strb.AppendLine("^AQ,80,80");
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.NewMember));
                strb.AppendLine("^FO20,150");
                strb.AppendLine("^AQ,50,50");
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.PickUpTime));
             
                strb.AppendLine("^FO240,150");
                strb.AppendLine("^AQ,50,50");
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.Cold));
                //^PQ2= Indicates number of copies to print
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^PQ{0}", "1"));
                //sb1.AppendLine("^PQ2");
                //^XZ=Indicates ending of ZPL page
                strb.AppendLine("^XZ");

                RawPrinterHelper.SendStringToPrinter(printerName, strb.ToString());
            }
        }
    }
For Printing



public class RawPrinterHelper
    {
        // Structure and API declarions:
        [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Ansi)]
        public class DOCINFOA
        {
            [MarshalAs(UnmanagedType.LPStr)]
            public string pDocName;
            [MarshalAs(UnmanagedType.LPStr)]
            public string pOutputFile;
            [MarshalAs(UnmanagedType.LPStr)]
            public string pDataType;
        }
        [DllImport("winspool.Drv", EntryPoint = "OpenPrinterA", SetLastError = true, CharSet = CharSet.Ansi, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool OpenPrinter([MarshalAs(UnmanagedType.LPStr)] string szPrinter, out IntPtr hPrinter, IntPtr pd);

        [DllImport("winspool.Drv", EntryPoint = "ClosePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool ClosePrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "StartDocPrinterA", SetLastError = true, CharSet = CharSet.Ansi, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool StartDocPrinter(IntPtr hPrinter, Int32 level, [In, MarshalAs(UnmanagedType.LPStruct)] DOCINFOA di);

        [DllImport("winspool.Drv", EntryPoint = "EndDocPrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool EndDocPrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "StartPagePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool StartPagePrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "EndPagePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool EndPagePrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "WritePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool WritePrinter(IntPtr hPrinter, IntPtr pBytes, Int32 dwCount, out Int32 dwWritten);

        // SendBytesToPrinter()
        // When the function is given a printer name and an unmanaged array
        // of bytes, the function sends those bytes to the print queue.
        // Returns true on success, false on failure.
        public static bool SendBytesToPrinter(string szPrinterName, IntPtr pBytes, Int32 dwCount)
        {
            Int32 dwError = 0, dwWritten = 0;
            IntPtr hPrinter = new IntPtr(0);
            DOCINFOA di = new DOCINFOA();
            bool bSuccess = false; // Assume failure unless you specifically succeed.

            di.pDocName = "My C#.NET RAW Document";
            di.pDataType = "RAW";

            // Open the printer.
            if (OpenPrinter(szPrinterName.Normalize(), out hPrinter, IntPtr.Zero))
            {
                // Start a document.
                if (StartDocPrinter(hPrinter, 1, di))
                {
                    // Start a page.
                    if (StartPagePrinter(hPrinter))
                    {
                        // Write your bytes.
                        bSuccess = WritePrinter(hPrinter, pBytes, dwCount, out dwWritten);
                        EndPagePrinter(hPrinter);
                    }
                    EndDocPrinter(hPrinter);
                }
                ClosePrinter(hPrinter);
            }
            // If you did not succeed, GetLastError may give more information
            // about why not.
            if (bSuccess == false)
            {
                dwError = Marshal.GetLastWin32Error();
            }
            return bSuccess;
        }

        public static bool SendFileToPrinter(string szPrinterName, string szFileName)
        {
            // Open the file.
            FileStream fs = new FileStream(szFileName, FileMode.Open);
            // Create a BinaryReader on the file.
            BinaryReader br = new BinaryReader(fs);
            // Dim an array of bytes big enough to hold the file's contents.
            Byte[] bytes = new Byte[fs.Length];
            bool bSuccess = false;
            // Your unmanaged pointer.
            IntPtr pUnmanagedBytes = new IntPtr(0);
            int nLength;

            nLength = Convert.ToInt32(fs.Length);
            // Read the contents of the file into the array.
            bytes = br.ReadBytes(nLength);
            // Allocate some unmanaged memory for those bytes.
            pUnmanagedBytes = Marshal.AllocCoTaskMem(nLength);
            // Copy the managed byte array into the unmanaged array.
            Marshal.Copy(bytes, 0, pUnmanagedBytes, nLength);
            // Send the unmanaged bytes to the printer.
            bSuccess = SendBytesToPrinter(szPrinterName, pUnmanagedBytes, nLength);
            // Free the unmanaged memory that you allocated earlier.
            Marshal.FreeCoTaskMem(pUnmanagedBytes);
            return bSuccess;
        }
        public static bool SendStringToPrinter(string szPrinterName, string szString)
        {
            IntPtr pBytes;
            Int32 dwCount;
            // How many characters are in the string?
            dwCount = (szString.Length + 1) * Marshal.SystemMaxDBCSCharSize;//szString.Length;
            // Assume that the printer is expecting ANSI text, and then convert
            // the string to ANSI text.
            pBytes = Marshal.StringToCoTaskMemAnsi(szString);
            // Send the converted ANSI string to the printer.
            SendBytesToPrinter(szPrinterName, pBytes, dwCount);
            Marshal.FreeCoTaskMem(pBytes);
            return true;
        }
    }
Call the above functionality using below code:

public void PrintLabel(string strFirstName, string strLastName, string strNoOfCopies, string PickUpTime, string Cold,string NewMember="")
        {
           
            UpcLabel lbl = new UpcLabel(strFirstName, strLastName, strNoOfCopies, PickUpTime, Cold, NewMember);
            //Printer name
            lbl.Print("ZDesigner QLn220 (ZPL)");
         
        }
        public void PrintBarcode(string pProductName,string pLocation,string pNoOfCopies)
        {
            UpcLabel upcLabel = new UpcLabel();
            //Printer name
            upcLabel.PrintBarcode("ZDesigner QLn220 (ZPL)", pProductName, pLocation, pNoOfCopies);
       
        }






Wednesday 1 October 2014

Access html tag in c# code behind and Set value to html tag from c# code behind

Accessing html tag data or value from c# code behind

Here is my Asp.Net page 


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="TestingHtmlControl.aspx.cs" Inherits="WebApplication2.TestingHtmlControl" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <select name="sltTest" id="slt">
        <option value="test1">test1</option>
        <option value="test2">test2</option>
        <option value="test3">test3</option>
        <option value="test4">test4</option>
    </select>
    </div>
        <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
    </form>
</body>
</html>

Now I am going to access my html select tag selected value from C#.Net code behind  

C#.Net Code

//Submit button click event
 protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string seletedDropDownValueOfHtmlSelect = "";
            seletedDropDownValueOfHtmlSelect = Request.Form["sltTest"].ToString();

//Assigning value to html select tag from C#.Net code behind
            var script = "document.getElementById('slt').value = 'test3';";
            ClientScript.RegisterStartupScript(typeof(string), "textvaluesetter", script, true);

            Response.Write("Name : " + seletedDropDownValueOfHtmlSelect);

        }

Tuesday 30 September 2014

Update or save file using ajax request

How to upload an image using "ajax reguest" in PHP

Note: We need to pass atleast one parameter otherthan file upload control. Then only the ajax file upload will work properly in PHP.

/*================Ajax request from HTML Page=================*/
function SaveImage(vId){
debugger;
   var vCategory=$("#hdnCategory").val();
   //Creating object for form data to collect control's data from HTML page
                   var m_data = new FormData();
   m_data.append('desc', $("#txtDescription").val());
                  //collecting data from file upload control
   m_data.append('file_attach', $('input[name=file_attach]')[0].files[0]);
m_data.append('rowId',vId);
m_data.append('category',vCategory);
            $.ajax({
              url: 'contact_me.php',  //PHP page url
              data: m_data,
              processData: false,
              contentType: false,
              type: 'POST',
              dataType:'json',
              success: function(response){
 alert('Image inserted successfully');
              },
 error:function(err){

  alert('Something went wrong'); }
            });



}

/*======================Server code======================*/

<?php
if($_POST)
{
   $output="";
   $conn=mysql_connect("localhost","root","");
   $db=mysql_select_db("<db name>");
   mysql_query("SET NAMES 'utf8'");
   mysql_query('SET CHARACTER SET utf8');
   if(!isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) != 'xmlhttprequest') {
        $output = json_encode(array( //create JSON data
            'type'=>'error',
            'text' => 'Sorry Request must be Ajax POST'
        ));
        die($output);
    }
    $file_attached = false;
if(isset($_FILES['file_attach']))
{
   //Id to update
   $rowId  = $_POST["rowId"];
$file_tmp_name  = $_FILES['file_attach']['tmp_name'];
   $file_content = file_get_contents($file_tmp_name);
//Here iam saving the image in BLOB datatype in MySql database. so iam converting the image into //base64.
   $encode=base64_encode($file_content);
        $sql_edit="<your query here>";
   $sql=mysql_query( $sql_edit);
echo json_encode("File uploaded successfully");
}else{
echo json_encode("No file to upload");
}
echo json_encode($output);

}
?>

//End of Server code
References: http://www.sanwebe.com/2014/04/ajax-contact-form-attachment-jquery-php

Monday 29 September 2014

How to Create an Animated GIF

   
If you've spent any time on the internet at all, you've probably come in contact with ananimated GIF. It's an image file that allows you to feature animated images -- which makes it seem like the image is moving. Look over to the right of this post to see an example of what a GIF looks like.
Pretty cool huh? The best part about GIFs is that they aren't too hard to make -- if you have access to Photoshop (free trial here) and a few minutes to spare, you will have an animated GIF in no time by following the steps below. I'm going to walk you through how to create a GIF using the GIF I created to help promote Marketing Trivia. Note: I am using the CS6 version of Photoshop, but the steps should be similar in other versions. 1) If you already have images created, gather the images you want in a separate folder. To upload them into Photoshop, click 'File,' then 'Scripts,' then 'Load Files Into Stack.' Then select 'Browse' and choose which files you'd like to use in your GIF, then click 'OK.'

1) If you already have images created, gather the images you want in a separate folder. To upload them into Photoshop, click 'File,' then 'Scripts,' then 'Load Files Into Stack.' Then select 'Browse' and choose which files you'd like to use in your GIF, then click 'OK.'


Saturday 27 September 2014

How to Disable Task Manager while running your WinForm Application in C#

CODE

1.start visual studio  as Administrator and create a winform Application


2. Create Two Events  i. Form_Load Event(for disable Task Manager)
                                    ii. Form_Closed  Event(for Enable task Manager)
both of those events contain a method "ShowTaskManager(boolean)" with boolean as argument
like below picture

3. Now define the Method

Add reference file i.e  "Microsoft.Win32 " dll file because we have to use RegistryKey Class
to  access  regedit tool and It's used to view and change settings in the system registry, which contains information(like a TaskManager) about how your computer runs.

4. Now you can run  application (should open this application as Administrator) and open Task Manager it will show a window  like below.
5. When you Close the application and open Task Manager then it works.




LOGIC BEHIND THIS

1.click start button
    (start)                                                                                                      (regedit tool) 

2.Type regedit then click the tool showing like above image in yellow color
3.It will open a window like below and follow 
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\]

4.     in this below code(define in ShowTaskManager() ) means it will create a System key(in below picture ) in policies folder 


 

5.

 When form_Load event will call "ShowTaskManager(true)" this method will invoke and call       "If  part" of this "ShowTaskManager(true)" method .

and that will create a field insight System key i.e  "DisableTaskMgr" with value "1"  means it will not appear Task Manager. 
i.e 

6.

When form_Closed event will call "ShowTaskManager(false)" this method will invoke and call       "Else  part" of this "ShowTaskManager(false)" method .

and that will  Delete System key So as previous  Task Manager.  will appear.




thank you for view my post if any query then comment  fastly .