Exemplo n.º 1
0
 public static Report_Audit getAccessReportObj()
 {
     if (auditReportObj == null)
     {
         auditReportObj = new Report_Audit();
     }
     return(auditReportObj);
 }
 public static Report_Audit getAccessReportObj()
 {
     if (auditReportObj == null)
     {
         auditReportObj = new Report_Audit();
     }
     return auditReportObj;
 }
Exemplo n.º 3
0
        /* public List<ReportRow> getData(String badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String wildCardType, String wildCardData)
         * {
         *   Report_Audit reportObj = Report_Audit.getAccessReportObj();
         *   SqlConnection conn = null;
         *
         *    SqlDataReader sqlreader = null;
         *    try
         *    {
         *        // create and open a connection object
         *        conn = ConnectionManager.getConnection();
         *        conn.Open();
         *
         *        String query = "";
         *        query = "SELECT TOP 1000 [FIRST_NAME],[LAST_NAME],[COMPANY_NAME],[DIVISION],[EMPLOYEE_ID],[CATEGORY_NAME],[BADGE_ID] FROM  [view_rs_audit_report_1]";
         *
         *
         *        Report_Audit.isConditionSelected = false;
         *
         *        if (!badgeId.Equals("null"))
         *        {
         *            if (Report_Audit.isConditionSelected)
         *                query = query + " AND   [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'";
         *            else
         *                query = query + " WHERE   [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'";
         *            Report_Audit.isConditionSelected = true;
         *        }
         *
         *        if (!companyId.Equals("null"))
         *        {
         *            if (Report_Audit.isConditionSelected)
         *                query = query + " AND   [view_rs_audit_report_1].COMPANY_ID = " + companyId;
         *            else
         *                query = query + " WHERE   [view_rs_audit_report_1].COMPANY_ID = " + companyId;
         *            Report_Audit.isConditionSelected = true;
         *        }
         *
         *
         *        if (!personId.Equals("null"))
         *        {
         *            if (Report_Audit.isConditionSelected)
         *                query = query + " AND   [view_rs_audit_report_1].PERSON_ID = " + personId;
         *            else
         *                query = query + " WHERE   [view_rs_audit_report_1].PERSON_ID = " + personId;
         *            Report_Audit.isConditionSelected = true;
         *        }
         *
         *        if (!empId.Equals("null"))
         *        {
         *            if (Report_Audit.isConditionSelected)
         *                query = query + " AND   [view_rs_audit_report_1].EMPLOYEE_ID = " + empId;
         *            else
         *                query = query + " WHERE   [view_rs_audit_report_1].EMPLOYEE_ID = " + empId;
         *            Report_Audit.isConditionSelected = true;
         *        }
         *
         *        if (!categoryId.Equals("null"))
         *        {
         *            if (Report_Audit.isConditionSelected)
         *                query = query + " AND   [view_rs_audit_report_1].CATEGORY_ID = " + categoryId;
         *            else
         *                query = query + " WHERE   [view_rs_audit_report_1].CATEGORY_ID = " + categoryId;
         *            Report_Audit.isConditionSelected = true;
         *        }
         *
         *        if (!divisionId.Equals("null"))
         *        {
         *            if (Report_Audit.isConditionSelected)
         *                query = query + " AND   [view_rs_audit_report_1].DIVISION_ID = " + divisionId;
         *            else
         *                query = query + " WHERE   [view_rs_audit_report_1].DIVISION_ID = " + divisionId;
         *            Report_Audit.isConditionSelected = true;
         *        }
         *
         *
         *        query = getWildCardQueryString(wildCardType, wildCardData, query);
         *
         *        SqlCommand command = new SqlCommand(query, conn);
         *        List<ReportRow> rowList = new List<ReportRow>();
         *        int count = 0;
         *        using (sqlreader = command.ExecuteReader())
         *        {
         *            while (sqlreader.Read())
         *            {
         *                Report_Audit report = new Report_Audit();
         *                report.CompanyName = sqlreader.GetSqlValue(2).ToString().Trim();
         *                report.FirstName = sqlreader.GetSqlValue(0).ToString().Trim();
         *                report.LastName = sqlreader.GetSqlValue(1).ToString().Trim();
         *                report.Division = sqlreader.GetSqlValue(3).ToString().Trim();
         *                report.EmpId = sqlreader.GetSqlValue(4).ToString().Trim();
         *                report.Category = sqlreader.GetSqlValue(5).ToString().Trim();
         *                report.Badge = sqlreader.GetSqlValue(6).ToString().Trim();
         *                report.Name = report.FirstName + " " + report.LastName;
         *
         *                ReportRow repRow = new ReportRow();
         *                repRow.id = (++count).ToString();
         *                repRow.datarow = report;
         *                rowList.Add(repRow);
         *            }
         *        }
         *
         *        return rowList;
         *    }
         *    finally
         *    {
         *        if (conn != null)
         *        {
         *            conn.Close();
         *        }
         *        if (sqlreader != null)
         *        {
         *            sqlreader.Close();
         *        }
         *    }
         *
         *
         * }*/


        public List <ReportRow> getData(String badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String badgeStatus, String wildCardType, String wildCardData)
        {
            Report_Audit  reportObj = Report_Audit.getAccessReportObj();
            SqlConnection conn      = null;

            SqlDataReader sqlreader = null;

            try
            {
                // create and open a connection object
                conn = ConnectionManager.getConnection();
                conn.Open();

                String query = "";

                query = "SELECT distinct comp.companyId AS COMPANY_ID, comp.companyName AS COMPANY_NAME, div.divisionId AS DIVISION_ID, div.divisionName AS DIVISION, p.employee AS EMPLOYEE_ID,p.id AS PERSON_ID, p.first_name AS FIRST_NAME, p.last_name AS LAST_NAME, ";
                query = query + " c.description AS CATEGORY_NAME,c.id AS CATEGORY_ID, b.unique_id AS BADGE_ID, p.address3 AS ADDRESS,p.address5 AS ZIP_CODE,bsts.cond_desc as BADGE_STATUS";

                query = query + " FROM person p, department d, category c, person_category pc,badge b, rs_company comp, rs_division div, badgests bsts ";
                query = query + " WHERE  ";
                query = query + " b.bid is NOT NULL AND (LEN(b.bid) = 11 OR LEN(b.bid) = 12) ";
                query = query + " AND b.person_id is NOT NULL ";
                query = query + " AND c.id = pc.category_id AND  p.id = pc.person_id ";
                query = query + " AND d.id = p.department AND b.person_id = p.id AND  d.user1 Like comp.companyName AND d.user2 like div.divisionName AND bsts.id = b.status AND b.unique_id IS NOT NULL AND b.unique_id <> ''";

                Report_Audit.isConditionSelected = true;

                if (!badgeId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                    {
                        query = query + " AND b.bid like '" + badgeId + "'";
                    }
                    else
                    {
                        query = query + " WHERE b.bid like '" + badgeId + "'";
                    }
                    Report_Audit.isConditionSelected = true;
                }

                if (!companyId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                    {
                        query = query + " AND comp.companyId = " + companyId;
                    }
                    else
                    {
                        query = query + " WHERE comp.companyId = " + companyId;
                    }
                    Report_Audit.isConditionSelected = true;
                }


                if (!personId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                    {
                        query = query + " AND p.employee = " + personId;
                    }
                    else
                    {
                        query = query + " WHERE p.employee = " + personId;
                    }
                    Report_Audit.isConditionSelected = true;
                }

                if (!empId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                    {
                        query = query + " AND p.employee = " + empId;
                    }
                    else
                    {
                        query = query + " WHERE p.employee = " + empId;
                    }
                    Report_Audit.isConditionSelected = true;
                }

                if (!categoryId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                    {
                        query = query + " AND c.id = " + categoryId;
                    }
                    else
                    {
                        query = query + " WHERE c.id = " + categoryId;
                    }
                    Report_Audit.isConditionSelected = true;
                }

                if (!divisionId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                    {
                        query = query + " AND div.divisionId = " + divisionId;
                    }
                    else
                    {
                        query = query + " WHERE div.divisionId = " + divisionId;
                    }
                    Report_Audit.isConditionSelected = true;
                }

                if (!badgeStatus.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                    {
                        query = query + " AND b.status = " + badgeStatus;
                    }
                    else
                    {
                        query = query + " WHERE b.status = " + badgeStatus;
                    }
                    Report_Audit.isConditionSelected = true;
                }

                query = getWildCardquery(wildCardType, wildCardData, query);

                SqlCommand command = new SqlCommand(query, conn);
                command.CommandTimeout = 300;
                List <ReportRow> rowList = new List <ReportRow>();
                int count = 0;
                using (sqlreader = command.ExecuteReader())
                {
                    while (sqlreader.Read())
                    {
                        Report_Audit report = new Report_Audit();
                        report.CompanyName = sqlreader.GetSqlValue(1).ToString().Trim();
                        report.FirstName   = sqlreader.GetSqlValue(6).ToString().Trim();
                        report.LastName    = sqlreader.GetSqlValue(7).ToString().Trim();
                        report.Division    = sqlreader.GetSqlValue(3).ToString().Trim();
                        report.EmpId       = sqlreader.GetSqlValue(4).ToString().Trim();
                        report.Category    = sqlreader.GetSqlValue(8).ToString().Trim();
                        report.Badge       = sqlreader.GetSqlValue(10).ToString().Trim();
                        report.Name        = report.FirstName + " " + report.LastName;
                        report.Status      = sqlreader.GetSqlValue(13).ToString().Trim();

                        ReportRow repRow = new ReportRow();
                        repRow.id      = (++count).ToString();
                        repRow.datarow = report;
                        rowList.Add(repRow);
                    }
                }

                return(rowList);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (sqlreader != null)
                {
                    sqlreader.Close();
                }
            }
        }
        /* public List<ReportRow> getData(String badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String wildCardType, String wildCardData)
        {
            Report_Audit reportObj = Report_Audit.getAccessReportObj();
            SqlConnection conn = null;

             SqlDataReader sqlreader = null;
             try
             {
                 // create and open a connection object
                 conn = ConnectionManager.getConnection();
                 conn.Open();

                 String query = "";
                 query = "SELECT TOP 1000 [FIRST_NAME],[LAST_NAME],[COMPANY_NAME],[DIVISION],[EMPLOYEE_ID],[CATEGORY_NAME],[BADGE_ID] FROM  [view_rs_audit_report_1]";

                 Report_Audit.isConditionSelected = false;

                 if (!badgeId.Equals("null"))
                 {
                     if (Report_Audit.isConditionSelected)
                         query = query + " AND   [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'";
                     else
                         query = query + " WHERE   [view_rs_audit_report_1].BADGE_ID like '" + badgeId+"'";
                     Report_Audit.isConditionSelected = true;
                 }

                 if (!companyId.Equals("null"))
                 {
                     if (Report_Audit.isConditionSelected)
                         query = query + " AND   [view_rs_audit_report_1].COMPANY_ID = " + companyId;
                     else
                         query = query + " WHERE   [view_rs_audit_report_1].COMPANY_ID = " + companyId;
                     Report_Audit.isConditionSelected = true;
                 }

                 if (!personId.Equals("null"))
                 {
                     if (Report_Audit.isConditionSelected)
                         query = query + " AND   [view_rs_audit_report_1].PERSON_ID = " + personId;
                     else
                         query = query + " WHERE   [view_rs_audit_report_1].PERSON_ID = " + personId;
                     Report_Audit.isConditionSelected = true;
                 }

                 if (!empId.Equals("null"))
                 {
                     if (Report_Audit.isConditionSelected)
                         query = query + " AND   [view_rs_audit_report_1].EMPLOYEE_ID = " + empId;
                     else
                         query = query + " WHERE   [view_rs_audit_report_1].EMPLOYEE_ID = " + empId;
                     Report_Audit.isConditionSelected = true;
                 }

                 if (!categoryId.Equals("null"))
                 {
                     if (Report_Audit.isConditionSelected)
                         query = query + " AND   [view_rs_audit_report_1].CATEGORY_ID = " + categoryId;
                     else
                         query = query + " WHERE   [view_rs_audit_report_1].CATEGORY_ID = " + categoryId;
                     Report_Audit.isConditionSelected = true;
                 }

                 if (!divisionId.Equals("null"))
                 {
                     if (Report_Audit.isConditionSelected)
                         query = query + " AND   [view_rs_audit_report_1].DIVISION_ID = " + divisionId;
                     else
                         query = query + " WHERE   [view_rs_audit_report_1].DIVISION_ID = " + divisionId;
                     Report_Audit.isConditionSelected = true;
                 }

                 query = getWildCardQueryString(wildCardType, wildCardData, query);

                 SqlCommand command = new SqlCommand(query, conn);
                 List<ReportRow> rowList = new List<ReportRow>();
                 int count = 0;
                 using (sqlreader = command.ExecuteReader())
                 {
                     while (sqlreader.Read())
                     {
                         Report_Audit report = new Report_Audit();
                         report.CompanyName = sqlreader.GetSqlValue(2).ToString().Trim();
                         report.FirstName = sqlreader.GetSqlValue(0).ToString().Trim();
                         report.LastName = sqlreader.GetSqlValue(1).ToString().Trim();
                         report.Division = sqlreader.GetSqlValue(3).ToString().Trim();
                         report.EmpId = sqlreader.GetSqlValue(4).ToString().Trim();
                         report.Category = sqlreader.GetSqlValue(5).ToString().Trim();
                         report.Badge = sqlreader.GetSqlValue(6).ToString().Trim();
                         report.Name = report.FirstName + " " + report.LastName;

                         ReportRow repRow = new ReportRow();
                         repRow.id = (++count).ToString();
                         repRow.datarow = report;
                         rowList.Add(repRow);
                     }
                 }

                 return rowList;
             }
             finally
             {
                 if (conn != null)
                 {
                     conn.Close();
                 }
                 if (sqlreader != null)
                 {
                     sqlreader.Close();
                 }
             }

        }*/
        public List<ReportRow> getData(String badgeId, String personId, String companyId, String divisionId, String empId, String categoryId, String badgeStatus,String wildCardType, String wildCardData)
        {
            Report_Audit reportObj = Report_Audit.getAccessReportObj();
            SqlConnection conn = null;

            SqlDataReader sqlreader = null;
            try
            {
                // create and open a connection object
                conn = ConnectionManager.getConnection();
                conn.Open();

                String query = "";

                query = "SELECT distinct comp.companyId AS COMPANY_ID, comp.companyName AS COMPANY_NAME, div.divisionId AS DIVISION_ID, div.divisionName AS DIVISION, p.employee AS EMPLOYEE_ID,p.id AS PERSON_ID, p.first_name AS FIRST_NAME, p.last_name AS LAST_NAME, ";
                query = query + " c.description AS CATEGORY_NAME,c.id AS CATEGORY_ID, b.unique_id AS BADGE_ID, p.address3 AS ADDRESS,p.address5 AS ZIP_CODE,bsts.cond_desc as BADGE_STATUS";

                query = query + " FROM person p, department d, category c, person_category pc,badge b, rs_company comp, rs_division div, badgests bsts ";
                query = query + " WHERE  ";
                query = query + " b.bid is NOT NULL AND (LEN(b.bid) = 11 OR LEN(b.bid) = 12) ";
                query = query + " AND b.person_id is NOT NULL ";
                query = query + " AND c.id = pc.category_id AND  p.id = pc.person_id ";
                query = query + " AND d.id = p.department AND b.person_id = p.id AND  d.user1 Like comp.companyName AND d.user2 like div.divisionName AND bsts.id = b.status AND b.unique_id IS NOT NULL AND b.unique_id <> ''";

                Report_Audit.isConditionSelected = true;

                if (!badgeId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                        query = query + " AND b.bid like '" + badgeId + "'";
                    else
                        query = query + " WHERE b.bid like '" + badgeId + "'";
                    Report_Audit.isConditionSelected = true;
                }

                if (!companyId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                        query = query + " AND comp.companyId = " + companyId;
                    else
                        query = query + " WHERE comp.companyId = " + companyId;
                    Report_Audit.isConditionSelected = true;
                }

                if (!personId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                        query = query + " AND p.employee = " + personId;
                    else
                        query = query + " WHERE p.employee = " + personId;
                    Report_Audit.isConditionSelected = true;
                }

                if (!empId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                        query = query + " AND p.employee = " + empId;
                    else
                        query = query + " WHERE p.employee = " + empId;
                    Report_Audit.isConditionSelected = true;
                }

                if (!categoryId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                        query = query + " AND c.id = " + categoryId;
                    else
                        query = query + " WHERE c.id = " + categoryId;
                    Report_Audit.isConditionSelected = true;
                }

                if (!divisionId.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                        query = query + " AND div.divisionId = " + divisionId;
                    else
                        query = query + " WHERE div.divisionId = " + divisionId;
                    Report_Audit.isConditionSelected = true;
                }

                if (!badgeStatus.Equals("null"))
                {
                    if (Report_Audit.isConditionSelected)
                        query = query + " AND b.status = " + badgeStatus;
                    else
                        query = query + " WHERE b.status = " + badgeStatus;
                    Report_Audit.isConditionSelected = true;
                }

                query = getWildCardquery(wildCardType, wildCardData, query);

                SqlCommand command = new SqlCommand(query, conn);
                command.CommandTimeout = 300;
                List<ReportRow> rowList = new List<ReportRow>();
                int count = 0;
                using (sqlreader = command.ExecuteReader())
                {
                    while (sqlreader.Read())
                    {
                        Report_Audit report = new Report_Audit();
                        report.CompanyName = sqlreader.GetSqlValue(1).ToString().Trim();
                        report.FirstName = sqlreader.GetSqlValue(6).ToString().Trim();
                        report.LastName = sqlreader.GetSqlValue(7).ToString().Trim();
                        report.Division = sqlreader.GetSqlValue(3).ToString().Trim();
                        report.EmpId = sqlreader.GetSqlValue(4).ToString().Trim();
                        report.Category = sqlreader.GetSqlValue(8).ToString().Trim();
                        report.Badge = sqlreader.GetSqlValue(10).ToString().Trim();
                        report.Name = report.FirstName + " " + report.LastName;
                        report.Status = sqlreader.GetSqlValue(13).ToString().Trim();

                        ReportRow repRow = new ReportRow();
                        repRow.id = (++count).ToString();
                        repRow.datarow = report;
                        rowList.Add(repRow);
                    }
                }

                return rowList;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (sqlreader != null)
                {
                    sqlreader.Close();
                }
            }
        }