public DataTable Search(string Id, string AnmaltAv, string Anmalan, string UtvecklatAv, string UtvecklarKommentar, string TestatAv, string TestNotering, string ProjektId)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("EXEC SP_FIND_TESTITEM '" + Id + "','" + Anmalan + "','" + UtvecklarKommentar + "','" + TestNotering + "','" + AnmaltAv + "','" + UtvecklatAv + "','" + TestatAv + "','" + ProjektId + "'");
return(DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]);
}
private DataTable GetDatabases()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("USE Master");
sb.AppendLine("SELECT NAME FROM master.dbo.sysdatabases");
sb.AppendLine(" ORDER BY NAME");
return(DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]);
}
private void AddPersHistStatisticFromDbs(DataRow dr, string databaseName, string tableName, string fieldName)
{
StringBuilder sb = new StringBuilder();
int Antal = 0;
sb.Clear();
sb.AppendLine("USE [" + databaseName + "]");
sb.AppendLine("SELECT COUNT(*) - (SELECT COUNT(*) FROM BA_ANSTALLD_GRUND) ANTAL FROM " + tableName);
Antal = (int)DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0].Rows[0]["ANTAL"];
dr[fieldName] = Antal;
}
private void AddVersionsinfoFromDbs(DataRow dr, string databaseName, string tableName, string fieldName1, string fieldName2)
{
StringBuilder sb = new StringBuilder();
sb.Clear();
sb.AppendLine("USE [" + databaseName + "]");
sb.AppendLine("SELECT TOP 1 LOP_NR_VERSION, LOP_NR_VER FROM " + tableName);
sb.AppendLine("ORDER BY LOP_NR_VERSION DESC");
DataTable dt = DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0];
string tmp = dt.Rows[0]["LOP_NR_VERSION"].ToString();
dr[fieldName1] = tmp.Replace(",", ".");
tmp = dt.Rows[0]["LOP_NR_VER"].ToString();
dr[fieldName2] = tmp.Substring(0, tmp.Length - 2);
}
public DataTable SorteradProjektLista()
{
StringBuilder sb3 = new StringBuilder();
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT LOP_NR_PROJEKT ID , PROJEKT_ID + ' - ' + PROJEKTNAMN PROJEKT FROM BA_PROJEKT");
sb.AppendLine("WHERE AKTIV = 'J'");
sb.AppendLine("AND GETDATE() BETWEEN ISNULL(DATUM_FOM, '1900-01-01') AND ISNULL(DATUM_TOM,'2099-12-31') ");
sb.AppendLine("ORDER BY PROJEKT_ID, PROJEKTNAMN");
DataTable dt = DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0];
DataRow dr = dt.NewRow();
dr["ID"] = -1;
dr["PROJEKT"] = "--Inget värde--";
dt.Rows.InsertAt(dr, 0);
return(dt);
}
private void AddStamplingStatisticFromDbs(DataRow dr, string databaseName, string tableName, string fieldName, bool isTidreg)
{
StringBuilder sb = new StringBuilder();
int Antal = 0;
sb.Clear();
sb.AppendLine("USE [" + databaseName + "]");
sb.AppendLine("SELECT COUNT(*) ANTAL FROM " + tableName);
if (isTidreg)
{
sb.AppendLine("WHERE STATUS_TID2 IS NOT NULL");
}
else
{
sb.AppendLine("WHERE STATUS_TID2 IS NULL");
}
Antal = (int)DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0].Rows[0]["ANTAL"];
dr[fieldName] = Antal;
}
public DataTable GetDbsStatsFromProcedure()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("DECLARE @DbsName sysname");
sb.AppendLine("DECLARE @SQL_ANTALANVANDARE nvarchar(4000)");
sb.AppendLine("DECLARE @AntalAnvandare int");
sb.AppendLine("DECLARE @AntalAnstallda int");
sb.AppendLine("DECLARE @AntalStamplingar int ");
sb.AppendLine("DECLARE @AntalProjekt int");
sb.AppendLine("DECLARE @AntalMedarbSamt int");
sb.AppendLine("DECLARE @AntalKonteringsbegrepp int");
sb.AppendLine("DECLARE @AntalLoner int");
sb.AppendLine("DECLARE @AntalRevisionstillfallen int");
sb.AppendLine("DECLARE @AntalResor int");
sb.AppendLine("DECLARE @AntalRekryt int");
sb.AppendLine("USE Master");
sb.AppendLine("IF OBJECT_ID('tempdb..#TMP_DATABASER') IS NOT NULL");
sb.AppendLine("DROP TABLE #TMP_DATABASER");
sb.AppendLine("CREATE TABLE #TMP_DATABASER");
sb.AppendLine("(");
sb.AppendLine(" NAME VARCHAR(50),");
sb.AppendLine(" ANTAL_ANVANDARE INT,");
sb.AppendLine(" ANTAL_ANSTALLDA INT,");
sb.AppendLine(" ANTAL_STAMPLINGAR INT,");
sb.AppendLine(" ANTAL_PROJEKT INT,");
sb.AppendLine(" ANTAL_MEDARB_SAMT INT,");
sb.AppendLine(" ANTAL_KONTBGR INT,");
sb.AppendLine(" ANTAL_LONER INT,");
sb.AppendLine(" ANTAL_REVISIONSTILLFALLEN INT,");
sb.AppendLine(" ANTAL_RESOR INT,");
sb.AppendLine(" ANTAL_REKRYT INT");
sb.AppendLine(")");
sb.AppendLine("DECLARE TEST_CURSOR CURSOR FOR ");
sb.AppendLine("SELECT NAME FROM master.dbo.sysdatabases");
sb.AppendLine(" ORDER BY 1");
sb.AppendLine("OPEN TEST_CURSOR");
sb.AppendLine("FETCH NEXT FROM TEST_CURSOR ");
sb.AppendLine(" INTO @DbsName");
sb.AppendLine("WHILE @@FETCH_STATUS = 0");
sb.AppendLine("BEGIN");
sb.AppendLine(" BEGIN TRY");
sb.AppendLine(" --Antal användare");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalAnvandare = COUNT(*) FROM BA_ANV'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalAnvandare int output', @AntalAnvandare output");
sb.AppendLine(" --Antal anställda");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalAnstallda = COUNT(*) FROM BA_ANSTALLD_GRUND'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalAnstallda int output', @AntalAnstallda output");
sb.AppendLine(" --Antal stämplingar");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalStamplingar = COUNT(*) FROM TI_STAMPLINGAR'");
sb.AppendLine("EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalStamplingar int output', @AntalStamplingar output");
sb.AppendLine(" --Antal projekt");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalProjekt = COUNT(*) FROM PR_PROJEKT'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalProjekt int output', @AntalProjekt output");
sb.AppendLine(" --Antal medarbetarsamtal");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalMedarbSamt = COUNT(*) FROM PA_MEDARB_SAMT'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalMedarbSamt int output', @AntalMedarbSamt output");
sb.AppendLine(" --Antal konteringsbegrepp");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalKonteringsbegrepp = COUNT(*) FROM BA_KONTBGR'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalKonteringsbegrepp int output', @AntalKonteringsbegrepp output");
sb.AppendLine(" --Antal löner");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalLoner = COUNT(*) FROM BA_LONPER_PERS'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalLoner int output', @AntalLoner output");
sb.AppendLine(" --Antal revisionstillfällen");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalRevisionstillfallen = COUNT(*) FROM LR_REVTILLF'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalRevisionstillfallen int output', @AntalRevisionstillfallen output");
sb.AppendLine(" --Antal resor");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalResor = COUNT(*) FROM RE_RESOR'");
sb.AppendLine(" EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalResor int output', @AntalResor output");
sb.AppendLine(" --Antal rekryteringar");
sb.AppendLine(" SET @SQL_ANTALANVANDARE = 'USE ' + @DbsName + ' SELECT @AntalRekryt = COUNT(*) FROM PA_JOBB'");
sb.AppendLine("EXEC sp_executesql @SQL_ANTALANVANDARE,N'@AntalRekryt int output', @AntalRekryt output");
sb.AppendLine(" INSERT INTO #TMP_DATABASER (NAME, ANTAL_ANVANDARE, ANTAL_ANSTALLDA, ANTAL_STAMPLINGAR, ANTAL_PROJEKT, ANTAL_MEDARB_SAMT, ANTAL_KONTBGR, ");
sb.AppendLine(" ANTAL_LONER, ANTAL_REVISIONSTILLFALLEN, ANTAL_RESOR, ANTAL_REKRYT)");
sb.AppendLine(" VALUES (@DbsName, @AntalAnvandare, @AntalAnstallda, @AntalStamplingar, @AntalProjekt, @AntalMedarbSamt, @AntalKonteringsbegrepp,");
sb.AppendLine(" @AntalLoner, @AntalRevisionstillfallen, @AntalResor, @AntalRekryt)");
sb.AppendLine(" END TRY ");
sb.AppendLine(" BEGIN CATCH");
sb.AppendLine(" --Empty catch");
sb.AppendLine(" END CATCH ");
sb.AppendLine(" FETCH NEXT FROM TEST_CURSOR ");
sb.AppendLine(" INTO @DbsName");
sb.AppendLine("END ");
sb.AppendLine("CLOSE TEST_CURSOR");
sb.AppendLine("DEALLOCATE TEST_CURSOR");
sb.AppendLine("SELECT * FROM #TMP_DATABASER ");
return(DataSQLQuery.ExecuteSQL(sb.ToString(), true).Tables[0]);
}
