public Task <List <Product> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "")
{
var product = Task.FromResult(_dapperManager.GetAll <Product>
($"select P.ProductName, P.ProductPrice, P.ProductOrigin, P.ProductDetails , sum(S.StockQty) As ProductTotalStock from [Product] as P Inner Join [Stock] as S on P.ID = S.ProductID WHERE P.ProductName like '%{search}%' AND P.DELETED = 'False' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY group by P.ProductName, P.ProductOrigin, P.ProductPrice, P.ProductDetails; ", null, commandType: CommandType.Text));
return(product);
}
//Metodo que obtiene una lista de todos los clientes habilitados de la empresa, que recibe por parametros el id de la institucion
public Task <List <Clientes> > ListarClientes(string id_institucion)
{
var clientes = Task.FromResult(_dapperManager.GetAll <Clientes>
($"SELECT * FROM [Clientes] WHERE ID_Institucion = '{id_institucion}' AND Estado = 'Habilitado'", null, commandType: CommandType.Text));
return(clientes);
}
public Task <List <Vehiculos> > ListAll(string id_institucion)
{
var vehiculos = Task.FromResult(_dapperManager.GetAll <Vehiculos>
($"SELECT * FROM [Vehiculos] WHERE ID_Institucion = '{id_institucion}' AND Estado = 'Habilitado'", null, commandType: CommandType.Text));
return(vehiculos);
}
public Task <List <Customer> > ListAll(string customerID)
{
var customer = Task.FromResult(_dapperManager.GetAll <Customer>
($"SELECT * FROM [Customer] WHERE CAST(CustomerPhone AS VARCHAR(20)) LIKE '{customerID}%';", null, commandType: CommandType.Text));
return(customer);
}
public Task <List <Stock> > ListAll(int skip, int take, string orderBy, int productID, string direction = "DESC")
{
var stock = Task.FromResult(_dapperManager.GetAll <Stock>
($"SELECT * FROM [Stock] WHERE ProductID = {productID} ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text));
return(stock);
}
public Task <Clients> GetById(int id)
{
return(Task.Run(() =>
{
var article = _dapperManager.Get <Clients>($"select * from [Clients] where ClientId = {id}", null,
commandType: CommandType.Text);
article.BookList = _dapperManager.GetAll <Books>($"select b.* From Books as b Inner join ClientBook as c on c.BookId = b.BookId where c.ClientId = {id}", null,
commandType: CommandType.Text);
return article;
}));
}
public async Task <List <dtInvAccounts> > GetAccounts(string AccCategory)
{
var dbPara = new DynamicParameters();
dbPara.Add("AccCategory", AccCategory, DbType.String);
dbPara.Add("Criteria", "AccountMaster", DbType.String);
//dtInvVoucher voucher = new dtInvVoucher();
var Accounts = Task.FromResult(_dapperManager.GetAll <dtInvAccounts>
("[FINWEB_INVENTORYVoucherSP]", dbPara,
commandType: CommandType.StoredProcedure));
//($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text));
return(await Accounts);
}
public Task <Books> GetById(int id)
{
return(Task.Run(() =>
{
var article = _dapperManager.Get <Books>($"select * from [Books] where BookId = {id}", null,
commandType: CommandType.Text);
article.AuthorList = _dapperManager.GetAll <Authors>
($"SELECT a.* FROM dbo.Authors a INNER JOIN dbo.AuthorBook ab ON a.AuthorId = ab.AuthorId WHERE ab.BookId = {id}; ", null, commandType: CommandType.Text);
article.BookGenreList = _dapperManager.GetAll <BookGenres>
($"SELECT a.* FROM dbo.BookGenres a INNER JOIN dbo.BookBookGenres ab ON a.BookGenreId = ab.BookGenreId WHERE ab.BookId = {id}; ", null, commandType: CommandType.Text);
return article;
}
));
}
public Task <List <Factura> > ListAll(string id_institucion)
{
var vehiculos = Task.FromResult(_dapperManager.GetAll <Factura>
($"SELECT * FROM [Factura] WHERE ID_Institucion = '{id_institucion}'", null, commandType: CommandType.Text));
return(vehiculos);
}
public Task <List <c_order> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "")
{
var c_Orders = Task.FromResult(_dapperManager.GetAll <c_order>
($"SELECT * FROM c_order WHERE name like '%{search}%' ORDER BY {orderBy} {direction} LIMIT {skip}, {take} ; ", null, commandType: CommandType.Text));
return(c_Orders); //LIMIT {skip} ROWS FETCH NEXT {take} ROWS ONLY
}
public Task <List <Article> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "")
{
var articles = Task.FromResult(_dapperManager.GetAll <Article>
($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text));
return(articles);
}
public Task <List <Article> > ListAll(int skip, int take, string orderBy, string direction = "DESC", string search = "")
{
var articles = Task.FromResult(_dapperManager.GetAll <Article>
($"WITH Artcl AS (SELECT ROW_NUMBER() OVER(ORDER BY a.ID) AS rownumber, * FROM [Article] as a WHERE Title LIKE '%{search}%') SELECT * FROM Artcl WHERE (rownumber > {take} * {skip}) AND (rownumber <= {take} * {skip + 1}) ORDER BY {orderBy} {direction}; ", null, commandType: CommandType.Text));
return(articles);
}
public async Task <List <LoginModel> > LoginUserNew(string UserName, string Password)
{
var dbPara = new DynamicParameters();
//dbPara.Add("Criteria", "AccountMaster", DbType.String);
var Accounts = Task.FromResult(_dapperManager.GetAll <LoginModel>
("Select ID,UserName,Password from [dbo].[Users]", dbPara,
commandType: CommandType.Text));
return(await Accounts);
}
public async override Task <IResponseMessage <GeneralSettingsVM> > GetAll(QueryParameters request)
{
string sql = "";
var dbPara = new DynamicParameters();
#region Page and filter code sample Codes
//string filter = string.IsNullOrEmpty(request.filter) ? "" :request.filter;
//dbPara.Add("@Filter", (string.IsNullOrEmpty(filter) ? "" : filter.ToLower()), DbType.String);
//dbPara.Add("@Offset", request.Offset, DbType.Int32);
//dbPara.Add("@Next", request.Next, DbType.Int32);
//string order = "";
//if (string.IsNullOrEmpty(request.OrderBy))
//{
// order = "BranchID";
//}
//else { order = request.OrderBy; }
//sql = "; with pagedDT as ";
//sql += " ( ";
//sql += " select *, ROW_NUMBER() OVER(ORDER BY " + order + " " + request.SortDirection + ") AS rowNumber from tb_branches ";
//if (!string.IsNullOrEmpty(request.filter))
//{
// sql += " where (Lower(BranchName) like '%'+@Filter+'%' ) ";
//}
//sql += " ) ";
//sql += " select *,(select Count(*) from tb_branches) as TotalCount from pagedDT ";
//sql += " where ";
//sql += " (rowNumber > @Offset and rowNumber<= @Next) ";
//if (settings != null && settings.Count > 0 && !string.IsNullOrEmpty(request.filter))
//{
// settings[0].TotalCount = settings.Count();
//}
#endregion
sql = "select * from dbo.GeneralSettings";
var settings = await Task.FromResult(dapperManager.GetAll <GeneralSettingsVM>(sql, dbPara, commandType: CommandType.Text));
return(new ResponseMessageBase <GeneralSettingsVM>()
{
MessageType = MessageTypes.Success, ResponseData = settings
});
}
public async Task <List <dtInvVoucherEntry> > GetVoucherEntry(long vid)
{
var dbPara = new DynamicParameters();
dbPara.Add("VId", vid, DbType.Int32);
dbPara.Add("Criteria", "VoucherEntry", DbType.String);
//dtInvVoucher voucher = new dtInvVoucher();
var voucherentry = Task.FromResult(_dapperManager.GetAll <dtInvVoucherEntry>
("[FINWEB_INVENTORYVoucherSP]", dbPara,
commandType: CommandType.StoredProcedure));
//($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text));
return(await voucherentry);
}
public async Task <List <VoucherMaster> > ListAll(int vtype)
//(int skip, int take, string orderBy, string direction = "DESC", string search = "")
{
var dbPara = new DynamicParameters();
dbPara.Add("VType", vtype, DbType.Int32);
dbPara.Add("BranchId", 31, DbType.Int32);
dbPara.Add("Criteria", "VoucherMaster", DbType.String);
var vouchermaster = Task.FromResult(_dapperManager.GetAll <VoucherMaster>
("[FINWEB_INVENTORYVoucherSP]", dbPara,
commandType: CommandType.StoredProcedure));
//($"SELECT * FROM [Article] WHERE Title like '%{search}%' ORDER BY {orderBy} {direction} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ", null, commandType: CommandType.Text));
return(await vouchermaster);
}
public Task <List <Article> > ListAll()
{
var articles = Task.FromResult(_dapperManager.GetAll <Article>("select * from [Article]", null, commandType: CommandType.Text));
return(articles);
}