public Result getEvent()
{
Result result = new Result();
try
{
int year = DateTime.Now.AddDays(-2).Year;
int month = DateTime.Now.AddDays(-2).Month;
int day = DateTime.Now.AddDays(-2).Day;
string startDate = "" + year + "-" + month + "-" + day;
query = "SELECT E.EventTime as 'Date/Time',u.Field12_50 as 'User Code',CONCAT(e.Surname,',',e.FirstName) as 'User Name',e.CardNumber as 'Token Number',e.PeripheralName as 'Where',e.EventTypeDescription as 'Event',e.EventDetails as 'Details' from EventsEx as E left join UsersEx as U on e.UserID = u.UserID where CAST(EventTime AS DATE)< CAST(GETDATE() AS DATE) and CAST(EventTime AS DATE)> CONVERT(date, '" + startDate + "') order by e.EventTime desc";
users = oemClient.GetListOfOperators().UsersDictionary();
net2Methods = oemClient.AuthenticateUser(0, password);
List <EventModel> eventList = new List <EventModel>();
DataSet ds = oemClient.QueryDb(query);
DataTable dt = ds.Tables[0];
int size = dt.Rows.Count;
var options = new ParallelOptions()
{
MaxDegreeOfParallelism = 10
};
Parallel.For(0, size, i =>
{
var eventInfo = new EventModel();
eventInfo.dateTime = dt.Rows[i][0].ToString() ?? "";
eventInfo.userCode = dt.Rows[i][1].ToString() ?? "";
eventInfo.userName = dt.Rows[i][2].ToString() ?? "";
eventInfo.tokenNumber = dt.Rows[i][3].ToString() ?? "";
eventInfo.where = dt.Rows[i][4].ToString() ?? "";
eventInfo.Event = dt.Rows[i][5].ToString() ?? "";
eventInfo.details = dt.Rows[i][6].ToString() ?? "";
eventList.Add(eventInfo);
});
result.status = true;
result.List = eventList;
return(result);
}
catch (Exception ex)
{
result.status = false;
result.message = ex.Message;
return(result);
}
}
static void Main(string[] args)
{
const int PORT = 8025;
int year = DateTime.Now.AddDays(-2).Year;
int month = DateTime.Now.AddDays(-2).Month;
int day = DateTime.Now.AddDays(-2).Day;
string startDate = "" + year + "-" + month + "-" + day;
string path = @"E:\Wasim\Document\Paxton\EventData_3_17_2019.xlsx";
OemClient oemClient = new OemClient("10.10.17.245", PORT);;
Dictionary <int, string> users = oemClient.GetListOfOperators().UsersDictionary();
Dictionary <string, int> net2Methods = oemClient.AuthenticateUser(0, "net1412");
int count = 0;
string query = "SELECT E.EventTime as 'Date/Time',u.Field12_50 as 'User Code',CONCAT(e.Surname,',',e.FirstName) as 'User Name',e.CardNumber as 'Token Number',e.PeripheralName as 'Where',e.EventTypeDescription as 'Event',e.EventDetails as 'Details' from EventsEx as E left join UsersEx as U on e.UserID = u.UserID where CAST(EventTime AS DATE)< CONVERT(date, '2019-03-17') and CAST(EventTime AS DATE)> CONVERT(date, '2019-03-15') order by e.EventTime desc";
DataSet ds = oemClient.QueryDb(query);
DataTable dt = ds.Tables[0];
DataTableReader reader = dt.CreateDataReader();
foreach (DataColumn col in dt.Columns)
{
Console.Write(col.ColumnName + " ");
}
Console.WriteLine();
while (reader.Read())
{
//Console.WriteLine("Total column found:"+reader.VisibleFieldCount);
for (int i = 0; i < reader.VisibleFieldCount; i++)
{
Console.Write("" + reader[i].ToString() + ",");
}
Console.WriteLine();
count++;
if (count == 100)
{
break;
}
}
//string path = @"E:\Wasim\Document\Paxton\Test.csv";
Console.WriteLine("Write Started:" + DateTime.Now.ToString());
//ExportDataSetToExcel(ds);
ExportToExcel(dt, path);
Console.WriteLine("Write Ended:" + DateTime.Now.ToString());
//ToCSV(dt, path);
Console.WriteLine("Done");
}
public void GenerateReport()
{
try
{
int year = DateTime.Now.AddDays(-2).Year;
int month = DateTime.Now.AddDays(-2).Month;
int day = DateTime.Now.AddDays(-2).Day;
string startDate = "" + year + "-" + month + "-" + day;
oemClient = new OemClient("10.10.17.245", PORT);;
users = oemClient.GetListOfOperators().UsersDictionary();
net2Methods = oemClient.AuthenticateUser(0, "net1412");
string query = "SELECT E.EventTime as 'Date/Time',u.Field12_50 as 'User Code',CONCAT(e.Surname,',',e.FirstName) as 'User Name',e.CardNumber as 'Token Number',e.PeripheralName as 'Where',e.EventTypeDescription as 'Event',e.EventDetails as 'Details' from EventsEx as E left join UsersEx as U on e.UserID = u.UserID where CAST(EventTime AS DATE)< CAST(GETDATE() AS DATE) and CAST(EventTime AS DATE)> CONVERT(date, '" + startDate + "') order by e.EventTime desc";
DataSet ds = oemClient.QueryDb(query);
DataTable dt = ds.Tables[0];
ExportToExcel(dt);
}
catch (Exception ex)
{
WriteToFile("Error occured at:" + DateTime.Now.ToString());
WriteToFile("Error description:\n" + ex.ToString());
}
}
