unit developersDB;
// opcenito za developere, login, dodavanje, details
interface

uses System.Web.UI, StrUtils, System.Configuration, System.Data, System.Data.SqlClient;

type DeveloperDetails = class
	public
	  Name : String;
		Address : string;
		WebSite : string;
		Tel : string;
		Fax : string;
    Email : String;
		Contact : string;
    Password : String;
end;

type TdevelopersDB = Class
		function GetConnectionString:string;
		function Login(email , password : String) : integer;
    function GetDeveloperDetails(developerID : integer) : DeveloperDetails;
		function AddDeveloper(Name, Address, WebSite, Email, Tel, Fax, Contact, password : String) : integer;
		function UpdateDeveloper(DeveloperID : integer; Name, Address, WebSite, Email, Tel, Fax, Contact, password : String) : boolean;
end;


implementation

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

function TdevelopersDB.Login(email , password : String) : integer;
var
	myConnection:SqlConnection;
	myCommand : SqlCommand;
	parameterDeveloperID, 
	parameterEmail,
	parameterPassword : SqlParameter;
begin
  // Create Instance of Connection and Command Object
	myConnection := SqlConnection.Create(GetConnectionString);
  myCommand := SqlCommand.Create('DeveloperLogin', myConnection);
	myCommand.CommandType := CommandType.StoredProcedure;

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

  parameterEmail := SqlParameter.Create('@Email', SqlDbType.NVarChar, 50);
  parameterEmail.Value := email;
  myCommand.Parameters.Add(parameterEmail);

  parameterPassword := SqlParameter.Create('@Password', SqlDbType.NVarChar, 50);
  parameterPassword.Value := password;
  myCommand.Parameters.Add(parameterPassword);

  // Open the connection and execute the Command
  myConnection.Open;
  myCommand.ExecuteNonQuery;
  myConnection.Close;

  Result := Integer(parameterDeveloperID.Value);
end;


function TdevelopersDB.AddDeveloper(Name, Address, WebSite, Email, Tel, Fax, Contact, password : String) : integer;
var
	myConnection:SqlConnection;
	myCommand : SqlCommand;
	parameterDeveloperID, 
	parameterName, 
	parameterAddress, 
	parameterWebSite, 
	parameterEmail,
	parameterTel, 
	parameterFax, 
	parameterContact,
	parameterPassword : SqlParameter;
begin
  // Create Instance of Connection and Command Object
	myConnection := SqlConnection.Create(GetConnectionString);
 	myCommand := SqlCommand.Create('DeveloperAdd', myConnection);
	myCommand.CommandType := CommandType.StoredProcedure;

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

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

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

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

  parameterEmail := SqlParameter.Create('@Email', SqlDbType.NVarChar, 50);
  parameterEmail.Value := email;
  myCommand.Parameters.Add(parameterEmail);

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

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

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

  parameterPassword := SqlParameter.Create('@Password', SqlDbType.NVarChar, 50);
  parameterPassword.Value := password;
  myCommand.Parameters.Add(parameterPassword);


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

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

end;//function

function TdevelopersDB.UpdateDeveloper(DeveloperID: integer; Name, Address, WebSite, Email, Tel, Fax, Contact, password : String) : boolean;
var
	myConnection:SqlConnection;
	myCommand : SqlCommand;
	parameterDeveloperID, 
	parameterName, 
	parameterAddress, 
	parameterWebSite, 
	parameterEmail,
	parameterTel, 
	parameterFax, 
	parameterContact,
	parameterPassword : SqlParameter;
	ret : integer;
begin
  // Create Instance of Connection and Command Object
	myConnection := SqlConnection.Create(GetConnectionString);
 	myCommand := SqlCommand.Create('DeveloperUpdate', myConnection);
	myCommand.CommandType := CommandType.StoredProcedure;

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

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

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

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

  parameterEmail := SqlParameter.Create('@Email', SqlDbType.NVarChar, 50);
  parameterEmail.Value := email;
  myCommand.Parameters.Add(parameterEmail);

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

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

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

  parameterPassword := SqlParameter.Create('@Password', SqlDbType.NVarChar, 50);
  parameterPassword.Value := password;
  myCommand.Parameters.Add(parameterPassword);


  try
    myConnection.Open;
    ret := myCommand.ExecuteNonQuery;
    myConnection.Close;

		if ret = 1 then 
    	Result := true
		else
    	Result := false;
  except
    Result := false;
  end

end;//function

function TdevelopersDB.GetDeveloperDetails(developerID : integer) : DeveloperDetails;
var
	myConnection:SqlConnection;
	myCommand : SqlCommand;
	parameterDeveloperID, 
	parameterName, 
	parameterAddress,
	parameterWebSite,
	parameterTel,
	parameterFax,
	parameterContact,
	parameterEmail,
	parameterPassword : SqlParameter;
	myDeveloperDetails : DeveloperDetails;
begin
  // Create Instance of Connection and Command Object
	myConnection := SqlConnection.Create(GetConnectionString);
  myCommand := SqlCommand.Create('DeveloperDetail', myConnection);

  // Mark the Command as a SPROC
  myCommand.CommandType := CommandType.StoredProcedure;

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

  parameterName := SqlParameter.Create('@Name', SqlDbType.NVarChar, 50);
  parameterName.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterName);

  parameterAddress := SqlParameter.Create('@Address', SqlDbType.NVarChar, 50);
  parameterAddress.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterAddress);

  parameterWebSite := SqlParameter.Create('@WebSite', SqlDbType.NVarChar, 50);
  parameterWebSite.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterWebSite);

  parameterEmail := SqlParameter.Create('@Email', SqlDbType.NVarChar, 50);
  parameterEmail.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterEmail);

  parameterTel := SqlParameter.Create('@Tel', SqlDbType.NVarChar, 50);
  parameterTel.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterTel);

  parameterFax := SqlParameter.Create('@Fax', SqlDbType.NVarChar, 50);
  parameterFax.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterFax);

  parameterContact := SqlParameter.Create('@Contact', SqlDbType.NVarChar, 50);
  parameterContact.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterContact);

  parameterPassword := SqlParameter.Create('@Password', SqlDbType.NVarChar, 50);
  parameterPassword.Direction := ParameterDirection.Output;
  myCommand.Parameters.Add(parameterPassword);

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

  //Create CustomerDetails Struct
  myDeveloperDetails := DeveloperDetails.Create;

  // Populate Struct using Output Params from SPROC
  myDeveloperDetails.Name := String(parameterName.Value);
  myDeveloperDetails.Address := String(parameterAddress.Value);
  myDeveloperDetails.WebSite := String(parameterWebSite.Value);
  myDeveloperDetails.Tel := String(parameterTel.Value);
  myDeveloperDetails.Fax := String(parameterFax.Value);
  myDeveloperDetails.Contact := String(parameterContact.Value);
  myDeveloperDetails.Password := String(parameterPassword.Value);
  myDeveloperDetails.Email := String(parameterEmail.Value);

  Result := DeveloperDetails.Create;
  Result := myDeveloperDetails;
end;


end.
