Thiết kế website giá rẻ

Question

How can i filter the data based on time. using oracle database, asp.net web form and query. oracle database outtime column datatype is date. Pls help

HTML

<!-- Main content -->
<section class="content">
  <div class="container-fluid">
      <div class="card">

          <div class="card-body">
              <div class="row">

                  <div class="col-md-3">
                      <div class="form-group m-0">
                          <label for="OrgList">Select a Floor</label>
                          <asp:DropDownList ID="FloorList" runat="server" CssClass="form-control select2" ClientIDMode="Static"></asp:DropDownList>
                      </div>
                      <!-- /.form-group -->
                  </div>

                  <div class="col-md-3">
                      <div class="form-group m-0">
                          <label for="OrgList">Select a Section</label>
                          <asp:DropDownList ID="SectionList" runat="server" CssClass="form-control select2" ClientIDMode="Static"></asp:DropDownList>
                      </div>
                      <!-- /.form-group -->
                  </div>

                  <div class="col-md-3">
                      <label>Search by date range:</label>
                      <div class="input-group">
                          <div class="input-group-prepend">
                              <span class="input-group-text">
                                  <i class="far fa-calendar-alt"></i>
                              </span>
                          </div>
                          <asp:TextBox runat="server" ClientIDMode="Static" ID="daterange" CssClass="form-control float-right" />
                          <span class="input-group-append">
                              <asp:Button runat="server" ID="attnFilter" Text="Run" OnClick="attendance_summary_report_Click" CssClass="btn btn-info btn-flat" />
                          </span>
                      </div>
                  </div>
                  <!-- /.input group -->
              </div>
          </div>
      </div>


      <!-- Main row -->
    <div class="row">
      <div class="col-md-12">
           <div class="card">
                <!-- /.card-header -->
                <div class="card-body">
                    <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
                    <rsweb:ReportViewer ID="Attn_RV" runat="server"></rsweb:ReportViewer>
                </div>
                <!-- /.card-body -->
              </div>
              <!-- /.card -->
      </div>
    </div>
    <!-- /.row -->

</div>

jQuery

window.onload = function() {
  $(function() {
    $('#OrgList, #FloorList, #SectionList').select2({
      theme: 'bootstrap4'
    });
  })
}

//=== end function
let date = new Date();
var firstDay = new Date(date.getFullYear(), date.getMonth(), 1);
var lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);

$(document).ready(function() {
  //Date range picker
  $('#daterange').daterangepicker({
    startDate: firstDay,
    endDate: lastDay,
    ranges: {
      'Current Month': [moment().startOf('month'), moment().endOf('month')],
      'Last Month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
    },
    locale: {
      format: 'DD-MMM-YYYY'
    }
  });

});

Asp

    string date = (daterange.Text == "") ? DateTime.Now.ToString("dd-MMM-yyyy") + " - " + DateTime.Now.ToString("dd-MMM-yyyy") : daterange.Text;
    string[] datearr = date.Split(new[] { " - " }, StringSplitOptions.None);
    string from = datearr[0].Trim();
    string to = datearr[1].Trim();
    //ReportProduct(from, to, orgid);
  }
}

private void DropFloorData() {
  string orgid = Session["ORGID"].ToString();
  var item = OracleDbQuery.GetCompanies().Find(o => o.Companyid == orgid);

  using(OracleConnection con = DbConn.GetConnection(item.Companyid)) {
    string query = "select floor_id, floor_desc from floor order by floor_desc";
    OracleCommand cmd = new OracleCommand(query, con);
    cmd.CommandType = CommandType.Text;
    con.Open();
    OracleDataReader rdr = cmd.ExecuteReader();
    if (rdr.HasRows) {
      FloorList.Items.Clear();
      FloorList.DataSource = rdr;
      FloorList.DataValueField = "floor_desc";
      FloorList.DataTextField = "floor_desc";
      FloorList.DataBind();

      FloorList.Items.Insert(0, new ListItem("All Floor", "%"));
    } else {
      FloorList.DataSource = null;
      FloorList.DataBind();
      FloorList.Items.Insert(0, new ListItem("All Floor", "%"));
    }
  }
}

private void DropSectionData() {
  string orgid = Session["ORGID"].ToString();
  var item = OracleDbQuery.GetCompanies().Find(o => o.Companyid == orgid);

  using(OracleConnection con = DbConn.GetConnection(item.Companyid)) {
    string query = "select section_no, section_name from section order by section_name";
    OracleCommand cmd = new OracleCommand(query, con);
    cmd.CommandType = CommandType.Text;
    con.Open();
    OracleDataReader rdr = cmd.ExecuteReader();
    if (rdr.HasRows) {
      SectionList.Items.Clear();
      SectionList.DataSource = rdr;
      SectionList.DataValueField = "section_name";
      SectionList.DataTextField = "section_name";
      SectionList.DataBind();
      SectionList.Items.Insert(0, new ListItem("All Section", "%"));
    } else {
      SectionList.DataSource = null;
      SectionList.DataBind();
      SectionList.Items.Insert(0, new ListItem("All Section", "%"));
    }
  }
}

protected void attendance_summary_report_Click(object sender, EventArgs e) {
  string date = (daterange.Text == "") ? DateTime.Now.ToString("dd-MMM-yyyy") + " - " + DateTime.Now.ToString("dd-MMM-yyyy") : daterange.Text;
  string[] datearr = date.Split(new [] {
    " - "
  }, StringSplitOptions.None);
  string from = datearr[0].Trim();
  string to = datearr[1].Trim();
  string section = SectionList.SelectedItem.Value;
  string floor = FloorList.SelectedItem.Value;
  string orgid = Session["ORGID"].ToString();
  ReportProduct(section, floor, from, to, orgid);
}

public void ReportProduct(string section, string floor, string from, string to, string orgid) {
  Attendance_ds timecard_dt = GetData(section, floor, from, to, orgid);
  Attn_RV.ProcessingMode = ProcessingMode.Local;
  Attn_RV.SizeToReportContent = true;
  Attn_RV.Width = Unit.Percentage(100);

  Attn_RV.Height = Unit.Percentage(100);
  Attn_RV.ZoomMode = ZoomMode.Percent;
  Attn_RV.ZoomPercent = 100;
  Attn_RV.ShowZoomControl = true;
  Attn_RV.BackColor = System.Drawing.Color.DeepSkyBlue;

  foreach(RenderingExtension extension in Attn_RV.LocalReport.ListRenderingExtensions()) {
    if (extension.Name == "IMAGE" || extension.Name == "WORD") {
      FieldInfo fi = extension.GetType().GetField("m_isVisible", BindingFlags.Instance | BindingFlags.NonPublic);
      fi.SetValue(extension, false);
    }
  }

  Attn_RV.LocalReport.DataSources.Clear();
  Attn_RV.LocalReport.DataSources.Add(new ReportDataSource("AttendanceDataSet", timecard_dt.Tables[2]));
  Attn_RV.LocalReport.ReportPath = Server.MapPath("~/reports/attn_reports/attn_summary_individual.rdlc");
}

private Attendance_ds GetData(string section, string floor, string from, string to, string orgid) {
  CompanyInfo company = OracleDbQuery.GetCompanies().Find(o => o.Companyid == orgid);
  using(OracleConnection con = DbConn.GetConnection(company.Companyid)) {
    string query = OracleDbQuery.GetMonthlyAttendanceSummary(floor, section, from, to, company.Company_name);
    OracleCommand cmd = new OracleCommand(query, con);
    cmd.CommandType = CommandType.Text;
    con.Open();
    OracleDataAdapter sda = new OracleDataAdapter(cmd);
    sda.Fill(attn_ds, attn_ds.AttendanceSumIndv.ToString());
  }
  return attn_ds;

}
}

}

Oracle Query:

    WITH
    CTE_EmployeeSearchByTime AS(
        SELECTTO_CHAR(ad.att_date, 'DD-MM-YYYY') AS ATTDATE,
        eo.company_name AS COMPANY,
        eo.floor_desc AS FLOOR,
        eo.section_name AS SECTION,
        eo.line_info AS LINE,
        ep.empno AS EMPNO,
        ep.first_name AS NAME,
        eo.des_name AS DESIGNATION,
        TO_CHAR(ad.in_time, 'HH24:MI:SS') AS INTIME,
        TO_CHAR(ad.out_time, 'HH24:MI:SS') AS OUTTIME,
        ad.othour AS OTFROMemp_personal epLEFT
    JOIN emp_official eo ON
        ep.empno = eo.empnoLEFT
    JOIN attendance_details ad ON
        ep.empno = ad.empnoWHERETO_CHAR(ad.att_date, 'DD-MON-YYYY') = '{todate}' AND ad.status LIKE 'P' AND TO_CHAR(ad.out_time, 'HH24:MI:SS') BETWEEN '{starttime}' AND '{endtime}' AND eo.company_name = '{company}'
    )
SELECT
    * FROMCTE_EmployeeSearchByTimeORDER BYFLOOR,
    LINE,
    SECTION,
    TO_NUMBER(EMPNO) ASC

I want to try solve this issue. Please help. Thanks advance

Thiết kế website giá rẻ

Danh mục

Filter Data Based on Time