public static void InsertProdOrderMaterial(ProdMaterialModel materialModel)
{
string procedure = "[dbo].[InsertProdOrderMaterial]";
object values = new
{
@OrderNumber = materialModel.OrderNumber,
@ItemNumber = materialModel.ItemNumber,
@ComponentNumber = materialModel.ComponentNumber,
@ComponentDescription = materialModel.ComponentDescription,
@ItemCategory = materialModel.ItemCategory,
@BatchNumber = materialModel.BatchNumber,
@BulkMaterialIndicator = materialModel.BulkMaterialIndicator,
@BackflushIndicator = materialModel.BackFlushIndicator,
@AllocatedOperationNumber = materialModel.Allocated,
@CoProductIndicator = materialModel.CoproductIndicator,
@PhantomIndicator = materialModel.PhantomIndicator,
@QtyWithdrawn = materialModel.QtyWithdrawn,
@RequirementDate = materialModel.RequirementDate,
@ReservationNumber = materialModel.ReservationNumber,
@ReservationItem = materialModel.ReservationItem,
@FixedQtyIndicator = materialModel.FixedQtyIndicator,
@ComponentScrap = materialModel.ComponentScrap,
@OperationScrapPercent = materialModel.OperationScrapPercent,
@RevisionLevel = materialModel.RevisionLevel
};
executeProcedure(procedure, values);
}
public static bool SelectProdMaterialWithdrawn(ProdMaterialModel prodMaterialModel)
{
string procedure = "[dbo].[SelectProdMaterialQty]";
var p = new DynamicParameters();
p.Add("@id", prodMaterialModel.ID);
p.Add("@QtyWithdrawn", dbType: DbType.Decimal, direction: ParameterDirection.Output);
decimal q;
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
var retrun = connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
q = p.Get <decimal>("@QtyWithdrawn");
}
catch (Exception)
{
throw;
}
}
if (q == prodMaterialModel.QtyWithdrawn)
{
return(true);
}
else
{
return(false);
}
}
public static int SelectProdMaterialID(ProdMaterialModel materialModel)
{
string procedure = "[dbo].[SelectProdMaterialID]";
var p = new DynamicParameters();
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
p.Add("@ReservationNumber", materialModel.ReservationNumber);
p.Add("@ReservationItem", materialModel.ReservationItem);
int i;
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
var retrun = connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
i = p.Get <int>("@id");
}
catch (Exception)
{
throw;
}
}
return(i);
}
/// <summary>
/// parse Parse Production Material data and insert data into SQL
/// </summary>
private static void productionMaterial()
{
string timeStart = DateTime.Now.ToString();
bool success = true;
int errorCount = 0;
int newLineCount = 0;
List <string> errors = new List <string>();
string[] lines;
string transaction = "Production Order Material";
try
{
lines = File.ReadAllLines(filePOMaterial);
}
catch (Exception e)
{
success = false;
lines = null;
string buildError = "No file found: \n\t" + e.ToString();
errorCount++;
txtWriter.Log(buildError, transaction, errorCount, newLineCount);
}
//define counters for info tracker to count each pass in the foreach function
//int countUpdateCommand = 0;
int countPassedUpdate = 0;
int countInserCommand = 0;
if (success)
{
ProdMaterialModel materialModel;
foreach (string line in lines)
{
materialModel = new ProdMaterialModel();
try
{
materialModel.ParseProdMaterial(line);
}
catch (Exception ee)
{
errors.Add($"Error parse Prod Material: \n {ee}");
success = false;
}
materialModel.ID = sqlTransactions.SelectProdMaterialID(materialModel);
string poNumber = materialModel.OrderNumber.Substring(0, 2);
if (materialModel.ID < 1 && poNumber != "-D" && poNumber != "00")
{
try
{
sqlTransactions.InsertProdOrderMaterial(materialModel);
newLineCount++;
countInserCommand++;
}
catch (Exception ee)
{
txtWriter.Log(ee.ToString(), materialModel.OrderNumber);
}
}
else
{
countPassedUpdate++;
}
success = true;
}
string strgInfo = $"{transaction} started at {timeStart}: \n \tInserts:{countInserCommand}\n\tUpdates:NA\n\tNo Transaction: {countPassedUpdate}\n\n";
txtWriter.writeInfo(strgInfo);
//define streams and send to txtWriter.Log writer
if (errorCount > 0)
{
txtWriter.Log(errors, transaction, errorCount, newLineCount);
}
}
success = true;
}