public List <ProductMasterEntity> GetProductMasterData(DBSite site, string id = "") { List <ProductMasterEntity> productMasterList = new List <ProductMasterEntity>(); string qry = ""; qry += " SELECT "; qry += " ProductMasterId, ProductID, ProductName, OpeningBalance, ProductDate, 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 "; qry += " WHERE product.UserID = " + Util_BLL.User.UserId; if (id != string.Empty) { qry += " AND ProductMasterId = " + id; } 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"])); DateTime date = Convert.ToDateTime(dr["ProductDate"]); pme.productDate = date; 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"]); productMasterList.Add(pme); } return(productMasterList); }
public List <AccountMasterEntity> GetAccountMasterData(DBSite site, int userId, string id = "") { List <AccountMasterEntity> accountMasterList = 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 GroupID"; qry += ", GroupName "; qry += " FROM tblAccountMaster as acc "; qry += " LEFT OUTER JOIN tblGroup as gp ON "; qry += " acc.GroupId = gp.GroupId "; //qry += Util_BLL.GetUserWhereCondition(); qry += " Where acc.UserId = " + Util_BLL.User.UserId; if (id != string.Empty) { qry += "AND AccountMasterId = " + id; } 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(); DateTime date = Convert.ToDateTime(dr["CreationDate"]); ac.CreationDate = date.ToShortDateString(); 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"]); accountMasterList.Add(ac); } return(accountMasterList); }