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);
}
public MessageReport DeleteMulti(List <string> lstid)
{
var re = new MessageReport();
re.Message = "Error";
re.isSuccess = false;
try
{
if (lstid != null && lstid.Count > 0)
{
var str = new StringBuilder();
str.AppendLine("delete from PublicEvent where");
var count = 0;
str.AppendLine(" EventID IN ( ");
foreach (var item in lstid)
{
count++;
str.AppendLine(string.Format("'{0}'{1}", item, count == lstid.Count ? "" : ","));
}
str.AppendLine(" )");
ExcuteSQLEvent.GetDataSet(str.ToString(), false);
re.Message = "Xóa thành công";
re.isSuccess = true;
}
else
{
re.Message = "Có lỗi xảy ra";
re.isSuccess = false;
}
}
catch (Exception ex)
{
re.Message = ex.Message;
re.isSuccess = false;
}
return(re);
}
/// <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]));
}
public DataTable ReportGetAllPagingByFirst_TRANSERCO(string key, string user, string lane, string alarmCode, string dateFromPicker)
{
var query = new StringBuilder();
//tblAlarm
//query.AppendLine("select * FROM(");
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.[Date] desc) AS NumberRow, (select convert(varchar(10), a.[Date], 103) + ' ' + left(convert(varchar(32), a.[Date], 108), 8)) as 'Date',a.[CardNumber] as 'CardNumber', a.[Plate] as 'Plate', a.[AlarmCode] as 'AlarmName', a.[LaneID] as 'LaneName', a.Description, a.[UserID] as 'Username'");
query.AppendLine("FROM dbo.[tblAlarm] a WITH (NOLOCK)");
query.AppendLine("WHERE 1 = 1 AND a.[AlarmCode] != '001'");
if (!string.IsNullOrWhiteSpace(dateFromPicker))
{
//ISODate("2017-10-01T00:00:00.000+07:00")
var fromdate = Convert.ToDateTime(dateFromPicker.Split(new[] { '-' })[0]).ToString("yyyy-MM-dd HH:mm:00");
var todate = Convert.ToDateTime(dateFromPicker.Split(new[] { '-' })[1]).ToString("yyyy-MM-dd HH:mm:59");
query.AppendLine(string.Format("AND a.[Date] >= '{0}' AND a.[Date] <= '{1}'", fromdate, todate));
}
else
{
//ISODate("2017-10-01T00:00:00.000+07:00")
var fromdate = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
var todate = DateTime.Now.ToString("yyyy-MM-dd 23:59:59");
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);
return(list.Tables[0]);
}