370 lines
19 KiB
C#
370 lines
19 KiB
C#
using ByteArrayStyle;
|
|
using Newtonsoft.Json;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Text.RegularExpressions;
|
|
using Xylem.Common.Hardware.WaterMeter.Genesis.Registers;
|
|
using Xylem.Common.Logic.ProductionOrderCore.Csd;
|
|
using Xylem.Common.Logic.ProductionOrderCore.Vako;
|
|
using Xylem.Common.Logic.ServiceCore;
|
|
using Xylem.Common.Logic.SoftwareAccessHelper;
|
|
|
|
namespace Xylem.Common.Logic.ProductionOrderCore.OrderData
|
|
{
|
|
public static class OrderProgrammingParameters
|
|
{
|
|
//
|
|
public static List<ProgrammingParameters> GetProgrammingParameters(String pcbID, String connectionString)
|
|
{
|
|
var ret = new List<ProgrammingParameters>();
|
|
var orderNr = default(int);
|
|
|
|
using (var dataAccess = new SqlDataAccess(connectionString))
|
|
{
|
|
//var sb = new StringBuilder();
|
|
//sb.AppendLine(" SELECT distinct[MapPcbIdToSerialNumber_SerialNumber], mapOrder.CordonelPressureTest_FertigungsAuftragsNr ");
|
|
//sb.AppendLine($" FROM Auftrag.dbo.Cordonel_PressureTest mapOrder ");
|
|
//sb.AppendLine($" left outer join [Auftrag].[dbo].[MapPcbIdToSerialNumber] map on mapOrder.CordonelPressureTest_PcbId = map.MapPcbIdToSerialNumber_PcbId ");
|
|
//sb.AppendLine($" left outer join Auftrag.dbo.AuftragPositionSerienNr mapSrn on map.[MapPcbIdToSerialNumber_SerialNumber] = mapSrn.SerienNr and mapSrn.Pruefgangnr = 0 ");
|
|
//sb.AppendLine($" left outer join Auftrag.dbo.AlleAuftragPositionen auftraginfo on auftraginfo.AuftragNr = mapSrn.AuftragNr and auftraginfo.PositionNr = mapSrn.PositionNr ");
|
|
//sb.AppendLine($" where mapOrder.CordonelPressureTest_PcbId = '{pcbID}'");
|
|
|
|
var mapData = dataAccess.ExecuteQuery($@"
|
|
SELECT DISTINCT
|
|
map.MapPcbIdToSerialNumber_SerialNumber
|
|
, mapOrder.CordonelPressureTest_FertigungsAuftragsNr
|
|
FROM Cordonel_PressureTest AS mapOrder
|
|
LEFT OUTER JOIN MapPcbIdToSerialNumber AS map
|
|
ON mapOrder.CordonelPressureTest_PcbId = map.MapPcbIdToSerialNumber_PcbId
|
|
LEFT OUTER JOIN AuftragPositionSerienNr AS mapSrn
|
|
ON map.MapPcbIdToSerialNumber_SerialNumber = mapSrn.SerienNr
|
|
AND mapSrn.Pruefgangnr = 0
|
|
LEFT OUTER JOIN AlleAuftragPositionen AS auftraginfo
|
|
ON auftraginfo.AuftragNr = mapSrn.AuftragNr
|
|
AND auftraginfo.PositionNr = mapSrn.PositionNr
|
|
WHERE mapOrder.CordonelPressureTest_PcbId = '{pcbID}'
|
|
AND mapOrder.CordonelPressureTest_IsDeleted = 0
|
|
AND map.MapPcbIdToSerialNumber_Deleted = 0");
|
|
|
|
if (mapData.Rows.Count != 1)
|
|
{
|
|
throw new ApplicationException("No base data found");
|
|
}
|
|
|
|
orderNr = (Int32)mapData.Rows[0]["CordonelPressureTest_FertigungsAuftragsNr"];
|
|
|
|
}
|
|
|
|
var radioParams = OrderRadioParameter.GetRadioParams(pcbID, connectionString);
|
|
//NA Cordonels do not have a radio address
|
|
if (radioParams.RadioAddress != 0)
|
|
{
|
|
|
|
ret.Add(new ProgrammingParameters("SENSUSRADIO_RadioAddress", BitConverter.GetBytes(radioParams.RadioAddress).Reverse().ToArray()));
|
|
|
|
if (radioParams.FrqOffset.HasValue)
|
|
{
|
|
ret.Add(new ProgrammingParameters("SENSUSRADIO_FrequencyOffset", BitConverter.GetBytes(radioParams.FrqOffset.Value).Reverse().ToArray()));
|
|
}
|
|
|
|
|
|
ret.Add(new ProgrammingParameters("SENSUSRADIO_EncryptionKey", radioParams.EncryptionKey.ToArray()));
|
|
|
|
|
|
|
|
if (radioParams.PowerLevel > 0 && radioParams.PowerLevelOption > 0 && radioParams.ImpedanceCodeNew > 0 && radioParams.ImpedanceCodeOption > 0)
|
|
{
|
|
ret.Add(new ProgrammingParameters("SENSUSRADIO_PowerLevel", BitConverter.GetBytes(radioParams.PowerLevel).Reverse().ToArray()));
|
|
ret.Add(new ProgrammingParameters("SENSUSRADIO_PowerLevelOption", BitConverter.GetBytes(radioParams.PowerLevelOption).Reverse().ToArray()));
|
|
ret.Add(new ProgrammingParameters("SENSUSRADIO_ImpedanceCodeNew", BitConverter.GetBytes(radioParams.ImpedanceCodeNew).Reverse().ToArray()));
|
|
ret.Add(new ProgrammingParameters("SENSUSRADIO_ImpedanceCodeOption", BitConverter.GetBytes(radioParams.ImpedanceCodeOption).Reverse().ToArray()));
|
|
}
|
|
}
|
|
var rawVako = VakoDbAdapter.GetRawVako(connectionString, orderNr);
|
|
var rawMapping = VakoDbAdapter.GetVakoMapping(connectionString, rawVako.Type);
|
|
var vakoData = VakoParser.Parse(rawVako, rawMapping);
|
|
|
|
var csdIdent = vakoData.KeyValues.FirstOrDefault(a => a.Name == "C_GEN_CSD")?.Value;
|
|
|
|
if (String.IsNullOrEmpty(csdIdent))
|
|
{
|
|
throw new ApplicationException("CSD Could not befound");
|
|
}
|
|
if (orderNr == 3204790)
|
|
{
|
|
csdIdent = "773328";
|
|
}
|
|
|
|
using (var dataAccess = new SqlDataAccess(connectionString))
|
|
{
|
|
var sb = new StringBuilder();
|
|
sb.AppendLine($" SELECT ");
|
|
sb.AppendLine($" RegisterName ,");
|
|
sb.AppendLine($" RegisterValue ");
|
|
sb.AppendLine($" from [Auftrag].[dbo].[Cordonel_ProgrammingMapper] ");
|
|
|
|
|
|
sb.AppendLine($" where 1=0 ");
|
|
|
|
|
|
foreach (var item in vakoData.KeyValues)
|
|
{
|
|
sb.AppendLine($" or (KEYNAME = '{item.Name}' and KeyValue = '{item.Charcode.Replace("\'", "\'\'")}') ");
|
|
}
|
|
var mapData = dataAccess.ExecuteQuery(sb.ToString());
|
|
|
|
foreach (DataRow row in mapData.Rows)
|
|
{
|
|
ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), (Byte[])row["RegisterValue"]));
|
|
}
|
|
}
|
|
|
|
var meterSize = BitConverter.ToUInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_MeterSize").RegisterValue.Reverse().ToArray(), 0);
|
|
var displayUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_DisplayUnits").RegisterValue.Reverse().ToArray(), 0);
|
|
var flowUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "METROLOGYASST_FlowUnits").RegisterValue.Reverse().ToArray(), 0);
|
|
|
|
|
|
|
|
|
|
using (var dataAccess = new SqlDataAccess(connectionString))
|
|
{
|
|
var sb = new StringBuilder();
|
|
sb.AppendLine($" SELECT ");
|
|
sb.AppendLine($" CordonelDnDisplayunitDisplaypow10_RegisterIdent as RegisterName, ");
|
|
sb.AppendLine($" [CordonelDnDisplayunitDisplaypow10_DisplayPow10] as RegisterValue ");
|
|
sb.AppendLine($" FROM [Auftrag].[dbo].[CordonelDnDisplayunitDisplaypow10] ");
|
|
sb.AppendLine($" where [CordonelDnDisplayunitDisplaypow10_MeterSize] = {meterSize} ");
|
|
sb.AppendLine($" and [CordonelDnDisplayunitDisplaypow10_DisplayUnit] = {displayUnits} ");
|
|
sb.AppendLine($" union ");
|
|
sb.AppendLine($" SELECT ");
|
|
sb.AppendLine($" CordonelDnFlowUnitFlowPoint_RegisterIdent as RegisterName, ");
|
|
sb.AppendLine($" CAST([CordonelDnFlowUnitFlowPoint_FlowPoint] AS varbinary) as RegisterValue ");
|
|
sb.AppendLine($" FROM [Auftrag].[dbo].[CordonelDnFlowUnitFlowPoint] ");
|
|
sb.AppendLine($" where[CordonelDnFlowUnitFlowPoint_FlowUnit] = {flowUnits} ");
|
|
sb.AppendLine($" and[CordonelDnFlowUnitFlowPoint_MeterSize] = {meterSize} ");
|
|
|
|
var mapData = dataAccess.ExecuteQuery(sb.ToString());
|
|
|
|
foreach (DataRow row in mapData.Rows)
|
|
{
|
|
ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), RegisterConverter.ValueToByteArray(Convert.ToInt32(row["RegisterValue"]))));
|
|
}
|
|
}
|
|
var ident = "GNS";
|
|
if (vakoData.KeyValues.Any(k => k.Key == "Produktgruppe") && vakoData.KeyValues.First(k => k.Key == "Produktgruppe").Charcode == "U")
|
|
{
|
|
ident += vakoData.KeyValues.First(k => k.Key == "Nennweite").Charcode;
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(csdIdent))
|
|
{
|
|
var csdTokens = csdIdent.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
|
|
|
|
if (csdTokens.Length >= 2)
|
|
{
|
|
var csdNr = csdTokens[1];
|
|
|
|
if (!string.IsNullOrWhiteSpace(csdNr))
|
|
{
|
|
csdIdent = csdNr;
|
|
}
|
|
}
|
|
}
|
|
|
|
var retStr = LocalWebRequest.GetRequest($"http://10.49.40.25/csdcore/csdservice/getstampings?CsdIdent={csdIdent}&GroupId=1&ProductIdent={ident}&ProductDn={rawVako.Dn}&ShowOnlyValidStampings=0", 80000);
|
|
var retListRealCsd = JsonConvert.DeserializeObject<List<CsdParameterResult>>(retStr);
|
|
if (retListRealCsd != null && retListRealCsd.Any())
|
|
{
|
|
|
|
foreach (var csdItemToConvert in retListRealCsd)
|
|
{
|
|
|
|
ret.Add(new ProgrammingParameters(csdItemToConvert.Name, ByteStyler.Parse(csdItemToConvert.ValueText, false), ProgrammingSource.Csd));
|
|
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
if (orderNr != 3249279)
|
|
{
|
|
|
|
|
|
if (radioParams.RadioAddress != 0)
|
|
{
|
|
if (vakoData.KeyValues.Any(f => f.Name.Contains("C_GEN_FUNKSCHLUESSEL")))
|
|
{
|
|
var KeyInfo = vakoData.KeyValues.FirstOrDefault(f => f.Name.Contains("C_GEN_FUNKSCHLUESSEL"));
|
|
var keyList = ret.FindAll(f => f.RegisterName == "SENSUSRADIO_EncryptionKey");
|
|
var heightstKey = keyList.OrderByDescending(o => o.Source).First(); //desc
|
|
|
|
var defEncryptionKey = new List<Byte>();
|
|
defEncryptionKey.AddRange(new Byte[] { 0x0E, 0x9F, 0xAC, 0xC6 }.ToArray());
|
|
defEncryptionKey.AddRange(new Byte[] { 0xA0, 0xE1, 0xEF, 0x1C }.ToArray());
|
|
defEncryptionKey.AddRange(new Byte[] { 0x3B, 0x27, 0xA4, 0x9B }.ToArray());
|
|
defEncryptionKey.AddRange(new Byte[] { 0xAC, 0x21, 0x65, 0x35 }.ToArray());
|
|
|
|
|
|
switch (KeyInfo.Charcode)
|
|
{
|
|
//1 Standard
|
|
case "1":
|
|
|
|
if (ByteStyler.ToString(heightstKey.RegisterValue) != ByteStyler.ToString(defEncryptionKey.ToArray()))
|
|
{
|
|
throw new ApplicationException("Order placed with default encryption, but the datasource has a diffrent key");
|
|
}
|
|
|
|
break;
|
|
//2 Zufallszahl
|
|
case "2":
|
|
//notdef
|
|
|
|
if (ByteStyler.ToString(heightstKey.RegisterValue) == ByteStyler.ToString(defEncryptionKey.ToArray()))
|
|
{
|
|
throw new ApplicationException("Order placed with radom encryption, but the datasource has a default key");
|
|
}
|
|
//not csd
|
|
else if (heightstKey.Source == ProgrammingSource.Csd)
|
|
{
|
|
throw new ApplicationException("Order placed with radom encryption, but the datasource has a customer key");
|
|
}
|
|
break;
|
|
//3 nach CSD
|
|
case "3":
|
|
if (heightstKey.Source != ProgrammingSource.Csd)
|
|
{
|
|
throw new ApplicationException("Order placed with Customer encryption but the datasource has no Customer key");
|
|
}
|
|
break;
|
|
default:
|
|
throw new ApplicationException("No Vako Key for Radiokey found, unkown parameter");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
throw new ApplicationException("No Vako Key for Radiokey found");
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
StringBuilder csv = new StringBuilder();
|
|
foreach (var item in ret)
|
|
{
|
|
csv.AppendLine($"{item.RegisterName}={ByteStyler.ToString(item.RegisterValue)} (From{item.Source})");
|
|
}
|
|
|
|
return ret;
|
|
|
|
}
|
|
|
|
public static List<ProgrammingParameters> GetProgrammingParametersFixNa(String connectionString)
|
|
{
|
|
var ret = new List<ProgrammingParameters>();
|
|
|
|
Int32 orderNr = 3215223;
|
|
|
|
var rawVako = VakoDbAdapter.GetRawVako(connectionString, orderNr);
|
|
var rawMapping = VakoDbAdapter.GetVakoMapping(connectionString, rawVako.Type);
|
|
var vakoData = VakoParser.Parse(rawVako, rawMapping);
|
|
|
|
var csdIdent = vakoData.KeyValues.FirstOrDefault(a => a.Name == "C_GEN_CSD")?.Value;
|
|
|
|
|
|
using (var dataAccess = new SqlDataAccess(connectionString))
|
|
{
|
|
var sb = new StringBuilder();
|
|
sb.AppendLine($" SELECT ");
|
|
sb.AppendLine($" RegisterName ,");
|
|
sb.AppendLine($" RegisterValue ");
|
|
sb.AppendLine($" from [Auftrag].[dbo].[Cordonel_ProgrammingMapper] ");
|
|
|
|
|
|
sb.AppendLine($" where 1=0 ");
|
|
|
|
|
|
foreach (var item in vakoData.KeyValues)
|
|
{
|
|
sb.AppendLine($" or (KEYNAME = '{item.Name}' and KeyValue = '{item.Charcode.Replace("\'", "\'\'")}') ");
|
|
}
|
|
var mapData = dataAccess.ExecuteQuery(sb.ToString());
|
|
|
|
foreach (DataRow row in mapData.Rows)
|
|
{
|
|
ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), (Byte[])row["RegisterValue"]));
|
|
}
|
|
}
|
|
|
|
var meterSize = BitConverter.ToUInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_MeterSize").RegisterValue.Reverse().ToArray(), 0);
|
|
var displayUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_DisplayUnits").RegisterValue.Reverse().ToArray(), 0);
|
|
var flowUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "METROLOGYASST_FlowUnits").RegisterValue.Reverse().ToArray(), 0);
|
|
|
|
|
|
|
|
|
|
using (var dataAccess = new SqlDataAccess(connectionString))
|
|
{
|
|
var sb = new StringBuilder();
|
|
sb.AppendLine($" SELECT ");
|
|
sb.AppendLine($" CordonelDnDisplayunitDisplaypow10_RegisterIdent as RegisterName, ");
|
|
sb.AppendLine($" [CordonelDnDisplayunitDisplaypow10_DisplayPow10] as RegisterValue ");
|
|
sb.AppendLine($" FROM [Auftrag].[dbo].[CordonelDnDisplayunitDisplaypow10] ");
|
|
sb.AppendLine($" where [CordonelDnDisplayunitDisplaypow10_MeterSize] = {meterSize} ");
|
|
sb.AppendLine($" and [CordonelDnDisplayunitDisplaypow10_DisplayUnit] = {displayUnits} ");
|
|
sb.AppendLine($" union ");
|
|
sb.AppendLine($" SELECT ");
|
|
sb.AppendLine($" CordonelDnFlowUnitFlowPoint_RegisterIdent as RegisterName, ");
|
|
sb.AppendLine($" CAST([CordonelDnFlowUnitFlowPoint_FlowPoint] AS varbinary) as RegisterValue ");
|
|
sb.AppendLine($" FROM [Auftrag].[dbo].[CordonelDnFlowUnitFlowPoint] ");
|
|
sb.AppendLine($" where[CordonelDnFlowUnitFlowPoint_FlowUnit] = {flowUnits} ");
|
|
sb.AppendLine($" and[CordonelDnFlowUnitFlowPoint_MeterSize] = {meterSize} ");
|
|
|
|
var mapData = dataAccess.ExecuteQuery(sb.ToString());
|
|
|
|
foreach (DataRow row in mapData.Rows)
|
|
{
|
|
ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), RegisterConverter.ValueToByteArray(Convert.ToInt32(row["RegisterValue"]))));
|
|
}
|
|
}
|
|
var ident = "GNS";
|
|
if (vakoData.KeyValues.Any(k => k.Key == "Produktgruppe") && vakoData.KeyValues.First(k => k.Key == "Produktgruppe").Charcode == "U")
|
|
{
|
|
ident += vakoData.KeyValues.First(k => k.Key == "Nennweite").Charcode;
|
|
}
|
|
|
|
|
|
|
|
var retStr = LocalWebRequest.GetRequest($"http://sla12iis01/csdcore/csdservice/getstampings?CsdIdent={csdIdent}&GroupId=1&ProductIdent={ident}&ProductDn={rawVako.Dn}&ShowOnlyValidStampings=0", 8000);
|
|
var retListRealCsd = JsonConvert.DeserializeObject<List<CsdParameterResult>>(retStr);
|
|
if (retListRealCsd != null && retListRealCsd.Any())
|
|
{
|
|
|
|
foreach (var csdItemToConvert in retListRealCsd)
|
|
{
|
|
if (csdItemToConvert.ValueText.Contains("-"))
|
|
{
|
|
ret.Add(new ProgrammingParameters(csdItemToConvert.Name, ByteStyler.Parse(csdItemToConvert.ValueText, false), ProgrammingSource.Csd));
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
StringBuilder csv = new StringBuilder();
|
|
foreach (var item in ret)
|
|
{
|
|
csv.AppendLine($"{item.RegisterName}={ByteStyler.ToString(item.RegisterValue)} (From{item.Source})");
|
|
}
|
|
|
|
return ret;
|
|
|
|
}
|
|
|
|
|
|
}
|
|
}
|