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 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 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]));
}