public static bool SelectProdHeaderChangeDate(ProdHeaderModel headerModel)
{
string procedure = "[dbo].[SelectProdOrderHeadLastChangeDate]";
var p = new DynamicParameters();
p.Add("@id", headerModel.ID);
p.Add("@LastChangeDate", dbType: DbType.Date, direction: ParameterDirection.Output);
DateTime d;
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
var retrun = connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
d = p.Get <DateTime>("@LastChangeDate");
// d = DateTime.Parse(i.ToString());
}
catch (Exception)
{
throw;
}
}
if (d.Date == headerModel.LastChangeDate.Date)
{
return(true);
}
else
{
return(false);
}
}
public static int SelectProdHeaderID(ProdHeaderModel headerModel)
{
string procedure = "[dbo].[SelectProdHeaderID]";
var p = new DynamicParameters();
p.Add("@OrderNumber", headerModel.OrderNumber);
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($"{headerModel.OrderNumber}, {e}");
throw;
}
}
return(i);
}
public static void UpdateProdOrderHeader(ProdHeaderModel headerModel)
{
string procedure = "[dbo].[UpdateProdOrderHeader]";
object values = new
{
@id = headerModel.ID,
@LineType = headerModel.LineType,
@Plant = headerModel.Plant,
@OrderNumber = headerModel.OrderNumber,
@MaterialNumber = headerModel.MaterialNumber,
@MaterialDescription = headerModel.MaterialDescription,
@RevisionLevel = headerModel.RevisionLevel,
@MRPcontroller = headerModel.MRPcontroller,
@ProductionScheduler = headerModel.ProductionScheduler,
@PlannerGroup = headerModel.PlannerGroup,
@OrderQuatity = headerModel.OrderQuatity,
@BaseUnitMeasure = headerModel.BaseUnitMeasure,
@OrderUnitMeasure = headerModel.OrderUnitMeasure,
@ScrapQuantity = headerModel.ScrapQuantity,
@ScrapPercent = headerModel.ScrapPercent,
@ExpectedYieldVariance = headerModel.ExpectedYieldVariance,
@BasicStartDate = headerModel.BasicStartDate,
@BasicfinishDate = headerModel.BasicfinishDate,
@ScheduledStartDate = headerModel.OriginalSchedStartDate,
@ScheduledFinishDate = headerModel.OriginalSchedFinishDate,
@ActualReleaseDate = headerModel.ActualReleaseDate,
@SchdulingMarginKey = headerModel.SchdulingMarginKey,
@FloatBeforeProduction = headerModel.FloatBeforeProduction,
@FloatAfterProduction = headerModel.FloatAfterProduction,
@ReleasedPeriod = headerModel.ReleasedPeriod,
@BatchNumber = headerModel.BatchNumber,
@GoodsReceipt = headerModel.GoodsReceipt,
@StorageLocation = headerModel.StorageLocation,
@salesOrderNumber = headerModel.SalesOrderNumber,
@SalesOrderItem = headerModel.SalesOrderItem,
@CreatedBy = headerModel.CreatedBy,
@CreationDate = headerModel.CreationDate,
@GRprocessingTime = headerModel.GRprocessingTime,
@UnderDeliveryTolerence = headerModel.UnderDeliveryTolerence,
@OverDeliveryTolerence = headerModel.OverDeliveryTolerence,
@UnlimitedDelivery = headerModel.UnlimitedDelivery,
@ProductionSchedulingProfile = headerModel.ProductionSchedulingProfile,
@PutInStockQuantity = headerModel.PutInStockQuantity,
@Priority = headerModel.Priority,
@SchedulingType = headerModel.SchedulingType,
@BasicFinishDate2 = headerModel.BasicFinishDate2,
@BasicStartDate3 = headerModel.BasicStartDate3,
@InterfaceAcknowledgeDate = headerModel.InterfaceAcknowledgeDate,
@OriginalSchedFinishDate = headerModel.OriginalSchedFinishDate,
@OriginalSchedStartDate = headerModel.OriginalSchedStartDate,
@OriginalOrderQuantity = headerModel.OriginalOrderQuantity,
@OriginalOrderScrapQuantity = headerModel.OriginalOrderScrapQuantity,
@OrderStatus = headerModel.OrderStatus,
@LoadQuantity = headerModel.LoadQuantity,
@QuantityConfirmed = headerModel.QuantityConfirmed,
@ProductionQuantity = headerModel.ProductionQuantity,
@OrderText = headerModel.OrderText,
@LastChangeDate = headerModel.LastChangeDate,
@ProfitCenter = headerModel.ProfitCenter,
@MaterialProfitCenter = headerModel.MaterialProfitCenter
};
executeProcedure(procedure, values);
}
/// <summary>
/// parse Production Header Data and insert data into SQL
/// </summary>
private static void productionHeader()
{
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 Header";
try
{
lines = File.ReadAllLines(filePOHeader);
}
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)
{
ProdHeaderModel headerModel;
foreach (string line in lines)
{
headerModel = new ProdHeaderModel();
try
{
headerModel.ParseProdHeader(line);
}
catch (Exception ee)
{
errors.Add($"Error parse Prod Header: \n {ee}");
success = false;
}
headerModel.ID = sqlTransactions.SelectProdHeaderID(headerModel);
if (headerModel.ID < 1 && success)
{
try
{
sqlTransactions.InsertProdOrderHeader(headerModel);
newLineCount++;
countInserCommand++;
}
catch (Exception ee)
{
txtWriter.Log(ee.ToString(), headerModel.OrderNumber);
}
}
else
{
try
{
if (sqlTransactions.SelectProdHeaderChangeDate(headerModel))
{
countPassedUpdate++;
}
else
{
sqlTransactions.UpdateProdOrderHeader(headerModel);
countUpdateCommand++;
}
}
catch (Exception ee)
{
errorCount++;
errors.Add($"Error at ChangeDate or Update prod headre: \n {ee}");
}
}
success = true;
}
string strgInfo = $"{transaction} had: \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;
}
