public void _getData( string queryString )
{
radHeader1.Checked = true;
dt = new DataTable();
dtCache = new DataTable();
datamethods2 dtmth = new datamethods2();
if (useRIMSConnString == true) dt = dtmth._dataTable_NoParameter_RIMS(queryString); // use RIMS connstring
else dt = dtmth._dataTable_NoParameter(queryString); // use RnS connString
if (dtmth.sqlUserError == string.Empty)
{
dv = new DataView();
dv = dt.DefaultView;
grdSearch.DataSource = dv;
dtCache = dt;
}
if (strHeader1.Trim() == string.Empty)
{
radHeader1.Text = "ID";
}
else
{
radHeader1.Text = strHeader1;
}
if (strHeader2.Trim() == string.Empty)
{
radHeader2.Text = "Description";
radHeader2.Visible = false;
}
else
{
radHeader2.Text = strHeader2;
}
if (strHeader3.Trim() == string.Empty)
{
radHeader3.Text = "Description 2";
radHeader3.Visible = false;
}
else
{
radHeader3.Text = strHeader3;
}
if (strHeader4.Trim() == string.Empty)
{
radHeader4.Text = "Description 3";
radHeader4.Visible = false;
}
else
{
radHeader4.Text = strHeader4;
}
}
private void button1_Click(object sender, EventArgs e)
{
datamethods2 dmth = new datamethods2();
if (radrecrnoonly.Checked == true && txtsearchvalue.Text.Trim() != string.Empty)
{
database2BindingSource.DataSource = database2TableAdapter.GetDataByRecrnoOnly(prv_recrno.Trim());
this.reportViewer1.RefreshReport();
}
// for like
if (radlikeonly.Checked == true && txtsearchvalue.Text.Trim() != string.Empty)
{
try
{
string selValue = cmblikeselection.SelectedItem.ToString();
String sqlSelect = @"select distinct * from database2 "
+ " where ltrim(rtrim(" + selValue.Trim() + ")) LIKE '"
+ txtsearchvalue.Text.Trim() + "%'";
DataTable dt = new DataTable();
dt = dmth._dataTable_NoParameter(sqlSelect, ConfigurationManager.ConnectionStrings["rnsConString"].ConnectionString);
if (dmth.sqlUserError.Trim() == string.Empty)
{
// bind the datasource to the selected query datatable
if (dt.Rows.Count > 0)
{
database2BindingSource.DataSource = dt;
this.reportViewer1.RefreshReport();
}
else MessageBox.Show("Records records not found!", "RnS Notification");
}
else
{
MessageBox.Show(dmth.sqlUserError.Trim());
MessageBox.Show("Error occured, pls, try again(1)!", "RnS Notification");
dt.Dispose();
}
}
catch
{
dmth.sqlUserError.Trim();
MessageBox.Show("Error occured, pls, try again(2)!", "RnS Notification");
}
}
if (radall.Checked == true )
{
database2BindingSource.DataSource = database2TableAdapter.GetData();
this.reportViewer1.RefreshReport();
}
}
private void populateGrid()
{
string strid = "'" + txtid.Text + "'";
String sqlSelect = @"select * from candidatecallup where recrno = " + strid;
//DataTable dt = new DataTable();
datamethods2 dtmth = new datamethods2();
dt_callup.Rows.Clear();
dt_callup = dtmth._dataTable_NoParameter(sqlSelect);
if (dtmth.sqlUserError == string.Empty)
{
if (dt_callup.Rows.Count > 0)
{
_addColumns(dt_callup);
}
}
else
{
MessageBox.Show(dtmth.sqlUserError, "RnS Notification");
}
}
private void populateGrid()
{
string strClientId = "'" + txtclientid.Text +"'";
string strRequestId = "'" + Program._searchedValue.Trim() + "'";
//if (!= string.Empty) txtrequestid.Text = Program._searchedValue;
String sqlSelect = @"select * from clientrequest where clientid = " + strClientId +
" and requestid = " + strRequestId;
DataTable dt = new DataTable();
datamethods2 dtmth = new datamethods2();
dt = dtmth._dataTable_NoParameter(sqlSelect);
if (dtmth.sqlUserError == string.Empty)
{
if (dt.Rows.Count > 0)
{
txtrequestid.Text = Program._searchedValue.Trim();
dtpdeadline.Value = Convert.ToDateTime(dt.Rows[0]["drequest"]);
dtprequesteddate.Value = Convert.ToDateTime(dt.Rows[0]["reqdate"]);
txttitle.Text = Program._searchedValue2;
if (Convert.ToBoolean(dt.Rows[0]["status"]) == true)
{
chkactive.Checked = true;
chkactive.Enabled = false;
dtpdeadline.Enabled = false;
}
_addColumns(dt);
}
else
{
// create a new request id
string strTime = DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
string strdate = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
string newRequestId = txtclientid.Text.Trim().ToUpper() + "/" + strdate + "/" + strTime;
txtrequestid.Text = newRequestId;
dtpdeadline.Text = DateTime.Now.Date.ToString();
txttitle.Text = "";
}
_formatCells();
}
else
{
MessageBox.Show(dtmth.sqlUserError,"RnS Notification");
}
txtrequestid.Enabled = false;
//mskrequestdate.Focus();
}
private void _getDownloaded()
{
string strQuery = string.Empty;
strQuery = "select recrno,Surname, firstname, middlename, Initials from database1";
datamethods2 dmth = new datamethods2();
DataTable dt = new DataTable();
dt = dmth._dataTable_NoParameter(strQuery);
if (dmth.sqlUserError == String.Empty)
{
if (dt.Rows.Count > 0)
{
dgvDestination.Rows.Clear();
for (int x = 0; x < dt.Rows.Count; x++)
{
int n = dgvDestination.Rows.Add();
dgvDestination.Rows[n].Cells[0].Value = dt.Rows[x]["recrno"].ToString().Trim();
dgvDestination.Rows[n].Cells[1].Value = dt.Rows[x]["surname"].ToString();
dgvDestination.Rows[n].Cells[2].Value = dt.Rows[x]["firstname"].ToString().Trim() + " " + dt.Rows[x]["middlename"].ToString().Trim();
dgvDestination.Rows[n].Cells[3].Value = dt.Rows[x]["initials"].ToString().Trim();
dgvDestination.Rows[n].Cells[4].Value = true;
}
}
else
{
MessageBox.Show("Records not available! ", "RnS Notification");
}
}
else
{
MessageBox.Show(dmth.sqlUserError, "RnS Notification");
}
}
private void saveRecords_database1()
{
string strRecrno = String.Empty;
string sqlInsertwhere = string.Empty;
List<string> lstRecrno = new List<string>();
int _totalSaved = 0;
// get all the recrno
for (int x = 0; x < grdCandidate.Rows.Count; x++)
{
if (grdCandidate.Rows[x].Cells[0].Value.ToString() != null && grdCandidate.Rows[x].Cells[0].Value.ToString().Trim() != string.Empty)
{
if (Convert.ToBoolean(grdCandidate.Rows[x].Cells[4].Value) == true) lstRecrno.Add(grdCandidate.Rows[x].Cells[0].Value.ToString().Trim());
}
}
if (lstRecrno.Count > 0)
{
sqlInsertwhere = " where recrno = '" + lstRecrno[0] + "'";
if (lstRecrno.Count - 1 > 0)
{
for (int x = 1; x < lstRecrno.Count; x++)
{
sqlInsertwhere += " or recrno = '" + lstRecrno[x] + "'";
}
}
}
if (sqlInsertwhere.Trim() == string.Empty) // do insert
{
MessageBox.Show("Select not made", "RnS Notification");
return;
}
// save the records
datamethods2 dthm = new datamethods2();
DataTable dt = new DataTable();
string sqlQueury = "Select distinct * from database1 ";
sqlQueury += sqlInsertwhere;
MessageBox.Show(sqlQueury);
dt = dthm._dataTable_NoParameter(sqlQueury);
if (dthm.sqlUserError == string.Empty)
{
if (dt.Rows.Count > 0)
{
_totalSaved = 0;
for (int x = 0; x < dt.Rows.Count; x++)
{
// insert into database 2
string sqlInsert = string.Empty;
sqlInsert = @" Insert into database2 "
+ "(recrno,surname,firstname,middlename,initials,address, "
+ " title,sex,age,phone,email,lgacode,lgadescr,birthdate,gradecode,gradedescr,screened,aptest,active,"
+ " qualdescr,qualcode,manager,testsult,modifiedby,"
+ " inview,documented,medical,interviewed,shl,induction)"
+ " values ('" + dt.Rows[x]["recrno"].ToString() + "',"
+ "'" + dt.Rows[x]["surname"].ToString() + "',"
+ "'" + dt.Rows[x]["firstname"].ToString() + "',"
+ "'" + dt.Rows[x]["middlename"].ToString() + "',"
+ "'" + dt.Rows[x]["initials"].ToString() + "',"
+ "'" + dt.Rows[x]["address"].ToString() + "',"
+ "'" + dt.Rows[x]["title"].ToString() + "',"
+ "'" + dt.Rows[x]["sex"].ToString() + "',"
+ "'" + (DateTime.Now.Year - Convert.ToDateTime(dt.Rows[x]["birthdate"]).Year).ToString() + "',"
+ "'" + dt.Rows[x]["phone"].ToString() + "',"
+ "'" + dt.Rows[x]["email"].ToString() + "',"
+ "'" + dt.Rows[x]["lgacode"].ToString() + "',"
+ "'" + dt.Rows[x]["lgadescr"].ToString() + "',"
+ "'" + dt.Rows[x]["birthdate"].ToString() + "',"
+ "'" + " " + "',"
+ "'" + " " + "',"
+ "'" + false + "',"
+ "'" + false + "',"
+ "'" + false + "',"
+ "'" + " " + "',"
+ "'" + " " + "',"
+ "'" + Program.pbUserName.Trim()+ "',"
+ "'" + " " + "',"
+ "'" + " " + "',"
+ "'" + false + "',"
+ "'" + false + "',"
+ "'" + false + "',"
+ "'" + false + "',"
+ "'" + false + "',"
+ "'" + false
+ ")";
MessageBox.Show(sqlInsert);
dthm._update_Insert_NoParameter(sqlInsert);
if (dthm.sqlUserError.Trim() != string.Empty) MessageBox.Show(dthm.sqlUserError, "RnS Notification");
else
{
string strTime = DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + x.ToString();
// insert into candidtaerequest 2
sqlInsert = string.Empty;
//sqlInsert = @" Insert into candidatecallup "
// + "(recrno,reqid,position,requestid,clientid,datesent, "
// + " interview,ippased,employed,remarks)"
// + " values ('" + dt.Rows[x]["recrno"].ToString() + "',"
// + "'" + strTime + "',"
// + "'" + lblposition.Text + "',"
// + "'" + txtrequestid.Text + "',"
// + "'" + txtclientid.Text + "',"
// + "'" + "01/01/1900" + "',"
// + "'" + false + "',"
// + "'" + false + "',"
// + "'" + false + "',"
// + "'" + " " + "'"
// + ")";
sqlInsert = @" Insert into candidatecallup "
+ "(recrno,reqid,posdescr,requestid,clientid, position,,reqdescr)"
+ " values ('" + dt.Rows[x]["recrno"].ToString() + "',"
+ "'" + strTime + "',"
+ "'" + lblposition.Text + "',"
+ "'" + txtrequestid.Text + "',"
+ "'" + txtclientid.Text + "',"
+ "'" + prv_Poscode.Trim() + "'"
+ "'" + lblrequestdescr.Text.Trim() + "'"
+ ")";
dthm._update_Insert_NoParameter(sqlInsert);
// update into database1
sqlInsert = string.Empty;
sqlInsert = @" update database1 set active = 'true' where recrno = '" + dt.Rows[x]["recrno"].ToString() + "'";
dthm._update_Insert_NoParameter(sqlInsert);
_totalSaved += 1;
try
{
grdCandidate.Rows[x].Cells[8].Value = true;
}
catch
{
}
}
}
}
else MessageBox.Show("There are no records to processed", "RnS Notification");
}
else MessageBox.Show(dthm.sqlUserError, "RnS Notification");
if (_totalSaved > 0) MessageBox.Show(_totalSaved.ToString() + " records was committed Successfully", "RnS Notification");
}
private void populateGrid()
{
if (txtclientid.Text == string.Empty || txtrequestid.Text == string.Empty)
{
MessageBox.Show("Both Client and Request must be specified","RnS Notification");
return;
}
grdCandidate.Rows.Clear();
dt_shortlisted.Rows.Clear();
string strClientId = "'" + txtclientid.Text + "'";
string strRequestId = "'" + Program._searchedValue.Trim() + "'";
int _totalSaved = 0;
_totalSaved = 0;
List<string> lstRecrno = new List<string>();
string strParam = string.Empty;
string strWhereCaluse = string.Empty;
if (cmbgender.SelectedItem.ToString().Trim() != string.Empty) strParam = strParam + " and a.sex = '" + cmbgender.SelectedItem.ToString().Trim() + "'";
if (txtlocation.Text.Trim() != string.Empty) strParam = strParam + " and a.lgacode = '" + prv_location.Trim() + "'";
if (txtqualification.Text.Trim() != string.Empty) strParam = strParam + " and a.qualcode = '" + prv_qual.Trim() + "'";
if (txtgrade.Text.Trim() != string.Empty) strParam = strParam + " and a.gradecode = '" + prv_grade.Trim() + "'";
if (chkaptested.Checked == true)
{
if (cmbtestresult.Text.Trim() == string.Empty)
{
MessageBox.Show("Specify Test Result", "RnS Notification");
return;
}
if (cmbtestresult.Text.Trim() != string.Empty)
{
strParam = strParam + " and a.aptest = 'true' and a.testsult = '" + cmbtestresult.Text.Trim() + "'";
}
}
if (mskage.Text != string.Empty && mskage2.Text != string.Empty)
{
if ( Convert.ToInt32(mskage.Text) < Convert.ToInt32(mskage2.Text))
{
strParam = strParam + " and a.age between " + mskage.Text + " and " + mskage2.Text;
}
if (Convert.ToInt32(mskage.Text) > Convert.ToInt32(mskage2.Text))
{
strParam = strParam + " and a.age between " + mskage2.Text + " and " + mskage.Text;
}
if (Convert.ToInt32(mskage.Text) == Convert.ToInt32(mskage2.Text))
{
strParam = strParam + " and a.age between " + mskage2.Text + " and " + mskage.Text;
}
}
//if (!= string.Empty) txtrequestid.Text = Program._searchedValue;
// ----- selection from database 1
if ( raddatabase1.Checked == true )
{
String sqlSelect = @"select distinct a.* from database1 a
where a.recrno not in (select recrno from database2)" + strParam;
// String sqlSelect = @"select distinct * from database1
// where active = 'false' and recrno not in (select recrno from database2)" + strParam;
DataTable dt = new DataTable();
datamethods2 dtmth = new datamethods2();
dt = dtmth._dataTable_NoParameter(sqlSelect);
if (dtmth.sqlUserError == string.Empty)
{
if (dt.Rows.Count > 0)
{
dt_shortlisted = dt;
_addColumns(dt);
}
else MessageBox.Show("Requested records not found!", "RnS Notification");
}
else
{
MessageBox.Show(dtmth.sqlUserError, "RnS Notification");
}
}
// ----- selection from database 2
//interview
if (chkicslinterview.Checked == true)
{
if (cmdtinterviewresult.Text.Trim() == string.Empty)
{
MessageBox.Show("Specify ICSL Interview Result", "RnS Notification");
return;
}
if (cmdtinterviewresult.Text.Trim() != string.Empty)
{
strParam = strParam + " and a.interviewed = 'true' and a.intersult = '" + cmdtinterviewresult.Text.Trim() + "'";
}
}
//medical
if (chkmedical.Checked == true)
{
if (cmbmedicalresult.Text.Trim() == string.Empty)
{
MessageBox.Show("Specify Medical Result", "RnS Notification");
return;
}
if (cmbmedicalresult.Text.Trim() != string.Empty)
{
strParam = strParam + " and a.medical = 'true' and a.medsult = '" + cmbmedicalresult.Text.Trim() + "'";
}
}
//essay
if (chkessay.Checked == true)
{
if (cmbessayresult.Text.Trim() == string.Empty)
{
MessageBox.Show("Specify Essay Result", "RnS Notification");
return;
}
if (cmbessayresult.Text.Trim() != string.Empty)
{
strParam = strParam + " and a.essay = 'true' and a.essaysult = '" + cmbessayresult.Text.Trim() + "'";
}
}
//induction
if (chkinduction.Checked == true)
{
if (cmbinductionresult.Text.Trim() == string.Empty)
{
MessageBox.Show("Specify ICSL Induction assessment", "RnS Notification");
return;
}
if (cmbinductionresult.Text.Trim() != string.Empty)
{
strParam = strParam + " and a.induction = 'true' and a.inductsult = '" + cmbinductionresult.Text.Trim() + "'";
}
}
//documented
if (chkdocumented.Checked == true) strParam = strParam + " and a.documented = 'true' ";
//chkshl
if (chkshl.Checked == true) strParam = strParam + " and a.shl = 'true' ";
//client induction
if (chkcinduction.Checked == true)
{
if (cmbcindsult.Text.Trim() == string.Empty)
{
MessageBox.Show("Specify client Induction assessment", "RnS Notification");
return;
}
if (cmbcindsult.Text.Trim() != string.Empty)
{
strParam = strParam + " and b.cinduction = 'true' and b.cindsult = '" + cmbcindsult.Text.Trim() + "'";
}
}
//client interview
if (chkcinterview.Checked == true)
{
if (chkcintsult.Text.Trim() == string.Empty)
{
MessageBox.Show("Specify client interview assessment", "RnS Notification");
return;
}
strParam = strParam + " and b.cinterview = 'true' and b.cintersult = '" + chkcintsult.Text.Trim() + "'";
}
//emp type
if (cmbemploymenttype.Text.Trim() != string.Empty)
{
strParam = strParam + " and b.emptype = '" + cmbemploymenttype.Text.Trim() + "'";
}
if (raddatabase2.Checked == true)
{
String sqlSelect = @"select distinct a.* from database2 a, candidatecallup b
where a.recrno = b.recrno and (a.active = 'false' or a.active = 'true' )" + strParam;
//MessageBox.Show(sqlSelect);
//return;
DataTable dt = new DataTable();
datamethods2 dtmth = new datamethods2();
dt = dtmth._dataTable_NoParameter(sqlSelect);
if (dtmth.sqlUserError == string.Empty)
{
if (dt.Rows.Count > 0)
{
dt_shortlisted = dt;
_addColumns(dt);
}
else MessageBox.Show("Requested records not found!", "RnS Notification");
}
else
{
MessageBox.Show(dtmth.sqlUserError, "RnS Notification");
}
}
grdCandidate.Focus();
raddatabase1.Enabled = false;
raddatabase2.Enabled = false;
}