public string RunFilter() { TransDate[] daylist = new TransDate[370]; FilterString = ""; FilterFlag = true; _cmd = new SqlCommand(); _cmd.Connection = frmMainForm._MasterCon; _cmd.CommandTimeout = 2000; if (clbDep.CheckedItems.Count > 0 && clbDep.CheckedItems.Count < clbDep.Items.Count) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clbDep.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += " [CRD_DEPARTMENT]= '" + clbDep.CheckedItems[v].ToString() + "' "; } FilterString += " )"; } catch { } } if (clb_company.CheckedItems.Count > 0 && clb_company.CheckedItems.Count < clb_company.Items.Count) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clb_company.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += " [COMPANY]= '" + clb_company.CheckedItems[v].ToString() + "' "; } FilterString += " )"; } catch { } } if (clb_location.CheckedItems.Count > 0 && clb_location.CheckedItems.Count < clb_location.Items.Count) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clb_location.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += " [LOCATION]= '" + clb_location.CheckedItems[v].ToString() + "' "; } FilterString += " )"; } catch { } } if (check_name.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + " ( CRD_NAME like='" + txt_name.Text + "' )"; } if (check_number.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } if (rbut_find_in.Checked || rbut_find_out.Checked || rbut_find_att.Checked || rbut_find_abs.Checked) { FilterString += initalFilter + " ( CRD_NO='" + txt_num.Text + "' )"; } else { FilterString += initalFilter + " ( CRD_NO='" + txt_num.Text + "' )"; } //FilterString += initalFilter + " ( TRN_CARD_NO='" + txt_num.Text + "' )"; } return(FilterString); }
public string[] RunFilter() { TransDate[] daylist = new TransDate[370]; FilterString = ""; FilterString2 = ""; //string filter = ""; FilterFlag = false; if (DateCheckbox.Checked) { try { /*if (dtpDateFrom.Value == dtpDateTo.Value) * { * FilterString += " where DATE >= '" + dtpDateFrom.Value.ToString("yyyy/MM/dd")+"' and DATE <= '" + dtpDateFrom.Value.ToString("yyyy/MM/dd") + "'"; * } * else * {*/ FilterString += " where dbo.DateTimeRemoveTime(DATE) >= '" + dtpDateFrom.Value.ToString("yyyy/MM/dd") + "' and dbo.DateTimeRemoveTime(DATE) <='" + dtpDateTo.Value.ToString("yyyy/MM/dd") + "'"; //} } catch { MessageBox.Show("Please Validate your entered Date !", "Error"); } FilterFlag = true; } FilterString2 = FilterString; if (!cbVisitor.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + " [Employee Name] NOT LIKE '%V' "; FilterString2 += initalFilter + " [NAME] NOT LIKE '%V' "; } if (TimecheckBox.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } try { if (txtfromTime.Text != "00:00" && txtToTime.Text != "00:00") { FilterString += initalFilter + " convert(Datetime,[Time]) >= '" + txtfromTime.Text + "' and convert(Datetime,[Time]) <='" + txtToTime.Text + "' "; FilterString2 += initalFilter + " convert(Datetime,[Time]) >= '" + txtfromTime.Text + "' and convert(Datetime,[Time]) <='" + txtToTime.Text + "' "; } else if (txtfromTime.Text != "00:00") { FilterString += initalFilter + " convert(Datetime,[Time]) >= '" + txtfromTime.Text + "' "; FilterString2 += initalFilter + " convert(Datetime,[Time]) >= '" + txtfromTime.Text + "' "; } else if (txtToTime.Text != "00:00") { FilterString += initalFilter + " convert(Datetime,[Time]) <='" + txtToTime.Text + "' "; FilterString2 += initalFilter + " convert(Datetime,[Time]) <='" + txtToTime.Text + "' "; } } catch { //MessageBox.Show("Please Validate your entered Date !", "Error"); } FilterFlag = true; } ///////////////////////////////////////////////////// if (checkBox2.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } try { FilterString += initalFilter + "[Employee Name] = '" + cmbEmployee.SelectedValue.ToString() + "'"; FilterString2 += initalFilter + "[NAME] = '" + cmbEmployee.SelectedValue.ToString() + "'"; } catch { } } if (DepCheckBox.Checked && !checkBox2.Checked && clbEmp.CheckedItems.Count > 0) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ( "; FilterFlag = true; } else { initalFilter = " and ( "; } try { for (int v = 0; v < clbEmp.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += "[Employee Name] = '" + clbEmp.CheckedItems[v].ToString() + "' "; FilterString2 += "[NAME] = '" + clbEmp.CheckedItems[v].ToString() + "' "; } FilterString += " ) "; } catch { } } else if (DepCheckBox.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } try { FilterString += initalFilter + "[Department] = '" + cmbDep.SelectedValue.ToString() + "'"; FilterString2 += initalFilter + "[Department] = '" + cmbDep.SelectedValue.ToString() + "'"; } catch { } } if (radioButtonIn.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + "Place = 'In'"; } else if (radioButtonOut.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + "Place = 'Out'"; } if (cmbLocation.SelectedIndex > 0) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + "LOCATION ='" + cmbLocation.SelectedItem.ToString() + "'"; FilterString2 += initalFilter + "LOCATION ='" + cmbLocation.SelectedItem.ToString() + "'"; } if (cmbCompany.SelectedIndex > 0) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + "COMPANY = '" + cmbCompany.SelectedItem.ToString() + "'"; FilterString2 += initalFilter + "COMPANY = '" + cmbCompany.SelectedItem.ToString() + "'"; } if (check_name.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + " ( [Employee Name]='" + txt_name.Text + "' )"; } if (check_number.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + " ([Card Number] ='" + txt_num.Text + "' )"; } //////////////////////////////////////////////// ////////////////////////////// //FilterFlag = false; ////////////////////// string[] xx = new string[2]; xx[0] = FilterString; xx[1] = FilterString2; return(xx); }
public string RunFilter() { TransDate[] daylist = new TransDate[370]; FilterString = ""; FilterFlag = true; _cmd = new SqlCommand(); _cmd.Connection = frmMainForm._MasterCon; _cmd.CommandTimeout = 2000; if (clbDep.CheckedItems.Count > 0 && clbDep.CheckedItems.Count < clbDep.Items.Count) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clbDep.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += " [CRD_DEPARTMENT]= '" + clbDep.CheckedItems[v].ToString() + "' "; } FilterString += " )"; } catch { } } if (clb_company.CheckedItems.Count > 0 && clb_company.CheckedItems.Count < clb_company.Items.Count) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clb_company.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += " [COMPANY]= '" + clb_company.CheckedItems[v].ToString() + "' "; } FilterString += " )"; } catch { } } if (clb_location.CheckedItems.Count > 0 && clb_location.CheckedItems.Count < clb_location.Items.Count) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clb_location.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += " [LOCATION]= '" + clb_location.CheckedItems[v].ToString() + "' "; } FilterString += " )"; } catch { } } return(FilterString); }
//private void DepCheckBox_CheckedChanged(object sender, EventArgs e) //{ // if (DepCheckBox.Checked) // { // if (cmbDep.SelectedValue != null) // { // _cmd = new SqlCommand("select * from EMPLOYEE_CARDS where CRD_DEPARTMENT='" + cmbDep.SelectedValue.ToString() + "' Order by CRD_NAME", frmMainForm._MasterCon); // _da = new SqlDataAdapter(_cmd); // DataTable dtEmployees = new DataTable(); // _da.Fill(dtEmployees); // cmbEmployee.DataSource = dtEmployees; // cmbEmployee.DisplayMember = "CRD_NAME"; // cmbEmployee.ValueMember = "CRD_NAME"; // cmbEmployee.Text = ""; // } // cmbDep.Enabled = true; // cmbDep_SelectedIndexChanged(null, null); // //RunFilter(); // } // else // { // cmbDep.Enabled = false; // //cmbDep.SelectedItem = null; // //RunFilter(); // } //} public void RunFilter() { TransDate[] daylist = new TransDate[370]; FilterString = ""; FilterFlag = false; _cmd = new SqlCommand(); _cmd.Connection = frmMainForm._MasterCon; _cmd.CommandTimeout = 2000; if (DateCheckbox.Checked) { try { FilterString += " where ( DATE >= '" + dtpDateFrom.Value.ToString("yyyy/MM/dd") + "' and DATE <='" + dtpDateTo.Value.ToString("yyyy/MM/dd") + "' )"; } catch { MessageBox.Show("Please Validate your entered Date !", "Error"); } FilterFlag = true; } else { //FilterFlag = false; } //if (DepCheckBox.Checked && !checkBox2.Checked && clbEmp.CheckedItems.Count > 0) if (clbDep.CheckedItems.Count > 0 && clbDep.CheckedItems.Count < clbDep.Items.Count) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clbDep.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } FilterString += " [Department]= '" + clbDep.CheckedItems[v].ToString() + "' "; } FilterString += " )"; } catch { } } if (clbEmp.CheckedItems.Count > 0) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where ("; FilterFlag = true; } else { initalFilter = " and ("; } try { for (int v = 0; v < clbEmp.CheckedItems.Count; v++) { if (v != 0) { FilterString += " or "; } if (v == 0) { FilterString += initalFilter; } string NAME = clbEmp.CheckedItems[v].ToString(); NAME = NAME.Replace("'", "''"); FilterString += "[NAME] = '" + NAME + "' "; } FilterString += " )"; } catch { } } //else if (DepCheckBox.Checked) //{ // string initalFilter = ""; // if (!FilterFlag) // { // initalFilter = " where "; // FilterFlag = true; // } // else // initalFilter = " and "; // try // { // FilterString += initalFilter + "[Department] = '" + cmbDep.SelectedValue.ToString() + "'"; // } // catch { } //} if (cmbLocation.SelectedIndex > 0) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + "LOCATION ='" + cmbLocation.SelectedItem.ToString() + "'"; } if (cmbCompany.SelectedIndex > 0) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + "COMPANY = '" + cmbCompany.SelectedItem.ToString() + "'"; } if (!cbVisitor.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + " NAME NOT LIKE '%V' "; } if (TimecheckBox.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } try { if (txtfromTime.Text != "00:00" && txtToTime.Text != "00:00") { FilterString += initalFilter + " convert(Datetime,[Enter Time]) >='" + txtfromTime.Text + "' and convert(Datetime,[Exit Time]) <='" + txtToTime.Text + "' "; } else if (txtfromTime.Text != "00:00") { FilterString += initalFilter + " convert(Datetime,[Enter Time]) >='" + txtfromTime.Text + "' "; } else if (txtToTime.Text != "00:00") { FilterString += initalFilter + " convert(Datetime,[Exit Time]) <='" + txtToTime.Text + "' "; } } catch { //MessageBox.Show("Please Validate your entered Date !", "Error"); } //FilterFlag = false; } if (check_name.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + " ( NAME like='" + txt_name.Text + "' )"; } if (check_number.Checked) { string initalFilter = ""; if (!FilterFlag) { initalFilter = " where "; FilterFlag = true; } else { initalFilter = " and "; } FilterString += initalFilter + " ( CARD='" + txt_num.Text + "' )"; } ///////////////////////////////////////////////////// //if (checkBox2.Checked) //{ // string initalFilter = ""; // if (!FilterFlag) // { // initalFilter = " where "; // FilterFlag = true; // } // else // initalFilter = " and "; // try // { // FilterString += initalFilter + " NAME = '" + cmbEmployee.SelectedValue.ToString() + "' "; // } // catch { } //} //////////////////////////////////////////////// ////////////////////////////// //FilterFlag = false; ////////////////////// try { //if (frmMainForm._ConfigPlace == "cairo") //{ if (checkBoxSHIFT.Checked) { _cmd.CommandText = "select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date ,[Exit Time],[WORK HOURS],[Location],[Company] from VSHIFT_FINAL " + FilterString + " order by [DATE],[name] "; Str = "select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date,[Exit Time],[WORK HOURS],[Location],[Company] from VSHIFT_FINAL " + FilterString; Str2 = "Select * from VSHIFT_FINAL " + FilterString; } else { _cmd.CommandText = "select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date,[Exit Time],[WORK HOURS],[Location],[Company], IN_MACHINE, OUT_MACHINE from VTransaction_IN_OUT_FINAL " + FilterString + " order by [DATE],[name] "; Str = "select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date,[Exit Time],[WORK HOURS],[Location],[Company], IN_MACHINE, OUT_MACHINE from VTransaction_IN_OUT_FINAL " + FilterString; Str2 = "Select * from VTransaction_IN_OUT_FINAL " + FilterString; } //} //else //{ //string command = " where "; /*for (int c = 0; c < frmMainForm._Machin.Length; c++) * { * if (frmMainForm._Machin[c] != null) * { * if (c != 0) * { command += " or "; } * command += " TRN_MACHINE= '" + frmMainForm._Machin[c] + "' "; * } * }*/ /*if (checkBoxSHIFT.Checked) * { * _cmd.CommandText = "select * from (select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date,[Exit Time],[WORK HOURS],[Location],[Company] from VSHIFT_FINAL " + FilterString + " ) as teet order by [DATE],[Name]"; * Str = "select * from (select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date,[Exit Time],[WORK HOURS],[Location],[Company] from VSHIFT_FINAL " + FilterString + " ) as [s] " ; * Str2 = "Select * from (Select * from VSHIFT_FINAL " + FilterString + " ) as [s] " ; * } * else * { * _cmd.CommandText = "select * from (select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date ,[Exit Time],[WORK HOURS],[Location],[Company], IN_MACHINE, OUT_MACHINE from VTransaction_IN_OUT_FINAL " + FilterString + " ) as teet order by [DATE],[Name]"; * Str = "select * from (select CARD,NAME,JOB,Department,DATE,[ENT DATE],[Enter Time],ext_date,[Exit Time],[WORK HOURS],[Location],[Company], IN_MACHINE, OUT_MACHINE from VTransaction_IN_OUT_FINAL " + FilterString + " ) as [s] "; * Str2 = "Select * from (Select * from VTransaction_IN_OUT_FINAL " + FilterString + " ) as [s] " ; * }*/ //} _cmd.CommandTimeout = 2000; _da = new SqlDataAdapter(_cmd); _Dt = new DataTable(); _Dt.Reset(); _da.Fill(_Dt); //////////////////////////////////////////////////////////////////////////////////// ultraGrid1.DataSource = _Dt; //////////////////////////////////////////////////////////////////////////////////// } catch (Exception ex) { //MessageBox.Show("Error Please Validate your entered Date ! : " + ex.Message, "Error"); } //try //{ // //_cmd = new SqlCommand("select CARD,NAME,JOB,Department,DATE,[Enter Time],[Exit Time],[WORK HOURS],[Location],[Company] from VTransaction_IN_OUT_FINAL " + FilterString + " order by [date],[name] ", frmMainForm._MasterCon); // //_cmd.CommandTimeout = 2000; // _cmd.CommandText = "select CARD,NAME,JOB,Department,DATE,[Enter Time],[Exit Time],[WORK HOURS],[Location],[Company] from VTransaction_IN_OUT_FINAL " + FilterString + " order by [date],[name] "; // //if(x==0) // Str ="select CARD,NAME,JOB,Department,DATE,[Enter Time],[Exit Time],[WORK HOURS],[Location],[Company] from VTransaction_IN_OUT_FINAL " + FilterString; // _da = new SqlDataAdapter(_cmd); // //Str = _da.SelectCommand.CommandText; // _Dt = new DataTable(); // _Dt.Reset(); // _da.Fill(_Dt); // //////////////////////////////////////////////////////////////////////////////////// // ultraGrid1.DataSource = _Dt; // //////////////////////////////////////////////////////////////////////////////////// //} //catch (Exception ex) //{ // //MessageBox.Show("Error Please Validate your entered Date ! : " + ex.Message, "Error"); //} bool searchFlag = false; ultraGrid1.DataSource = _Dt; //ultraGrid1.DisplayLayout.Bands[0].SortedColumns.Add(ultraGrid1.DisplayLayout.Bands[0].Columns["Date"], false); }