/// <summary>
/// Execute DataTable async
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public static async Task <DataTable> ExecuteDataTableAsync(this MySqlCommand command)
{
command.CheckNull(nameof(command));
var dt = new DataTable();
using var dataAdapter = new MySqlDataAdapter(command);
await dataAdapter.FillAsync(dt);
return(dt);
}
/// <summary>
/// Execute DataTable async
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
internal static async Task <DataTable> ExecuteFirstDataTableAsync(this MySqlCommand command)
{
command.CheckNull(nameof(command));
var ds = new DataSet();
using var dataAdapter = new MySqlDataAdapter(command);
await dataAdapter.FillAsync(ds);
return(ds.Tables[0]);
}
public async Task <DataTable> RetDataTableAsync(string sql)
{
var datatable = new DataTable();
using (var con = new MySql.Data.MySqlClient.MySqlConnection(commStr))
{
using (var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, con))
{
await con.OpenAsync();
cmd.CommandType = CommandType.Text;
var sda = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
await sda.FillAsync(datatable);
}
}
return(datatable);
}
private async void getCallsFromDB()
{
dataFromDB = new DataTable();
string preparedquery = "SELECT * FROM calls WHERE tech='" + ICResponse.Properties.Settings.Default.AgentFirstName + " " + ICResponse.Properties.Settings.Default.AgentLastName + "' OR tech='" + ICResponse.Properties.Settings.Default.LoginUsername + "' ORDER BY ticketID DESC LIMIT 100";
using (dbConn = new MySqlConnection("Server=" + ICResponse.Properties.Settings.Default.DBServer +
";Database=" + ICResponse.Properties.Settings.Default.DBName +
";Uid=" + ICResponse.Properties.Settings.Default.DBUser +
";Pwd=" + ICResponse.Properties.Settings.Default.DBPass + ";"))
{
await dbConn.OpenAsync();
}
using (dbDataAdapter = new MySqlDataAdapter(preparedquery, dbConn))
{
await dbDataAdapter.FillAsync(dataFromDB);
}
MyCallsDataGrid.ItemsSource = dataFromDB.DefaultView;
dbConn.Close();
}
private async void GetOverViewCalls()
{
try
{
DataTable dataFromDB = new DataTable();
using (MySqlConnection dbConn = new MySqlConnection("Server=" + ICResponse.Properties.Settings.Default.DBServer +
";Database=" + ICResponse.Properties.Settings.Default.DBName +
";Uid=" + ICResponse.Properties.Settings.Default.DBUser +
";Pwd=" + ICResponse.Properties.Settings.Default.DBPass +
";Convert Zero Datetime=True;"))
{
using (MySqlDataAdapter dbDataAdapter = new MySqlDataAdapter(ICResponse.Properties.Settings.Default.GetOverview, dbConn))
{
dbConn.Open();
await dbDataAdapter.FillAsync(dataFromDB);
OverViewCallsGrid.ItemsSource = dataFromDB.DefaultView;
dbDataAdapter.Dispose();
}
dbConn.Close();
}
}
catch (MySqlException me)
{
System.Diagnostics.Debug.WriteLine(me);
throw;
}
}
private async void btnExportCalls_Click_1(object sender, RoutedEventArgs e)
{
if (begindateforpicker.Equals("") || enddateforpicker.Equals(""))
{
ModernDialog msgbox = new ModernDialog();
msgbox.Title = "Pick Date Range";
msgbox.Content = "Please select a range of dates.";
msgbox.Buttons = new[] { msgbox.OkButton };
msgbox.ShowDialog();
}
else
{
string reportpath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
StringBuilder reportbuilder = new StringBuilder();
StringBuilder vmbuilder = new StringBuilder();
int i = 0;
int contactedcounter = 0;
//int voicemailcounter = 0;
string datecolumnvalue = string.Empty;
string monthformat = string.Empty;
string finalmonthformat = string.Empty;
string preparedquery = "SELECT * FROM `calls` WHERE tech='" + ICResponse.Properties.Settings.Default.AgentFirstName
+ " "
+ ICResponse.Properties.Settings.Default.AgentLastName
+ "' OR tech='"
+ ICResponse.Properties.Settings.Default.LoginUsername
+ "'AND date BETWEEN '"
+ begindateforpicker
+ "' AND '"
+ enddateforpicker + "'";
dataFromDB.Clear();
dataFromDB = new DataTable();
using (dbConn = new MySqlConnection("Server=" + ICResponse.Properties.Settings.Default.DBServer +
";Database=" + ICResponse.Properties.Settings.Default.DBName +
";Uid=" + ICResponse.Properties.Settings.Default.DBUser +
";Pwd=" + ICResponse.Properties.Settings.Default.DBPass + ";"))
{
await dbConn.OpenAsync();
}
using (dbDataAdapter = new MySqlDataAdapter(preparedquery, dbConn))
{
await dbDataAdapter.FillAsync(dataFromDB);
}
MyCallsDataGrid.ItemsSource = dataFromDB.DefaultView;
foreach (DataRow dRow in dataFromDB.Rows)
{
i++;
string BBcompany = String.Empty;
string BBcontact = String.Empty;
string BBduration = String.Empty;
//if (dRow["voicemail"].ToString().Equals("Voicemail"))
//{
// BBcompany = dRow["company"].ToString();
// BBcontact = dRow["contact"].ToString();
// BBduration = dRow["duration"].ToString();
// vmbuilder.AppendLine(dRow["ticketID"].ToString());
// vmbuilder.AppendLine(dRow["origin"].ToString());
// vmbuilder.AppendLine("[b]" + BBcompany + "[/b]"); // [b]
// vmbuilder.AppendLine("[i]" + BBcontact + "[/i]"); // [i]
// vmbuilder.AppendLine(dRow["phone"].ToString());
// datecolumnvalue = dRow["date"].ToString();
// monthformat = datecolumnvalue.Replace("12:00:00 AM", "");
// vmbuilder.AppendLine(monthformat);
// vmbuilder.AppendLine(dRow["startTime"].ToString());
// vmbuilder.AppendLine(dRow["stopTime"].ToString());
// vmbuilder.AppendLine("[u]" + BBduration + "[/u]"); // [u]
// vmbuilder.AppendLine(dRow["voicemail"].ToString());
// vmbuilder.AppendLine();
// vmbuilder.AppendLine();
// voicemailcounter++;
//}
//else
//{
BBcompany = dRow["company"].ToString();
BBcontact = dRow["contact"].ToString();
BBduration = dRow["duration"].ToString();
reportbuilder.AppendLine(dRow["ticketID"].ToString());
//reportbuilder.AppendLine(dRow["origin"].ToString());
reportbuilder.AppendLine("[b]" + BBcompany + "[/b]"); // [b]
reportbuilder.AppendLine("[i]" + BBcontact + "[/i]"); // [i]
reportbuilder.AppendLine(dRow["phone"].ToString());
datecolumnvalue = dRow["date"].ToString();
monthformat = datecolumnvalue.Replace("12:00:00 AM", "");
reportbuilder.AppendLine(monthformat);
reportbuilder.AppendLine(dRow["startTime"].ToString());
reportbuilder.AppendLine(dRow["stopTime"].ToString());
reportbuilder.AppendLine("[u]" + BBduration + "[/u]"); // [u]
//reportbuilder.AppendLine(dRow["voicemail"].ToString());
reportbuilder.AppendLine(dRow["issue"].ToString());
reportbuilder.AppendLine(dRow["resolution"].ToString());
reportbuilder.AppendLine();
reportbuilder.AppendLine();
contactedcounter++;
//}
}
dbConn.Close();
vmbuilder.AppendLine("Total Contacted: " + contactedcounter.ToString());
//vmbuilder.AppendLine("Total Voicemails: " + voicemailcounter.ToString());
vmbuilder.AppendLine("Total Results: " + dataFromDB.Rows.Count.ToString());
string datetimepatt = @"MM_dd_yyyy_hh_mm_ss_tt";
DateTime savenow = System.DateTime.Now;
string currdatetime = savenow.ToString(datetimepatt);
string partialpath = @"\ICResponse_Report_" + currdatetime + ".txt";
string fullpath = reportpath + partialpath;
using (StreamWriter outfile = new StreamWriter(fullpath, true))
{
await outfile.WriteAsync(reportbuilder.ToString());
await outfile.WriteAsync(vmbuilder.ToString());
outfile.Close();
}
ModernDialog msgbox = new ModernDialog();
msgbox.Title = "Export Completed";
msgbox.Content = fullpath + " , Results:" + dataFromDB.Rows.Count.ToString();
msgbox.Buttons = new[] { msgbox.OkButton };
msgbox.ShowDialog();
this.Cursor = Cursors.Arrow;
}
}