private void DataGrdVuUserQueriesLoad()
{
string mysqlSelectQuery = null;
bool isColourEnable = false;
DataGrdVuUserQueries.Rows.Clear();
if (radioButtonWorkingItinary.Checked)
{
mysqlSelectQuery = "SELECT `queryid`, `querycurrentstate`, `place`, `fromdate`, `todate`, `querystartdate` " +
"FROM `queries` WHERE " +
"`userid` = " + frmUserId.ToString() + " ";
mysqlSelectQuery = mysqlSelectQuery +
"AND `querycurrentstate` = " + Properties.Resources.queryStageGenerated;
isColourEnable = true;
}
else if (radioButtonCompletedItinerary.Checked)
{
mysqlSelectQuery = "SELECT `queryid`, `querycurrentstate`, `place`, `fromdate`, `todate`, `querystartdate` " +
"FROM `queries` WHERE " +
"`userid` = " + frmUserId.ToString() + " ";
mysqlSelectQuery = mysqlSelectQuery +
"AND `querycurrentstate` > " + Properties.Resources.queryStageGenerated + " " +
"AND `querycurrentstate` < " + Properties.Resources.queryStageRejected;
isColourEnable = false;
}
else if (radioButtonWorkingVouchers.Checked)
{
mysqlSelectQuery = "SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
"FROM `queries` as `T1` inner join `finalizedqueries` as `T2` on `T1`.`queryid` = `T2`.`queryid` " +
"WHERE " +
"`T2`.`userid` = " + frmUserId.ToString() + " " +
"AND `T1`.`querycurrentstate` > " + Properties.Resources.queryStageRejected + " " +
"AND `T1`.`querycurrentstate` < " + Properties.Resources.queryStageVoucherCompleted;
isColourEnable = true;
}
else if (radioButtonCompletedBooking.Checked)
{
mysqlSelectQuery = "SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
"FROM `queries` as `T1` inner join `finalizedqueries` as `T2` on `T1`.`queryid` = `T2`.`queryid` " +
"WHERE " +
"`T2`.`userid` = " + frmUserId.ToString() + " " +
"AND `T1`.`querycurrentstate` = " + Properties.Resources.queryStageVoucherCompleted;
isColourEnable = false;
}
else if (radioButtonAllQueries.Checked)
{
mysqlSelectQuery = "SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
"FROM `queries` as `T1` inner join `finalizedqueries` as `T2` " +
"on `T1`.`queryid` = `T2`.`queryid` " +
"WHERE " +
"`T2`.`userid` = " + frmUserId.ToString() + " " +
"UNION " +
"SELECT `T1`.`queryid`, `T1`.`querycurrentstate`, `T1`.`place`, `T1`.`fromdate`, `T1`.`todate`, `T1`.`querystartdate` " +
"FROM `queries` as `T1` " +
"WHERE " +
"`T1`.`userid` = " + frmUserId.ToString() + " ";
isColourEnable = false;
}
else
{
MessageBox.Show("No Option is selected");
return;
}
try
{
frmUserMysqlConnection.Open();
}
catch (Exception errConnOpen)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnOpen.Message);
}
DataSet queryDataset = new DataSet();
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mysqlSelectQuery, frmUserMysqlConnection);
try
{
mySqlDataAdapter.Fill(queryDataset, "ASSIGNED_QUERIES");
if (queryDataset != null)
{
foreach (DataRow item in queryDataset.Tables["ASSIGNED_QUERIES"].Rows)
{
int index = DataGrdVuUserQueries.Rows.Add();
DataGrdVuUserQueries.Rows[index].Cells["QueryId"].Value = item["queryid"].ToString();
DataGrdVuUserQueries.Rows[index].Cells["QueryState"].Value = item["querycurrentstate"].ToString() + " ( " + MyPdfDocuments.PrintCurrentQueryStage(Convert.ToInt32(item["querycurrentstate"])) + " )";
DataGrdVuUserQueries.Rows[index].Cells["fromDate"].Value = item["fromdate"].ToString();
DataGrdVuUserQueries.Rows[index].Cells["toDate"].Value = item["todate"].ToString();
DataGrdVuUserQueries.Rows[index].Cells["Location"].Value = item["place"].ToString();
DataGrdVuUserQueries.Rows[index].Cells["AssignedDate"].Value = item["querystartdate"].ToString();
double noOfdays = (DateTime.Today - DateTime.Parse(item["querystartdate"].ToString())).TotalDays;
if (isColourEnable)
{
if (noOfdays > 2)
{
DataGrdVuUserQueries.Rows[index].DefaultCellStyle.BackColor = Color.Red;
}
else if (noOfdays > 1)
{
DataGrdVuUserQueries.Rows[index].DefaultCellStyle.BackColor = Color.Yellow;
}
}
}
}
DataGrdVuUserQueries.Sort(QueryState, ListSortDirection.Ascending);
DataGrdVuUserQueries.ClearSelection();
}
catch (Exception errQuery)
{
MessageBox.Show("Error in executing command because : " + errQuery.Message);
}
try
{
frmUserMysqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in Closing mysql connection because : " + errConnClose.Message);
}
}
private void DataGrdVuAdminQueriesLoad(string argumentString)
{
string mysqlSelectQuery = null;
mysqlSelectQuery = "SELECT `queryid`, `place`, `fromdate`, `todate`, `querystartdate`, `name`, `querycurrentstate` " +
"FROM `queries` WHERE ";
if (string.Equals(argumentString, "ITINERARY"))
{
mysqlSelectQuery += "`querycurrentstate` >= " + Properties.Resources.queryStageDoneByUser +
" AND `querycurrentstate` <= " + Properties.Resources.queryStageMailed;
}
else if (string.Equals(argumentString, "FINALIZE OFFER"))
{
mysqlSelectQuery += "`querycurrentstate` = " + Properties.Resources.queryStageMailed;
}
else if (string.Equals(argumentString, "UPDATE ACCEPTED OFFER"))
{
mysqlSelectQuery += "`querycurrentstate` >= " + Properties.Resources.queryStageDealDone +
" AND `querycurrentstate` <= " + Properties.Resources.queryStageVoucherCompleted;
}
else if (string.Equals(argumentString, "VOUCHERS"))
{
mysqlSelectQuery += "`querycurrentstate` >= " + Properties.Resources.queryStageVoucherCompleted;
}
else if (string.Equals(argumentString, "VIEW ALL"))
{
mysqlSelectQuery += "`querycurrentstate` != 0";
}
else if (string.Equals(argumentString, "PAYMENT UPDATE"))
{
mysqlSelectQuery += "`querycurrentstate` != 0";
}
else
{
MessageBox.Show("Wrong method invoked");
return;
}
try
{
frmMysqlConnection.Open();
}
catch (Exception errConnOpen)
{
MessageBox.Show("Error in opening mysql connection because : " + errConnOpen.Message);
return;
}
DataSet queryDataset = new DataSet();
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mysqlSelectQuery, frmMysqlConnection);
try
{
mySqlDataAdapter.Fill(queryDataset, "ASSIGNED_QUERIES");
DataGrdVuAdminQueries.Rows.Clear();
if (queryDataset != null)
{
foreach (DataRow item in queryDataset.Tables["ASSIGNED_QUERIES"].Rows)
{
int index = DataGrdVuAdminQueries.Rows.Add();
DataGrdVuAdminQueries.Rows[index].Cells["QueryId"].Value = item["queryid"].ToString();
DataGrdVuAdminQueries.Rows[index].Cells["FromDate"].Value = item["fromdate"].ToString();
DataGrdVuAdminQueries.Rows[index].Cells["Name"].Value = item["name"].ToString();
DataGrdVuAdminQueries.Rows[index].Cells["QueryStage"].Value = item["querycurrentstate"].ToString() + " ( " + MyPdfDocuments.PrintCurrentQueryStage(Convert.ToInt32(item["querycurrentstate"])) + " )";
DataGrdVuAdminQueries.Rows[index].Cells["ToDate"].Value = item["todate"].ToString();
DataGrdVuAdminQueries.Rows[index].Cells["Location"].Value = item["place"].ToString();
DataGrdVuAdminQueries.Rows[index].Cells["AssignedDate"].Value = item["querystartdate"].ToString();
}
}
DataGrdVuAdminQueries.ClearSelection();
}
catch (Exception errQuery)
{
MessageBox.Show("Error in executing command because : " + errQuery.Message);
}
try
{
frmMysqlConnection.Close();
}
catch (Exception errConnClose)
{
MessageBox.Show("Error in Closing mysql connection because : " + errConnClose.Message);
}
}