public override bool UpdatePrice(iFinanceDetails f)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
string q = string.Empty;
q = @"
UPDATE [dbo].[financialdetails]
SET [purchaseprice] = @Price
WHERE [vehicleid] = @pId";
SqlCommand cmd = new SqlCommand(q, conn)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@Price", f.PurchasePrice);
cmd.Parameters.AddWithValue("@pId", f.VehicleId);
try
{
conn.Open();
int ret = ExecuteNonQuery(cmd);
return(ret == 1);
}
catch (SqlException ex)
{
ErrorHandler.writeSQLExceptionToLogFile(ex);
return(false);
}
}
}
protected virtual iFinanceDetails GetFinancialDetailsFromReader(IDataReader reader, bool singleRecord)
{
if (singleRecord)
{
reader.Read();
}
iFinanceDetails financedetail = new iFinanceDetails(
Helpers.ConvertNullInt64ToZero(reader["id"]),
reader["leaseorfinance"].ToString(),
Helpers.ConvertNullInt64ToZero(reader["vehicleid"]),
reader["paymentcycle"].ToString(),
Helpers.ConvertNullDblToZero(reader["paymentwithtax"]),
Helpers.ConvertNullDblToZero(reader["originaldown"]),
Helpers.ConvertNullDblToZero(reader["securitydeposit"]),
Helpers.ConvertNullToBlank(reader["poendoflease"]),
Helpers.ConvertNullInt64ToZero(reader["kmallowance"]),
Helpers.ConvertNullDblToZero(reader["excesskmcharge"]),
Helpers.ConvertNullIntToZero(reader["leaseterm"]),
Helpers.CheckNullableDate(reader["leaseexpiry"]),
Helpers.ConvertNullDblToZero(reader["buyback"]),
Helpers.ConvertNullDblToZero(reader["balloon"]),
Helpers.ConvertNullDblToZero(reader["purchaseprice"]),
Helpers.ConvertNullToBlank(reader["summary"].ToString()),
Helpers.ConvertNullToBlank(reader["comments"].ToString()),
Helpers.ConvertNullToBlank(reader["status"].ToString()),
Convert.ToBoolean(reader["adstatus"])
);
return(financedetail);
}
public abstract bool UpdatePrice(iFinanceDetails f);
public abstract bool Update(iFinanceDetails c);
public abstract int Insert(iFinanceDetails c);
public override bool Update(iFinanceDetails f)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
string q = string.Empty;
q = @"
UPDATE [dbo].[financialdetails]
SET [leaseorfinance] = @pLeaseOrFinance
,[vehicleid] = @pVehicleid
,[paymentcycle] = @pPayCycle
,[paymentwithtax] = @pPaymentWithTax
,[originaldown] = @pOriginalDown
,[securitydeposit] = @pSecurityDeposit
,[poendoflease] = @pPoendOfLease
,[kmallowance] = @pKmAllowance
,[excesskmcharge] = @pExcessKMcharge
,[leaseterm] = @pLeaseterm
,[leaseexpiry] = @pLeaseExpiry
,[buyback] = @pBuyback
,[balloon] = @pBalloon
,[purchaseprice] = @pPurchsePrice
,[summary] = @pSummary
,[comments] = @pComments
WHERE [id] = @pid";
SqlCommand cmd = new SqlCommand(q, conn)
{
CommandText = q,
CommandType = CommandType.Text
};
cmd.Parameters.Add("@pid", SqlDbType.Int).Value = f.Id;
cmd.Parameters.Add("@pLeaseOrFinance", SqlDbType.VarChar).Value = f.LeaseOrFinance;
cmd.Parameters.Add("@pVehicleid", SqlDbType.Int).Value = f.VehicleId;
cmd.Parameters.Add("@pPayCycle", SqlDbType.VarChar).Value = f.PaymentCycle;
cmd.Parameters.Add("@pPaymentWithTax", SqlDbType.Money).Value = f.PaymentWithTax;
cmd.Parameters.Add("@pOriginalDown", SqlDbType.Money).Value = f.OriginalDown;
cmd.Parameters.Add("@pSecurityDeposit", SqlDbType.Money).Value = f.SecurityDeposit;
cmd.Parameters.Add("@pPoendOfLease", SqlDbType.VarChar).Value = f.PoEndOfLease;
cmd.Parameters.Add("@pKmAllowance", SqlDbType.Int).Value = f.KmAllowance;
cmd.Parameters.Add("@pExcessKMcharge", SqlDbType.Money).Value = f.ExcessKmCharge;
cmd.Parameters.Add("@pLeaseterm", SqlDbType.Int).Value = f.LeaseTerm;
cmd.Parameters.Add("@pLeaseExpiry", SqlDbType.Date).Value = ((object)f.LeaseExpiry) ?? DBNull.Value;
cmd.Parameters.Add("@pBuyback", SqlDbType.Money).Value = f.BuyBack;
cmd.Parameters.Add("@pBalloon", SqlDbType.Money).Value = f.Balloon;
cmd.Parameters.Add("@pPurchsePrice", SqlDbType.Money).Value = f.PurchasePrice;
cmd.Parameters.Add("@pSummary", SqlDbType.Text).Value = f.Summary;
cmd.Parameters.Add("@pComments", SqlDbType.Text).Value = f.Comments;
try
{
conn.Open();
int rc = ExecuteNonQuery(cmd);
return(rc == 1);
}
catch (SqlException ex)
{
ErrorHandler.writeSQLExceptionToLogFile(ex);
return(false);
}
finally
{
conn.Close();
}
}
}
public override int Insert(iFinanceDetails f)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
string q = string.Empty;
q = @"
INSERT INTO [dbo].[financialdetails]
([leaseorfinance]
,[vehicleid]
,[paymentcycle]
,[paymentwithtax]
,[originaldown]
,[securitydeposit]
,[poendoflease]
,[kmallowance]
,[excesskmcharge]
,[leaseterm]
,[leaseexpiry]
,[buyback]
,[balloon]
,[purchaseprice]
,[summary]
,[comments]
,[adstatus])
VALUES
(@pLeaseOrFinance
,@pVehicleid
,@pPayCycle
,@pPaymentWithTax
,@pOriginalDown
,@pSecurityDeposit
,@pPoendOfLease
,@pKmAllowance
,@pExcessKMcharge
,@pLeaseterm
,@pLeaseExpiry
,@pBuyback
,@pBalloon
,@pPurchsePrice
,@pSummary
,@pComments
,@pAdStatus);
SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(q, conn)
{
CommandText = q,
CommandType = CommandType.Text
};
cmd.Parameters.Add("@pLeaseOrFinance", SqlDbType.VarChar).Value = f.LeaseOrFinance;
cmd.Parameters.Add("@pVehicleid", SqlDbType.Int).Value = f.VehicleId;
cmd.Parameters.Add("@pPayCycle", SqlDbType.VarChar).Value = f.PaymentCycle ?? string.Empty;
cmd.Parameters.Add("@pPaymentWithTax", SqlDbType.Money).Value = f.PaymentWithTax;
cmd.Parameters.Add("@pOriginalDown", SqlDbType.Money).Value = f.OriginalDown;
cmd.Parameters.Add("@pSecurityDeposit", SqlDbType.Money).Value = f.SecurityDeposit;
cmd.Parameters.Add("@pPoendOfLease", SqlDbType.VarChar).Value = f.PoEndOfLease ?? string.Empty;
cmd.Parameters.Add("@pKmAllowance", SqlDbType.Int).Value = f.KmAllowance;
cmd.Parameters.Add("@pExcessKMcharge", SqlDbType.Money).Value = f.ExcessKmCharge;
cmd.Parameters.Add("@pLeaseterm", SqlDbType.Int).Value = f.LeaseTerm;
cmd.Parameters.Add("@pLeaseExpiry", SqlDbType.Date).Value = ((object)f.LeaseExpiry) ?? DBNull.Value;
cmd.Parameters.Add("@pBuyback", SqlDbType.Money).Value = f.BuyBack;
cmd.Parameters.Add("@pBalloon", SqlDbType.Money).Value = f.Balloon;
cmd.Parameters.Add("@pPurchsePrice", SqlDbType.Money).Value = f.PurchasePrice;
cmd.Parameters.Add("@pSummary", SqlDbType.Text).Value = f.Summary;
cmd.Parameters.Add("@pComments", SqlDbType.Text).Value = f.Comments;
cmd.Parameters.Add("@pAdStatus", SqlDbType.Bit).Value = f.AdStatus;
try
{
conn.Open();
int rc = Convert.ToInt32(ExecuteScalar(cmd));
return(rc);
}
catch (SqlException ex)
{
ErrorHandler.writeSQLExceptionToLogFile(ex);
return(0);
}
finally
{
conn.Close();
}
}
}