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