unit productsDB;

interface

uses SysUtils, System.Configuration, System.Drawing, System.Web.UI.WebControls, System.Data, System.Data.SqlClient;


type TproductsDB = class
	private
		function GetConnectionString:string;
	public
		function GetNew : SqlDataReader;
    function GetTrazeno : SqlDataReader;
    function GetProduct(pid : integer; IsRandom : boolean = false) : SqlDataReader;
    function GetRandomProduct : SqlDataReader;
    function GetProductsInCategory(CatName : string) : DataTable;
    function GetProductsInPayType(PayTypeName : string) : DataTable;
    function GetNumberOfProducts : integer;
		function GetProductsFromSearch(ss : string; b:boolean=false) : DataTable;
		function GetProductsByDeveloper(pid : integer) : DataTable;
		function GetProductReviews(pid : integer) : DataTable;
		function GetAverageReview(pid : integer) : SqlDataReader;
  	function AddProduct(DeveloperID:integer; DeveloperName, ProductName, AppType, AppPayType, Description, SearchWords, ImageName : String) : integer;
  	function UpdateProduct(ProductID:integer; DeveloperName, ProductName, AppType, AppPayType, Description, SearchWords : String) : integer;
		function DeleteProduct(pid : integer) : integer;

  	function AddProductReview(ProductID, UserRating : integer; UserName, UserEMail, UserComment : String) : integer;
 
end;


implementation

function TproductsDB.GetConnectionString: string;
begin
	Result:=ConfigurationSettings.AppSettings.Get('ConnectionString')
end;

function TproductsDB.UpdateProduct(ProductID:integer; DeveloperName, ProductName, AppType, AppPayType, Description, SearchWords : String) : integer;
var
	myConnection:SqlConnection;
	myCommand : SqlCommand;
	parameterProductID, 
	parameterProductName, 
	parameterAppType, 
	parameterAppPayType, 
	parameterDescription,
	parameterKeyWords, 
	parameterSearchWords : SqlParameter;
begin
  // Create Instance of Connection and Command Object
	myConnection := SqlConnection.Create(GetConnectionString);
 	myCommand := SqlCommand.Create('ProductUpdate', myConnection);
	myCommand.CommandType := CommandType.StoredProcedure;

	  // Add Parameters to SPROC
  parameterProductID := SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  parameterProductID.Value := TObject(ProductID);
  myCommand.Parameters.Add(parameterProductID);


  parameterProductName := SqlParameter.Create('@ProductName', SqlDbType.NVarChar, 40);
  parameterProductName.Value := ProductName;
  myCommand.Parameters.Add(parameterProductName);

  parameterAppType := SqlParameter.Create('@AppType', SqlDbType.NVarChar, 30);
  parameterAppType.Value := AppType;
  myCommand.Parameters.Add(parameterAppType);

  parameterAppPayType := SqlParameter.Create('@AppPayType', SqlDbType.NVarChar, 20);
  parameterAppPayType.Value := AppPayType;
  myCommand.Parameters.Add(parameterAppPayType);

  parameterDescription := SqlParameter.Create('@Description', SqlDbType.NVarChar, 1500);
  parameterDescription.Value := Description;
  myCommand.Parameters.Add(parameterDescription);

  parameterKeyWords := SqlParameter.Create('@KeyWords', SqlDbType.NVarChar, 3000);
  parameterKeyWords.Value := DeveloperName + ' ' + Description + ' ' + AppType + ' ' + AppPayType + ' ' + SearchWords;;
  myCommand.Parameters.Add(parameterKeyWords);

  parameterSearchWords := SqlParameter.Create('@SearchWords', SqlDbType.NVarChar, 300);
  parameterSearchWords.Value := SearchWords; 
  myCommand.Parameters.Add(parameterSearchWords);

  try
    myConnection.Open;
    myCommand.ExecuteNonQuery;
    myConnection.Close;

	    Result := Integer(parameterProductID.Value);
  except
    Result := 0;
  end

end;//updateproduct

function TproductsDB.AddProduct(DeveloperID:integer; DeveloperName, ProductName, AppType, AppPayType, Description, SearchWords, ImageName : String) : integer;
var
	myConnection:SqlConnection;
	myCommand : SqlCommand;
	parameterDeveloperID, 
	parameterProductID, 
	parameterProductName, 
	parameterAppType, 
	parameterAppPayType, 
	parameterDescription,
	parameterKeyWords, 
	parameterSearchWords, 
	parameterImageName,
	parameterDateEntered : SqlParameter;
	Y,M,D:Word;
begin
  // Create Instance of Connection and Command Object
	myConnection := SqlConnection.Create(GetConnectionString);
 	myCommand := SqlCommand.Create('ProductAdd', myConnection);
	myCommand.CommandType := CommandType.StoredProcedure;

	  // Add Parameters to SPROC
  parameterProductID := SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  parameterProductID.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterProductID);

  parameterDeveloperID := SqlParameter.Create('@DeveloperID', SqlDbType.int, 4);
  parameterDeveloperID.Value := TObject(DeveloperID);
  myCommand.Parameters.Add(parameterDeveloperID);

  parameterProductName := SqlParameter.Create('@ProductName', SqlDbType.NVarChar, 40);
  parameterProductName.Value := ProductName;
  myCommand.Parameters.Add(parameterProductName);

  parameterAppType := SqlParameter.Create('@AppType', SqlDbType.NVarChar, 30);
  parameterAppType.Value := AppType;
  myCommand.Parameters.Add(parameterAppType);

  parameterAppPayType := SqlParameter.Create('@AppPayType', SqlDbType.NVarChar, 20);
  parameterAppPayType.Value := AppPayType;
  myCommand.Parameters.Add(parameterAppPayType);

  parameterDescription := SqlParameter.Create('@Description', SqlDbType.NVarChar, 1500);
  parameterDescription.Value := Description;
  myCommand.Parameters.Add(parameterDescription);

  parameterKeyWords := SqlParameter.Create('@KeyWords', SqlDbType.NVarChar, 3000);
  parameterKeyWords.Value := DeveloperName + ' ' + Description + ' ' + AppType + ' ' + AppPayType + ' ' + SearchWords;;
  myCommand.Parameters.Add(parameterKeyWords);

  parameterSearchWords := SqlParameter.Create('@SearchWords', SqlDbType.NVarChar, 300);
  parameterSearchWords.Value := SearchWords; 
  myCommand.Parameters.Add(parameterSearchWords);

  parameterImageName := SqlParameter.Create('@ImageName', SqlDbType.NVarChar, 100);
  parameterImageName.Value := ImageName;
  myCommand.Parameters.Add(parameterImageName);

  parameterDateEntered := SqlParameter.Create('@DateEntered', SqlDbType.smalldatetime);
	DecodeDate(Date, Y, M, D);
//  parameterDateEntered.Value := IntToStr(M) + '/'+ IntToStr(D) + '/' + IntToStr(Y);
  parameterDateEntered.Value := DateTime.FromOADate(Date);
  myCommand.Parameters.Add(parameterDateEntered);

  try
    myConnection.Open;
    myCommand.ExecuteNonQuery;
    myConnection.Close;

    Result := Integer(parameterProductID.Value);
  except
    Result := 0;
  end

end;//addproduct

function TproductsDB.AddProductReview(ProductID, UserRating : integer; UserName, UserEMail, UserComment : String) : integer;
var
	myConnection : SqlConnection;
	myCommand : SqlCommand;
	parameterReviewID, 
	parameterProductID, 
	parameterUserName, 
	parameterUserEMail, 
	parameterUserComment, 
	parameterDateEntered, 
	parameterUserRating : SqlParameter;
begin
  // Create Instance of Connection and Command Object
	myConnection := SqlConnection.Create(GetConnectionString);
 	myCommand := SqlCommand.Create('ReviewAdd', myConnection);
	myCommand.CommandType := CommandType.StoredProcedure;

	  // Add Parameters to SPROC
  parameterReviewID := SqlParameter.Create('@ReviewID', SqlDbType.Int, 4);
  parameterReviewID.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterReviewID);

  parameterProductID := SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  parameterProductID.Value := TObject(ProductID);
  myCommand.Parameters.Add(parameterProductID);

  parameterUserRating := SqlParameter.Create('@UserRating', SqlDbType.Int, 4);
  parameterUserRating.Value := TObject(UserRating);
  myCommand.Parameters.Add(parameterUserRating);

  parameterUserName := SqlParameter.Create('@UserName', SqlDbType.NVarChar, 50);
  parameterUserName.Value := UserName;
  myCommand.Parameters.Add(parameterUserName);

  parameterUserEMail := SqlParameter.Create('@UserEMail', SqlDbType.NVarChar, 50);
  parameterUserEMail.Value := UserEMail;
  myCommand.Parameters.Add(parameterUserEMail);

  parameterUserComment := SqlParameter.Create('@UserComment', SqlDbType.NVarChar, 999);
  parameterUserComment.Value := UserComment;
  myCommand.Parameters.Add(parameterUserComment);

  parameterDateEntered := SqlParameter.Create('@DateEntered', SqlDbType.smalldatetime);
  parameterDateEntered.Value := DateTime.FromOADate(Date);
  myCommand.Parameters.Add(parameterDateEntered);

  try
    myConnection.Open;
    myCommand.ExecuteNonQuery;
    myConnection.Close;

    Result := Integer(parameterReviewID.Value);
  except
    Result := 0;
  end;

end;//addproductreview


function TproductsDB.GetProductsInCategory(CatName : string) : DataTable;
var
 conn:SqlConnection;
 atParameter: SqlParameter;
 Adapter : SqlDataAdapter;
begin
  conn:=SqlConnection.Create(GetConnectionString);

  atParameter:=SqlParameter.Create('@AppType', SqlDbType.NVarChar);

  Adapter := SqlDataAdapter.Create('GetProductsInCategory', conn);//'SELECT Products.ProductID, Products.ProductName, Products.Description, Developers.Name FROM Products, Developers WHERE Developers.DeveloperID = Products.DeveloperID AND Products.AppType = @AppType ORDER BY Products.DateEntered DESC', conn);
  Adapter.SelectCommand.Parameters.Add(atParameter).Value := CatName;
	Adapter.SelectCommand.CommandType:=CommandType.StoredProcedure;

  Result := DataTable.Create;
  Adapter.Fill(Result);
end;

function TproductsDB.GetProductsInPayType(PayTypeName : string) : DataTable;
var
 conn:SqlConnection;
 atParameter: SqlParameter;
 Adapter : SqlDataAdapter;
begin
  conn:=SqlConnection.Create(GetConnectionString);

  atParameter:=SqlParameter.Create('@AppPayType', SqlDbType.NVarChar);

  Adapter := SqlDataAdapter.Create('GetProductsInPayType', conn);
  Adapter.SelectCommand.Parameters.Add(atParameter).Value := PayTypeName;
	Adapter.SelectCommand.CommandType:=CommandType.StoredProcedure;

  Result := DataTable.Create;
  Adapter.Fill(Result);
end;

function TproductsDB.GetProductsFromSearch(ss : string; b:boolean = false) : DataTable;
const MinWordLength = 1;
type TStringArray = array of string;
var
 conn:SqlConnection;
 Adapter : SqlDataAdapter;

 target:string;
 sl : TStringArray;
 i : integer;
 
     procedure Split (aString, aDelimeter : string; var rslt : TStringArray);
     var  cnt, ix, lth : integer;
     begin
		 		cnt:=0;
     		SetLength(rslt, cnt);
        lth := Length (aDelimeter);
        while (Length (aString) > MinWordLength) do
        begin
        	ix := pos (aDelimeter, aString);
          if ix <= 0 then ix := Length (aString) + 1;
					cnt:=cnt+1;
          SetLength (rslt, cnt);
          rslt[cnt-1]:= Copy (aString, 1, ix - 1);
          Delete (aString, 1, ix + lth - 1);
        end;
     end;
begin

		Split(ss,' ', sl);
		for i:=0 to High(sl) do
		begin
			if b then 
				target := target + ' AND Products.KeyWords LIKE ''%' + sl[i] + '%'''
			else
				target := target + ' OR  Products.KeyWords LIKE ''%' + sl[i] + '%'''
		end;
		target := Copy(target,Length(' AND '),MaxInt);
{	
	sl := TStringList.Create;
	try
		Split(' ', Trim(ss),sl);
		for i:=0 to sl.Items.Count-1 do
		begin
			if b then 
				target := target + ' AND KeyWords LIKE ''%' + sl[i] + '%'''
			else
				target := target + ' OR KeyWords LIKE ''%' + sl[i] + '%'''
		end;
	finally
		sl.Free;
	end;
}	
  conn:=SqlConnection.Create(GetConnectionString);
	
	//sqls:='SELECT Products.ProductID, Products.ProductName, Products.Description, Developers.Name FROM Products, Developers WHERE Developers.DeveloperID = Products.DeveloperID AND ( ' + target + ' )';
//  atParameter:=SqlParameter.Create('@AppType', SqlDbType.NVarChar);
  Adapter := SqlDataAdapter.Create('SELECT Products.ProductID, Products.ProductName, Products.AppType, Products.AppPayType, Products.Description, Developers.Name FROM Products, Developers WHERE Developers.DeveloperID = Products.DeveloperID AND ( ' + target + ' )', conn);
//  Adapter.SelectCommand.Parameters.Add(atParameter).Value := CatName;

	//Adapter.SelectCommand.CommandType:=CommandType.StoredProcedure;

  Result := DataTable.Create;
 	Adapter.Fill(Result);

  adapter.Free;
  conn.Dispose;
end;


function TproductsDB.GetNumberOfProducts : integer;
var
 conn:SqlConnection;
 command:SqlCommand;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  command:=SqlCommand.Create('GetNumberOfProducts', conn);
	command.CommandType:=CommandType.StoredProcedure;
  conn.Open;
	try
  	Result := Integer(command.ExecuteScalar);
	finally
		conn.Free;
	end;
end;

function TproductsDB.GetNew : SqlDataReader;
var
 conn:SqlConnection;
 command:SqlCommand;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  command:=SqlCommand.Create('GetNewestProducts', conn);
	command.CommandType:=CommandType.StoredProcedure;
  conn.Open;
  result := command.ExecuteReader(CommandBehavior.CloseConnection)
end;


function TproductsDB.GetTrazeno : SqlDataReader;
var
 conn:SqlConnection;
 command:SqlCommand;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  command:=SqlCommand.Create('GetMostWantedProducts', conn);
	command.CommandType:=CommandType.StoredProcedure;
  conn.Open;
  result := command.ExecuteReader(CommandBehavior.CloseConnection)
end;

function TproductsDB.DeleteProduct(pid : integer) : integer;
var
 conn:SqlConnection;
 command:SqlCommand;
 atParameter: SqlParameter;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  command:=SqlCommand.Create('DeleteProductByID', conn);
  atParameter:=SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  command.Parameters.Add(atParameter).Value := TObject(pid);
	command.CommandType:=CommandType.StoredProcedure;
  conn.Open;
  result := command.ExecuteNonQuery;
	conn.Close;
	command.Free;
	atParameter.Free;
end;


function TproductsDB.GetProduct(pid : integer; IsRandom:boolean = false) : SqlDataReader;
var
 conn:SqlConnection;
 command:SqlCommand;
 NRACommand:SqlCommand;
 atParameter: SqlParameter;
 nraParameter: SqlParameter;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  command:=SqlCommand.Create('GetProductByID', conn);
  atParameter:=SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  command.Parameters.Add(atParameter).Value := TObject(pid);
	command.CommandType:=CommandType.StoredProcedure;

	
  NRAcommand:=SqlCommand.Create('IncreaseProductNRA', conn);
  nraParameter:=SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  NRAcommand.Parameters.Add(nraParameter).Value := TObject(pid);
	NRAcommand.CommandType:=CommandType.StoredProcedure;

  conn.Open;
	if NOT IsRandom then NRAcommand.ExecuteNonQuery();
  result := command.ExecuteReader(CommandBehavior.CloseConnection);
	atParameter.Free;
end;

function TproductsDB.GetRandomProduct : SqlDataReader;
var
 conn : SqlConnection;
 command : SqlCommand;
 MINcommand : SqlCommand;
 rid : integer;
 
    function RandomInRange(intLow, intHi : integer): integer;
    var itmp, iRS: integer;
    begin
        Randomize();
        if intHi < intLow then
        begin
            itmp   := intLow;
            intLow := intHi;
            intHi  := itmp;
        end;
        iRS := intHi - intLow;
        Result := Trunc(iRS * Random + intLow);
    end;

begin
  conn:=SqlConnection.Create(GetConnectionString);
  command:=SqlCommand.Create('GetMaxProductID', conn);
	command.CommandType:=CommandType.StoredProcedure;
  MINcommand:=SqlCommand.Create('GetMinProductID', conn);
	MINcommand.CommandType:=CommandType.StoredProcedure;
  conn.Open;
	try
		try
  		rid:=RandomInRange(Integer(MINcommand.ExecuteScalar), 1 + Integer(command.ExecuteScalar));
		except
			rid:=0;
		end;
	finally
		conn.Free;
	end;
	
  Result:=GetProduct(rid, true);
end;

function TproductsDB.GetProductsByDeveloper(pid : integer) : DataTable;
var
 conn:SqlConnection;
 atParameter: SqlParameter;
 Adapter : SqlDataAdapter;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  Adapter := SqlDataAdapter.Create('GetProductsByDeveloper', conn);
  atParameter:=SqlParameter.Create('@DeveloperID', SqlDbType.Int, 4);
  Adapter.SelectCommand.Parameters.Add(atParameter).Value := TObject(pid);
	Adapter.SelectCommand.CommandType:=CommandType.StoredProcedure;
  Result := DataTable.Create;
  Adapter.Fill(Result);
	atParameter.Free;
end;

function TproductsDB.GetProductReviews(pid : integer) : DataTable;
var
 conn:SqlConnection;
 atParameter: SqlParameter;
 Adapter : SqlDataAdapter;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  Adapter := SqlDataAdapter.Create('GetProductReviews', conn);
  atParameter:=SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  Adapter.SelectCommand.Parameters.Add(atParameter).Value := TObject(pid);
	Adapter.SelectCommand.CommandType:=CommandType.StoredProcedure;
  Result := DataTable.Create;
  Adapter.Fill(Result);
	atParameter.Free;
end;

function TproductsDB.GetAverageReview(pid : integer) : SqlDataReader;
var
 conn:SqlConnection;
 command:SqlCommand;
 atParameter: SqlParameter;
begin
  conn:=SqlConnection.Create(GetConnectionString);
  command:=SqlCommand.Create('RatingAverage', conn);
  atParameter:=SqlParameter.Create('@ProductID', SqlDbType.Int, 4);
  command.Parameters.Add(atParameter).Value := TObject(pid);
	command.CommandType:=CommandType.StoredProcedure;

  conn.Open;
  result := command.ExecuteReader(CommandBehavior.CloseConnection);
	atParameter.Free;
end;//GetAverageReview

end.
