using LaaPackages.SqlClient; using Logic.ProductionToProductMapper.Cordonel; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Net; using System.Net.Http; using System.Text; using System.Threading.Tasks; using System.Web.Http; using Xylem.Common.Hardware.WaterMeter.WaterMeterCore.Consts; using Xylem.Common.Logic.ProductionOrderCore.ProcessState; using Xylem.Common.Logic.ProductionOrderCore.TestResults; using Xylem.Common.Logic.ServiceCore; namespace Service.MeterProcessState.Controllers { [RoutePrefix("api/ProccesState")] public class ProccesStateController : ApiController { public static class GlobalConfig { public static Lazy connectionString = new Lazy(() => System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString ); } [Route("SetPressureStateDetailed"), HttpPost] public async Task SetPressureStateDetailed([FromBody] PressureResultData testResult) { try { if (testResult == null) { throw new ApplicationException("No TestResults passed"); } if (!testResult.Passed.HasValue) { throw new ApplicationException("Can not save results wich have no passed state"); } return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { var query = new StringBuilder(); query.AppendLine(" declare @pcbID int"); query.AppendLine(" declare @Valid bit"); query.AppendLine(" declare @Text nvarchar(250)"); query.AppendLine(" declare @StationId int"); query.AppendLine(" declare @LeakRate nvarchar(50)"); query.AppendLine(" declare @TestPressure nvarchar(50)"); query.AppendLine(" declare @TesterName nvarchar(50)"); query.AppendLine(" declare @TestId int"); query.AppendLine(" declare @Ordernr int"); query.AppendLine($" set @pcbID = {testResult.PcbId}"); query.AppendLine($" set @Valid = {(testResult.Passed.Value ? "1" : "0")}"); query.AppendLine($" set @Text = '{testResult.Remark}'"); query.AppendLine($" set @LeakRate = '{testResult.LeakRate}'"); query.AppendLine($" set @TestPressure = '{testResult.TestPressure}'"); query.AppendLine($" set @TesterName = '{testResult.TesterName}'"); query.AppendLine($" set @StationId = 2"); query.AppendLine(" SELECT TOP (1)"); query.AppendLine(" @Ordernr = [CordonelAssignedPicking_FertigungsAuftragsNr]"); query.AppendLine(" FROM [Auftrag].[dbo].[Cordonel_AssignedPicking]"); query.AppendLine(" where [CordonelAssignedPicking_PcbId] = @pcbID"); query.AppendLine(" and [CordonelAssignedPicking_IsDeleted] = 0"); query.AppendLine(" INSERT INTO [dbo].[Cordonel_PressureTest]"); query.AppendLine(" ([CordonelPressureTest_PcbId]"); query.AppendLine(" ,[CordonelPressureTest_FertigungsAuftragsNr]"); query.AppendLine(" ,[CordonelPressureTest_Date]"); query.AppendLine(" ,[CordonelPressureTest_Valid]"); query.AppendLine(" ,[CordonelPressureTest_IsDeleted]"); query.AppendLine(" ,[CordonelPressureTest_Text]"); query.AppendLine(" ,[CordonelPressureTest_StationId])"); query.AppendLine(" VALUES"); query.AppendLine(" (@pcbID"); query.AppendLine(" ,@Ordernr"); query.AppendLine(" ,GETUTCDATE() "); query.AppendLine(" ,@Valid"); query.AppendLine(" ,0"); query.AppendLine(" ,@Text"); query.AppendLine(" ,@StationId)"); query.AppendLine(" set @TestId = @@IDENTITY"); query.AppendLine(" INSERT INTO [dbo].[Cordonel_PressureTest_He]"); query.AppendLine(" ([CordonelPressureTestHe_TestID]"); query.AppendLine(" ,[CordonelPressureTestHe_LeakRate]"); query.AppendLine(" ,[CordonelPressureTestHe_TestPressure]"); query.AppendLine(" ,[CordonelPressureTestHe_TesterName])"); query.AppendLine(" VALUES"); query.AppendLine(" (@TestId"); query.AppendLine(" ,@LeakRate"); query.AppendLine(" ,@TestPressure"); query.AppendLine(" ,@TesterName)"); foreach (var item in testResult.TestPoints) { query.AppendLine(" INSERT INTO [dbo].[Cordonel_PressureTest_TestPoints]"); query.AppendLine(" ([CordonelPressureTest_Id]"); query.AppendLine(" ,[CordonelPressureTest_TestPointNr]"); query.AppendLine(" ,[CordonelPressureTest_TestPointResult])"); query.AppendLine(" VALUES"); query.AppendLine(" (@TestId"); query.AppendLine($" ,{item.Ident}"); query.AppendLine($" ,'{item.Input.Replace(",", ".")}')"); } //query.AppendLine($" declare @TestIdStr nvarchar(250) "); //query.AppendLine($" set @TestIdStr = cast(@TestId as nvarchar(250)) "); //query.AppendLine($" if (substring(@TestIdStr, LEN(@TestIdStr) , 1 ) = '0' ) "); //query.AppendLine($" BEGIN "); //query.AppendLine($" Select 1 as PressuretestNeeded "); //query.AppendLine($" end "); //query.AppendLine($" else "); //query.AppendLine($" begin "); //query.AppendLine($" INSERT INTO [dbo].[Druckpruefung] select 0,'Übersprungen wegen Stichprobe', current_timestamp ,0,0,0,0,@pcbID, null,null,null,null,'WZ',NULL,NULL,NULL, @Ordernr, 1 ,null,null,1 "); //query.AppendLine($" Select 0 as PressuretestNeeded "); //query.AppendLine($" end "); var dt = dataacces.ExecuteQuery(query.ToString()); return "1"; // return ((int)dt.Rows[0]["PressuretestNeeded"]).ToString(); } })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } [Route("GetPressureStateDetailed"), HttpGet] public async Task GetPressureStateDetailed(Int32 PcbId, Int32? TopCount = null) { try { var ret = new List(); return await Task.Run(() => { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { var query = new StringBuilder(); var dtCheckOrder = dataacces.ExecuteQuery($"select [CordonelAssignedPicking_FertigungsAuftragsNr] from [Cordonel_AssignedPicking] where [CordonelAssignedPicking_PcbId] = {PcbId} "); if (dtCheckOrder.Rows.Count != 1) { return Request.CreateResponse(HttpStatusCode.InternalServerError, "No unique picking found"); } if (TopCount.HasValue) { query.AppendLine($" SELECT TOP ({TopCount.Value}) "); } else { query.AppendLine($" SELECT "); } query.AppendLine($" [CordonelPressureTest_Id] "); query.AppendLine($" ,[CordonelPressureTest_PcbId] "); query.AppendLine($" ,[CordonelPressureTest_FertigungsAuftragsNr]"); query.AppendLine($" ,[CordonelPressureTest_Date] "); query.AppendLine($" ,[CordonelPressureTest_Valid] "); query.AppendLine($" ,[CordonelPressureTest_IsDeleted] "); query.AppendLine($" ,[CordonelPressureTest_Text] "); query.AppendLine($" ,[CordonelPressureTest_StationId] "); query.AppendLine($"--[Cordonel_PressureTest_He] "); query.AppendLine($" ,[CordonelPressureTestHe_Id] "); query.AppendLine($" ,[CordonelPressureTestHe_TestID] "); query.AppendLine($" ,[CordonelPressureTestHe_LeakRate] "); query.AppendLine($" ,[CordonelPressureTestHe_TestPressure] "); query.AppendLine($" ,[CordonelPressureTestHe_TesterName] "); query.AppendLine($" FROM [dbo].[Cordonel_PressureTest] "); query.AppendLine($" "); query.AppendLine($" inner join [Cordonel_PressureTest_He] "); query.AppendLine($" on [CordonelPressureTest_Id] "); query.AppendLine($" = [CordonelPressureTestHe_TestID] "); query.AppendLine($" "); query.AppendLine($" where [CordonelPressureTest_IsDeleted] = 0 "); query.AppendLine($" and [CordonelPressureTest_PcbId] = {PcbId} "); query.AppendLine($" and [CordonelPressureTest_StationId] = 2 "); query.AppendLine($" order by [CordonelPressureTest_Date] desc "); var dtMain = dataacces.ExecuteQuery(query.ToString()); query = new StringBuilder(); query.AppendLine($" SELECT pttp.[CordonelPressureTestTestPoints_Id] "); query.AppendLine($" ,pttp.[CordonelPressureTest_Id] "); query.AppendLine($" ,pttp.[CordonelPressureTest_TestPointNr] "); query.AppendLine($" ,pttp.[CordonelPressureTest_TestPointResult] "); query.AppendLine($" FROM [dbo].[Cordonel_PressureTest] pt "); query.AppendLine($" inner join [Cordonel_PressureTest_He] ptHe "); query.AppendLine($" on pt.[CordonelPressureTest_Id] "); query.AppendLine($" = ptHe.[CordonelPressureTestHe_TestID] "); query.AppendLine($" inner join [Auftrag].[dbo].[Cordonel_PressureTest_TestPoints] pttp "); query.AppendLine($" on pt.[CordonelPressureTest_Id] = pttp.[CordonelPressureTest_Id] "); query.AppendLine($" where [CordonelPressureTest_IsDeleted] = 0 "); query.AppendLine($" and [CordonelPressureTest_PcbId] = {PcbId} "); query.AppendLine($" and [CordonelPressureTest_StationId] = 2 "); query.AppendLine($" order by [CordonelPressureTest_Date] desc "); var dtTestPoints = dataacces.ExecuteQuery(query.ToString()); foreach (DataRow drMain in dtMain.Rows) { var add = new PressureResultData(); add.LeakRate = drMain["CordonelPressureTestHe_LeakRate"].ToString(); add.Passed = (Boolean)drMain["CordonelPressureTest_Valid"]; add.PcbId = drMain["CordonelPressureTest_PcbId"].ToString(); add.Remark = drMain["CordonelPressureTest_Text"].ToString(); add.Rev = 1; add.TesterName = drMain["CordonelPressureTestHe_TesterName"].ToString(); add.TestPressure = drMain["CordonelPressureTestHe_TestPressure"].ToString(); var testID = (Int32)drMain["CordonelPressureTest_Id"]; add.TestPoints = new List(); foreach (var drTestPoints in dtTestPoints.Select($"CordonelPressureTest_Id = {testID}")) { add.TestPoints.Add( new PressureTestPoints() { Ident = (Int32)drTestPoints["CordonelPressureTest_TestPointNr"], Input = drTestPoints["CordonelPressureTest_TestPointResult"].ToString() }); } ret.Add(add); } } return Request.CreateResponse(HttpStatusCode.OK, ret); }); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } // [Route("SetPressureStateHydralic"), HttpPost] public async Task SetPressureState(String PcbId, Int32 FertigungsauftragsNr) { try { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { var query = new StringBuilder(); query.AppendLine($" INSERT INTO [dbo].[Druckpruefung] select 0,'Übersprungen wegen Stichprobe', current_timestamp ,0,0,0,0,{PcbId}, null,null,null,null,'WZ',NULL,NULL,NULL, {FertigungsauftragsNr}, 1 ,null,null,1 "); var dt = dataacces.ExecuteQuery(query.ToString()); } return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return "store"; })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } [Route("SetPressureState"), HttpPost] public async Task SetPressureState(String PcbId, Int32 FertigungsauftragsNr, [FromBody] PressureTestResult testResult) { try { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { var query = new StringBuilder(); String ResultText = testResult.Passed ? "1" : "0"; query.AppendLine("INSERT INTO [dbo].[Cordonel_PressureTest]"); query.AppendLine(" ( "); query.AppendLine(" [CordonelPressureTest_PcbId] "); query.AppendLine(" ,[CordonelPressureTest_FertigungsAuftragsNr] "); query.AppendLine(" ,[CordonelPressureTest_Date] "); query.AppendLine(" ,[CordonelPressureTest_Valid] "); query.AppendLine(" ,[CordonelPressureTest_IsDeleted] "); if (!string.IsNullOrEmpty(testResult.FreeText)) { query.AppendLine(" ,[CordonelPressureTest_Text] "); } if (testResult.StationId.HasValue) { query.AppendLine(" ,[CordonelPressureTest_StationId] "); } query.AppendLine(" ) "); query.AppendLine(" VALUES "); query.AppendLine($" ({PcbId} "); query.AppendLine($",{FertigungsauftragsNr} "); query.AppendLine($" ,GETUTCDATE() "); query.AppendLine($" ,{ResultText} "); query.AppendLine($" ,0 "); if (!string.IsNullOrEmpty(testResult.FreeText)) { query.AppendLine($" , '{testResult.FreeText}' "); } if (testResult.StationId.HasValue) { query.AppendLine($" , {testResult.StationId.Value} "); } query.AppendLine($" )"); var dt = dataacces.ExecuteQuery(query.ToString()); } return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return "store"; })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } [Route("SetPickingState"), HttpPost] public async Task SetPickingState(String PcbId, Int32 FertigungsauftragsNr) { try { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { var query = new StringBuilder(); if (FertigungsauftragsNr == 100) { query.AppendLine($" update [dbo].[Cordonel_AssignedPicking] set CordonelAssignedPicking_IsDeleted = 1 where CordonelAssignedPicking_PcbId = '{PcbId}' "); query.AppendLine($" update [Auftrag].[dbo].[MapPcbIdToSerialNumber] set MapPcbIdToSerialNumber_Deleted = 1 where [MapPcbIdToSerialNumber_PcbId] = '{PcbId}' "); } query.AppendLine($"if (select count(*) from dbo.[Cordonel_AssignedPicking] where [CordonelAssignedPicking_FertigungsAuftragsNr] = {FertigungsauftragsNr} and [CordonelAssignedPicking_PcbId] = {PcbId} and CordonelAssignedPicking_IsDeleted = 0) = 0 "); query.AppendLine($"begin "); query.AppendLine($" INSERT INTO [dbo].[Cordonel_AssignedPicking] "); query.AppendLine($" ([CordonelAssignedPicking_PcbId] "); query.AppendLine($" ,[CordonelAssignedPicking_FertigungsAuftragsNr] "); query.AppendLine($" ,[CordonelAssignedPicking_Date] "); query.AppendLine($" ,[CordonelAssignedPicking_IsDeleted]) "); query.AppendLine($" VALUES "); query.AppendLine($" ({PcbId} "); query.AppendLine($" ,{FertigungsauftragsNr} "); query.AppendLine($" ,GETUTCDATE() "); query.AppendLine($" ,0) "); query.AppendLine($" "); query.AppendLine($" select @@IDENTITY "); query.AppendLine($"end "); query.AppendLine($"else "); query.AppendLine($"begin "); query.AppendLine($" select -1 "); query.AppendLine($"end "); var dt = dataacces.ExecuteQuery(query.ToString()); var newID = -1; var ret = false; if (int.TryParse(dt.Rows[0][0].ToString(), out newID)) { if (newID != -1) { ret = true; } } return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return ret; })); } } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } [Route("setState"), HttpPost] public async Task setState(String PcbId, Int32 code, String version) { try { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { if (version.ToLower() == "sentinel") { SaveSapState(PcbId, 310, version); SaveSapState(PcbId, 400, version); SaveSapState(PcbId, 997, version); SaveSapState(PcbId, 998, version); } else { var dt = dataacces.ExecuteQuery($"Insert into dbo.MeterProcessState (ProcessState_PcbId, [ProcessState_State],ProcessState_DateUtc ) values ({PcbId}, {code.GetHashCode()}, GETUTCDATE())"); } if (!string.IsNullOrEmpty(version)) { try { var fixDisplaycode = DisplayCodes.None; var SapCode = 0; if (Enum.TryParse(code.ToString(), out fixDisplaycode)) { switch (fixDisplaycode) { case DisplayCodes.None: case DisplayCodes.Error: case DisplayCodes.PressureTestedFailed: case DisplayCodes.PickingFailed: case DisplayCodes.FwUpdateActive: case DisplayCodes.FwUpdateFailed: case DisplayCodes.FwUpToDate: case DisplayCodes.FlowTestFailed: case DisplayCodes.FinalTestFailed: case DisplayCodes.PickingStarted: case DisplayCodes.FlowCalibrationOurOfRange: case DisplayCodes.ZeroFlowFailed: case DisplayCodes.FlowCalibrated: case DisplayCodes.PickingTested: default: break; case DisplayCodes.PressureTested: SapCode = 110; break; case DisplayCodes.Mounted: SapCode = 300; break; case DisplayCodes.ZeroFlow: SapCode = 120; break; case DisplayCodes.FlowTested: SapCode = 200; break; } if (SapCode != 0) { SaveSapState(PcbId, SapCode, version); } } } catch (Exception) { throw; } } return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return "store"; })); } } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } [Route("GetLastState")] public async Task GetState(String PcbId) { try { return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { var dt = GetStateDt(PcbId, 1); foreach (var item in dt.Select()) { return item["ProcessState_State"]; } return 0; })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex.ToString() + "
" + ex.InnerException.ToString()); } } private System.Data.DataTable GetStateDt(String PcbId, Int32? top = 1) { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { var topFilter = top.HasValue ? "top " + top.Value.ToString() : ""; var dt = dataacces.ExecuteQuery($"select {topFilter} [ProcessState_State] ,ProcessState_DateUtc, ProcessState_PcbId, [ProcessState_Id] from dbo.MeterProcessState where ProcessState_PcbId like '{PcbId}' order by ProcessState_DateUtc desc "); return dt; } } [Route("GetState")] public async Task GetState(String PcbId, Int32? top = 1) { try { return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { var dt = GetStateDt(PcbId, top); dt.TableName = "Result"; return dt; })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex.ToString() + "
" + ex.InnerException.ToString()); } } [Route("setSapState"), HttpPost] public async Task setSapState(String PcbId, Int32 code, String version = "-") { try { SaveSapState(PcbId, code, version); return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return "store"; })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } /// /// Send sap progress if its not allready done /// /// /// /// /// true for new ones fals for exstinsting private static Boolean SaveSapState(String PcbId, Int32 code, String version) { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { StringBuilder sb = new StringBuilder(); sb.AppendLine($" declare @PcbID as int, @order as int, @count as int,@ProgressNr as int "); sb.AppendLine($" set @PcbID = {PcbId} "); sb.AppendLine($" set @ProgressNr= {code} "); sb.AppendLine($" "); sb.AppendLine($" SELECT TOP (1) @order = [CordonelAssignedPicking_FertigungsAuftragsNr] "); sb.AppendLine($" FROM [Auftrag].[dbo].[Cordonel_AssignedPicking] "); sb.AppendLine($" where [CordonelAssignedPicking_PcbId] = @PcbID "); sb.AppendLine($" "); sb.AppendLine($" select @count = COUNT (*) from Cordonel_ProgressFeedback c "); sb.AppendLine($" inner join [Auftrag].[dbo].[Fortschrittsrueckmeldung] "); sb.AppendLine($" sap on sap.FertigungsauftragNr = @order "); sb.AppendLine($" and c.ProgressFeedbackID = sap.id "); sb.AppendLine($" and sap.Vorgangsnr = @ProgressNr and c.PcbID = @PcbID "); sb.AppendLine($" if @count =0 "); sb.AppendLine($" begin "); sb.AppendLine($" insert into [Auftrag].[dbo].[Fortschrittsrueckmeldung] "); sb.AppendLine($" select 'GNS', @order, 1, CURRENT_TIMESTAMP, @ProgressNr,null,0,null,0,'{version}','webrequest'; "); sb.AppendLine($" insert into Cordonel_ProgressFeedback "); sb.AppendLine($" select @PcbID ,@@IDENTITY "); sb.AppendLine($" end "); sb.AppendLine($" select @count "); var dt = dataacces.ExecuteQuery(sb.ToString()); if (dt.Rows[0][0].ToString() == "0") { return true; } return false; } } [Route("GetCordonelAppVersion")] public HttpResponseMessage GetCordonelAppVersion(String PcbId, Boolean returnRadioFRQ = false) { try { var query = $@" DECLARE @pcb AS INT = @{nameof(PcbId)} SELECT cap.AppId , cap.Version , cap.IsUpdatable FROM CordonelAppVersion AS cap WHERE cap.ProgressId = (SELECT TOP 1 Id FROM CordonelProgress AS cp WHERE cp.PcbId = @pcb ORDER BY cp.Id DESC)"; if (returnRadioFRQ) { query += $@" UNION SELECT CAST(-2 AS INT) AS AppId , CAST(gm.Frequenz AS nvarchar(5)) AS Version , CAST(0 AS BIT) AS IsUpdatable FROM MapPcbIdToSerialNumber AS map INNER JOIN Genesis_Meter AS gm ON gm.Seriennummer = map.MapPcbIdToSerialNumber_SerialNumber AND map.MapPcbIdToSerialNumber_Deleted = 0 WHERE map.MapPcbIdToSerialNumber_PcbId = @pcb"; } var apps = SqlConnection .CreateSqlConnection(GlobalConfig.connectionString.Value) .CreateCommand(query) .SetParameter(nameof(PcbId), PcbId) .ExecuteReader(x => new { Id = x.GetValue(0), Version = x.GetValue(1), IsUpdateable = x.GetValue(2) }); return this.Request.CreateResponse(HttpStatusCode.OK, apps); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex.ToString() + "
" + ex.InnerException.ToString()); } } [Route("SetCordonelProgress"), HttpPost] public async Task SetCordonelProgress(Int32 PcbId, String ProgressName, String version, [FromBody] List AppVersionDic) { try { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { StringBuilder sb = new StringBuilder(); sb.AppendLine($" INSERT INTO [dbo].CordonelProgress "); sb.AppendLine($" ([PcbId] "); sb.AppendLine($" ,[Date] "); sb.AppendLine($" ,[Name] "); sb.AppendLine($" ,[VersionStr]) "); sb.AppendLine($" VALUES "); sb.AppendLine($" ({PcbId} "); sb.AppendLine($" ,CURRENT_TIMESTAMP "); sb.AppendLine($" ,'{ProgressName}' "); sb.AppendLine($" ,'{version}') "); if (AppVersionDic != null && AppVersionDic.Any()) { sb.AppendLine($" declare @ProgressId as int "); sb.AppendLine($" set @ProgressId = @@IDENTITY "); foreach (var item in AppVersionDic) { sb.AppendLine($" insert into [dbo].[CordonelAppVersion] select {item.Id}, '{item.Version}',{item.IsUpdateable.GetHashCode()},@ProgressId "); } } var dt = dataacces.ExecuteQuery(sb.ToString()); } return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { return "store"; })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex); } } [Route("TaskKillByTime")] public async Task TaskKillByTime(Int32 seconds = 300) { try { return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { //if (kill) //{ // var dt = dataacces.ExecuteQuery("exec WhoLockNoFilter " + seconds); //} return 1; } })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex.ToString() + "
" + ex.InnerException.ToString()); } } [Route("GetCordonelSkipProcessStates")] public async Task GetCordonelSkipProcessStates(Int32 PcbId, Int32 OrderNumber) { try { return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => { using (var dataacces = new SqlDataAccess(GlobalConfig.connectionString.Value)) { StringBuilder sb = new StringBuilder(); sb.AppendLine($" select [id] "); sb.AppendLine($" ,[OrderNumber] "); sb.AppendLine($" ,[PcbId] "); sb.AppendLine($" ,[SkipRadioParameters] "); sb.AppendLine($" ,[SkipFWCheck] "); sb.AppendLine($" ,[SkipBatteyCheck] "); sb.AppendLine($" ,[SkipLUT] "); sb.AppendLine($" ,[Date] "); sb.AppendLine($" ,[Name] "); sb.AppendLine($" FROM [Auftrag].[dbo].[CordonelSkipChecks] "); sb.AppendLine($" where "); sb.AppendLine($" (OrderNumber = {OrderNumber} or OrderNumber is null) "); sb.AppendLine($" and (PcbId = {PcbId} or PcbId is null) "); var dt = dataacces.ExecuteQuery(sb.ToString()); if (dt.Rows.Count < 1) { return new SkipProcess { SkipBatteyCheck = false, SkipFWCheck = false, SkipLUT = false, SkipRadioParameters = false }; } var ret = new SkipProcess(); foreach (var item in dt.Select()) { try { ret.OrderNumber = OrderNumber; ret.PcbId = PcbId; ret.SkipRadioParameters = (Boolean)item["SkipRadioParameters"]; ret.SkipFWCheck = (Boolean)item["SkipFWCheck"]; ret.SkipBatteyCheck = (Boolean)item["SkipBatteyCheck"]; ret.SkipLUT = (Boolean)item["SkipLUT"]; ret.Date = (DateTime)item["Date"]; ret.Name = item["Name"].ToString(); } catch (Exception) { } } return ret; } })); } catch (Exception ex) { return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex.ToString() + "
" + ex.InnerException.ToString()); } } //[Route("GetProductionSkipAndLifeTimeInfo")] //public async Task GetProductionSkipAndLifeTimeInfo(Int32? pcbId = null, Int32? orderNumber = null) //{ // try // { // if (!pcbId.HasValue && !orderNumber.HasValue) // { // return Request.CreateResponse(HttpStatusCode.InternalServerError, new ApplicationException("No Argument")); // } // return Request.CreateResponse(HttpStatusCode.OK, await Task.Run(() => // { // using (var dataAccess = new SqlDataAccess(GlobalConfig.connectionString.Value)) // { // StringBuilder sb = new StringBuilder(); // int DefaultMaxMonthsNaRegion = 6; // int DefaultMaxMonthsEmeaRegion = 3; // Double DefaultRequiredLifeTimeYearsAssembly = 21.0; // Double DefaultRequiredLifeTimeYearsShipping = 20.0; // Double DefaultMaxDrainedBatteryLoadPercent = 10.0; // sb.AppendLine("Select IdendKey, [Value] from ProductionDefaultKeys where IdendKey in ("); // sb.AppendLine(" 'DefaultMaxMonthsNaRegion'"); // sb.AppendLine(" ,'DefaultMaxMonthsEmeaRegion'"); // sb.AppendLine(" ,'DefaultRequiredLifeTimeYearsAssembly'"); // sb.AppendLine(" ,'DefaultRequiredLifeTimeYearsShipping'"); // sb.AppendLine(" ,'DefaultMaxDrainedBatteryLoadPercent'"); // sb.AppendLine(" )"); // var dt = dataAccess.ExecuteQuery(sb.ToString()); // foreach (var item in dt.Select()) // { // switch (item["IdendKey"].ToString()) // { // case "DefaultMaxMonthsNaRegion": // int.TryParse(item["Value"].ToString(), out DefaultMaxMonthsNaRegion); // break; // case "DefaultMaxMonthsEmeaRegion": // int.TryParse(item["Value"].ToString(), out DefaultMaxMonthsEmeaRegion); // break; // case "DefaultRequiredLifeTimeYearsAssembly": // double.TryParse(item["Value"].ToString(), out DefaultRequiredLifeTimeYearsAssembly); // break; // case "DefaultRequiredLifeTimeYearsShipping": // double.TryParse(item["Value"].ToString(), out DefaultRequiredLifeTimeYearsShipping); // break; // case "DefaultMaxDrainedBatteryLoadPercent": // double.TryParse(item["Value"].ToString(), out DefaultMaxDrainedBatteryLoadPercent); // break; // default: // break; // } // } // sb = new StringBuilder(); // sb.AppendLine($" select [id] "); // sb.AppendLine($" ,[OrderNumber] "); // sb.AppendLine($" ,[PcbId] "); // sb.AppendLine($" ,[SkipRadioParametersCheck] "); // sb.AppendLine($" ,[OverrideFwCheck] "); // sb.AppendLine($" ,[OverrideLutCheck] "); // sb.AppendLine($" ,[RequiredLifeTimeYearsAssembly] "); // sb.AppendLine($" ,[RequiredLifeTimeYearsShipping] "); // sb.AppendLine($" ,[KitronProductionDate] "); // sb.AppendLine($" ,[MaxDrainedBatteryLoadPercent] "); // sb.AppendLine($" ,[MaxStorageMonths] "); // sb.AppendLine($" ,[ApprovalDate] "); // sb.AppendLine($" ,[ApproverName] "); // sb.AppendLine($" ,[ApprovalComment] "); // sb.AppendLine($" FROM [Auftrag].[dbo].[ProductionSkipAndLifeTimeInfo] "); // sb.AppendLine($" where 1 = 1 "); // if (orderNumber.HasValue) // { // sb.AppendLine($" and (OrderNumber = {orderNumber.Value} or OrderNumber is null) "); // } // if (pcbId.HasValue) // { // sb.AppendLine($" and (PcbId = {pcbId.Value} or PcbId is null) "); // } // // PcbId != null sticht ordernumber , Ordernumbner // //TODO Roland: The defaults should come from a DefaultProductionSkipAndLifeTimeInfo table // dt = dataAccess.ExecuteQuery(sb.ToString()); // if (dt.Rows.Count == 0) // { // var stat = new GenesisStatus // { // OverrideLutCheck = null, // OverrideFwCheck = null, // SkipRadioParametersCheck = false, // RequiredLifeTimeYearsAssembly = DefaultRequiredLifeTimeYearsAssembly, // RequiredLifeTimeYearsShipping = DefaultRequiredLifeTimeYearsShipping, // MaxDrainedBatteryLoadPercent = DefaultMaxDrainedBatteryLoadPercent, // }; // //TODO Roland implement defaults // // get region from order if (Region == "NA") // stat.ProductionDueDate = stat.KitronProductionDate.AddMonths(DefaultMaxMonthsNaRegion); // // else // stat.ProductionDueDate = stat.KitronProductionDate.AddMonths(DefaultMaxMonthsEmeaRegion); // return stat; // } // var ret = new GenesisStatus(); // //TODO Roland check for multiple rows take the last? // foreach (var item in dt.Select()) // { // try // { // ret.OrderNumber = orderNumber; // ret.PcbId = pcbId.ToString(); // ret.SkipRadioParametersCheck = (Boolean)item["SkipRadioParametersCheck"]; // ret.OverrideFwCheck = (int)item["OverrideFwCheck"]; // ret.OverrideLutCheck = (int)item["OverrideLutCheck"]; // //ret.RequiredLifeTimeYearsAssembly = (Double)item["RequiredLifeTimeYearsAssembly"]; // //ret.RequiredLifeTimeYearsShipping = (Double)item["RequiredLifeTimeYearsShipping"]; // //ret.MaxDrainedBatteryLoadPercent = (Double)item["MaxDrainedBatteryLoadPercent"]; // //ret.KitronProductionDate = (DateTime)item["KitronProductionDate"]; // var maxStorageMonths = (Int32)item["MaxStorageMonths"]; // ret.ProductionDueDate = ret.KitronProductionDate + TimeSpan.FromDays(30.0 * maxStorageMonths); // ret.ApprovalDate = (DateTime)item["ApprovalDate"]; // ret.ApproverName = item["ApproverName"].ToString(); // ret.ApprovalComment = item["ApprovalComment"].ToString(); // } // catch (Exception) // { // } // } // return ret; // } // })); // } // catch (Exception ex) // { // return Request.CreateResponse(HttpStatusCode.ExpectationFailed, ex.ToString() + "
" + ex.InnerException.ToString()); // } //} } }