set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[sp_OrderId]
AS
BEGIN
DECLARE @Date VARCHAR(50)
DECLARE @Time VARCHAR(50)
DECLARE @Num INT
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @Name VARCHAR(50)
DECLARE @TimeFormat VARCHAR(50)
DECLARE @OrderId VARCHAR(50)
SELECT @Date = CONVERT(VARCHAR(50), GETDATE(),112)
SELECT @Time = Convert(Varchar(50),GETDATE(), 114)
SET @Num = 0
SET @Pos = 1
SET @TimeFormat = ''
WHILE(@Pos <= LEN(@Time))
BEGIN
SELECT @NextPos = CHARINDEX(N':', @Time, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@Time) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@Time, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
IF @TimeFormat = ''
SET @TimeFormat = @Name
ELSE
SET @TimeFormat = @TimeFormat + @Name
SET @Num = @Num + 1
END
SET @OrderId = @Date + @TimeFormat
SELECT @OrderId = RTRIM(LTRIM(SUBSTRING(@OrderId, 1, len(@OrderId) - 3 )))
SELECT CONVERT(BIGINT, @OrderId) AS OrderId
END
Hey Friends,
here is the code to generate resize image at the time of uploading. Hope It will help.
public string ResizeBookImage(FileUpload ftpControl, int intWidth, int intHeight, string UploadDirectory, string strTempDirectory)
{
string strFileName = "";
if (ftpControl.HasFile)
{
imgResizedName = Guid.NewGuid();
string ext = Path.GetExtension(ftpControl.FileName);
strFileName = imgResizedName.ToString();
string strFName = ftpControl.FileName;
ftpControl.SaveAs(strTempDirectory + ftpControl.FileName);
string strFCName = strTempDirectory + strFName;
System.Drawing.Image image = System.Drawing.Image.FromFile(strFCName);
int srcWidth = image.Width;
int srcHeight = image.Height;
//int thumbHeight = (srcHeight / srcWidth) * thumbWidth;
Bitmap bmp = new Bitmap(intWidth, intHeight);
string dest = UploadDirectory + strFileName + ".jpg";
System.Drawing.Graphics gr = System.Drawing.Graphics.FromImage(bmp);
gr.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
gr.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality;
gr.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High;
System.Drawing.Rectangle rectDestination = new System.Drawing.Rectangle(0, 0, intWidth, intHeight);
gr.DrawImage(image, rectDestination, 0, 0, srcWidth, srcHeight, GraphicsUnit.Pixel);
bmp.Save(dest, System.Drawing.Imaging.ImageFormat.Jpeg);
bmp.Dispose();
image.Dispose();
File.Delete(strTempDirectory + strFName);
}
return strFileName;
}
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchString]
(
@Keywords VARCHAR(MAX),
@SearchType VARCHAR(100)
)
AS
DECLARE @SearchText VARCHAR(MAX)
DECLARE @Num INT
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @TableCount INT
DECLARE @CountSep INT
DECLARE @Count INT
DECLARE @BookId UNIQUEIDENTIFIER
DECLARE @AuthorName VARCHAR(500)
DECLARE @ISBN VARCHAR(50)
DECLARE @BookName VARCHAR(500)
DECLARE @PubYear VARCHAR(20)
BEGIN
IF @SearchType='General'
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='temp')
SET @TableCount = 1
ELSE
BEGIN
CREATE TABLE temp (BookID UNIQUEIDENTIFIER,AuthorName VARCHAR(500),
ISBN VARCHAR(50),BookName VARCHAR(500),PubYear VARCHAR(20))
SET @TableCount = 0
END
SELECT @CountSep = CHARINDEX(N':', @Keywords,1)
IF @CountSep = 0
BEGIN
SELECT BookID FROM (
SELECT DISTINCT Books.BookID AS BookID, Books.PublisherId, Books.IsDeleted FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE BookPrice.ISBN like'%' + @Keywords + '%' OR
Books.BookName like'%' + @Keywords + '%'
OR Books.YearofPublication like'%' + @Keywords + '%'
OR Author.AuthorName like'%' + @Keywords + '%' AND Publisher.Publisher='XXX' ) AS tmpBook
INNER JOIN Publisher ON tmpBook.PublisherId=Publisher.PublisherId WHERE Publisher.Publisher='XXX'
AND tmpBook.IsDeleted = 0
END
ELSE
BEGIN
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@Keywords))
BEGIN
SELECT @NextPos = CHARINDEX(N':', @Keywords, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
BEGIN
SELECT @NextPos = LEN(@Keywords) + 1
END
SELECT @SearchText = RTRIM(LTRIM(SUBSTRING(@Keywords, @Pos, @NextPos - @Pos)))
IF @Pos = 1
BEGIN
DECLARE Outer_Cursor CURSOR FOR
SELECT BookID,AuthorName,ISBN,BookName,PubYear FROM (
SELECT DISTINCT Books.BookID AS BookID, Books.PublisherId,
Books.IsDeleted, Author.AuthorName AS AuthorName,
BookPrice.ISBN AS ISBN,
Books.BookName AS BookName, Books.YearofPublication AS PubYear
FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE BookPrice.ISBN like'%' + @SearchText + '%' OR
Books.BookName like'%' + @SearchText + '%'
OR Books.YearofPublication like'%' + @SearchText + '%'
OR Author.AuthorName like'%' + @SearchText + '%' AND Publisher.Publisher='XXX' ) AS tmpBook
INNER JOIN Publisher ON tmpBook.PublisherId=Publisher.PublisherId WHERE Publisher.Publisher='XXX'
AND tmpBook.IsDeleted = 0
OPEN Outer_Cursor
FETCH NEXT FROM outer_cursor INTO @BookId, @AuthorName, @ISBN, @BookName, @PubYear
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO temp (BookId, AuthorName, ISBN, BookName, PubYear)
VALUES (@BookId, @AuthorName, @ISBN, @BookName, @PubYear)
FETCH NEXT FROM outer_cursor INTO @BookId, @AuthorName, @ISBN, @BookName, @PubYear
END
CLOSE outer_cursor
DEALLOCATE outer_cursor
END
ELSE
BEGIN
DELETE FROM temp WHERE BookID NOT IN (
SELECT BookID FROM temp
WHERE ISBN like'%' + @SearchText + '%' OR
BookName like'%' + @SearchText + '%'
OR PubYear like'%' + @SearchText + '%'
OR AuthorName like'%' + @SearchText + '%'
)
END
SET @Pos = @NextPos+1
END
SELECT BookID FROM temp AS tblRecordset
DROP TABLE temp
END
END
ELSE IF @SearchType='ISBN'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND BookPrice.ISBN like'%' + @Keywords + '%' AND BookPrice.IsDeleted=0
ELSE IF @SearchType='Title'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Books.BookName like'%' + @Keywords + '%' AND Books.IsDeleted=0
ELSE IF @SearchType='Author'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId = Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Author.AuthorName like'%' + @Keywords + '%' AND BookAuthor.IsDeleted=0
ELSE IF @SearchType='PublicationYear'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Books.YearofPublication like'%' + @Keywords + '%' AND Books.IsDeleted=0
ELSE IF @SearchType='Subject'
SELECT DISTINCT BooksCategory.BookID AS BookID FROM
BooksCategory INNER JOIN Category ON BooksCategory.CategoryId=Category.CategoryId
INNER JOIN Books ON BooksCategory.BookId = Books.BookId INNER JOIN Publisher
ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Category.Category like'%' + @Keywords + '%' AND BooksCategory.IsDeleted=0
END
