public static Overall GetHR()
{
String connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
HRCore hRCore = new HRCore();
Overall overall = new Overall();
SqlConnection con = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("select (select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gippromo a where a.codfunci not in (select codfunci from[196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'funcisit'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].giprotac a where a.codfunci not in (select codfunci from[196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'post'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipcashb a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'benefits'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipfalta a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'leave'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipemvac a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'entitlements'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipmedex a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'medicalinfo'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipmedif a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'medicaladd'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipbenef a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'beneficiaries'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipmedal a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'awards'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipdisci a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'disciplinary'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipemmem a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'unions'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipanexo a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'attachments'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipaentr a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'allowances'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipdscnt a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'deduction'," +
"(select count(*) from[196.3.190.28].[gippreprod2016].[dbo].gipempbk a where a.codfunci not in (select codfunci from [196.3.190.28].[gippreprod2016].[dbo].gipfunci b where codfunci is not null)) 'banks';", con);
try
{
con.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
overall.FuncSit = int.Parse(reader["funcisit"].ToString());
overall.Posts = int.Parse(reader["post"].ToString());
overall.Benefits = int.Parse(reader["benefits"].ToString());
overall.Leave = int.Parse(reader["leave"].ToString());
overall.Entitlements = int.Parse(reader["entitlements"].ToString());
overall.MedicalInfo = int.Parse(reader["medicalinfo"].ToString());
overall.MedicalAdd = int.Parse(reader["medicaladd"].ToString());
overall.Beneficiaries = int.Parse(reader["beneficiaries"].ToString());
overall.Awards = int.Parse(reader["awards"].ToString());
overall.DisciplinaryAction = int.Parse(reader["disciplinary"].ToString());
overall.Unions = int.Parse(reader["unions"].ToString());
overall.Attachments = int.Parse(reader["attachments"].ToString());
overall.Allowances = int.Parse(reader["allowances"].ToString());
overall.Deductions = int.Parse(reader["deduction"].ToString());
overall.BankAccounts = int.Parse(reader["banks"].ToString());
overall.HRCore = GetIntegrity();
}
}
con.Close();
return(overall);
}
catch (Exception ex)
{
ex.ToString();
}
return(overall);
}
public static HRCore GetIntegrity()
{
String connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
HRCore hRCore = new HRCore();
SqlConnection con = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("select" +
" (select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPCONTA A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '') + (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPCONTA A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL))) 'Contacts', " +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPCONTA A) 'AllContacts'," +
"(select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPHABFU A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '')+ (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPHABFU A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL)))'Qualifications'," +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPHABFU A) 'AllQualifications'," +
"(select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPADDRE A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '')+ (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPADDRE A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL)))'Address'," +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPADDRE A) 'AllAddress'," +
"(select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPEMERC A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '')+ (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPEMERC A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL)))'EmergencyContacts'," +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPEMERC A) 'AllEmergencyContacts'," +
"(select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPCCART A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '')+ (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPCCART A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL)))'Documents'," +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPCCART A) 'AllDocuments'," +
"(select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPAGREG A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '')+ (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPAGREG A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL)))'Dependents'," +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPAGREG A) 'AllDependents'," +
"(select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPPEMEM A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '')+ (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPPEMEM A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL)))'Professional Membership'," +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPPEMEM A) 'AllMemberships'," +
"(select(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPWORKP A, [196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA B WHERE A.CODPESOA = B.CODPESOA AND B.CONTRIB = '')+ (select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPWORKP A WHERE A.CODPESOA NOT IN(SELECT CODPESOA FROM[196.3.190.28].GIPPREPROD2016.dbo.GIPPESOA WHERE CODPESOA IS NOT NULL)))'Work Permits'," +
"(select Count(*) from[196.3.190.28].GIPPREPROD2016.dbo.GIPWORKP A) 'AllPermits'; ", con);
try
{
con.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
hRCore.Address = int.Parse(reader["Address"].ToString());
hRCore.Contacts = int.Parse(reader["Contacts"].ToString());
hRCore.Qualification = int.Parse(reader["Qualifications"].ToString());
hRCore.EmergencyContacts = int.Parse(reader["EmergencyContacts"].ToString());
hRCore.Documents = int.Parse(reader["Documents"].ToString());
hRCore.Dependents = int.Parse(reader["Dependents"].ToString());
hRCore.ProfessionalMemberships = int.Parse(reader["Professional Membership"].ToString());
hRCore.WorkPermits = int.Parse(reader["Work Permits"].ToString());
hRCore.AllAddress = int.Parse(reader["Address"].ToString()) / int.Parse(reader["AllAddress"].ToString());
hRCore.AllContacts = int.Parse(reader["AllContacts"].ToString());
hRCore.AllDependents = int.Parse(reader["AllDependents"].ToString());
hRCore.AllQualifications = int.Parse(reader["AllQualifications"].ToString());
hRCore.AllDocuments = int.Parse(reader["AllDocuments"].ToString());
hRCore.AllMemberships = int.Parse(reader["AllMemberships"].ToString());
hRCore.AllEmergencyContacts = int.Parse(reader["AllEmergencyContacts"].ToString());
hRCore.AllPermits = int.Parse(reader["AllPermits"].ToString());
}
}
con.Close();
}
catch (Exception ex)
{
ex.ToString();
}
return(hRCore);
}
