public DataTable GetMemberUniqueRuns(int memberID)
{
string sql = "select distinct l.Location, p.Product from RunLog rl " +
"join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " +
"join Product p on rlp.ProductID = p.ProductID " +
"join Location l on l.LocationID = rl.DeliverToLocationID " +
"where rl.RiderMemberID = " + memberID + " " +
"order by Location";
return(DBHelperFactory.DBHelper().ExecuteDataTable(sql));
}
public List <string> ListMobileNumbersWithTag(string tag)
{
List <string> ret = new List <string>();
string sql = "select distinct m.MobileNumber " +
"from Member m " +
"join Member_Tag mt on mt.MemberID = m.MemberID " +
"join Tag t on t.TagID = mt.TagID " +
"and m.LeaveDate is null " +
"where t.Tag = '" + tag + "'";
DataTable tbl = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
if (tbl != null && tbl.Rows.Count > 0)
{
foreach (DataRow r in tbl.Rows)
{
ret.Add(r[0].ToString());
}
}
return(ret);
}
public List <string> ListMobileNumbersWithAnyTagsIn(string tagsCsv)
{
List <string> ret = new List <string>();
string[] tags = tagsCsv.Split(',');
List <string> tagList = new List <string>();
foreach (string tag in tags)
{
if (tag.Trim() != string.Empty)
{
tagList.Add(tag);
}
}
string inClause = "in (";
foreach (string t in tagList)
{
inClause += "'" + t.Trim() + "',";
}
inClause = inClause.Substring(0, inClause.Length - 1);
inClause += ")";
string sql = "select distinct m.MobileNumber " +
"from Member m " +
"join Member_Tag mt on mt.MemberID = m.MemberID " +
"join Tag t on t.TagID = mt.TagID " +
"and m.LeaveDate is null " +
"where t.Tag " + inClause;
DataTable tbl = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
if (tbl != null && tbl.Rows.Count > 0)
{
foreach (DataRow r in tbl.Rows)
{
ret.Add(r[0].ToString());
}
}
return(ret);
}
/*
*
* public DataTable Report_RecentRunLog()
* {
* string sql =
* "SELECT " +
* "date(CallDate) as Date, " +
* "TIME(rpad(replace(CallTime, '.',':'), 5, '0')) as Time, " +
* "CONCAT(m.FirstName, ' ', m.LastName) as Rider, " +
* "Consignment, " +
* "CollectFrom as Origin, " +
* "Destination, " +
* "CONCAT(con.FirstName, ' ', con.LastName) as Controller " +
* "FROM RawRunLog rr " +
* "LEFT JOIN Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "LEFT JOIN Member con on rr.Controller = (CONCAT(con.LastName, ' ', con.FirstName)) " +
* "order by CallDate desc, RawRunLogID desc " +
* "LIMIT 100;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top10Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RunLog rl " +
* "LEFT join Member m on m.MemberID = rl.RiderMemberID " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) top " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top102013Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.FirstName is not null " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) sub " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_RunButNoLogin()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Rider, date(m.JoinDate) as Joined, m.EmailAddress as Email, date(max(rr.CallDate)) as LastRun, count(*) as Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.MemberID not in " +
* "(select m.MemberID from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate is not null) " +
* "and rr.CallDate > '2013-05-01' " +
* "and m.LeaveDate is null " +
* "group by m.MemberID " +
* "order by max(rr.CallDate) desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_AverageCallsPerDay()
* {
* string sql = "SELECT dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as ShiftDay " +
* ", round(count(*) / 34.28) as AverageCalls " +
* //", ceil((count(*) / @weeks) * @riderfactor) as RidersRequired " +
* "FROM RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "GROUP BY dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end);";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_CallsPerHourHeatMap()
* {
* string sql = "select dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as Day, " +
* "Hour(CallTime) as Hour, count(*) as Calls " +
* "from RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND Hour(CallTime) >= 0 AND Hour(CallTime) <= 23 " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "group by dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime)";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_TodaysUsers()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Member " +
* "from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate > CURRENT_DATE() " +
* "order by lastLoginDate desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*
*
* public DataTable Report_BoxesByProductByMonth()
* {
* string sql = "select concat(MONTHNAME(rl.DutyDate), ' ', year(rl.DutyDate)) as Month " +
* ", p.Product, sum(rlp.Quantity) as BoxesCarried from RunLog rl " +
* "join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " +
* "join Product p on p.ProductID = rlp.ProductID " +
* "group by Month, Product " +
* "order by month(rl.DutyDate), Product;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*/
public DataTable Report_RunLog()
{
string sql = "select RunLogID as ID, date_format(DutyDate, '%Y-%m-%d') as 'DutyDate', " +
"coalesce(date_format(CallDateTime, '%Y-%m-%d %H:%i'), 'N/A') as 'CallDateTime', cf.Location as 'CallFrom', cl.Location as 'From', " +
"dl.Location as 'To', coalesce(date_format(rl.CollectDateTime, '%H:%i'), 'NOT ACCEPTED') as Collected, " +
"date_format(rl.DeliverDateTime, '%H:%i') as Delivered, " +
//"timediff(rl.DeliverDateTime, rl.CollectDateTime) as 'Run Time', " +
"fl.Location as 'Destination', concat(m.FirstName, ' ', m.LastName) as Rider, " +
"v.VehicleType as 'Vehicle', rl.Description as 'Consignment', " +
"concat(c.FirstName, ' ', c.LastName) as Controller from RunLog rl " +
"left join Member m on m.MemberID = rl.RiderMemberID " +
"join Member c on c.MemberID = rl.ControllerMemberID " +
"join Location cf on cf.LocationID = rl.CallFromLocationID " +
"join Location cl on cl.LocationID = rl.CollectionLocationID " +
"join Location dl on dl.LocationID = rl.DeliverToLocationID " +
"join Location fl on fl.LocationID = rl.FinalDestinationLocationID " +
"left join VehicleType v on v.VehicleTypeID = rl.VehicleTypeID " +
"where DutyDate > '2013-12-31' or CallDateTime > '2013-12-31' " +
"order by rl.DutyDate desc, rl.CallDateTime desc;";
DataTable ret = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
return(ret);
}
/*
*
* public DataTable Report_RecentRunLog()
* {
* string sql =
* "SELECT " +
* "date(CallDate) as Date, " +
* "TIME(rpad(replace(CallTime, '.',':'), 5, '0')) as Time, " +
* "CONCAT(m.FirstName, ' ', m.LastName) as Rider, " +
* "Consignment, " +
* "CollectFrom as Origin, " +
* "Destination, " +
* "CONCAT(con.FirstName, ' ', con.LastName) as Controller " +
* "FROM RawRunLog rr " +
* "LEFT JOIN Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "LEFT JOIN Member con on rr.Controller = (CONCAT(con.LastName, ' ', con.FirstName)) " +
* "order by CallDate desc, RawRunLogID desc " +
* "LIMIT 100;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top10Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RunLog rl " +
* "LEFT join Member m on m.MemberID = rl.RiderMemberID " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) top " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_Top102013Riders()
* {
* string sql =
* "select Name from " +
* "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.FirstName is not null " +
* "group by Name " +
* "order by Runs desc " +
* "LIMIT 10) sub " +
* "order by Name;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_RunButNoLogin()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Rider, date(m.JoinDate) as Joined, m.EmailAddress as Email, date(max(rr.CallDate)) as LastRun, count(*) as Runs " +
* "from RawRunLog rr " +
* "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " +
* "where m.MemberID not in " +
* "(select m.MemberID from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate is not null) " +
* "and rr.CallDate > '2013-05-01' " +
* "and m.LeaveDate is null " +
* "group by m.MemberID " +
* "order by max(rr.CallDate) desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_AverageCallsPerDay()
* {
* string sql = "SELECT dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as ShiftDay " +
* ", round(count(*) / 34.28) as AverageCalls " +
* //", ceil((count(*) / @weeks) * @riderfactor) as RidersRequired " +
* "FROM RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "GROUP BY dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end);";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_CallsPerHourHeatMap()
* {
* string sql = "select dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as Day, " +
* "Hour(CallTime) as Hour, count(*) as Calls " +
* "from RawRunLog " +
* "WHERE CallDate > AddDate(CURRENT_DATE, -240) " +
* "AND Hour(CallTime) >= 0 AND Hour(CallTime) <= 23 " +
* "AND(Consignment like '%blood%' " +
* "or Consignment like '%plate%' " +
* "or Consignment like '%plas%' " +
* "or Consignment like '%ffp%' " +
* "or Consignment like '%sample%' " +
* "or Consignment like '%drugs%' " +
* "or Consignment like '%cd%' " +
* "or Consignment like '%data%' " +
* "or Consignment like '%disk%' " +
* "or Consignment like '%disc%' " +
* "or Consignment like '%package%') " +
* "group by dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime) " +
* "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime)";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
* public DataTable Report_TodaysUsers()
* {
* string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Member " +
* "from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate > CURRENT_DATE() " +
* "order by lastLoginDate desc;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*
*
* public DataTable Report_BoxesByProductByMonth()
* {
* string sql = "select concat(MONTHNAME(rl.DutyDate), ' ', year(rl.DutyDate)) as Month " +
* ", p.Product, sum(rlp.Quantity) as BoxesCarried from RunLog rl " +
* "join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " +
* "join Product p on p.ProductID = rlp.ProductID " +
* "group by Month, Product " +
* "order by month(rl.DutyDate), Product;";
* return DBHelperFactory.DBHelper().ExecuteDataTable(sql);
* }
*
*/
public DataTable Report_RunLog(string year)
{
string sql = "select RunLogID as ID, date_format(DutyDate, '%Y-%m-%d') as 'DutyDate', " +
"coalesce(date_format(CallDateTime, '%Y-%m-%d %H:%i'), 'N/A') as 'CallDateTime', cf.Location as 'CallFrom', " +
"CASE WHEN rl.RunLogType='M' THEN Concat(cl.Location,' ', rl.CollectionPostcode) ELSE cl.Location END 'From', " +
"CASE WHEN rl.RunLogType = 'M' THEN Concat(dl.Location,' ', rl.DeliverToPostcode) ELSE dl.Location END as 'To', coalesce(date_format(rl.CollectDateTime, '%H:%i'), 'NOT ACCEPTED') as Collected, " +
"date_format(rl.DeliverDateTime, '%H:%i') as Delivered, " +
"CASE WHEN rl.RunLogType = 'M' THEN Concat(fl.Location,' ', rl.DeliverToPostcode) ELSE fl.Location END as 'Destination', concat(m.LastName, ' ', m.FirstName) as Rider, " +
"v.VehicleType as 'Vehicle', rl.Description as 'Consignment', " +
"concat(c.LastName, ' ', c.FirstName) as Controller from RunLog rl " +
"left join Member m on m.MemberID = rl.RiderMemberID " +
"join Member c on c.MemberID = rl.ControllerMemberID " +
"join Location cf on cf.LocationID = rl.CallFromLocationID " +
"join Location cl on cl.LocationID = rl.CollectionLocationID " +
"join Location dl on dl.LocationID = rl.DeliverToLocationID " +
"join Location fl on fl.LocationID = rl.FinalDestinationLocationID " +
"left join VehicleType v on v.VehicleTypeID = rl.VehicleTypeID " +
"where YEAR(DutyDate) = " + year +
" order by rl.DutyDate desc, rl.CallDateTime desc;";
DataTable ret = DBHelperFactory.DBHelper().ExecuteDataTable(sql);
return(ret);
}
public DataTable ExecuteSQL(string sql)
{
return(DBHelperFactory.DBHelper().ExecuteDataTable(sql));
}
public DataTable RunReport(SERVDataContract.Report report)
{
return(DBHelperFactory.DBHelper().ExecuteDataTable(report.Query));
}
private int GetTagId(string tagName)
{
return(Convert.ToInt32(DBHelperFactory.DBHelper().ExecuteScalar("select TagID from Tag where Tag = " + SERV.Utils.String.DBSafeString(tagName))));
}