public IHttpActionResult getTotalbasedonPositionStatus(string id)
{
Console.WriteLine("in TotalbasedonPositionStatus");
string sSQL = "";
switch (id)
{
case "active":
sSQL = "select count(*) as Column1 from [ScoDatabank].[EHDB].[xferEmploymentHistory] where positionstatus LIKE 'ACTIVE%'";
break;
case "onleave":
sSQL = "select count(*) as Column1 from[ScoDatabank].[EHDB].[xferEmploymentHistory] where positionstatus LIKE 'ON LEAVE%'";
break;
case "separated":
sSQL = "select count(*) as Column1 from[ScoDatabank].[EHDB].[xferEmploymentHistory] where positionstatus LIKE 'SEPARATED%'";
break;
case "tempsep":
sSQL = "select count(*) as Column1 from[ScoDatabank].[EHDB].[xferEmploymentHistory] where positionstatus LIKE 'TEMP SEP%'";
break;
}
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getEmployeeTransactionsHistory([FromBody] EmployeeTransactionsHistory emptranshistory)
{
Console.WriteLine(emptranshistory.EmployeeSSN);
string sSQL = "select EmployeeSSN, CBID, CountyCode, AppointmentType, HistoryRemarks, PayLetterNumber, TimeBase,";
sSQL += "AppointmentTenure, AppointmentMonths, AppointmentExpirationDate, SalaryPER, PayFrequency, BasePay,";
sSQL += "SalaryRate, SalaryTotal, SalaryFull, PlusSalary, PlusSalaryExpirationDate, AnniversaryDate,";
sSQL += "AcceleratedAnniversaryDate, AlternateRange, PayrollStatus, ShiftDifferential, SpecialPay, WorkWeekGroup,";
sSQL += "FireSeasonSalaryRate, PriorServiceCode, RetirementSystem, PERSRetirementMemberDate, SafetyMember,";
sSQL += "Survivor, OASDI, RetirementRate, ExemptAuthority, PersAccountCode, PersEmployerCode, ClassType,";
sSQL += "FixedMaintMonthlyDeduction, EstablishedEarningsID1, EstablishedEarningsAmount1, EstablishedEarningsID2,";
sSQL += "EstablishedEarningsAmount2, EstablishedEarningsID3, EstablishedEarningsAmount3, ImmediatePay,";
sSQL += "LumSumSickLeave, LumSumSickHours, LumSumVaction, LumSumVactionHours, LumSumExtraHours, LumSumOvertimeHours,";
sSQL += "LumSumPayCode, LumSumUnitSerial, SeparationExpirationDate, IntermittentBeginDate1, IntermittentEndDate1,";
sSQL += "IntermittentHours1, IntermittentBeginDate2, IntermittentEndDate2, IntermittentHours2, IntermittentBeginDate3,";
sSQL += "IntermittentEndDate3, IntermittentHours3, IntermittentHoursExpect, LegalReferenceAnnuitant ";
sSQL += " from [EHDB].[xferTransactionHistory] ";
sSQL += " where EmployeeSSN = '" + emptranshistory.EmployeeSSN + "'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <EmployeeTransactionsHistory> >(json);
return(Ok(listData));
}
public IHttpActionResult getClassDataAudit()
{
Console.WriteLine("in ClassDataAudit");
string sSQL = "SELECT[CD_AUDIT_TRAIL_USER_ID] as 'AuditUserID'";
sSQL += " ,[CD_CLASS_TYPE] as 'ClassType' ";
sSQL += " ,[CD_CLASS_CODE] as 'ClassCode' ";
sSQL += " ,[CD_RECORD_EFF_CDATE] as 'EffDate' ";
sSQL += " ,[CD_PROCESSING_CDATE] as 'ProcessingDate' ";
sSQL += " ,[CD_AUDIT_OVERRIDE_CODE] as 'OverrideCode' ";
sSQL += " ,[CD_RECORD_PROCESS_CODE] as 'ProcessCode' ";
sSQL += " ,[CD_MAJOR_OCCUPATION_GRP_ID] as 'OccuGroupID' ";
sSQL += " ,[CD_MAJOR_OCCUPATION_GRP_NBR] as 'OccGroupNumber' ";
sSQL += " ,[CD_CLASS_ESTABLISH_CDATE] as 'ClassEstDate' ";
sSQL += " ,[CD_CLASS_STATUS_IND] as 'ClassStatusInd' ";
sSQL += " ,[CD_CBID_DESIGNATION_ID] as 'CBIDDesignationID' ";
sSQL += " ,[CD_CBID_UNIT_ID] as 'CBIDUnitID' ";
sSQL += " ,[CD_FULL_CLASS_TITLE_1_60] as 'ClassTitle1' ";
sSQL += " ,[CD_FULL_CLASS_TITLE_61_120] as 'ClassTitle2' ";
sSQL += " ,[CD_ABBR_CLASS_TITLE] as 'AbbrClassTitle' ";
sSQL += " ,[CD_PAY_LETTER_NBR] as 'LetterNumber' ";
sSQL += " FROM[ScoDatabank].[CSP].[xferClassData] ";
sSQL += " where CD_PROCESSING_CDATE LIKE '" + System.DateTime.Today.Year + "%' ";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <ClassDataAudit> >(json);
return(Ok(listData));
}
public IHttpActionResult getTotalUniqueEmployeesinTransactions()
{
Console.WriteLine("in TotalUniqueEmployeesinTransactions");
string sSQL = "select distinct EmployeeSSN from [EHDB].[xferTransactionHistory]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getTotalINDEmployees()
{
Console.WriteLine("in TotalINDEmployees");
string sSQL = "select count(*) as 'Column1' from [EHDB].[xferTransactionHistory] where timebase = 'IND'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getTotalACAPaymentRecords()
{
Console.WriteLine("in TotalACA");
string sSQL = "select count(*) from [ACA].[xferPaymentHistory]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getTotalTIMSTables()
{
Console.WriteLine("in TotalTIMSTables");
string sSQL = "select count(*) from [TIMS].[xferT_TIMS_TABLE]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getTotalSalaryRanges()
{
Console.WriteLine("in TotalSalaryRanges");
string sSQL = "select count(*) from [CSP].[xferSalaryRange]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getEmployeeACATransactions()
{
Console.WriteLine("in get");
string sSQL = "select * from [ACA].[xferTransaction] where TransactionSSN = '614140684'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <EmployeeACATransactions> >(json);
return(Ok(listData));
}
public IHttpActionResult getEmployeePositionDetails(string id)
{
Console.WriteLine(id);
string sSQL = "select * from [ACA].[xferPosition] where PositionSSN = '" + id + "'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <EmployeePositionDetails> >(json);
return(Ok(listData));
}
public IHttpActionResult getEmployeePaymentHistory([FromBody] EmployeeDetails empdetails)
{
Console.WriteLine(empdetails.EmployeeSSN);
string sSQL = "select * from [ACA].[xferPaymentHistory] where PaymentHistorySSN = '" + empdetails.EmployeeSSN + "'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <EmployeePaymentHistory> >(json);
return(Ok(listData));
}
public IHttpActionResult getUserListing()
{
Console.WriteLine("in getUserListing");
string sSQL = "";
sSQL = "select UserID, Username, ContactName from DBO.UserMaster";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getTotalCivilEmployeeHistory()
{
Console.WriteLine("in TotalCivilEmployeeHistory");
string sSQL = "SELECT count(*) as 'Column1'";
sSQL += " FROM [EHDB].[xferEmploymentHistory] where employmenttype LIKE 'CIVIL%'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getClassKeyInformation()
{
Console.WriteLine("in get");
string sSQL = "select PK_CLASS_KEY_ID as 'ClassKeyID', CK_CLASS_TYPE as 'ClassType', CK_CLASS_CODE as 'ClassCode' from [CSP].[xferClassKey]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <ClassKey> >(json);
return(Ok(listData));
}
public IHttpActionResult getMenus()
{
Console.WriteLine("in getMenus");
string sSQL = "";
sSQL = "select MenuID, MenuTitle, IsParent, MenuLink, ParentID from APP.MenuMaster";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getEmployeeACATransactions([FromBody] EmployeeDetails empdetails)
{
Console.WriteLine(empdetails.EmployeeSSN);
string sSQL = "select * from [ACA].[xferTransaction] where TransactionSSN = '" + empdetails.EmployeeSSN + "' and PositionsequenceNumber = '" + empdetails.FutureUse1 + "'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <EmployeeACATransactions> >(json);
return(Ok(listData));
}
public IHttpActionResult getTotalEmployeeTransactions()
{
Console.WriteLine("in TotalCSUCEmployeeHistory");
string sSQL = "SELECT count(*) as 'Column1'";
sSQL += " FROM [EHDB].[xferTransactionHistory]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getDataRefreshLog()
{
Console.WriteLine("in DataRefreshLog");
string sSQL = "SELECT [DataRefreshID] as 'ID', [SchemaAbbreviation] as 'Abbr', [SchemaName] as 'Schema'";
sSQL += " ,[Date] as 'DateRefreshed', [Time] as 'Timerefreshed'";
sSQL += " FROM[APP].[DataRefreshLog]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getTotalEmployeeWithoutCity()
{
Console.WriteLine("in getTotalEmployeeWithoutCity");
string sSQL = "";
sSQL = "select count(*) as 'Total'";
sSQL = sSQL + " from aca.xferEmployee WHERE City = ''";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getUserDetails(int id)
{
Console.WriteLine("in getUserDetails");
string sSQL = "";
sSQL = "select UserID, LTRIM(RTRIM(Username)) as Username, Password, LTRIM(RTRIM(ContactName)) as ContactName from DBO.UserMaster";
sSQL = sSQL + " where UserID =" + id;
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult logUserAudit(int id)
{
Console.WriteLine("in logUserAudit");
string sSQL = "";
sSQL = "insert into App.UserAudit(UserID, Action, Date, Time) values(";
sSQL += id + ", 'Login Event', '" + System.DateTime.Today.Month + "/" + System.DateTime.Today.Day + "/" + System.DateTime.Today.Year + "', '" + DateTime.Now.ToString() + "')";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getUserAllChildMenus(int id)
{
Console.WriteLine("in getUserAllChildMenus");
string sSQL = "";
sSQL = "select MenuID, LTRIM(RTRIM(MenuTitle)) as MenuTitle, LTRIM(RTRIM(MenuLink)) as MenuLink, LTRIM(RTRIM(menuicon)) as menuicon from app.menumaster ";
sSQL = sSQL + " where parentid = " + id;
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getAllEmployeeWithoutAddressReport()
{
Console.WriteLine("in EmployeeWithoutAddressReport");
string sSQL = "";
sSQL = "select EmployeeSSN as 'EmployeeSSN', LTRIM(RTRIM(FirstName)) + ' ' + LTRIM(RTRIM(MiddleInitial)) + ' ' + LTRIM(RTRIM(LastName)) as 'LastName',";
sSQL = sSQL + " City as 'City', Street as 'Street', State as 'State', ZipCode as 'Zip'";
sSQL = sSQL + " from aca.xferEmployee WHERE Street = '' or City = '' or State = '' or ZipCode = ''";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getTotalAuditUsersinCurrentYearMonth(int id)
{
Console.WriteLine("in TotalAuditUsersClassDatainCurrentYearMonth");
string sSQL = "SELECT count(*) as 'Column1'";
sSQL += " FROM [CSP].[xferClassData] ";
sSQL += " where CD_PROCESSING_CDATE LIKE ";
sSQL += " CAST((CAST(YEAR(GETDATE()) as VARCHAR) + ";
sSQL += " RIGHT('0' + CAST(" + id + " as VARCHAR), 2) + '%') as VARCHAR) ";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult getUserParentMenus(int id)
{
Console.WriteLine("in getUserParentMenus");
string sSQL = "";
sSQL = "select MenuID, LTRIM(RTRIM(MenuTitle)) as MenuTitle, LTRIM(RTRIM(MenuLink)) as MenuLink, LTRIM(RTRIM(menuicon)) as menuicon from app.menumaster where menuid in ";
sSQL = sSQL + "(select menuid from app.menumaster where parentid = 0)";
sSQL = sSQL + "and menuid in (select menuid from app.usermenumapping ";
sSQL = sSQL + " where userid = " + id + ")";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}
public IHttpActionResult searchClassificationsbyClassCode([FromBody] SearchClassifications searchclass)
{
Console.WriteLine(searchclass.classificationname);
string sSQL = "select pk_class_data_id as 'ClassDataID', fk_class_key_id as 'ClassKeyID', CD_Full_class_title_1_60 as 'Title', cd_abbr_class_title as 'Abbr', ";
sSQL += " CD_class_code as 'ClassCode' from csp.xferclassdata where CD_class_code LIKE '" + searchclass.classcode.Trim() + "%'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <ClassificationDataSearchResults> >(json);
return(Ok(listData));
}
public IHttpActionResult getEmployeeTransactionsHistoryListing([FromBody] EmployeeTransactionsHistory emptranshistory)
{
Console.WriteLine(emptranshistory.EmployeeSSN);
string sSQL = "SELECT EmployeeSSN, PositionSequence,PositionNumber,TransactionEffectiveDate";
sSQL += " ,TransactionCode,EntryDate,PPSDReference1,HistoryType,HistoryRemarks ";
sSQL += " from [EHDB].[xferTransactionHistory] ";
sSQL += " where EmployeeSSN = '" + emptranshistory.EmployeeSSN + "'";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <EmployeeTransactionsHistory> >(json);
return(Ok(result));
}
public IHttpActionResult searchClassKey([FromBody] ClassKey classkey)
{
Console.WriteLine(classkey.ClassKeyID);
string sSQL = "select pk_class_key_id as 'ClassKeyID', ck_class_type as 'ClassType', ck_class_code as 'ClassCode' ";
sSQL += " from csp.xferclasskey where pk_class_key_id = " + classkey.ClassKeyID;
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
var json = JsonConvert.SerializeObject(result);
var listData = JsonConvert.DeserializeObject <List <ClassKey> >(json);
return(Ok(listData));
}
public IHttpActionResult getTotalACATransRecords()
{
try
{
Console.WriteLine("in TotalACATransRecords\n");
string sSQL = "select count(*) from [ACA].[xferTransaction]";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
Console.Write("Results returned!\n");
return(Ok(result));
} catch (Exception e)
{
Console.Write(e.StackTrace);
return(null);
}
}
public IHttpActionResult getAllEmployeeWithOutHealthCoverageReport()
{
Console.WriteLine("in AllEmployeeWithOutHealthCoverageReport");
string sSQL = "";
sSQL = "select a.EmployeeSSN as 'EmployeeSSN', LTRIM(RTRIM(a.FirstName)) + ' ' + LTRIM(RTRIM(a.MiddleInitial)) + ' ' + LTRIM(RTRIM(a.LastName)) as 'LastName',";
sSQL = sSQL + " d.StatusCode as 'ACAStatus', e.PositionNumber as 'PositionNo', e.PositionSequenceNumber as 'PosSeqNo'";
sSQL = sSQL + " from aca.xferEmployee as a, aca.xferHealthRecord as b, ACA.xferPosition as c,";
sSQL = sSQL + " ACA.xferTransaction as d, aca.xferPaymentHistory as e";
sSQL = sSQL + " where(b.HealthConvertDate = null or b.HealthConvertDate = '') and";
sSQL = sSQL + " a.EmployeeSSN = b.HealthSSN and a.EmployeeSSN = c.PositionSSN and";
sSQL = sSQL + " a.EmployeeSSN = d.TransactionSSN and a.EmployeeSSN = e.PaymentHistorySSN";
var appBlock = new SqlDbConnectionBaseClass();
var result = appBlock.ExecuteForSelect(sSQL);
return(Ok(result));
}