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);
}
