internal void RecordInvoiceInSMS(SpeedySpots.API.Interfaces.Invoice oInvoice)
{
string sql = @"INSERT INTO QBInvoice VALUES (
@QBInvoiceRefID,
@QBCustomerRefID,
@invoiceNumber,
@amount,
@fileName,
@dueDateTime,
@importedDateTime,
@IAJobID)";
try
{
using (SqlCommand sqlCommand = new SqlCommand(sql))
{
sqlCommand.Parameters.Add(new SqlParameter("QBInvoiceRefID", oInvoice.InvoiceID));
sqlCommand.Parameters.Add(new SqlParameter("QBCustomerRefID", oInvoice.CustomerID));
sqlCommand.Parameters.Add(new SqlParameter("invoiceNumber", oInvoice.InvoiceNumber));
sqlCommand.Parameters.Add(new SqlParameter("amount", oInvoice.Amount));
sqlCommand.Parameters.Add(new SqlParameter("fileName", oInvoice.Filename));
sqlCommand.Parameters.Add(new SqlParameter("dueDateTime", oInvoice.DueDateTime));
sqlCommand.Parameters.Add(new SqlParameter("importedDateTime", DateTime.Now));
sqlCommand.Parameters.Add(new SqlParameter("IAJobID", oInvoice.IAJobID));
using (SqlConnection sqlConn = new SqlConnection(_connectString))
{
sqlConn.Open();
if (sqlConn.State == ConnectionState.Open)
{
sqlCommand.Connection = sqlConn;
sqlCommand.ExecuteNonQuery();
}
}
}
}
catch (SqlException ex)
{
_logger.ErrorFormat("Error RecordInvoiceInSMS: Invoice {0} - {1} - {2}", oInvoice.InvoiceID.ToString(), ex.Message, sql);
}
}
internal void UpdateInvoiceInSMS(SpeedySpots.API.Interfaces.Invoice oInvoice)
{
string sql = @"UPDATE QBInvoice SET
QBInvoiceRefID = @QBInvoiceRefID,
QBCustomerRefID = @QBCustomerRefID,
InvoiceNumber = @invoiceNumber,
Amount = @amount,
[FileName] = @fileName,
DueDateTime = @dueDateTime,
ImportedDateTime = @importedDateTime,
IAJobID = @iAJobID
WHERE QBInvoiceRefID = @QBInvoiceRefID";
try
{
using (SqlCommand sqlCommand = new SqlCommand(sql))
{
sqlCommand.Parameters.Add(new SqlParameter("QBInvoiceRefID", oInvoice.InvoiceID));
sqlCommand.Parameters.Add(new SqlParameter("QBCustomerRefID", oInvoice.CustomerID));
sqlCommand.Parameters.Add(new SqlParameter("invoiceNumber", oInvoice.InvoiceNumber));
sqlCommand.Parameters.Add(new SqlParameter("amount", oInvoice.Amount));
sqlCommand.Parameters.Add(new SqlParameter("fileName", oInvoice.Filename));
sqlCommand.Parameters.Add(new SqlParameter("dueDateTime", oInvoice.DueDateTime));
sqlCommand.Parameters.Add(new SqlParameter("importedDateTime", DateTime.Now));
sqlCommand.Parameters.Add(new SqlParameter("iAJobID", oInvoice.IAJobID));
using (SqlConnection sqlConn = new SqlConnection(_connectString))
{
sqlConn.Open();
if (sqlConn.State == ConnectionState.Open)
{
sqlCommand.Connection = sqlConn;
sqlCommand.ExecuteNonQuery();
}
}
}
}
catch (SqlException ex)
{
_logger.ErrorFormat("Error UpdateInvoiceInSMS: Invoice {0} - {1} - {2}", oInvoice.InvoiceID.ToString(), ex.Message, sql);
}
}
internal bool DoesInvoiceExsit(SpeedySpots.API.Interfaces.Invoice oInvoice, out string oldFileName)
{
bool invoiceExsist = false;
oldFileName = string.Empty;
string sql = "SELECT QBInvoiceID,[Filename] FROM QBInvoice WHERE QBInvoiceRefID = @invoiceID";
try
{
using (SqlCommand sqlCommand = new SqlCommand(sql))
{
sqlCommand.Parameters.Add(new SqlParameter("invoiceID", oInvoice.InvoiceID));
using (SqlConnection sqlConn = new SqlConnection(_connectString))
{
sqlConn.Open();
if (sqlConn.State == ConnectionState.Open)
{
sqlCommand.Connection = sqlConn;
using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
{
if (sqlReader.HasRows)
{
invoiceExsist = true;
sqlReader.Read();
oldFileName = sqlReader["Filename"].ToString();
}
}
}
}
}
}
catch (SqlException ex)
{
_logger.ErrorFormat("Error DoesInvoiceExsit: {0} - {1}", ex.Message, sql);
}
return invoiceExsist;
}