public BindingList <int> GetAllKP() { CreateConnection(); BindingList <int> PKList = new BindingList <int>(); try { using (SqlCommand command = new System.Data.SqlClient.SqlCommand()) { connection.Open(); command.Connection = connection; command.CommandText = SQLProvider.GetQuery(SQLProvider.SQLStatic.GettAllKP).ToString(); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { PKList.Add((int)dr[0]); } } } catch (SqlException ex) { throw new RepositoryException("GetAllKP", ex); } finally { if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); } } return(PKList); }
public DataTable GetPipelineElements(DateTime?weldDateFrom, DateTime?weldDateTo) { ISQLFlexible tempSQLObject; tempSQLObject = SQLProvider.GetQuery( SQLProvider.SQLStatic.GetJointsByDate) .WhereAnd().Where("wr.MinDate", "<=", string.Format("N'{0}-{1}-{2}'", weldDateTo.Value.Year, weldDateTo.Value.Month, weldDateTo.Value.Day)) .WhereAnd().Where("wr.MinDate", ">=", string.Format("N'{0}-{1}-{2}'", weldDateFrom.Value.Year, weldDateFrom.Value.Month, weldDateFrom.Value.Day)); return(GetPipelineElements(tempSQLObject.ToString())); }
public DataTable GetPipelineElements(string pipeNumber, string[] pipeTypes) { ISQLFlexible tempSQLObject; string types = string.Concat(" N'", Guid.NewGuid(), "'"); foreach (var cpt in pipeTypes) { types = string.Concat(types, ", N'", cpt, "'"); } types = string.Concat("(", types, ")"); tempSQLObject = SQLProvider.GetQuery( SQLProvider.SQLStatic.GetPipeByParametersPieces) .WhereAnd().Where("p.number", "LIKE", string.Concat(" N'", pipeNumber, "%'")) .WhereAnd().Where("PmSt.type", "IN", types); return(GetPipelineElements(tempSQLObject.ToString())); }
public BindingList <decimal> CountPipe(DateTime startDate, DateTime finalDate) { CreateConnection(); BindingList <decimal> countPipe = new BindingList <decimal>(); try { using (SqlCommand command = new System.Data.SqlClient.SqlCommand()) { connection.Open(); command.Connection = connection; command.Parameters.AddWithValue("@startDate", startDate); command.Parameters.AddWithValue("@finalDate", finalDate); command.CommandText = SQLProvider.GetQuery(SQLProvider.SQLStatic.CountPipesInformation).ToString(); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { countPipe.Add(dr[0] == System.DBNull.Value ? (decimal)(0) : (decimal)((int)dr[0])); countPipe.Add(dr[1] == System.DBNull.Value ? (decimal)(0) : (decimal)((int)dr[1])); countPipe.Add(dr[2] == System.DBNull.Value ? (decimal)(0) : (decimal)dr[2]); } } } catch (SqlException ex) { throw new RepositoryException("CountPipe", ex); } finally { if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); } } return(countPipe); }
public DataSet CountWeldInf(DateTime startDate, DateTime finalDate) { CreateConnection(); DataSet pipeDataSet = new DataSet(); BindingList <Pipe> pipes = new BindingList <Pipe>(); try { using (SqlDataAdapter adapter = new SqlDataAdapter()) { using (SqlCommand command = new System.Data.SqlClient.SqlCommand()) { connection.Open(); adapter.TableMappings.Add("Table", "Pipe"); command.Connection = connection; command.Parameters.AddWithValue("@startDate", startDate); command.Parameters.AddWithValue("@finalDate", finalDate); command.CommandText = SQLProvider.GetQuery(SQLProvider.SQLStatic.CountPipesWeldInformation).ToString(); adapter.SelectCommand = command; adapter.Fill(pipeDataSet); } } } catch (SqlException ex) { throw new RepositoryException("CountWeldInf", ex); } finally { if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); } } return(pipeDataSet); }
public DataSet GetReleaseNotes(DateTime startDate, DateTime finalDate) { CreateConnection(); DataSet releaseNoteDataSet = new DataSet(); try { using (SqlDataAdapter adapter = new SqlDataAdapter()) { using (SqlCommand command = new System.Data.SqlClient.SqlCommand()) { connection.Open(); adapter.TableMappings.Add("Table", "ReleaseNote"); command.Connection = connection; command.Parameters.AddWithValue("@startDate", startDate); command.Parameters.AddWithValue("@finalDate", finalDate); command.CommandText = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetReleaseNotes).ToString(); adapter.SelectCommand = command; adapter.Fill(releaseNoteDataSet); } } } catch (SqlException ex) { throw new RepositoryException("GetReleaseNotes", ex); } finally { if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); } } return(releaseNoteDataSet); }
public DataSet GetPipesByStatus(DateTime startDate, DateTime finalDate, List <Guid> categories, MillReportType reportType, List <string> statuses, bool previewFlag = false) { CreateConnection(); DataSet pipeDataSet = new DataSet(); try { using (SqlDataAdapter adapter = new SqlDataAdapter()) { using (SqlCommand command = new System.Data.SqlClient.SqlCommand()) { connection.Open(); adapter.TableMappings.Add("Table", "Pipe"); command.Connection = connection; //input search criteria value command.Parameters.AddWithValue("@startDate", startDate); command.Parameters.AddWithValue("@finalDate", finalDate); //temporary SQLobject ISQLFlexible tempSQLObject; switch (reportType) { case MillReportType.ByCategories: { var categoryParameters = new string[categories.Count]; for (int i = 0; i < categories.Count; i++) { categoryParameters[i] = string.Format("@Category{0}", i); command.Parameters.AddWithValue(categoryParameters[i], categories[i]); } var statusParameters = new string[statuses.Count]; for (int j = 0; j < statuses.Count; j++) { statusParameters[j] = string.Format("@Status{0}", j); command.Parameters.AddWithValue(statusParameters[j], statuses[j]); } string requiredFrequency = "'" + InspectionFrequencyType.R.ToString() + "'"; tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllActivePipesByDate).WhereAnd().Where("Pipe.isActive", "=", "1").WhereAnd().Where("PipeTest.frequencyType", "=", requiredFrequency); if (previewFlag) { tempSQLObject.Top(1); } tempSQLObject = (categories.Count != 0) ? tempSQLObject.WhereAnd().Where("PipeTest.categoryId", "IN", "(" + string.Join(", ", categoryParameters) + ")") : tempSQLObject; if (statuses.Count != 0) { tempSQLObject = tempSQLObject.WhereAnd().Where("PipeTestResult.status", "IN", "(" + string.Join(", ", statusParameters) + ")"); } command.CommandText = tempSQLObject.ToString(); }; break; case MillReportType.ByProducing: tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllProduced).WhereAnd().Where("PipeMillStatus", "=", "'Produced'").WhereAnd().Where("Pipe.isActive", "=", "1"); if (previewFlag) { tempSQLObject.Top(1); } command.CommandText = tempSQLObject.ToString(); break; case MillReportType.ByShipped: tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllShipped).WhereAnd().Where("PipeMillStatus", "=", "'Shipped'").WhereAnd().Where("Pipe.isActive", "=", "1"); if (previewFlag) { tempSQLObject.Top(1); } command.CommandText = tempSQLObject.ToString(); break; default: { throw new Exception("Cannot form query"); } } adapter.SelectCommand = command; adapter.Fill(pipeDataSet); } } } catch (SqlException ex) { throw new RepositoryException("GetPipesByStatus", ex); } finally { if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); } } return(pipeDataSet); }
public DataTable GetPipelineElements() { return(GetPipelineElements(SQLProvider.GetQuery(SQLProvider.SQLStatic.GetPipelinePieces).ToString())); }