public MN_Card_Custom GetInfoByCardNumber(string cardnumber)
{
var query = new StringBuilder();
query.AppendLine("select c.CardNumber,c.CardNo,cu.CustomerName,cu.Mobile as CustomerMobile,cu.Address as CustomerAddress,cu.Avatar,cu.CustomerGroupID,cg.CustomerGroupName from tblCard c");
query.AppendLine("left join tblCustomer cu on c.CustomerID = CONVERT(varchar(255), cu.CustomerID)");
query.AppendLine("left join tblCustomerGroup cg on cu.CustomerGroupID = CONVERT(varchar(255), cg.CustomerGroupID)");
query.AppendLine(string.Format("where c.CardNumber = '{0}'", cardnumber));
return(SqlExQuery <MN_Card_Custom> .ExcuteQuery(query.ToString()).FirstOrDefault());
}
public List <BM_ResidentCustom> GetResidentPaging(string key, string group, int pageNumber, int pageSize, ref int total)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("SELECT * FROM(");
sb.AppendLine("select ROW_NUMBER() OVER(ORDER BY a.Name asc) AS RowNumber, * from (");
sb.AppendLine("select r.*,g.Name as GroupName from BM_Resident r");
sb.AppendLine("left join BM_ResidentGroup g on g.Id = r.ResidentGroupId");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%' OR r.Mobile like '%{0}%' OR r.Email like N'%{0}%')", key));
}
if (!string.IsNullOrEmpty(group) && !group.Equals("0"))
{
sb.AppendLine(string.Format(" AND (g.ParentId = '{0}' or g.Id = '{0}') ", group));
}
sb.AppendLine(") as a");
sb.AppendLine(") as b");
sb.AppendLine(string.Format("WHERE RowNumber BETWEEN(({0}-1) * {1}+1) AND({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_ResidentCustom> .ExcuteQuery(sb.ToString());
//Tính tổng
sb.Clear();
sb.AppendLine("select count(r.Id) as TotalCount from BM_Resident r");
sb.AppendLine("left join BM_ResidentGroup g on g.Id = r.ResidentGroupId");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%' OR r.Mobile like '%{0}%' OR r.Email like N'%{0}%')", key));
}
if (!string.IsNullOrEmpty(group) && !group.Equals("0"))
{
sb.AppendLine(string.Format(" AND (g.ParentId = '{0}' or g.Id = '{0}') ", group));
}
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(sb.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public List <BM_ResidentCustom> GetMemberApartment(string ApartmentId)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("select m.RoleId,r.Id,r.Name,r.Mobile,r.Email");
sb.AppendLine("from BM_Apartment_Member m");
sb.AppendLine("left join BM_Resident r on r.Id = m.ResidentId");
sb.AppendLine(string.Format("where m.IsDeleted = 'False' and m.ApartmentId = '{0}'", ApartmentId));
var listData = SqlExQuery <BM_ResidentCustom> .ExcuteQuery(sb.ToString());
return(listData);
}
public List <tblCustomer> GetListCustmer(string key, string customerGr, string selectedId, int page, int pageSize, ref int totalItem)
{
var query = new StringBuilder();
query.AppendLine("Select* FROM (");
query.AppendLine("SELECT ROW_NUMBER () OVER ( Order by CustomerName desc) AS RowNumber, a.*,");
query.AppendLine("CONvert (varchar(50) ,CustomerID) as cs");
query.AppendLine(" FROM ( ");
query.AppendLine(" SELECT a.[CustomerID]");
query.AppendLine(" ,a.[CustomerCode]");
query.AppendLine(" ,a.[CustomerName]");
query.AppendLine(" ,a.[Address]");
query.AppendLine(" ,a.[IDNumber]");
query.AppendLine(" ,a.[Mobile]");
query.AppendLine(" ,a.[CustomerGroupID]");
query.AppendLine(" ,a.[Description]");
query.AppendLine(" ,a.[EnableAccount]");
query.AppendLine(" ,a.[Account]");
query.AppendLine(" ,a.[Password]");
query.AppendLine(" ,a.[Avatar]");
query.AppendLine(" ,a.[Inactive]");
query.AppendLine(" ,a.[SortOrder]");
query.AppendLine(" ,a.[CompartmentId]");
query.AppendLine(" ,a.[AccessLevelID]");
query.AppendLine(" ,a.[Finger1]");
query.AppendLine(" ,a.[Finger2]");
query.AppendLine(" ,a.[UserIDofFinger]");
query.AppendLine(" ,a.[DevPass]");
query.AppendLine(" ,a.[AccessExpireDate]");
query.AppendLine(" ,a.[ContractStartDate]");
query.AppendLine(" ,a.[ContractEndDate]");
query.AppendLine(" ,a.[AddressUnsign] FROM tblCustomer a");
query.AppendLine(")as a");
query.AppendLine(")as c1");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", page, pageSize));
var list = SqlExQuery <tblCustomer> .ExcuteQuery(query.ToString());
//tính tổng
query.Clear();
query.AppendLine("SELECT COUNT(*) TotalCount");
query.AppendLine("FROM tblCustomer c WITH(NOLOCK)");
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(query.ToString());
totalItem = _total != null ? _total.TotalCount : 0;
return(list);
}
public List <BM_Building_ServiceCustom> GetServiceApartment(string ApartmentId)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("select r.Id, r.Name, m.SchedulePay, m.ScheduleType, m.Price, ");
sb.AppendLine("CONVERT(varchar(50),FORMAT(m.StartDate,'dd/MM/yyyy')) as DateStart,");
sb.AppendLine("CONVERT(varchar(50),FORMAT(m.EndDate,'dd/MM/yyyy')) as DateEnd");
sb.AppendLine("from BM_Apartment_Service m");
sb.AppendLine("left join BM_Building_Service r on r.Id = m.ServiceId");
sb.AppendLine(string.Format("where m.IsDeleted = 'False' and m.ApartmentId = '{0}'", ApartmentId));
var listData = SqlExQuery <BM_Building_ServiceCustom> .ExcuteQuery(sb.ToString());
return(listData);
}
public List <BM_ApartmentCustom> GetApartmentPaging(string key, int pageNumber, int pageSize, ref int total)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("SELECT * FROM(");
sb.AppendLine("select ROW_NUMBER() OVER(ORDER BY a.Name asc) AS RowNumber, a.*,");
sb.AppendLine("b.Name AS BuildingName,f.Name AS FloorName ");
sb.AppendLine("from BM_Apartment a");
sb.AppendLine("left join BM_Building b on b.Id = a.BuildingId");
sb.AppendLine("left join BM_Floor f on f.Id = a.FloorId");
sb.AppendLine("where a.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (a.Name like N'%{0}%')", key));
}
sb.AppendLine(") as b");
sb.AppendLine(string.Format("WHERE RowNumber BETWEEN(({0}-1) * {1}+1) AND({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_ApartmentCustom> .ExcuteQuery(sb.ToString());
//Tính tổng
sb.Clear();
sb.AppendLine("select count(a.Id) as TotalCount");
sb.AppendLine("from BM_Apartment a");
sb.AppendLine("left join BM_Building b on b.Id = a.BuildingId");
sb.AppendLine("left join BM_Floor f on f.Id = a.FloorId");
sb.AppendLine("where a.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (a.Name like N'%{0}%')", key));
}
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(sb.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public List <BM_Building_ServiceCustom> GetResidentPaging(string key, int pageNumber, int pageSize, ref int total)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("SELECT * FROM(");
sb.AppendLine("select ROW_NUMBER() OVER(ORDER BY a.Name asc) AS RowNumber, * from (");
sb.AppendLine("select r.* from BM_Building_Service r");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%')", key));
}
sb.AppendLine(") as a");
sb.AppendLine(") as b");
sb.AppendLine(string.Format("WHERE RowNumber BETWEEN(({0}-1) * {1}+1) AND({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_Building_ServiceCustom> .ExcuteQuery(sb.ToString());
//Tính tổng
sb.Clear();
sb.AppendLine("select count(r.Id) as TotalCount from BM_Building_Service r");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%')", key));
}
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(sb.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public List <BM_Apartment_ReceiptView> GetAllPagingByFirstTSQL(string key, string buildingIdSearch, string floorIdSearch, string apartmentIdSearch, string residentIdSearch, string typeSearch, string statusSearch, string userSearch, string userCreatedIdSearch, string fromDatePaid, string toDatePaid, int pageNumber, int pageSize, ref int total)
{
//if (!string.IsNullOrEmpty(fromDatePaid))
//{
// fromDatePaid = Convert.ToDateTime(fromDatePaid).ToString("yyyy/MM/dd HH:mm:ss");
//}
//if (!string.IsNullOrEmpty(toDatePaid))
//{
// toDatePaid = Convert.ToDateTime(toDatePaid).ToString("yyyy/MM/dd HH:mm:ss");
//}
//if (!string.IsNullOrEmpty(key))
// query.AppendLine(string.Format("AND (LP.CardNo LIKE '%{0}%' OR LP.CardNumber LIKE '%{0}%' OR U.Name LIKE '%{0}%' OR LC.ControllerName LIKE '%{0}%')", KeyWord));
////search toDate - fromdate
//if (!string.IsNullOrEmpty(fromDatePaid) && !string.IsNullOrEmpty(toDatePaid))
//{
// query.AppendLine(string.Format("AND LP.DateCreated >= '{0}' AND LP.DateCreated <= '{1}'", fromDatePaid, toDatePaid));
//}
//else if (!string.IsNullOrEmpty(fromDatePaid) && string.IsNullOrEmpty(toDatePaid))
//{
// query.AppendLine(string.Format("AND LP.DateCreated >= '{0}'", fromDatePaid));
//}
//else if (string.IsNullOrEmpty(fromDatePaid) && !string.IsNullOrEmpty(toDatePaid))
//{
// query.AppendLine(string.Format("AND LP.DateCreated <= '{0}'", toDatePaid));
//}
var query = new StringBuilder();
//danh sách
query.AppendLine("SELECT * FROM(");
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.DateCreated desc) as RowNumber,a.*");
query.AppendLine("FROM(");
query.AppendLine("SELECT AR.* from BM_Apartment_Receipt AR");
//query.AppendLine("inner join [User] U on U.Id = LP.UserId ");
//query.AppendLine("left join [tblLockerController] LC on LC.Id = LP.ControllerID ");
query.AppendLine("where 1=1");
query.AppendLine(") AS a");
query.AppendLine(") AS b");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_Apartment_ReceiptView> .ExcuteQuery(query.ToString());
//Tính tổng
query.Clear();
query.AppendLine("SELECT COUNT(*) TotalCount");
query.AppendLine("FROM BM_Apartment_Receipt AR WITH(NOLOCK)");
//query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(varchar(50), cg.CardGroupID)");
//query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(varchar(50), cu.CustomerID)");
//query.AppendLine("LEFT JOIN tblCustomerGroup cug ON cu.CustomerGroupID = CONVERT(varchar(50), cug.CustomerGroupID)");
query.AppendLine("WHERE 1=1");
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(query.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public SelectListModel CountEventEveryDay(string fromdate, string todate)
{
//Model send back
var model = new SelectListModel();
//
var fdate = "";
var tdate = "";
int inNumber = 0;
int outNumber = 0;
//Check using Loop
var check = _tblLaneService.GetAllActive().FirstOrDefault(n => n.IsLoop == true);
//Date time
if (!string.IsNullOrWhiteSpace(fromdate) && !string.IsNullOrWhiteSpace(todate))
{
fdate = Convert.ToDateTime(fromdate).ToString("yyyy/MM/dd 00:00:00");
tdate = Convert.ToDateTime(todate).AddDays(1).ToString("yyyy/MM/dd 00:00:00");
}
else
{
fdate = DateTime.Now.ToString("yyyy/MM/dd 00:00:00");
tdate = DateTime.Now.AddDays(1).ToString("yyyy/MM/dd 00:00:00");
}
//IN
var sqlCardEventIN = new StringBuilder();
sqlCardEventIN.AppendLine("SELECT * from tblCardEvent WITH (NOLOCK)");
sqlCardEventIN.AppendLine("WHERE IsDelete = 0");
sqlCardEventIN.AppendLine(string.Format("AND ( DateTimeIn >= '{0}' AND DateTimeIn < '{1}' )", fdate, tdate));
var cardeIN = SqlExQuery <tblCardEvent> .ExcuteQuery(sqlCardEventIN.ToString()).Count;
inNumber += cardeIN;
if (check != null)
{
var sqlLoopEventIN = new StringBuilder();
sqlLoopEventIN.AppendLine("SELECT * from tblLoopEvent WITH (NOLOCK)");
sqlLoopEventIN.AppendLine("WHERE IsDelete = 0 AND EventCode = '2'");
sqlLoopEventIN.AppendLine(string.Format("AND ( DateTimeIn >= '{0}' AND DateTimeIn < '{1}' )", fdate, tdate));
var loopeIN = SqlExQuery <tblLoopEvent> .ExcuteQuery(sqlLoopEventIN.ToString()).Count;
inNumber += loopeIN;
}
//OUT
var sqlCardEventOUT = new StringBuilder();
sqlCardEventOUT.AppendLine("SELECT * from tblCardEvent WITH (NOLOCK)");
sqlCardEventOUT.AppendLine("WHERE IsDelete = 0 AND EventCode = '2'");
sqlCardEventOUT.AppendLine(string.Format("AND ( DateTimeOut >= '{0}' AND DateTimeOut < '{1}' )", fdate, tdate));
var cardeOUT = SqlExQuery <tblCardEvent> .ExcuteQuery(sqlCardEventOUT.ToString()).Count;
outNumber += cardeOUT;
if (check != null)
{
var sqlLoopEventOUT = new StringBuilder();
sqlLoopEventOUT.AppendLine("SELECT * from tblLoopEvent WITH (NOLOCK)");
sqlLoopEventOUT.AppendLine("where IsDelete = 0 AND EventCode = '2'");
sqlLoopEventOUT.AppendLine(string.Format("AND ( DateTimeOut >= '{0}' AND DateTimeOut < '{1}' )", fdate, tdate));
var loopeOUT = SqlExQuery <tblLoopEvent> .ExcuteQuery(sqlLoopEventOUT.ToString()).Count;
outNumber += loopeOUT;
}
model.ItemValue = inNumber.ToString();
model.ItemText = outNumber.ToString();
return(model);
}