
public static class AutoShipStreamListener
{
    private const int ReadChunkSize = 8192;
    private const int AcceptPollMs = 250;

    // Bounded on purpose. When the writer falls behind, the connection threads
    // block on Add, which stops them draining their sockets, which pushes back on
    // the sender through TCP's own flow control. An unbounded queue would instead
    // buffer the whole feed in memory until the process died.
    //
    // The unit is now a whole shipment rather than a record, so this is a much
    // larger amount of data than the same number used to hold. 32 shipments of a
    // few hundred cartons is still a few megabytes.
    private const int WriteQueueCapacity = 32;

    private static System.Collections.Concurrent.BlockingCollection<SendCartonShipment> _writeQueue;

    public static void Run()
    {
        System.Net.Sockets.TcpListener listener = null;
        System.Threading.Thread writerThread = null;

        try
        {
            _writeQueue = new System.Collections.Concurrent.BlockingCollection<SendCartonShipment>(WriteQueueCapacity);

            writerThread = new System.Threading.Thread(WriteLoop);
            writerThread.IsBackground = true;
            writerThread.Name = "AutoShipStream.Writer";
            writerThread.Start();
			
            listener = new System.Net.Sockets.TcpListener(System.Net.IPAddress.Any, AutoShipStreamConfig.Port);
            listener.Start();

            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: listening on port " + AutoShipStreamConfig.Port +
                " for SENDCARTON records of " + AutoShipStreamConfig.RecordLength + " characters.");

            while (!AutoShipStreamControl.ShouldStop())
            {
                // Pending() rather than a blocking Accept, so shutdown is noticed
                // within a poll interval instead of hanging until a client happens
                // to connect.
                if (!listener.Pending())
                {
                    System.Threading.Thread.Sleep(AcceptPollMs);
                    continue;
                }

                System.Net.Sockets.TcpClient client = listener.AcceptTcpClient();

                // Hand off and go straight back to accepting. Reading is the slow
                // part and must never hold up the next connection.
                System.Threading.Thread clientThread = new System.Threading.Thread(delegate () { HandleClient(client); });
                clientThread.IsBackground = true;
                clientThread.Name = "AutoShipStream.Client";
                clientThread.Start();
            }
        }
        catch (Exception ex)
        {
            ShipLink.Helpers.LogHelper.LogMessage("AutoShipStream: listener terminated:" + Environment.NewLine + ex.ToString());
        }
        finally
        {
            try
            {
                if (listener != null)
                {
                    listener.Stop();
                }
            }
            catch (Exception) { }

            // Drain rather than drop: shipments already framed off the wire have
            // been taken from the sender, which will not send them again.
            if (_writeQueue != null)
            {
                _writeQueue.CompleteAdding();
            }

            if (writerThread != null)
            {
                writerThread.Join(TimeSpan.FromSeconds(10));
            }

            ShipLink.Helpers.LogHelper.LogMessage("AutoShipStream: listener stopped.");
        }
    }

    private static void HandleClient(System.Net.Sockets.TcpClient client)
    {
        string endpoint = "unknown";

        try
        {
            if (client.Client != null && client.Client.RemoteEndPoint != null)
            {
                endpoint = client.Client.RemoteEndPoint.ToString();
            }
			
            using (client)
            using (System.Net.Sockets.NetworkStream stream = client.GetStream())
            {
               // ReadRecords(stream, AutoShipDb.Truncate(endpoint, 64));
               ReadRecordsFromFile(AutoShipDb.Truncate(endpoint, 64));
            }
        }
        catch (System.IO.IOException ex)
        {
            ShipLink.Helpers.LogHelper.LogMessage("AutoShipStream: connection from " + endpoint + " dropped: " + ex.Message);
        }
        catch (Exception ex)
        {
            ShipLink.Helpers.LogHelper.LogMessage("AutoShipStream: error handling " + endpoint + ":" + Environment.NewLine + ex.ToString());
        }
    }

	
	public static void ReadRecordsFromFile(string endpoint)
{
   
    byte[] buffer = new byte[ReadChunkSize];

    // The feed must be single-byte - fixed character offsets only mean
    // anything when one character is one byte - so ASCII is the correct
    // decoder here.
    System.Text.Encoding encoding = System.Text.Encoding.ASCII;

    SendCartonFramer framer = new SendCartonFramer(AutoShipStreamConfig.RecordLength);
    SendCartonShipment open = NewShipment(endpoint);

    int sequenceNumber = 0;

    // Wakes the loop up periodically even when the sender has gone quiet, so
    // a transaction left open mid-stream is stored rather than held in
    // memory forever. A TCP read that times out leaves the socket usable, so
    // this costs nothing when the sender is simply slow.
    //stream.ReadTimeout = IdleCheckMs();

    //while (!AutoShipStreamControl.ShouldStop())
    //{
        var stream = System.IO.File.OpenRead("e:\\SendCarton_100Orders_Feed.txt");
        int read=0;

        try
        {
            read = stream.Read(buffer, 0, buffer.Length);
        }
        catch (System.IO.IOException ex)
        {
            if (!IsReadTimeout(ex))
            {
                throw;
            }

            open = FlushIfIdle(open, endpoint);
           // continue;
        }

        if (read == 0)
        {
            //break;
        }

        List<string> framed = framer.Add(encoding.GetString(buffer, 0, read));

        for (int i = 0; i < framed.Count; i++)
        {
            sequenceNumber++;
            SendCartonRecord record = SendCartonParser.Parse(framed[i], sequenceNumber);

            if (!record.ParsedOk)
            {
                // Logged, and still added: it becomes a parcel with no order
                // and its error stored. Dropping it here would lose it
                // permanently - the sender will not send it again - and
                // leave nothing to diagnose.
                ShipLink.Helpers.LogHelper.LogMessage(
                    "AutoShipStream: record " + sequenceNumber + " from " + endpoint +
                    " did not parse: " + record.ParseError);
            }

            open.Add(record);

            if (record.IsLastTransaction)
            {
                open = Close(open, ShipmentCloseReason.LastTran, endpoint);
            }
            else if (open.Records.Count >= AutoShipStreamConfig.MaxRecordsPerShipment)
            {
                ShipLink.Helpers.LogHelper.LogMessage(
                    "AutoShipStream: " + endpoint + " sent " + open.Records.Count +
                    " records with no LASTTRAN = 'Y' (limit " +
                    AutoShipStreamConfig.MaxRecordsPerShipment +
                    "). Storing them as one shipment; it is probably half a transaction.");

                open = Close(open, ShipmentCloseReason.SizeCap, endpoint);
            }
        }
    //}

    // The connection is going away. Anything still held is stored, not
    // dropped, for the same reason.
    string remainder = framer.TakeRemainder();
    if (remainder.Length > 0)
    {
        sequenceNumber++;

        ShipLink.Helpers.LogHelper.LogMessage(
            "AutoShipStream: " + endpoint + " closed with " + remainder.Length +
            " characters that do not make a whole " + AutoShipStreamConfig.RecordLength +
            "-character record. Storing them as an unparsed parcel.");

        open.Add(SendCartonParser.Parse(remainder, sequenceNumber));
    }

    if (!open.IsEmpty)
    {
        Close(open, ShipmentCloseReason.ConnectionClosed, endpoint);
    }
}
    /// <summary>
    /// The read / frame / parse / group loop for one connection.
    ///
    /// A read returns whatever bytes have arrived - it has no relationship to
    /// record boundaries. The framer carries the tail of a split record over to
    /// the next read, which is the only way a record split across two packets
    /// survives, and it is also what absorbs the sender's line wrapping.
    /// </summary>
    private static void ReadRecords(System.Net.Sockets.NetworkStream stream, string endpoint)
    {
        byte[] buffer = new byte[ReadChunkSize];

        // The feed must be single-byte - fixed character offsets only mean
        // anything when one character is one byte - so ASCII is the correct
        // decoder here.
        System.Text.Encoding encoding = System.Text.Encoding.ASCII;

        SendCartonFramer framer = new SendCartonFramer(AutoShipStreamConfig.RecordLength);
        SendCartonShipment open = NewShipment(endpoint);

        int sequenceNumber = 0;

        // Wakes the loop up periodically even when the sender has gone quiet, so
        // a transaction left open mid-stream is stored rather than held in
        // memory forever. A TCP read that times out leaves the socket usable, so
        // this costs nothing when the sender is simply slow.
        stream.ReadTimeout = IdleCheckMs();

        while (!AutoShipStreamControl.ShouldStop())
        {
            int read;

            try
            {
                read = stream.Read(buffer, 0, buffer.Length);
            }
            catch (System.IO.IOException ex)
            {
                if (!IsReadTimeout(ex))
                {
                    throw;
                }

                open = FlushIfIdle(open, endpoint);
                continue;
            }

            if (read == 0)
            {
                break;
            }

            List<string> framed = framer.Add(encoding.GetString(buffer, 0, read));

            for (int i = 0; i < framed.Count; i++)
            {
                sequenceNumber++;
                SendCartonRecord record = SendCartonParser.Parse(framed[i], sequenceNumber);

                if (!record.ParsedOk)
                {
                    // Logged, and still added: it becomes a parcel with no order
                    // and its error stored. Dropping it here would lose it
                    // permanently - the sender will not send it again - and
                    // leave nothing to diagnose.
                    ShipLink.Helpers.LogHelper.LogMessage(
                        "AutoShipStream: record " + sequenceNumber + " from " + endpoint +
                        " did not parse: " + record.ParseError);
                }

                open.Add(record);

                if (record.IsLastTransaction)
                {
                    open = Close(open, ShipmentCloseReason.LastTran, endpoint);
                }
                else if (open.Records.Count >= AutoShipStreamConfig.MaxRecordsPerShipment)
                {
                    ShipLink.Helpers.LogHelper.LogMessage(
                        "AutoShipStream: " + endpoint + " sent " + open.Records.Count +
                        " records with no LASTTRAN = 'Y' (limit " +
                        AutoShipStreamConfig.MaxRecordsPerShipment +
                        "). Storing them as one shipment; it is probably half a transaction.");

                    open = Close(open, ShipmentCloseReason.SizeCap, endpoint);
                }
            }
        }

        // The connection is going away. Anything still held is stored, not
        // dropped, for the same reason.
        string remainder = framer.TakeRemainder();
        if (remainder.Length > 0)
        {
            sequenceNumber++;

            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: " + endpoint + " closed with " + remainder.Length +
                " characters that do not make a whole " + AutoShipStreamConfig.RecordLength +
                "-character record. Storing them as an unparsed parcel.");

            open.Add(SendCartonParser.Parse(remainder, sequenceNumber));
        }

        if (!open.IsEmpty)
        {
            Close(open, ShipmentCloseReason.ConnectionClosed, endpoint);
        }
    }

    private static SendCartonShipment NewShipment(string endpoint)
    {
        SendCartonShipment shipment = new SendCartonShipment();
        shipment.SourceEndpoint = endpoint;

        if (Config.License != null && Config.License.CustomerPC != null)
        {
            shipment.PCId = Config.License.CustomerPC.Id;
        }

        return shipment;
    }

    /// <summary>
    /// Seals the open transaction, queues it, and returns a fresh empty one.
    /// </summary>
    private static SendCartonShipment Close(SendCartonShipment shipment, string reason, string endpoint)
    {
        if (shipment.IsEmpty)
        {
            return shipment;
        }

        shipment.CloseReason = reason;
        shipment.Build();

        if (shipment.ErrorMessage != null)
        {
            ShipLink.Helpers.LogHelper.LogMessage("AutoShipStream: " + shipment.Describe() + " - " + shipment.ErrorMessage);
        }

        Enqueue(shipment);
        return NewShipment(endpoint);
    }

    private static SendCartonShipment FlushIfIdle(SendCartonShipment shipment, string endpoint)
    {
        if (shipment.IsEmpty)
        {
            return shipment;
        }

        double idleSeconds = DateTime.UtcNow.Subtract(shipment.LastRecordAt).TotalSeconds;
        if (idleSeconds < AutoShipStreamConfig.GroupIdleFlushSeconds)
        {
            return shipment;
        }

        ShipLink.Helpers.LogHelper.LogMessage(
            "AutoShipStream: " + endpoint + " has sent nothing for " + (int)idleSeconds +
            " seconds with " + shipment.Records.Count +
            " record(s) held and no LASTTRAN = 'Y'. Storing them as one shipment.");

        return Close(shipment, ShipmentCloseReason.IdleTimeout, endpoint);
    }

    /// <summary>
    /// How often to wake up and reconsider an idle transaction. A quarter of the
    /// flush window, so the flush happens within 25% of when it was asked for
    /// without waking the thread constantly.
    /// </summary>
    private static int IdleCheckMs()
    {
        int ms = (AutoShipStreamConfig.GroupIdleFlushSeconds * 1000) / 4;
        return ms < 1000 ? 1000 : ms;
    }

    private static bool IsReadTimeout(System.IO.IOException ex)
    {
        System.Net.Sockets.SocketException socketException = ex.InnerException as System.Net.Sockets.SocketException;
        return socketException != null && socketException.SocketErrorCode == System.Net.Sockets.SocketError.TimedOut;
    }

    private static void Enqueue(SendCartonShipment shipment)
    {
        System.Collections.Concurrent.BlockingCollection<SendCartonShipment> queue = _writeQueue;

        if (queue == null || queue.IsAddingCompleted)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: writer is shut down; " + shipment.Describe() + " could not be queued.");
            return;
        }

        try
        {
            // Blocks when the queue is full - see WriteQueueCapacity.
            queue.Add(shipment);
        }
        catch (InvalidOperationException)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: writer closed while queuing; " + shipment.Describe() + " was not stored.");
        }
    }

    /// <summary>
    /// THE SINGLE WRITER. Every insert goes through this one loop, on this one
    /// thread, and that is not an optimisation.
    ///
    /// ShipmentData.Id is the print order, and IDENTITY is allocated at INSERT
    /// rather than at COMMIT - so two connections inserting concurrently can
    /// commit out of Id order, and a batch claimed in that window splits them and
    /// prints them backwards. One writer removes the window.
    ///
    /// It is also what makes ShipmentParcel.Id the print order WITHIN a shipment:
    /// the parcels are inserted one after another inside a single transaction, in
    /// arrival order, with nothing else allocating identities in between.
    /// </summary>
    private static void WriteLoop()
    {
        System.Collections.Concurrent.BlockingCollection<SendCartonShipment> queue = _writeQueue;
        if (queue == null)
        {
            return;
        }

        try
        {
            foreach (SendCartonShipment shipment in queue.GetConsumingEnumerable())
            {
                try
                {
                    long id = InsertShipment(shipment);

                    ShipLink.Helpers.LogHelper.LogMessage(
                        "AutoShipStream: stored ShipmentData " + id + " - " + shipment.Describe() + ".");
                }
                catch (Exception ex)
                {
                    // Log the order numbers: this shipment is gone, and this line
                    // is the only remaining evidence that it ever arrived.
                    ShipLink.Helpers.LogHelper.LogMessage(
                        "AutoShipStream: FAILED TO STORE " + shipment.Describe() + " [" +
                        OrderNumbersOf(shipment) + "]:" + Environment.NewLine + ex.ToString());
                }
            }
        }
        catch (Exception ex)
        {
            ShipLink.Helpers.LogHelper.LogMessage("AutoShipStream: writer loop terminated:" + Environment.NewLine + ex.ToString());
        }
    }

    private static string OrderNumbersOf(SendCartonShipment shipment)
    {
        List<string> numbers = new List<string>();

        for (int i = 0; i < shipment.Orders.Count; i++)
        {
            numbers.Add(shipment.Orders[i].OrderNumber);
        }

        return numbers.Count == 0 ? "no parseable order number" : string.Join(", ", numbers.ToArray());
    }

    // -------------------------------------------------------------------------
    // Storage.
    //
    // One shipment, one transaction, four tables, in dependency order:
    //
    //   ShipmentData -> ShipmentOrder -> ShipmentParcel -> ShipmentOrderDetail
    //
    // Details come last because a detail points at both the order it belongs to
    // and the carton it was reported on, and the carton has to exist first.
    //
    // All or nothing. A shipment half-stored would ship half an order, and there
    // is nothing downstream that could notice.
    // -------------------------------------------------------------------------

    private const string InsertShipmentSql =
        @"INSERT INTO dbo.ShipmentData
              (OrderNumber, OrderCount, ParcelCount, RecordCount, CloseReason,
               ShipStatus, PrintStatus, IsTimeOut, PCId, CreatedDate,
               AttemptCount, ErrorMessage, SourceEndpoint)
          VALUES
              (@OrderNumber, @OrderCount, @ParcelCount, @RecordCount, @CloseReason,
               @ShipStatus, @PrintStatus, 0, @PCId, GETUTCDATE(),
               0, @ErrorMessage, @SourceEndpoint);
          SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

    private const string InsertOrderSql =
        @"INSERT INTO dbo.ShipmentOrder
              (ShipmentData_Id, SeqNo, OrderNumber, OrderExt, AltOrder,
               StationId, OperatorId,
               ShipToId, ShipToName, ShipToAddr1, ShipToAddr2, ShipToAddr3,
               ShipToAttn, ShipToCity, ShipToState, ShipToZip, ShipToCountry, Phone,
               CustName, CustAddr1, CustAddr2, CustAttn, CustCity, CustState,
               CustZip, CustCountry, CustomerNr, Shipper,
               Carrier, Mode, ChargeCode, InvoiceCode, FreightType, DestZone,
               ThirdPartyBillAcct, CustPo, CustPoNo,
               Custom1, Custom2, Custom3, Custom4,
               TotOrdVal, TotOrdValRaw, DeclaredCartonTotal, SawLastCarton, ErrorMessage)
          VALUES
              (@ShipmentData_Id, @SeqNo, @OrderNumber, @OrderExt, @AltOrder,
               @StationId, @OperatorId,
               @ShipToId, @ShipToName, @ShipToAddr1, @ShipToAddr2, @ShipToAddr3,
               @ShipToAttn, @ShipToCity, @ShipToState, @ShipToZip, @ShipToCountry, @Phone,
               @CustName, @CustAddr1, @CustAddr2, @CustAttn, @CustCity, @CustState,
               @CustZip, @CustCountry, @CustomerNr, @Shipper,
               @Carrier, @Mode, @ChargeCode, @InvoiceCode, @FreightType, @DestZone,
               @ThirdPartyBillAcct, @CustPo, @CustPoNo,
               @Custom1, @Custom2, @Custom3, @Custom4,
               @TotOrdVal, @TotOrdValRaw, @DeclaredCartonTotal, @SawLastCarton, @ErrorMessage);
          SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

    private const string InsertParcelSql =
        @"INSERT INTO dbo.ShipmentParcel
              (ShipmentData_Id, ShipmentOrder_Id, SeqNo,
               CartonId, CartonSeq, CartonTotal,
               Weight, WeightRaw, Length, Width, Height, DimUnit, Cost, TotVal,
               IsLastCarton, IsLastTran, PrintStatus, Data, ErrorMessage)
          VALUES
              (@ShipmentData_Id, @ShipmentOrder_Id, @SeqNo,
               @CartonId, @CartonSeq, @CartonTotal,
               @Weight, @WeightRaw, @Length, @Width, @Height, @DimUnit, @Cost, @TotVal,
               @IsLastCarton, @IsLastTran, @PrintStatus, @Data, @ErrorMessage);
          SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

    private const string InsertOrderDetailSql =
        @"INSERT INTO dbo.ShipmentOrderDetail
              (ShipmentData_Id, ShipmentOrder_Id, ShipmentParcel_Id, SeqNo,
               PartNumber, PartDesc, Upc, UnitTotal, UnitTotalRaw, TotVal, TotValRaw)
          VALUES
              (@ShipmentData_Id, @ShipmentOrder_Id, @ShipmentParcel_Id, @SeqNo,
               @PartNumber, @PartDesc, @Upc, @UnitTotal, @UnitTotalRaw, @TotVal, @TotValRaw);";

    private static long InsertShipment(SendCartonShipment shipment)
    {
        using (SqlConnection connection = AutoShipDb.Open())
        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            try
            {
                long shipmentId = InsertHeader(connection, transaction, shipment);

                // ShipmentOrder rows, and a map from the grouping key back to the
                // identity the database just gave them, so the parcels and
                // details below can point at the right one.
                Dictionary<string, long> orderIds = new Dictionary<string, long>(StringComparer.Ordinal);

                for (int i = 0; i < shipment.Orders.Count; i++)
                {
                    SendCartonOrderGroup group = shipment.Orders[i];
                    long orderId = InsertOrder(connection, transaction, shipmentId, group);
                    orderIds.Add(group.OrderKey, orderId);
                }

                // Parcels in arrival order across the whole shipment - this loop
                // is what fixes the print order, so it must walk Records rather
                // than walking each order's cartons in turn.
                Dictionary<string, int> detailSeqByOrder = new Dictionary<string, int>(StringComparer.Ordinal);

                for (int i = 0; i < shipment.Records.Count; i++)
                {
                    SendCartonRecord record = shipment.Records[i];

                    object orderId = null;
                    if (record.ParsedOk)
                    {
                        long resolved;
                        if (orderIds.TryGetValue(record.OrderKey, out resolved))
                        {
                            orderId = resolved;
                        }
                    }

                    long parcelId = InsertParcel(connection, transaction, shipmentId, orderId, i + 1, record);

                    // A record that did not parse has no trustworthy part number
                    // and no order to hang a line off, so it contributes a parcel
                    // and nothing else.
                    if (orderId == null)
                    {
                        continue;
                    }

                    int detailSeq;
                    detailSeqByOrder.TryGetValue(record.OrderKey, out detailSeq);
                    detailSeq++;
                    detailSeqByOrder[record.OrderKey] = detailSeq;

                    InsertOrderDetail(connection, transaction, shipmentId, (long)orderId, parcelId, detailSeq, record);
                }

                transaction.Commit();
                return shipmentId;
            }
            catch (Exception)
            {
                try
                {
                    transaction.Rollback();
                }
                catch (Exception) { }

                throw;
            }
        }
    }

    private static long InsertHeader(SqlConnection connection, SqlTransaction transaction, SendCartonShipment shipment)
    {
        using (SqlCommand command = AutoShipDb.Command(connection, InsertShipmentSql, transaction))
        {
            command.Parameters.Add(AutoShipDb.P("@OrderNumber", Fit(shipment.PrimaryOrderNumber, 15)));
            command.Parameters.Add(AutoShipDb.P("@OrderCount", shipment.Orders.Count));
            command.Parameters.Add(AutoShipDb.P("@ParcelCount", shipment.Records.Count));
            command.Parameters.Add(AutoShipDb.P("@RecordCount", shipment.Records.Count));
            command.Parameters.Add(AutoShipDb.P("@CloseReason", shipment.CloseReason));
            command.Parameters.Add(AutoShipDb.P("@ShipStatus", ShipStatus.Ready));
            command.Parameters.Add(AutoShipDb.P("@PrintStatus", PrintStatus.Pending));
            command.Parameters.Add(AutoShipDb.P("@PCId", shipment.PCId));
            command.Parameters.Add(AutoShipDb.P("@ErrorMessage", AutoShipDb.Truncate(shipment.ErrorMessage, 4000)));
            command.Parameters.Add(AutoShipDb.P("@SourceEndpoint", shipment.SourceEndpoint));

            return Convert.ToInt64(command.ExecuteScalar());
        }
    }

    private static long InsertOrder(
        SqlConnection connection, SqlTransaction transaction, long shipmentId, SendCartonOrderGroup group)
    {
        SendCartonRecord h = group.Header;

        using (SqlCommand command = AutoShipDb.Command(connection, InsertOrderSql, transaction))
        {
            command.Parameters.Add(AutoShipDb.P("@ShipmentData_Id", shipmentId));
            command.Parameters.Add(AutoShipDb.P("@SeqNo", group.SeqNo));

            command.Parameters.Add(AutoShipDb.P("@OrderNumber", Fit(h.Order, 15)));
            command.Parameters.Add(AutoShipDb.P("@OrderExt", Fit(h.OrderExt, 10)));
            command.Parameters.Add(AutoShipDb.P("@AltOrder", Fit(h.AltOrder, 16)));

            command.Parameters.Add(AutoShipDb.P("@StationId", Fit(h.StationId, 3)));
            command.Parameters.Add(AutoShipDb.P("@OperatorId", Fit(h.OperatorId, 10)));

            command.Parameters.Add(AutoShipDb.P("@ShipToId", Fit(h.ShipToId, 10)));
            command.Parameters.Add(AutoShipDb.P("@ShipToName", Fit(h.ShipToName, 35)));
            command.Parameters.Add(AutoShipDb.P("@ShipToAddr1", Fit(h.ShipToAddr1, 35)));
            command.Parameters.Add(AutoShipDb.P("@ShipToAddr2", Fit(h.ShipToAddr2, 35)));
            command.Parameters.Add(AutoShipDb.P("@ShipToAddr3", Fit(h.ShipToAddr3, 35)));
            command.Parameters.Add(AutoShipDb.P("@ShipToAttn", Fit(h.ShipToAttn, 35)));
            command.Parameters.Add(AutoShipDb.P("@ShipToCity", Fit(h.ShipToCity, 25)));
            command.Parameters.Add(AutoShipDb.P("@ShipToState", Fit(h.ShipToState, 10)));
            command.Parameters.Add(AutoShipDb.P("@ShipToZip", Fit(h.ShipToZip, 12)));
            command.Parameters.Add(AutoShipDb.P("@ShipToCountry", Fit(h.ShipToCountry, 16)));
            command.Parameters.Add(AutoShipDb.P("@Phone", Fit(h.Phone, 30)));

            command.Parameters.Add(AutoShipDb.P("@CustName", Fit(h.CustName, 35)));
            command.Parameters.Add(AutoShipDb.P("@CustAddr1", Fit(h.CustAddr1, 35)));
            command.Parameters.Add(AutoShipDb.P("@CustAddr2", Fit(h.CustAddr2, 35)));
            command.Parameters.Add(AutoShipDb.P("@CustAttn", Fit(h.CustAttn, 35)));
            command.Parameters.Add(AutoShipDb.P("@CustCity", Fit(h.CustCity, 25)));
            command.Parameters.Add(AutoShipDb.P("@CustState", Fit(h.CustState, 10)));
            command.Parameters.Add(AutoShipDb.P("@CustZip", Fit(h.CustZip, 12)));
            command.Parameters.Add(AutoShipDb.P("@CustCountry", Fit(h.CustCountry, 16)));
            command.Parameters.Add(AutoShipDb.P("@CustomerNr", Fit(h.CustomerNr, 10)));
            command.Parameters.Add(AutoShipDb.P("@Shipper", Fit(h.Shipper, 9)));

            command.Parameters.Add(AutoShipDb.P("@Carrier", Fit(h.Carrier, 10)));
            command.Parameters.Add(AutoShipDb.P("@Mode", Fit(h.Mode, 2)));
            command.Parameters.Add(AutoShipDb.P("@ChargeCode", Fit(h.ChargeCode, 1)));
            command.Parameters.Add(AutoShipDb.P("@InvoiceCode", Fit(h.InvoiceCode, 1)));
            command.Parameters.Add(AutoShipDb.P("@FreightType", Fit(h.FreightType, 10)));
            command.Parameters.Add(AutoShipDb.P("@DestZone", Fit(h.DestZone, 50)));
            command.Parameters.Add(AutoShipDb.P("@ThirdPartyBillAcct", Fit(h.ThirdPartyBillAcct, 50)));
            command.Parameters.Add(AutoShipDb.P("@CustPo", Fit(h.CustPo, 15)));
            command.Parameters.Add(AutoShipDb.P("@CustPoNo", Fit(h.CustPoNo, 20)));

            command.Parameters.Add(AutoShipDb.P("@Custom1", Fit(h.Custom1, 50)));
            command.Parameters.Add(AutoShipDb.P("@Custom2", Fit(h.Custom2, 50)));
            command.Parameters.Add(AutoShipDb.P("@Custom3", Fit(h.Custom3, 50)));
            command.Parameters.Add(AutoShipDb.P("@Custom4", Fit(h.Custom4, 250)));

            command.Parameters.Add(AutoShipDb.P("@TotOrdVal", SendCartonParser.ToDecimal(h.TotOrdVal)));
            command.Parameters.Add(AutoShipDb.P("@TotOrdValRaw", Fit(h.TotOrdVal, 13)));
            command.Parameters.Add(AutoShipDb.P("@DeclaredCartonTotal", group.DeclaredCartonTotal));
            command.Parameters.Add(AutoShipDb.P("@SawLastCarton", group.SawLastCarton));
            command.Parameters.Add(AutoShipDb.P("@ErrorMessage", AutoShipDb.Truncate(group.ErrorMessage, 1000)));

            return Convert.ToInt64(command.ExecuteScalar());
        }
    }

    private static long InsertParcel(
        SqlConnection connection, SqlTransaction transaction,
        long shipmentId, object orderId, int seqNo, SendCartonRecord record)
    {
        using (SqlCommand command = AutoShipDb.Command(connection, InsertParcelSql, transaction))
        {
            command.Parameters.Add(AutoShipDb.P("@ShipmentData_Id", shipmentId));
            command.Parameters.Add(AutoShipDb.P("@ShipmentOrder_Id", orderId));
            command.Parameters.Add(AutoShipDb.P("@SeqNo", seqNo));

            command.Parameters.Add(AutoShipDb.P("@CartonId", Fit(record.Carton, 10)));
            command.Parameters.Add(AutoShipDb.P("@CartonSeq", SendCartonParser.ToInt(record.CartonSeq)));
            command.Parameters.Add(AutoShipDb.P("@CartonTotal", SendCartonParser.ToInt(record.CartonTotal)));

            command.Parameters.Add(AutoShipDb.P("@Weight", SendCartonParser.ToDecimal(record.Weight)));
            command.Parameters.Add(AutoShipDb.P("@WeightRaw", Fit(record.Weight, 9)));
            command.Parameters.Add(AutoShipDb.P("@Length", SendCartonParser.ToDecimal(record.Length)));
            command.Parameters.Add(AutoShipDb.P("@Width", SendCartonParser.ToDecimal(record.Width)));
            command.Parameters.Add(AutoShipDb.P("@Height", SendCartonParser.ToDecimal(record.Height)));
            command.Parameters.Add(AutoShipDb.P("@DimUnit", Fit(record.DimUnit, 2)));
            command.Parameters.Add(AutoShipDb.P("@Cost", SendCartonParser.ToDecimal(record.Cost)));
            command.Parameters.Add(AutoShipDb.P("@TotVal", SendCartonParser.ToDecimal(record.TotVal)));

            command.Parameters.Add(AutoShipDb.P("@IsLastCarton", record.IsLastCarton));
            command.Parameters.Add(AutoShipDb.P("@IsLastTran", record.IsLastTransaction));
            command.Parameters.Add(AutoShipDb.P("@PrintStatus", PrintStatus.Pending));

            // Never truncated to fit: this is the evidence. If it will not fit
            // the column, the layout has changed and the insert should fail
            // loudly rather than store a shortened record that looks fine.
            command.Parameters.Add(AutoShipDb.P("@Data", record.Raw));
            command.Parameters.Add(AutoShipDb.P("@ErrorMessage", AutoShipDb.Truncate(record.ParseError, 1000)));

            return Convert.ToInt64(command.ExecuteScalar());
        }
    }

    private static void InsertOrderDetail(
        SqlConnection connection, SqlTransaction transaction,
        long shipmentId, long orderId, long parcelId, int seqNo, SendCartonRecord record)
    {
        using (SqlCommand command = AutoShipDb.Command(connection, InsertOrderDetailSql, transaction))
        {
            command.Parameters.Add(AutoShipDb.P("@ShipmentData_Id", shipmentId));
            command.Parameters.Add(AutoShipDb.P("@ShipmentOrder_Id", orderId));
            command.Parameters.Add(AutoShipDb.P("@ShipmentParcel_Id", parcelId));
            command.Parameters.Add(AutoShipDb.P("@SeqNo", seqNo));

            command.Parameters.Add(AutoShipDb.P("@PartNumber", Fit(record.PartNr, 15)));
            command.Parameters.Add(AutoShipDb.P("@PartDesc", Fit(record.PartDesc, 35)));
            command.Parameters.Add(AutoShipDb.P("@Upc", Fit(record.Upc, 11)));
            command.Parameters.Add(AutoShipDb.P("@UnitTotal", SendCartonParser.ToInt(record.UnitTotal)));
            command.Parameters.Add(AutoShipDb.P("@UnitTotalRaw", Fit(record.UnitTotal, 5)));
            command.Parameters.Add(AutoShipDb.P("@TotVal", SendCartonParser.ToDecimal(record.TotVal)));
            command.Parameters.Add(AutoShipDb.P("@TotValRaw", Fit(record.TotVal, 13)));

            command.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// Empty string to null, and a hard cap at the column width.
    ///
    /// Values are already trimmed to at most the field width, so the cap only
    /// bites if the layout has changed - and an over-long value that reached the
    /// database as a truncation error would abort a whole shipment. Blank fixed
    /// width fields are all spaces, which trim to "", and "" is not the same
    /// thing as "the sender sent a value"; null says so.
    /// </summary>
    private static string Fit(string value, int maxLength)
    {
        if (string.IsNullOrEmpty(value))
        {
            return null;
        }

        return AutoShipDb.Truncate(value, maxLength);
    }
}