public static int SelectProdRoutID(ProdRouteModel routeModel)
{
string procedure = "[dbo].[SelectProdRouteID]";
var p = new DynamicParameters();
p.Add("@OrderNumber", routeModel.OrderNumber);
p.Add("@OperationNumber", routeModel.OperationNumber);
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
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 e)
{
txtWriter.writeInfo($"{routeModel.OrderNumber}, {e}");
throw;
}
}
return(i);
}
public static bool SelectProdRouteQty(ProdRouteModel routeModel)
{
string procedure = "[dbo].[SelectProdOrderRouteQty]";
var p = new DynamicParameters();
p.Add("@id", routeModel.ID);
p.Add("@ConfirmedQty", dbType: DbType.Decimal, direction: ParameterDirection.Output);
p.Add("@ConfirmedScrap", dbType: DbType.Decimal, direction: ParameterDirection.Output);
decimal good;
decimal scrap;
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
var retrun = connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
good = p.Get <decimal>("@ConfirmedQty");
scrap = p.Get <decimal>("@ConfirmedScrap");
}
catch (Exception)
{
throw;
}
}
if (good == routeModel.ConfirmedQty && scrap == routeModel.ConfirmedScrap)
{
return(true);
}
else
{
return(false);
}
}
public static void UpdateProdOrderRoute(ProdRouteModel routeModel)
{
string procedure = "[dbo].[UpdateProdOrderRout]";
var p = new DynamicParameters();
p.Add("@id", routeModel.ID);
p.Add("@OrderNumber", routeModel.OrderNumber);
p.Add("@Sequence", routeModel.Sequence);
p.Add("@OperationNumber", routeModel.OperationNumber);
p.Add("@WorkCenterNumber", routeModel.WorkCenterNumber);
p.Add("@BaseQty", routeModel.BaseQty);
p.Add("@StdValue1", routeModel.StdValue1);
p.Add("@StdValueUOM1", routeModel.StdValueUOM1);
p.Add("@StdValue2", routeModel.StdValue2);
p.Add("@StdValueUOM2", routeModel.StdValueUOM2);
p.Add("@StdValue3", routeModel.StdValue3);
p.Add("@StdValueUOM3", routeModel.StdValueUOM3);
p.Add("@StdValue4", routeModel.StdValue4);
p.Add("@StdValueUOM4", routeModel.StdValueUOM4);
p.Add("@StdValue5", routeModel.StdValue5);
p.Add("@StdValueUOM5", routeModel.StdValueUOM5);
p.Add("@StdValue6", routeModel.StdValue6);
p.Add("@StdValueUOM6", routeModel.StdValueUOM6);
p.Add("@StdValue7", routeModel.StdValue7);
p.Add("@StdValueUOM7", routeModel.StdValueUOM7);
p.Add("@ScheduledStartDate", routeModel.ScheduledStartDate);
p.Add("@ScheduledEndDate", routeModel.ScheduledEndDate);
p.Add("@OperationQty", routeModel.OperationQty);
p.Add("@OperationUOM", routeModel.OperationUOM);
p.Add("@ConfirmedQty", routeModel.ConfirmedQty);
p.Add("@ConfirmedScrap", routeModel.ConfirmedScrap);
p.Add("@ReworkQty", routeModel.ReworkQty);
p.Add("@OperationLongText1", routeModel.OperationLongTextLine1);
p.Add("@OperationLongText2 ", routeModel.OperationLongTextLine2);
p.Add("@OperationLongText3 ", routeModel.OperationLongTextLine3);
p.Add("@OperationLongText4", routeModel.OperationLongTextLine4);
p.Add("@OperationLongText5", routeModel.OperationLongTextLine5);
p.Add("@OperationSystemStatus", routeModel.Operationsystemstatus);
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
}
catch (Exception e)
{
txtWriter.writeInfo($"{routeModel.ConfirmedQty}-{routeModel.ConfirmedScrap}, {e}");
throw;
}
}
}
/// <summary>
/// parse Production Routes data and insert data into SQL
/// </summary>
private static void productionRoutes()
{
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 Routes";
try
{
lines = File.ReadAllLines(filePORoute);
}
catch (Exception e)
{
success = false;
lines = null;
string buildError = "No file found: \n\t" + e.ToString();
//string buildError = string.Format(, "Nofile found:", 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)
{
ProdRouteModel prodRoute;
foreach (string line in lines)
{
prodRoute = new ProdRouteModel();
try
{
prodRoute.ParseProdRoutes(line);
}
catch (Exception ee)
{
errors.Add($"Error parse Prod Header: \n {ee}");
success = false;
}
prodRoute.ID = sqlTransactions.SelectProdRoutID(prodRoute);
if (prodRoute.ID < 1)
{
try
{
sqlTransactions.InsertProdOrderRout(prodRoute);
newLineCount++;
countInserCommand++;
}
catch (Exception ee)
{
txtWriter.Log(ee.ToString(), prodRoute.OrderNumber);
}
}
else
{
try
{
if (sqlTransactions.SelectProdRouteQty(prodRoute))
{
countPassedUpdate++;
}
else
{
sqlTransactions.UpdateProdOrderRoute(prodRoute);
countUpdateCommand++;
}
}
catch (Exception ee)
{
errorCount++;
errors.Add($"Error at Route Quantity: \n {ee}");
}
}
success = true;
}
string strgInfo = $"{transaction} started at {timeStart}:\n \tInserts:{countInserCommand}\n\tUpdates:{countUpdateCommand}\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;
}
