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