/// <summary>
/// Set columns in DataGrid based on Book type properties & attributes
/// </summary>
private void SetDataGrid()
{
this._booksDataTable = new ExtendedDataTable();
Type type = typeof(Book);
var properties = type.GetProperties();
foreach (var prop in properties)
{
var dataColumn = GetDataColumn(prop);
this._booksDataTable.Columns.Add(dataColumn);
}
// var dataColumn2 = new DataColumn("PriceSort", typeof(decimal));
// dataColumn2.Caption = "sdfsdf";
//this._booksDataTable.Columns.Add(dataColumn2);
}
/// <summary>
/// Custom Sort for DataGrid
/// Although the DataGrid has sorting capability for columns
/// it didn't work correctly for columns containg controls
/// & I insisted to have sort perticularly based on Labels contents
/// so I added this event handler
/// </summary>
/// <param name="sender">DataGrid as object</param>
/// <param name="e">DataGridSortingEventArgs</param>
protected void OnSorting(object sender, DataGridSortingEventArgs e)
{
var dataGrid = sender as DataGrid;
if (dataGrid == null)
{
return;
}
ExtendedDataTable dataTable = (dataGrid.ItemsSource as DataView).Table as ExtendedDataTable;
DataGridColumn column = e.Column;
ExtendedDataColumn extendedDataColumn = dataTable.Columns[column.SortMemberPath] as ExtendedDataColumn;
column.SortDirection = GetSortDirection(column);
DataView dataView = dataGrid.ItemsSource as DataView;
Type comparerType;
BaseCustomComparer comparer;
switch (extendedDataColumn.ColumnType)
{
case ColumnType.Label:
// sort by decimal values in content of the label
dataView.Sort = string.Empty;
Type contentType = extendedDataColumn.ContentType;
comparerType = typeof(LabelComparer <>).MakeGenericType(contentType);
comparer = Activator.CreateInstance(comparerType, column.SortDirection.Value) as BaseCustomComparer;
dataTable.ApplySort(column.SortMemberPath, comparer);
break;
case ColumnType.ComboBox:
dataView.Sort = string.Empty;
comparer = new ComboBoxComparer <string>(column.SortDirection.Value);
dataTable.ApplySort(column.SortMemberPath, comparer);
break;
case ColumnType.Button:
// not allow to sort by button
break;
case ColumnType.CheckBox:
case ColumnType.Text:
default:
dataView.Sort = GetSort(column);
break;
}
e.Handled = true;
}
public string GetCadsRaw(string device_id = "%", string cad_id = "%", string bus_id = "%")
{
if (device_id == "%" && cad_id == "%" && bus_id == "%")
{
return("");
}
ExtendedDataTable dt = GetCadTable(device_id, cad_id, bus_id);
List <object> myList = new List <object>();
foreach (DataRow dr in dt.Rows)
{
myList.Add(dr.ItemArray);
}
string json = JsonConvert.SerializeObject(myList);
return(json);
}
public string GetCads(string device_id = "%", string cad_id = "%", string bus_id = "%")
{
if (device_id == "%" && cad_id == "%" && bus_id == "%")
{
return("");
}
ExtendedDataTable dt = GetCadTable(device_id, cad_id, bus_id);
List <object> myList = dt.toList(); //new List<object>();
//foreach (DataRow dr in dt.Rows)
//{
// myList.Add(dr.Table);
//}
//string json = new JavaScriptSerializer().Serialize(myList);
string json = JsonConvert.SerializeObject(myList);
//UpdateDownloadedCad(device_id, cad_id);
//System.IO.File.WriteAllText("c:\\temp\\Output.json", json);
return(json);
}
private ExtendedDataTable GetCadTable(string device_id = "%", string cad_id = "%", string bus_id = "%")
{
ExtendedDataTable dt = new ExtendedDataTable();
//List<object> obj = new List<object>();
try
{
using (MySqlConnection cn = new MySqlConnection(DbConnect.ConnectionString))
{
cn.Open();
//List<string> SelectList = new List<string>();
StringBuilder SelectList = new StringBuilder();
SelectList.Append("a.id as cad_number_id,");
SelectList.Append("cad_number as cad_number_cad_number,");
SelectList.Append("a.agency_id as cad_number_agency_id,");
SelectList.Append("pcr_demographic_id as cad_number_pcr_demographic_id,");
SelectList.Append("pcr_disposition_id as cad_number_pcr_disposition_id,");
SelectList.Append("call_intake_id as cad_number_call_intake_id,");
SelectList.Append("pcr_id as cad_number_pcr_id,");
SelectList.Append("bus_id as cad_number_bus_id,");
SelectList.Append("date as cad_number_date,");
SelectList.Append("schedule_return as cad_number_schedule_return,");
SelectList.Append("downloaded as cad_number_downloaded,");
SelectList.Append("downloaded_time as cad_number_downloaded_time,");
SelectList.Append("cancelled as cad_number_cancelled,");
SelectList.Append("firstCrewMember as cad_number_firstCrewMember,");
SelectList.Append("secondCrewMember as cad_number_secondCrewMember,");
SelectList.Append("is_schedule_return as cad_number_is_schedule_return,");
SelectList.Append("is_cancelled as cad_number_is_cancelled,");
SelectList.Append("a.utc_insert as cad_number_utc_insert,");
SelectList.Append("a.utc_update as cad_number_utc_update,");
SelectList.Append("user_login_id as cad_number_user_login_id,");
SelectList.Append("schedule_cad_id as cad_number_schedule_cad_id,");
SelectList.Append("caller_name as cad_number_caller_name,");
SelectList.Append("caller_phone as cad_number_caller_phone,");
SelectList.Append("transfer_care as cad_number_transfer_care,");
SelectList.Append("is_transfer_care as cad_number_is_transfer_care,");
SelectList.Append("a.active as cad_number_active,");
SelectList.Append("is_dry_run as cad_number_is_dry_run,");
SelectList.Append("first_name as pcr_demographic_first_name,");
SelectList.Append("last_name as pcr_demographic_last_name,");
SelectList.Append("e.address as address_address,");
SelectList.Append("city.id as city_id,");
SelectList.Append("city.city_name as city_city,");
SelectList.Append("state.id as state_id,");
SelectList.Append("state.state_name as state_state,");
SelectList.Append("zip.id as zip_id,");
SelectList.Append("zip.zip_code as zip_zip,");
SelectList.Append("country.id as country_id,");
SelectList.Append("country.country_name as country_country");
string SelectString = SelectList.ToString(); // string.Join(",", SelectList);
string QueryString = "SELECT " + SelectString + " FROM cad_number a " + System.Environment.NewLine +
"left outer join call_intake b " + System.Environment.NewLine +
"on a.call_intake_id = b.id " + System.Environment.NewLine +
"left outer join pcr_demographic c " + System.Environment.NewLine +
"on a.pcr_demographic_id = c.id " + System.Environment.NewLine +
"left outer join person d " + System.Environment.NewLine +
"on c.pt_person = d.id " + System.Environment.NewLine +
"left outer join address e " + System.Environment.NewLine +
"on e.id = ifnull(b.facility_id, b.address_id) " + System.Environment.NewLine +
"left outer join city on e.city_id=city.id " + System.Environment.NewLine +
"left outer join state on e.state_id=state.id " + System.Environment.NewLine +
"left outer join zip on e.zip_id=zip.id " + System.Environment.NewLine +
"left outer join country on e.country_id=country.id " + System.Environment.NewLine +
"where a.id like '" + cad_id + "' and a.agency_id like '" + device_id + "'" + " and a.bus_id like '" + bus_id + "'" +
" and (ifnull(downloaded_time,0)=0 or year(downloaded_time)=1970)";
//where a.id = '70d9fafb-64da-4eb3-b3d8-f99950952474' and and a.agency_id = '90b16e1c-aa76-11e5-b94a-842b2b4bbc99';
MySqlCommand cmd = new MySqlCommand(QueryString, cn);
dt.Load(cmd.ExecuteReader());
}
}
catch (Exception ex) { Logger.LogException(ex); return(null); }
return(dt);
}
