//----------------- Search Method [ For Payment ] ---------------------------- public List <SalePurchaseEntity> GetMatchedRecords(DBSite site, string payment_made_or_received, string value_to_search) { List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>(); SalePurchaseEntity sale_purchase_entity = null; string qry = " SELECT DISTINCT LedgerNumber FROM tblLedger as ldg " + " LEFT OUTER JOIN tblAccountMaster as acc" + " ON ldg.AccountMasterId = acc.AccountMasterId " + " WHERE ( " + " ( BillNumber LIKE '%" + value_to_search + "%')" + " OR ( CheckNumber LIKE '%" + value_to_search + "%')" + " OR ( AccountName LIKE '%" + value_to_search + "%')"; if (IsDate(value_to_search)) { qry += " OR ( LDate = '" + value_to_search + "' ) "; } if (util.IsNumber(value_to_search)) { qry += " OR ( Credit = '" + value_to_search + "' ) "; qry += " OR( Debit = '" + value_to_search + "' ) "; } qry += " )"; qry += " AND ldg.UserId = " + Util_BLL.User.UserId; qry += " AND ldg.FYear = " + Util_BLL.User.fYear; qry += " AND ldg.SourceId = " + payment_made_or_received; string ledger_number = null; DataTable dt = site.ExecuteSelect(qry); foreach (DataRow row in dt.Rows) { sale_purchase_entity = new SalePurchaseEntity(); ledger_number = util.CheckNull(row["LedgerNumber"]); sale_purchase_entity = GetPaymentInformation(site, payment_made_or_received, ledger_number)[0]; sale_purchase_entity_list.Add(sale_purchase_entity); } return(sale_purchase_entity_list); }
public List <ProductMasterBLL.ProductMasterEntity> GetMatchedRecords(DBSite site, string value_to_search) { List <ProductMasterBLL.ProductMasterEntity> selected_product_list = new List <ProductMasterBLL.ProductMasterEntity>(); string qry = ""; qry += " SELECT "; qry += " ProductMasterId, ProductID, ProductName, OpeningBalance, CostPrice, SellingPrice "; qry += " , ReOrderQty, ProductDescription, product.UserID, FYear, product.CreatedAt "; qry += ", category.CategoryId CategoryId "; qry += ", CategoryName "; qry += ", uom.UOMId UOMId "; qry += ", product.LocationId LocationId "; qry += ", product.AccountId AccountId "; qry += ", UnitName "; qry += " FROM ( tblProductMaster as product "; qry += " LEFT OUTER JOIN tblProductCategory as category ON "; qry += " product.CategoryId = category.CategoryId )"; qry += " LEFT OUTER JOIN tblUOM as uom ON "; qry += " product.UOM= uom.UOMId "; //------------ selection condition ---------------------- qry += " WHERE product.UserID = " + Util_BLL.User.UserId + " AND "; qry += " (( ProductID LIKE '%" + value_to_search + "%' ) OR"; qry += " ( ProductName LIKE '%" + value_to_search + "%' ) OR"; qry += " ( UnitName LIKE '%" + value_to_search + "%' ) OR"; qry += " ( CategoryName LIKE '%" + value_to_search + "%' ) "; if (util.IsNumber(value_to_search)) { qry += "OR ( OpeningBalance = '" + value_to_search + "' ) OR"; qry += " ( CostPrice = '" + value_to_search + "' ) OR"; qry += " ( SellingPrice = '" + value_to_search + "' ) "; } qry += " ) "; DataTable dt = site.ExecuteSelect(qry); ProductMasterEntity pme; foreach (DataRow dr in dt.Rows) { pme = new ProductMasterEntity(); pme.ProductMasterId = util.CheckNullInt(dr["ProductMasterId"]); pme.ProductId = util.CheckNull(dr["ProductID"]); pme.ProductName = util.CheckNull(dr["ProductName"]); pme.OpeningBalance = util.ToDecimal(util.CheckNullDecimal(dr["OpeningBalance"])); pme.CostPrice = util.ToDecimal(util.CheckNullDecimal(dr["CostPrice"])); pme.SellingPrice = util.ToDecimal(util.CheckNullDecimal(dr["SellingPrice"])); pme.UOM = util.CheckNullInt(dr["UOMId"]); pme.UOMName = util.CheckNull(dr["UnitName"]); pme.CategoryID = util.CheckNullInt(dr["CategoryID"]); pme.CategoryName = util.CheckNull(dr["CategoryName"]); pme.AccountId = util.CheckNullInt(dr["AccountId"]); pme.LocationId = util.CheckNullInt(dr["LocationId"]); pme.ReOrderQty = util.CheckNullDecimal(dr["ReOrderQty"]); pme.ProductDescription = util.CheckNull(dr["ProductDescription"]); pme.UserID = util.CheckNullInt(dr["UserID"]); pme.FYear = util.CheckNullInt(dr["FYear"]); selected_product_list.Add(pme); } return(selected_product_list); }
public List <User> GetMatchedUsers(DBSite site, string value_to_search) { List <User> users = new List <User>(); User user = null; string qry = " SELECT " + " UserInfoId" + ", UserName" + ", Email" + ", SenderID" + ", SMSUser" + ", SMSPassword" + ", SMSDelivery" + ", UserPassword" + ", CreationDate" + ", AmountPaid" + ", StartDate" + ", EndDate" + ", Address" + ", City" + ", Country" + ", Phone" + ", Mobile" + ", TinNumber" + ", SalesTaxNumber" + ", CSTNumber" + ", UserType" + ", Remarks"; qry += " FROM tblUserInfo "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // qry += " WHERE "; //qry += " WHERE UserID = " + User.userID + " AND FYear= " + User.fYear + " AND "; qry += "AND (( UserName LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Email LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Address LIKE '%" + value_to_search + "%' ) OR"; qry += " ( City LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Country LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Phone LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Mobile LIKE '%" + value_to_search + "%' ) OR"; qry += " ( TinNumber LIKE '%" + value_to_search + "%' ) OR"; qry += " ( SalesTaxNumber LIKE '%" + value_to_search + "%' ) OR"; if (util.IsDate(value_to_search)) { qry += " ( CreationDate = '" + value_to_search + "' ) OR"; qry += " ( EndDate = '" + value_to_search + "' ) OR"; qry += " ( StartDate = '" + value_to_search + "' ) OR"; } if (util.IsNumber(value_to_search)) { qry += " ( AmountPaid = '" + value_to_search + "' ) OR "; } qry += " ( CSTNumber LIKE '%" + value_to_search + "%' ) "; qry += ")"; DataTable dt = site.ExecuteSelect(qry); foreach (DataRow row in dt.Rows) { user = new User(); user.UserId = util.CheckNullInt(row["UserInfoId"]); user.UserName = util.CheckNull(row["UserName"]); user.EmailId = util.CheckNull(row["Email"]); user.SenderId = util.CheckNull(row["SenderID"]); user.SMSUser = util.CheckNull(row["SMSUser"]); user.SMSPassword = util.CheckNull(row["SMSPassword "]); user.SMSDelivery = util.CheckNullInt(row["SMSDelivery"]); user.UserPassword = util.CheckNull(row["UserPassword"]); user.AmountPaid = util.CheckNullDecimal(row["AmountPaid"]); user.CreationDate = ((DateTime)row["CreationDate"]).ToShortDateString(); user.StartDate = ((DateTime)row["StartDate"]).ToShortDateString(); user.EndDate = ((DateTime)row["EndDate"]).ToShortDateString(); user.Address = util.CheckNull(row["Address"]); user.City = util.CheckNull(row["City"]); user.Country = util.CheckNull(row["Country"]); user.Phone = util.CheckNull(row["Phone"]); user.Mobile = util.CheckNull(row["Mobile"]); user.TinNumber = util.CheckNull(row["PinNumber"]); user.SalesTaxNumber = util.CheckNull(row["SalesTaxNumber"]); user.CSTNumber = util.CheckNull(row["CSTNumber"]); user.UserType = util.CheckNullInt(row["UserType"]); user.Remarks = util.CheckNull(row["Remarks"]); users.Add(user); } return(users); }
public List <AccountMasterEntity> GetMatchedRecords(DBSite site, string value_to_search) { List <AccountMasterEntity> selected_account_list = new List <AccountMasterEntity>(); string qry = ""; qry += "SELECT "; qry += "AccountMasterId, AccountName, CreationDate, OpeningBalance,"; qry += "DRCR, Address, City, Phone, Mobile, Email,"; qry += " Remarks, acc.UserID, FYear "; qry += ", acc.GroupId "; qry += ", GroupName "; qry += " FROM tblAccountMaster as acc "; qry += " LEFT OUTER JOIN tblGroup as gp ON "; qry += " acc.GroupId = gp.GroupId "; //----- selection condition ---------- qry += " WHERE acc.UserID = " + Util_BLL.User.UserId + " AND FYear= " + Util_BLL.User.fYear + " AND "; //------ user from session ----- qry += " (( AccountName LIKE '%" + value_to_search + "%' ) OR"; if (IsDate(value_to_search)) { qry += " ( CreationDate = '" + value_to_search + "' ) OR"; } qry += " ( Address LIKE '%" + value_to_search + "%' ) OR"; qry += " ( City LIKE '%" + value_to_search + "%' ) OR"; if (util.IsNumber(value_to_search)) { qry += " ( OpeningBalance = '" + value_to_search + "' ) OR"; qry += " ( Phone = '" + value_to_search + "' ) OR"; qry += " ( Mobile = '" + value_to_search + "' ) OR"; } qry += " ( Email LIKE '%" + value_to_search + "%' ) OR"; qry += " ( GroupName LIKE '%" + value_to_search + "%') )"; DataTable dt = site.ExecuteSelect(qry); AccountMasterEntity ac; foreach (DataRow dr in dt.Rows) { ac = new AccountMasterEntity(); ac.AccountMasterId = util.CheckNullInt(dr["AccountMasterId"].ToString()); ac.AccountName = dr["AccountName"].ToString(); ac.CreationDate = util.CheckNullDate(dr["CreationDate"]); ac.OpeningBalance = util.ToDecimal(util.CheckNullDecimal(dr["OpeningBalance"])); ac.DRCR = util.CheckNullInt(dr["DRCR"]); ac.DRCR_Name = (util.CheckNullInt(dr["DRCR"]) == 0) ? "Credit" : "Debit"; ac.Address = util.CheckNull(dr["Address"].ToString()); ac.City = util.CheckNull(dr["City"].ToString()); ac.Phone = util.CheckNull(dr["Phone"].ToString()); ac.Mobile = util.CheckNull(dr["Mobile"].ToString()); ac.Email = util.CheckNull(dr["Email"].ToString()); ac.Remarks = util.CheckNull(dr["Remarks"].ToString()); ac.GroupId = util.CheckNullInt(dr["GroupId"]); ac.GroupName = util.CheckNull(dr["GroupName"]); ac.UserID = util.CheckNullInt(dr["UserID"]); ac.FYear = util.CheckNullInt(dr["FYear"]); selected_account_list.Add(ac); } return(selected_account_list); }