Пример #1
0
        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));
        }
Пример #2
0
        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));
        }
Пример #3
0
        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));
        }
Пример #4
0
        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));
        }
Пример #5
0
        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));
        }
Пример #6
0
        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));
        }
Пример #7
0
        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));
        }
Пример #8
0
        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));
        }
Пример #11
0
        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));
        }
Пример #13
0
        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));
        }
Пример #14
0
        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));
        }
Пример #17
0
        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));
        }
Пример #18
0
        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));
        }
Пример #19
0
        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));
        }
Пример #23
0
        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));
        }
Пример #24
0
        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));
        }
Пример #26
0
        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));
        }
Пример #27
0
        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));
        }
Пример #28
0
        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));
        }
Пример #29
0
 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);
     }
 }
Пример #30
0
        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));
        }