private List <MCustomer> searchListCustomer() { var keySeach = txtKeySeach.Text.Trim().ToLower(); List <MCustomer> result = new List <MCustomer>(); try { List <MCustomer> lstCustomer = SQLiteCommon.GetALlCustomers(); List <KeyDevice> lstKeyDevice = SQLiteCommon.GetALlKeyDevices(); List <CustomerKey> lstCusKey = SQLiteCommon.GetALlCustomerKeys(); result = (from cus in lstCustomer join cusKey in lstCusKey on cus.ID equals cusKey.CusId into grp from g in grp.DefaultIfEmpty() join keyDevice in lstKeyDevice on g?.KeyCode equals keyDevice.KeyCode into grp1 from g1 in grp1.DefaultIfEmpty() select new MCustomer { ID = cus.ID, CompanyName = cus.CompanyName, CompanyAddress = cus.CompanyAddress, CompanyMobile = cus.CompanyMobile, CusName = cus.CusName, CusEmail = cus.CusEmail, CusMobile = cus.CusMobile, KeyCode = g != null ? g.KeyCode : "", MachineCode = g1 != null ? g1.MachineCode : "", MacAddress = g1 != null ? g1.MacAddress : "", UserId = g1 != null ? g1.UserId : "", CreateDate = g1?.CreateDate }).Where(i => (string.IsNullOrEmpty(keySeach) || i.KeyCode.ToLower().Contains(keySeach) || i.CusName.ToLower().Contains(keySeach) || i.MacAddress.ToLower().Contains(keySeach))).ToList(); return(result); } catch (Exception ex) { log.Error(ex); throw ex; } }
private void loadListCustomer() { var keySeach = txtKeySeach.Text.Trim().ToLower(); DateTime dateFrom = dtpFrom?.Value ?? DateTime.MinValue; DateTime dateTo = dtpTo?.Value ?? DateTime.MinValue; dateFrom = new DateTime(dateFrom.Year, dateFrom.Month, dateFrom.Day, 0, 0, 0); dateTo = new DateTime(dateTo.Year, dateTo.Month, dateTo.Day, 23, 59, 59); try { List <MCustomer> lstCustomer = SQLiteCommon.GetALlCustomers(); List <KeyDevice> lstKeyDevice = SQLiteCommon.GetALlKeyDevices(); List <CustomerKey> lstCusKey = SQLiteCommon.GetALlCustomerKeys(); var lstCusKeyGroup = lstCusKey.GroupBy(i => new { i.CusId, i.KeyCode }); var data = (from cus in lstCustomer join cusKey in lstCusKeyGroup on cus.ID equals cusKey.Key.CusId into grp from g in grp.DefaultIfEmpty() join keyDevice in lstKeyDevice on g?.Key.KeyCode equals keyDevice.KeyCode into grp1 from g1 in grp1.DefaultIfEmpty() select new MCustomer { ID = cus.ID, CompanyName = cus.CompanyName, CompanyAddress = cus.CompanyAddress, CompanyMobile = cus.CompanyMobile, CusName = cus.CusName, CusEmail = cus.CusEmail, CusMobile = cus.CusMobile, KeyCode = g != null ? g.Key.KeyCode : "", MachineCode = g1 != null ? g1.MachineCode : "", MacAddress = g1 != null ? g1.MacAddress : "", UserId = g1 != null ? g1.UserId : "", CreateDate = g1?.CreateDate }).Where(i => (string.IsNullOrEmpty(keySeach) || i.KeyCode.ToLower().Contains(keySeach) || i.CusName.ToLower().Contains(keySeach) || i.MacAddress.ToLower().Contains(keySeach) || i.MachineCode.ToLower().Contains(keySeach)) && (string.IsNullOrEmpty(i.KeyCode) || (i.CreateDate >= dateFrom && i.CreateDate <= dateTo))) .GroupBy(i => new { i.ID, i.CompanyName, i.CompanyAddress, i.CompanyMobile, i.CusName, i.CusMobile, i.CusEmail }) .Select(i => new MCustomer { ID = i.Key.ID, CompanyName = i.Key.CompanyName, CompanyAddress = i.Key.CompanyAddress, CompanyMobile = i.Key.CompanyMobile, CusName = i.Key.CusName, CusEmail = i.Key.CusEmail, CusMobile = i.Key.CusMobile }) .ToList(); BindingDataGridView(dgvCustomerList, data); loadCustomerKey(); } catch (Exception ex) { log.Error(ex); ShowMsg(MessageBoxIcon.Error, ex.Message); } }