public static int Insert(this SqlFin5 sqlFin5, IProduct product)
{
int count = -1;
try
{
Log.Logger.Information($"Updating product with code {product.Code}");
using (SqlConnection connection = sqlFin5.Connect())
{
SqlCommand command = connection.CreateCommand();
command.CommandText = SQL_INSERT;
command.CommandType = System.Data.CommandType.Text;
command.Parameters.Add("@code", System.Data.SqlDbType.VarChar, 25).Value = product.Code;
command.Parameters.Add("@name", System.Data.SqlDbType.VarChar, 100).Value = product.Name;
command.Parameters.Add("@costPrice", System.Data.SqlDbType.Decimal).Value = product.CostPrice;
command.Parameters.Add("@buyPrice", System.Data.SqlDbType.Decimal).Value = product.BuyPrice;
command.Parameters.Add("@sellPrice", System.Data.SqlDbType.Decimal).Value = product.SellPrice;
command.Parameters.Add("@barcode", System.Data.SqlDbType.VarChar, 25).Value = product.Barcode;
count = command.ExecuteNonQuery();
Log.Logger.Information($"Inserted {count} products with code {product.Code}");
}
}
catch (Exception ex)
{
Log.Logger.Error(ex, "Error during SQL product insert.");
}
return(count);
}
public static int Count(this SqlFin5 sqlFin5, IProduct product)
{
int count = -1;
try
{
Log.Logger.Information($"Counting existing entries with code {product.Code}");
using (SqlConnection connection = sqlFin5.Connect())
{
SqlCommand command = connection.CreateCommand();
command.CommandText = SQL_COUNT;
command.CommandType = System.Data.CommandType.Text;
command.Parameters.Add("@code", System.Data.SqlDbType.VarChar, 25).Value = product.Code;
count = Convert.ToInt32(command.ExecuteScalar());
}
Log.Logger.Information($"Found {count} occurences of product with code {product.Code}");
}
catch (Exception ex)
{
Log.Logger.Error(ex, "Error during SQL product count!");
}
return(count);
}
public static void Insert(this SqlFin5 sqlFin5, IOrder order)
{
bool inserted = false;
try
{
using (SqlConnection connection = sqlFin5.Connect())
{
SqlCommand command;
//check if the document is already stored
command = connection.CreateCommand();
command.CommandText = "SELECT COUNT(*) FROM [dbo].[FJ] WHERE [SHENIM2] = @reference";
command.Parameters.Add("@reference", System.Data.SqlDbType.VarChar, 60).Value = order.Reference;
int count = Convert.ToInt32(command.ExecuteScalar());
if (count > 0) //order not yet inserted to SQL Financa 5
{
Log.Logger.Warning($"There is already {order.Reference} in SQL Financa 5. Insert will not be perfomed.");
}
else
{
//create order header
command = connection.CreateCommand();
command.CommandText = "[dbo].[FJ_INSERT_FJ]";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@PKODUSER", System.Data.SqlDbType.VarChar, 15).Value = SyncFinanca.Properties.Settings.Default.FIN5_FJ_PKODUSER;
command.Parameters.Add("@PDATEDOK", System.Data.SqlDbType.DateTime).Value = order.Date;
command.Parameters.Add("@PKODFKL", System.Data.SqlDbType.VarChar, 15).Value = SyncFinanca.Properties.Settings.Default.FIN5_FJ_PKODFKL;
command.Parameters.Add("@PKMON", System.Data.SqlDbType.VarChar, 15).Value = order.Currency.Name;
command.Parameters.Add("@PKMAG", System.Data.SqlDbType.VarChar, 3).Value = SyncFinanca.Properties.Settings.Default.FIN5_FJ_KMAG;
command.Parameters.Add("@PKURS2", System.Data.SqlDbType.Decimal).Value = order.Currency.Rate;
command.Parameters.Add("@PNRRENDOR", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;
command.ExecuteNonQuery();
int headerId = Convert.ToInt32(command.Parameters["@PNRRENDOR"].Value.ToString());
//create order lines
foreach (IOrderLine line in order.GetLines())
{
command = connection.CreateCommand();
command.CommandText = "[dbo].[FJ_INSERT_FJSCR]";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@PNrRendor", System.Data.SqlDbType.Int).Value = headerId;
command.Parameters.Add("@PTipKLL", System.Data.SqlDbType.VarChar, 1).Value = SyncFinanca.Properties.Settings.Default.FIN5_FJSCR_TIPKLL;
command.Parameters.Add("@KODART", System.Data.SqlDbType.VarChar, 25).Value = line.Product.Code;
command.Parameters.Add("@CMIM", System.Data.SqlDbType.Decimal).Value = line.Price;
command.Parameters.Add("@SASI", System.Data.SqlDbType.Decimal).Value = line.Quantity;
command.Parameters.Add("@VPATVSH", System.Data.SqlDbType.Decimal).Value = line.Value;
command.Parameters.Add("@VTVSH", System.Data.SqlDbType.Decimal).Value = line.TaxValue;
command.Parameters.Add("@VTOT", System.Data.SqlDbType.Decimal).Value = line.TotalValue;
command.ExecuteNonQuery();
}
//create stockout document
command = connection.CreateCommand();
command.CommandText = "[dbo].[FJ_INSERT_FD]";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@PNrRendor", System.Data.SqlDbType.Int).Value = headerId;
command.ExecuteNonQuery();
//update some values in order header
command = connection.CreateCommand();
command.CommandText = @"UPDATE [FJ] SET [SHENIM2] = @reference WHERE [NRRENDOR] = @headerId";
command.CommandType = System.Data.CommandType.Text;
command.Parameters.Add("@headerId", System.Data.SqlDbType.Int).Value = headerId;
command.Parameters.Add("@reference", System.Data.SqlDbType.VarChar, 60).Value = order.Reference;
command.ExecuteNonQuery();
Log.Logger.Information($"{order.Reference} inserted in SQL Financa 5.");
}
}
}
catch (Exception ex)
{
Log.Logger.Error(ex, "Error during order creation in SQL Financa 5.");
}
}
