public bool CheckCardInEvent(string cardnumber)
{
var query = new StringBuilder();
query.AppendLine("SELECT TOP 1(e.Id) FROM dbo.[tblCardEvent] e WITH(NOLOCK)");
query.AppendLine(string.Format("Where e.[CardNumber] = '{0}'", cardnumber));
var obj = ExcuteSQLEvent.GetDataSet(query.ToString(), false);
return(ExcuteSQLEvent.ConvertTo <ReportInOut_API_3rd_data>(obj.Tables[0]).Count > 0 ? true : false);
}
/// <summary>
/// get event in out
/// </summary>
/// <param name="KeyWord"></param>
/// <param name="IsHaveTimeIn"></param>
/// <param name="fromdate"></param>
/// <param name="todate"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <ReportInOut_API_3rd_data> GetReportInOut(string key, string cardGroupId, bool isHaveTimeIn, string vehicleGroupId, string fromdate, string todate, int pageIndex, int pageSize, ref int totalItem, ref int totalPage)
{
if (!string.IsNullOrEmpty(fromdate))
{
fromdate = Convert.ToDateTime(fromdate).ToString("yyyy/MM/dd HH:mm:ss");
}
if (!string.IsNullOrEmpty(todate))
{
todate = Convert.ToDateTime(todate).ToString("yyyy/MM/dd HH:mm:ss");
}
var query = new StringBuilder();
query.AppendLine("SELECT * FROM(");
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.[DatetimeOut] desc) as RowNumber,a.*");
query.AppendLine("FROM(");
query.AppendLine("SELECT e.Id, e.[CardNumber], CAST(CASE WHEN e.[PlateOut] <> '' THEN e.[PlateOut] ELSE e.[PlateIn] END AS nvarchar(50)) as Plate, CONVERT(VARCHAR, e.[DatetimeIn], 126) AS DatetimeIn ,CONVERT(VARCHAR, e.[DatetimeOut], 126) AS DatetimeOut, e.[Moneys], e.[CardGroupID],e.[VehicleGroupID]");
query.AppendLine("FROM dbo.[tblCardEvent] e WITH(NOLOCK)");
if (isHaveTimeIn)
{
query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '1'");
}
else
{
query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '2'");
}
query.AppendLine(string.Format("{0}", isHaveTimeIn ? string.Format("AND e.[DatetimeIn] >= '{0}' AND e.[DatetimeIn] <= '{1}' ", fromdate, todate) : string.Format("AND e.[DatetimeOut] >= '{0}' AND e.[DatetimeOut] <= '{1}' ", fromdate, todate)));
if (!string.IsNullOrWhiteSpace(key))
{
query.AppendLine(string.Format("AND (e.[CardNumber] LIKE '%{0}%' OR REPLACE(REPLACE(e.[PlateIn], '-', ''), '.', '') LIKE '%{0}%' OR REPLACE(REPLACE(e.[PlateOut], '-', ''), '.', '') LIKE '%{0}%')", key));
}
if (!string.IsNullOrWhiteSpace(cardGroupId))
{
query.AppendLine(string.Format("AND e.[CardGroupID] = '{0}'", cardGroupId));
}
if (!string.IsNullOrWhiteSpace(vehicleGroupId))
{
query.AppendLine(string.Format("AND e.[VehicleGroupID] = '{0}'", vehicleGroupId));
}
query.AppendLine(") as a");
query.AppendLine(") as TEMP");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageIndex, pageSize));
//--Count Total
query.AppendLine("SELECT COUNT(Id) as totalCount");
query.AppendLine("FROM ( SELECT Id FROM dbo.[tblCardEvent]");
query.AppendLine("e WITH(NOLOCK)");
if (isHaveTimeIn)
{
query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '1'");
}
else
{
query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '2'");
}
query.AppendLine(string.Format("{0}", isHaveTimeIn ? string.Format("AND e.[DatetimeIn] >= '{0}' AND e.[DatetimeIn] <= '{1}' ", fromdate, todate) : string.Format("AND e.[DatetimeOut] >= '{0}' AND e.[DatetimeOut] <= '{1}' ", fromdate, todate)));
////Nhom the
//if (!string.IsNullOrWhiteSpace(CardGroupID))
//{
// var t = CardGroupID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
// if (t.Any())
// {
// var count = 0;
// query.AppendLine("and e.CardGroupID IN ( ");
// foreach (var item in t)
// {
// count++;
// query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
// }
// query.AppendLine(" )");
// }
//}
if (!string.IsNullOrWhiteSpace(key))
{
query.AppendLine(string.Format("AND (e.[CardNumber] LIKE '%{0}%' OR REPLACE(REPLACE(e.[PlateIn], '-', ''), '.', '') LIKE '%{0}%' OR REPLACE(REPLACE(e.[PlateOut], '-', ''), '.', '') LIKE '%{0}%')", key));
}
if (!string.IsNullOrWhiteSpace(cardGroupId))
{
query.AppendLine(string.Format("AND e.[CardGroupID] = '{0}'", cardGroupId));
}
if (!string.IsNullOrWhiteSpace(vehicleGroupId))
{
query.AppendLine(string.Format("AND e.[VehicleGroupID] = '{0}'", vehicleGroupId));
}
query.AppendLine(") as e");
var list = ExcuteSQLEvent.GetDataSet(query.ToString(), false);
totalItem = list.Tables.Count > 1 ? Convert.ToInt32(list.Tables[1].Rows[0]["totalCount"].ToString()) : 0;
totalPage = totalItem % pageSize > 0 ? totalItem / pageSize + 1 : totalItem / pageSize;
return(ExcuteSQLEvent.ConvertTo <ReportInOut_API_3rd_data>(list.Tables[0]));
}
public List <ReportAlarm> GetAllPagingByFirst(string key, string user, string lane, string alarmCode, string fromdate, string todate, int pageNumber, int pageSize, ref int total)
{
string database = "MPARKING";
if (!string.IsNullOrEmpty(fromdate))
{
fromdate = Convert.ToDateTime(fromdate).ToString("yyyy/MM/dd HH:mm:ss");
}
else
{
fromdate = Convert.ToDateTime(DateTime.Now).ToString("yyyy/MM/dd HH:mm:ss");
}
if (!string.IsNullOrEmpty(todate))
{
todate = Convert.ToDateTime(todate).ToString("yyyy/MM/dd HH:mm:ss");
}
else
{
todate = Convert.ToDateTime(DateTime.Now).ToString("yyyy/MM/dd HH:mm:ss");
}
var query = new StringBuilder();
query.AppendLine("select DISTINCT * FROM(");
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.[Date] desc) AS RowNumber, a.*");
query.AppendLine(string.Format(",ISNULL((select CardNo from [{0}].dbo.[tblCard] c where c.IsLock = '0' AND c.IsDelete = '0' and c.CardNumber = a.CardNumber),'') as CardNo", database));
query.AppendLine("FROM dbo.[tblAlarm] a WITH (NOLOCK)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND a.[Date] >= '{0}' AND a.[Date] <= '{1}'", fromdate, todate));
if (!string.IsNullOrWhiteSpace(alarmCode))
{
query.AppendLine(string.Format("AND a.[AlarmCode] = '{0}'", alarmCode));
}
if (!string.IsNullOrWhiteSpace(lane))
{
var t = lane.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and LaneID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(user))
{
var t = user.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(key))
{
query.AppendLine(string.Format("AND (a.[CardNumber] LIKE '%{0}%' OR a.[Plate] LIKE '%{0}%')", key));
}
query.AppendLine(") as a");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageNumber, pageSize));
//COUNT TOTAL
query.AppendLine("SELECT COUNT(Id) totalCount");
query.AppendLine("FROM dbo.[tblAlarm] a WITH (NOLOCK)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND a.[Date] >= '{0}' AND a.[Date] <= '{1}'", fromdate, todate));
if (!string.IsNullOrWhiteSpace(alarmCode))
{
query.AppendLine(string.Format("AND a.[AlarmCode] = '{0}'", alarmCode));
}
if (!string.IsNullOrWhiteSpace(lane))
{
var t = lane.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and LaneID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(user))
{
var t = user.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(key))
{
query.AppendLine(string.Format("AND (a.[CardNumber] LIKE '%{0}%' OR a.[Plate] LIKE '%{0}%' )", key));
}
var list = ExcuteSQLEvent.GetDataSet(query.ToString(), false);
total = list.Tables.Count > 1 ? Convert.ToInt32(list.Tables[1].Rows[0]["totalCount"].ToString()) : 0;
return(ExcuteSQLEvent.ConvertTo <ReportAlarm>(list.Tables[0]));
}