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; }