public DataTable Excel(string KeyWord)
{
var query = new StringBuilder();
var dtrtn = new DataTable();
try
{
//query.AppendLine("SELECT * FROM(");
query.AppendLine(string.Format("select ROW_NUMBER() OVER(ORDER BY MainCard asc) AS STT,"));
query.AppendLine("a.MainCard as 'Mã thẻ chính', a.[CardNumber] AS 'Mã thẻ',a.[CardNo]");
query.AppendLine("FROM(");
query.AppendLine("SELECT *");
query.AppendLine("FROM dbo.[tblSubCard] e WITH (NOLOCK)");
query.AppendLine("WHERE e.[IsDelete] = 0");
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND (e.[CardNumber] LIKE '%{0}%' OR e.[CardNo] LIKE '%{0}%' OR e.[MainCard] LIKE '%{0}%')", KeyWord));
}
query.AppendLine(") as a");
dtrtn = ExcuteSQL.GetDataSet(query.ToString(), false).Tables[0];
}
catch (Exception ex)
{
}
return(dtrtn);
}
public List <SelectListModelAutocomplete> AutoComplete(string key = "")
{
var cus = new List <SelectListModelAutocomplete>();
var query = new StringBuilder();
query.AppendLine("select top 10 CardNumber, CardNo from tblCard");
query.AppendLine("where IsDelete = 'False'");
if (!string.IsNullOrEmpty(key))
{
query.AppendLine(string.Format("AND (CardNumber like N'%{0}%' OR CardNo LIKE N'%{0}%')", key));
}
query.AppendLine("order by CardNumber,CardNo");
var data = ExcuteSQL.GetDataSet(query.ToString(), false);
var list = ExcuteSQL.ConvertTo <tblCard>(data.Tables[0]);
foreach (var item in list)
{
var t = new SelectListModelAutocomplete();
t.id = item.CardNumber;
t.name = item.CardNumber;
t.value = item.CardNumber;
cus.Add(t);
}
return(cus);
}
public int CountParent()
{
var sb = new StringBuilder();
sb.AppendLine("SELECT COUNT(*) AS COUNT FROM tblCustomerGroup WHERE Inactive = 'False' AND ParentId = '0' ");
var lst = ExcuteSQL.GetDataSet(sb.ToString(), false);
var count = Convert.ToInt32(lst.Tables[0].Rows[0][0].ToString());
return(count);
}
public List <tblCustomer> GetAllActiveByKeySQL(string key, int max)
{
var query = new StringBuilder();
query.AppendLine(string.Format("SELECT top {0} * FROM tblCustomer where Inactive = 'False'", max));
if (!string.IsNullOrEmpty(key))
{
query.AppendLine(string.Format("AND (CustomerCode LIKE '%{0}%' OR CustomerName LIKE N'%{0}%' OR IDNumber LIKE '%{0}%' OR Mobile LIKE '%{0}%')", key));
}
var dataSet = ExcuteSQL.GetDataSet(query.ToString(), false);
var list = ExcuteSQL.ConvertTo <tblCustomer>(dataSet.Tables[0]);
return(list);
}
public List <tblCard> GetListCard(string KeyWord, string AnotherKey, string CardGroupIDs, string CustomerID, string CustomerGroupID, string _newexpire, bool chbEnableMinusActive)
{
var sb = new StringBuilder();
sb.AppendLine("SELECT ca.Cardnumber");
sb.AppendLine("from tblCard ca");
sb.AppendLine("LEFT JOIN dbo.tblCardGroup g ON ca.CardGroupID=g.CardGroupID");
sb.AppendLine("LEFT join tblCustomer cus on ca.CustomerID = CONVERT(varchar(255), cus.CustomerID)");
sb.AppendLine("where ca.IsDelete = 0 and ca.IsLock=0 and g.CardType=0");
//Neu so ngay gia han <0 va neu ko check thi ko cho gia han
if (chbEnableMinusActive == false)
{
sb.AppendLine(string.Format("and DATEDIFF(DAY, ca.[ExpireDate], '{0}') >=0 AND ca.[ExpireDate] <= '{0}'", _newexpire));
}
//Update theo filler
if (!string.IsNullOrWhiteSpace(KeyWord))
{
sb.AppendLine(" and (ca.CardNo LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.CardNumber LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or cus.CustomerCode LIKE '%" + KeyWord + "%'");
sb.AppendLine(" or cus.CustomerName LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or cus.Address LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or cus.AddressUnsign LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.Plate1 LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.Plate2 LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.Plate3 LIKE N'%" + KeyWord + "%')");
}
if (!string.IsNullOrWhiteSpace(AnotherKey))
{
sb.AppendLine(" and (ca.CardNo LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.CardNumber LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or cus.CustomerCode LIKE '%" + AnotherKey + "%'");
sb.AppendLine(" or cus.CustomerName LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or cus.Address LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or cus.AddressUnsign LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.Plate1 LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.Plate2 LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.Plate3 LIKE N'%" + AnotherKey + "%')");
}
if (!string.IsNullOrWhiteSpace(CardGroupIDs))
{
sb.AppendLine(" and ca.CardGroupID = '" + CardGroupIDs + "'");
}
if (!string.IsNullOrWhiteSpace(AuthCardGroupIds))
{
var t = AuthCardGroupIds.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
sb.AppendLine("AND ca.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
sb.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
sb.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(CustomerID))
{
sb.AppendLine(" and ca.CustomerID = '" + CustomerID + "'");
}
if (!string.IsNullOrWhiteSpace(CustomerGroupID))
{
sb.AppendLine(" and cus.CustomerGroupID IN ('" + CustomerGroupID + "')");
}
var list = ExcuteSQL.GetDataSet(sb.ToString(), false);
return(ExcuteSQL.ConvertTo <tblCard>(list.Tables[0]));
}
public DataTable GetReportUploadProcessDetailExcel(string KeyWord, List <string> customerGroupId, string _fromdate, string _todate, string CardGroupID, string Actions, string UserID, string type = "", string eventstatus = "")
{
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(");
//tblCardProcess
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY [Date] desc) AS STT, (select convert(varchar(10), c.[Date], 103) + ' ' + left(convert(varchar(32), c.[Date], 108), 8)) as 'Thời gian', c.CardNumber as 'Mã thẻ', c.[UserIDofFinger] as 'User trên tb', cg.CardGroupName as 'Nhóm thẻ', c.Actions as 'Hành vi', cu.CustomerName as 'Chủ thẻ', '' as 'Nhóm KH',cu.[Address] as 'Địa chỉ', u.UserName as 'NV thực hiện', c.[SuccessControllerIDs] as 'Thiết bị', cu.[CustomerGroupID], c.[EventType], (select convert(varchar(10), c.[AccessDateExpire], 103)) AS 'Hết hạn'");
query.AppendLine("FROM dbo.[tblAccessUploadProcess] c WITH (NOLOCK)");
query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(nvarchar(255), cg.CardGroupID)");
query.AppendLine("LEFT JOIN tblUser u on c.UserID = CONVERT(nvarchar(255), u.UserID)");
//query.AppendLine("LEFT JOIN tblCard ca ON c.CardNumber = ca.CardNumber AND ca.IsDelete = 0");
query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(nvarchar(255),cu.CustomerID)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND c.[Date] >= '{0}' AND c.[Date] <= '{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 c.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(Actions))
{
query.AppendLine(string.Format("AND c.[Actions] = '{0}'", Actions));
}
//User
if (!string.IsNullOrWhiteSpace(UserID))
{
var t = UserID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and c.UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
//Nhom KH
if (customerGroupId.Any())
{
query.AppendLine("AND cu.CustomerGroupID IN (");
var count = 0;
foreach (var item in customerGroupId)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == customerGroupId.Count ? "" : ","));
}
query.AppendLine(")");
}
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND ( c.[CardNumber] LIKE '%{0}%' OR c.[Description] LIKE '%{0}%' )", KeyWord));
}
if (!string.IsNullOrWhiteSpace(type))
{
query.AppendLine(string.Format("AND c.[EventType] LIKE '%{0}%'", type));
}
if (!string.IsNullOrWhiteSpace(eventstatus))
{
query.AppendLine(string.Format("AND c.[Description] LIKE '%{0}%'", eventstatus));
}
query.AppendLine(") as a");
return(ExcuteSQL.GetDataSet(query.ToString(), false).Tables[0]);
}
public List <ReporttblAccessUploadProcess> GetReportUploadProcessDetail(string KeyWord, List <string> customerGroupId, string _fromdate, string _todate, string CardGroupID, string Actions, string UserID, int pageIndex, int pageSize, ref int total, string type = "", string eventstatus = "")
{
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(");
//tblCardProcess
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY [Date] desc) AS RowNumber, cu.CustomerName, cu.[Address], cu.[CustomerGroupID], c.[CardGroupID], c.[Date], c.[UserID], c.[Actions], c.[UserIDofFinger], c.[SuccessControllerIDs], c.[CardNumber], c.[EventType], c.[AccessDateExpire]");
query.AppendLine("FROM dbo.[tblAccessUploadProcess] c WITH (NOLOCK)");
//query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(nvarchar(255), cg.CardGroupID)");
//query.AppendLine("LEFT JOIN tblUser u on c.UserID = CONVERT(nvarchar(255), u.UserID)");
//query.AppendLine("LEFT JOIN tblCard ca ON c.CardNumber = ca.CardNumber AND ca.IsDelete = 0");
query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(nvarchar(255),cu.CustomerID)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND c.[Date] >= '{0}' AND c.[Date] <= '{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 c.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(Actions))
{
query.AppendLine(string.Format("AND c.[Actions] = '{0}'", Actions));
}
//User
if (!string.IsNullOrWhiteSpace(UserID))
{
var t = UserID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and c.UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
//Nhom KH
if (customerGroupId.Any())
{
query.AppendLine("AND cu.CustomerGroupID IN (");
var count = 0;
foreach (var item in customerGroupId)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == customerGroupId.Count ? "" : ","));
}
query.AppendLine(")");
}
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND ( c.[CardNumber] LIKE '%{0}%' OR c.[Description] LIKE '%{0}%' )", KeyWord));
}
if (!string.IsNullOrWhiteSpace(type))
{
query.AppendLine(string.Format("AND c.[EventType] LIKE '%{0}%'", type));
}
if (!string.IsNullOrWhiteSpace(eventstatus))
{
query.AppendLine(string.Format("AND c.[Description] LIKE '%{0}%'", eventstatus));
}
query.AppendLine(") as a");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageIndex, pageSize));
//--COUNT TOTAL
query.AppendLine("SELECT COUNT(Id) totalCount");
query.AppendLine("FROM dbo.[tblAccessUploadProcess] c WITH (NOLOCK)");
//query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(nvarchar(255), cg.CardGroupID)");
//query.AppendLine("LEFT JOIN tblUser u on c.UserID = CONVERT(nvarchar(255), u.UserID)");
//query.AppendLine("LEFT JOIN tblCard ca ON c.CardNumber = ca.CardNumber AND ca.IsDelete = 0");
query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(nvarchar(255),cu.CustomerID)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND c.[Date] >= '{0}' AND c.[Date] <= '{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 c.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(Actions))
{
query.AppendLine(string.Format("AND c.[Actions] = '{0}'", Actions));
}
//User
if (!string.IsNullOrWhiteSpace(UserID))
{
var t = UserID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and c.UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
//Nhom KH
if (customerGroupId.Any())
{
query.AppendLine("AND cu.CustomerGroupID IN (");
var count = 0;
foreach (var item in customerGroupId)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == customerGroupId.Count ? "" : ","));
}
query.AppendLine(")");
}
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND ( c.[CardNumber] LIKE '%{0}%' OR c.[Description] LIKE '%{0}%' )", KeyWord));
}
if (!string.IsNullOrWhiteSpace(type))
{
query.AppendLine(string.Format("AND c.[EventType] LIKE '%{0}%'", type));
}
if (!string.IsNullOrWhiteSpace(eventstatus))
{
query.AppendLine(string.Format("AND c.[Description] LIKE '%{0}%'", eventstatus));
}
var list = ExcuteSQL.GetDataSet(query.ToString(), false);
total = list.Tables.Count > 1 ? Convert.ToInt32(list.Tables[1].Rows[0]["totalCount"].ToString()) : 0;
return(ExcuteSQL.ConvertTo <ReporttblAccessUploadProcess>(list.Tables[0]));
}