using System; using System.Net; using System.Net.Http; using System.Text; using System.Threading.Tasks; using System.Web.Http; using Xylem.Common.Logic.ServiceCore; namespace Xylem.Common.Service.MeterProcessState.Controllers { [RoutePrefix("api/Helium")] public class HeliumController : ApiController { public static class GlobalConfig { public static Lazy connectionString = new Lazy(() => System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString ); } private class HeliumMeter { public int PcbId { get; set; } public int Source { get; set; } public int HeliumId { get; set; } public int WaterId { get; set; } public int IdentNr { get; set; } public int Region { get; set; } public int Metersize { get; set; } public int Bodylength { get; set; } public int PressureSensor { get; set; } public String Plate { get; set; } public double DeadVolume { get; set; } } [Route("GetMeter")] public async Task GetMeter(int PcbId) { try { using (var dataAccess = new SqlDataAccess(GlobalConfig.connectionString.Value)) { HeliumMeter ret = null; StringBuilder query = new StringBuilder(); query.AppendLine(" select mapRecipe.Id as [Source], "); query.AppendLine(" mapRecipe.[PcbId], null,null, "); query.AppendLine(" null,null, "); query.AppendLine(" null, "); query.AppendLine(" null as RegionCode,mapRecipe.Region as RegionValue, "); query.AppendLine(" null as DnCode,Dn as DnValue, "); query.AppendLine(" null as LengthCode, Length as LengthValue, "); query.AppendLine(" null as PressureCode,Pressure as PressureValue, 0 as InternalDn, "); query.AppendLine(" hr.Id as HeliumID, "); query.AppendLine(" wr.Id as WaterID , Plate,DeadVolume "); query.AppendLine(" from ht.PcbRecipe mapRecipe "); query.AppendLine(" join ht.HeliumRecipes hr on hr.Id = mapRecipe.[HeliumRecipeId] "); query.AppendLine(" inner "); query.AppendLine(" join ht.WaterRecipes wr on wr.Id = mapRecipe.WaterRecipeId "); query.AppendLine(" "); query.AppendLine($" where mapRecipe.[PcbId] = {PcbId} "); var dt = dataAccess.ExecuteQuery(query.ToString()); if (dt.Rows.Count >= 2) { throw new ApplicationException("No UNIQUE special recipie found"); } foreach (var item in dt.Select()) { // // // // //Plate //DeadVolume ret = new HeliumMeter() { PcbId = PcbId, Source = (int)item["Source"], HeliumId = (int)item["HeliumID"], WaterId = (int)item["WaterID"] , Region = (int)item["RegionValue"], Metersize = (int)item["DnValue"], Bodylength = (int)item["LengthValue"] , PressureSensor = (bool)item["PressureValue"] ? 1:0, Plate = item["plate"].ToString(), DeadVolume = (double)item["deadvolume"] }; } if (ret == null) { query = new StringBuilder(); query.AppendLine(" select distinct 0 as [Source] , "); query.AppendLine(" pick.CordonelAssignedPicking_PcbId, pick.CordonelAssignedPicking_FertigungsAuftragsNr,pick.CordonelAssignedPicking_Date, "); query.AppendLine(" ap.AuftragNr,ap.PositionNr, "); query.AppendLine(" ap.Identnr, "); query.AppendLine(" vRegion.Charcode as RegionCode,vRegion.Wert as RegionValue, "); query.AppendLine(" vDn.Charcode as DnCode,vDn.Wert as DnValue, "); query.AppendLine(" vLength.Charcode as LengthCode,vLength.Wert as LengthValue, "); query.AppendLine(" vPressure.Charcode as PressureCode,vPressure.Wert as PressureValue, vDninternal.Wert as InternalDn, "); query.AppendLine(" hr.Id as HeliumID, "); query.AppendLine(" wr.Id as WaterID , tv.plate, tv.deadvolume "); query.AppendLine(" from Cordonel_AssignedPicking pick "); query.AppendLine(" inner join AuftragPosition_Gesamt ap on ap.FertigungsauftragNr = pick.CordonelAssignedPicking_FertigungsAuftragsNr "); query.AppendLine(" inner join Identnr i on ap.Identnr = i.Identnr "); query.AppendLine(" inner join VAKO_Merkmale vRegion on vRegion.Basis = 'GNS' and vRegion.Name = 'C_GEN_PRODUKGRUPPE' and vRegion.Charcode = substring(i.VakoCode, vRegion.Stelle, vRegion.Laenge) "); query.AppendLine(" inner join VAKO_Merkmale vDn on vDn.Basis = 'GNS' and vDn.Name = 'C_GEN_NENNWEITE' and vDn.Charcode = substring(i.VakoCode, vDn.Stelle, vDn.Laenge) "); query.AppendLine(" inner join VAKO_Merkmale vLength on vLength.Basis = 'GNS' and vLength.Name = 'C_GEN_BAULAENGE' and vLength.Charcode = substring(i.VakoCode, vLength.Stelle, vLength.Laenge) "); query.AppendLine(" inner join VAKO_Merkmale vPressure on vPressure.Basis = 'GNS' and vPressure.Name = 'C_GEN_GEHAEUSEOPTION' and vPressure.Charcode = substring(i.VakoCode, vPressure.Stelle, vPressure.Laenge) "); query.AppendLine(" inner join VAKO_Merkmale vDninternal on vDninternal.Basis = 'GNS' and vDninternal.Name = 'C_GEN_NENNWEITE' and vDninternal.Charcode = substring(i.VakoCode, vDninternal.Stelle, 1) + 'D' "); query.AppendLine(" "); query.AppendLine(" left outer join ht.HeliumRecipes hr on Replace(vRegion.Charcode, 'D', 'E') = hr.MatchRegion and substring(hr.MatchMeterSize,1,1) = substring(vDn.Charcode,1,1) and hr.MatchBodyLength = vLength.Charcode and hr.MatchPressureSensor = vPressure.Charcode "); query.AppendLine(" left outer join ht.WaterRecipes wr on Replace(vRegion.Charcode, 'D', 'E') = wr.MatchRegion and substring(wr.MatchMeterSize,1,1) = substring(vDn.Charcode,1,1) and wr.MatchBodyLength = vLength.Charcode and wr.MatchPressureSensor = vPressure.Charcode "); query.AppendLine(" left outer join ht.TestVariants tv on Replace(vRegion.Charcode, 'D', 'E') = tv.RegionCode and substring(tv.MeterSizeCode,1,1) = substring(vDn.Charcode,1,1) and tv.BodyLengthCode = vLength.Charcode and tv.PressureSensorCode = vPressure.Charcode "); query.AppendLine($" where pick.CordonelAssignedPicking_IsDeleted = 0 and pick.CordonelAssignedPicking_PcbId = {PcbId} "); dt = dataAccess.ExecuteQuery(query.ToString()); if (dt.Rows.Count != 1) { throw new ApplicationException("No UNIQUE special recipie found"); } foreach (var item in dt.Select()) { ret = new HeliumMeter() { PcbId = PcbId, Source = (int)item["Source"], HeliumId = (int)item["HeliumID"], WaterId = (int)item["WaterID"] , Plate = item["plate"].ToString(), DeadVolume = (double)item["deadvolume"] }; if (item["RegionValue"].ToString().ToUpper().Contains("DE") || item["RegionValue"].ToString().ToUpper().Contains("EMEA"))// { ret.Region = 2; } else if (item["RegionValue"].ToString().ToUpper().Contains("USA")) { ret.Region = 1; } else { //No Region Mapping } int m; if (int.TryParse(item["InternalDn"].ToString(), out m)) { ret.Metersize = m; } else { //no meter size } if (int.TryParse(item["LengthValue"].ToString(), out m)) { ret.Bodylength = m; } else { //no length size } if (item["PressureCode"].ToString().ToUpper().Contains("X"))// { ret.PressureSensor = 0; } else if (item["RegionValue"].ToString().ToUpper().Contains("D")) { ret.PressureSensor = 1; } else { //No pressure Mapping } } } if (ret == null) { return Request.CreateResponse(HttpStatusCode.InternalServerError, "No Recipe found"); } else { return Request.CreateResponse(HttpStatusCode.OK, ret); } } } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.ToString() + "
" + ex.InnerException.ToString()); } } } }