public static Products InsertUpdate(Products product)
{
string SqlStatement = @"[sp_IU_Products]";
Object xid;
try
{
using (var connection = new SqlConnection(SqlConn))
{
connection.Open();
using (var tr = connection.BeginTransaction())
{
try
{
xid = connection.ExecuteScalar(SqlStatement, new
{
id = product.Id,
code = product.Code,
description = product.Description,
extendedDescription = product.ExtendedDescription,
productBrandId = product.ProductBrand.Id,
rubroId = product.Rubro.Id,
subRubroId = (product.SubRubro == null) ? 0 : product.SubRubro.Id,
sucursalId = product.SucursalId,
umId = product.Unit.Id,
EAN = product.EAN,
externalCode = product.ExternalCode,
price = product.Price,
price2 = product.Price2,
price3 = product.Price3,
stockAlert = product.StockAlert,
isActive = true
}, tr, null, System.Data.CommandType.StoredProcedure);
product.Id = Convert.ToInt32(xid);
BehavioursMapper.InsertUpdateForProductId(product.Id, product.Behaviours, connection, tr);
tr.Commit();
}
catch (Exception)
{
tr.Rollback();
throw;
}
}
}
}
catch (Exception e)
{
throw e;
}
return(new Products());
}
public static Users ValidateUser(string user, string hashpassword)
{
string SqlStatement = @"select a.*, b.* from users a inner join sucursales b on a.sucursalid = b.id
where a.NickName = @user and a.PasswordHash = @hashpassword";
IList <Users> usr;
try
{
using (var connection = new SqlConnection(SqlConn))
{
usr = connection.Query <Users, Sucursal, Users>(SqlStatement,
(user, sucursal) =>
{
user.Sucursal = sucursal;
return(user);
}, new { user, hashpassword }, splitOn: "Id").Distinct().ToList();
if (usr != null && usr.Count() > 0)
{
usr[0].Behaviours = BehavioursMapper.GetByUser(usr[0].Id, connection);
usr[0].Roles = RolesMapper.GetByUser(usr[0].Id, connection);
}
else
{
return(null);
}
}
}
catch (Exception e)
{
throw e;
}
return(usr.First());
}
public static IList <Promotions> GetPromotions(int sucursalId, bool onlyActive = true)
{
String SqlStatement = @"select p.*, s.* from promotions p inner join sucursales s on p.SucursalId = s.id";
String SqlStatement2 = @"select pc.*, r.*, sr.*, sr2.*, sr3.*, sr4.*
from PromotionsConfig pc inner join Rubro r on pc.RubroId = r.Id
inner join SubRubro sr on pc.SubRubroId = sr.Id
inner join SubRubro sr2 on pc.SubRubroId = sr2.Id
inner join SubRubro sr3 on pc.SubRubroId = sr3.Id
inner join SubRubro sr4 on pc.SubRubroId = sr4.Id";
IList <Promotions> promos;
IList <PromotionsConfig> promosConfig;
try
{
using (var connection = new SqlConnection(SqlConn))
{
promos = connection.Query <Promotions, Sucursal, Promotions>(SqlStatement,
(prom, sucursal) =>
{
prom.Sucursal = sucursal;
return(prom);
}, null, splitOn: "Id").Distinct().ToList();
if (promos != null && promos.Count > 0)
{
var behaviours = BehavioursMapper.GetAllByPromotions(connection);
foreach (var item in promos)
{
item.Behaviours = behaviours.Where(b => b.IdParent == item.Id).ToList();
}
promosConfig = connection.Query <PromotionsConfig, Rubro, SubRubro,
SubRubro, SubRubro, SubRubro, PromotionsConfig>(SqlStatement2,
(pc, rubro, subRubro, subRubro2, subRubro3, subRubro4) =>
{
pc.Rubro = rubro;
pc.SubRubro = subRubro;
pc.SubRubro2 = subRubro2;
pc.SubRubro3 = subRubro3;
pc.SubRubro4 = subRubro4;
return(pc);
}, null, splitOn: "Id, Id, Id, Id, Id").Distinct().ToList();
var behaviours2 = BehavioursMapper.GetAllByPromotionsConfig(connection);
foreach (var item in promosConfig)
{
item.Behaviours = behaviours2.Where(b => b.IdParent == item.Id).ToList();
}
}
}
if (onlyActive)
{
promos = promos.Where(p => (p.IsActive == true && p.ActiveTo < System.DateTime.Now)).ToList();
}
}
catch (Exception e)
{
throw e;
}
return(promos);
}
public static Products GetById(int productId)
{
string SqlStatement = @"SELECT p.[Id]
,p.[Code]
,p.[Description]
,p.[ExtendedDescription]
,p.[ProductBrandId]
,p.[RubroId]
,p.[SubRubroId]
,p.[SucursalId]
,p.[UmId]
,p.[EAN]
,p.[ExternalCode]
,p.[Price]
,p.[Price2]
,p.[Price3]
,p.[StockAlert]
,s.[Stock]
,r.*
,sr.*
,u.*
,pb.*
FROM [dbo].[Products] p
INNER JOIN rubro r ON p.RubroId = r.Id
LEFT JOIN SubRubro sr ON p.SubRubroId = sr.Id
INNER JOIN Units u ON p.UmId = u.Id
LEFT JOIN Stock s ON p.Id = s.ProductsId AND s.SucursalId = p.SucursalId OR s.SucursalId IS NULL
LEFT JOIN ProductBrand pb ON p.ProductBrandId = pb.Id OR p.ProductBrandId IS NULL
WHERE p.Id = @productId";
Products prod;
try
{
using (var connection = new SqlConnection(SqlConn))
{
prod = connection.Query <Products, Rubro, SubRubro, Unit, ProductBrand, Products>(SqlStatement,
(prod, rubro, subrubro, unit, productBrand) =>
{
prod.Rubro = rubro;
prod.SubRubro = subrubro;
prod.Unit = unit;
prod.ProductBrand = productBrand;
return(prod);
}, new { productId = productId }, splitOn: "Id, Id, Id, Id, Id").Distinct().FirstOrDefault();
if (prod != null)
{
var behaviours = BehavioursMapper.GetAllByProduct(connection);
prod.Behaviours = behaviours.Where(b => b.IdParent == prod.Id).ToList();
}
}
}
catch (Exception e)
{
throw e;
}
return(prod);
}
public static PageItems <Products> GetToList(int sucursal, int PageSize, int CurrentPag, int RubroId = 0, int SubRubroId = 0)
{
string filterStatement = "";
if (RubroId != 0 && SubRubroId != 0)
{
filterStatement = @"AND (p.RubroId = @rubroId AND p.SubRubroId = @subRubroId) ";
}
else if (RubroId != 0)
{
filterStatement = @"AND p.RubroId = @rubroId ";
}
string SqlStatement = @"SELECT p.[Id]
,p.[Code]
,p.[Description]
,p.[ExtendedDescription]
,p.[ProductBrandId]
,p.[RubroId]
,p.[SubRubroId]
,p.[SucursalId]
,p.[UmId]
,p.[EAN]
,p.[ExternalCode]
,p.[Price]
,p.[Price2]
,p.[Price3]
,p.[StockAlert]
,s.[Stock]
,r.*
,sr.*
,u.*
,pb.*
FROM [dbo].[Products] p
INNER JOIN rubro r ON p.RubroId = r.Id
LEFT JOIN SubRubro sr ON p.SubRubroId = sr.Id
INNER JOIN Units u ON p.UmId = u.Id
LEFT JOIN Stock s ON p.Id = s.ProductsId AND s.SucursalId = p.SucursalId OR s.SucursalId IS NULL
LEFT JOIN ProductBrand pb ON p.ProductBrandId = pb.Id OR p.ProductBrandId IS NULL
WHERE ( p.SucursalId = @sucursalId OR p.SucursalId IS NULL ) AND p.IsActive = 1 " + filterStatement +
"ORDER BY r.id, sr.id OFFSET (@currentPage - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY";
string SqlStatement2 = @"SELECT COUNT(*) FROM Products p
WHERE ( p.SucursalId = @sucursalId OR p.SucursalId IS NULL ) AND p.IsActive = 1 " + filterStatement;
IList <Products> prods;
int recordCounts = 0;
try
{
using (var connection = new SqlConnection(SqlConn))
{
prods = connection.Query <Products, Rubro, SubRubro, Unit, ProductBrand, Products>(SqlStatement,
(prod, rubro, subrubro, unit, productBrand) =>
{
prod.Rubro = rubro;
prod.SubRubro = subrubro;
prod.Unit = unit;
prod.ProductBrand = productBrand;
return(prod);
}, new { sucursalId = sucursal, currentPage = CurrentPag, pageSize = PageSize, rubroId = RubroId, subRubroId = SubRubroId },
splitOn: "Id, Id, Id, Id, Id").Distinct().ToList();
if (prods != null && prods.Count > 0)
{
var behaviours = BehavioursMapper.GetAllByProduct(connection);
foreach (var item in prods)
{
item.Behaviours = behaviours.Where(b => b.IdParent == item.Id).ToList();
}
recordCounts = (int)GetValueSQL(SqlStatement2, new { sucursalId = sucursal, rubroId = RubroId, subRubroId = SubRubroId }, connection);
}
}
}
catch (Exception e)
{
throw e;
}
return(new PageItems <Products>()
{
Items = prods, CurrentPage = CurrentPag, RecordCounts = recordCounts
});
}
public static IList <Products> GetBySucursal(int sucursal)
{
string SqlStatement = @"SELECT p.[Id]
,p.[Code]
,p.[Description]
,p.[ExtendedDescription]
,p.[ProductBrandId]
,p.[RubroId]
,p.[SubRubroId]
,p.[SucursalId]
,p.[UmId]
,p.[EAN]
,p.[ExternalCode]
,p.[Price]
,p.[Price2]
,p.[Price3]
,p.[StockAlert]
,s.[Stock]
,r.*
,sr.*
,u.*
,pb.*
FROM [dbo].[Products] p
INNER JOIN rubro r ON p.RubroId = r.Id
LEFT JOIN SubRubro sr ON p.SubRubroId = sr.Id
INNER JOIN Units u ON p.UmId = u.Id
LEFT JOIN Stock s ON p.Id = s.ProductsId AND s.SucursalId = p.SucursalId OR s.SucursalId IS NULL
LEFT JOIN ProductBrand pb ON p.ProductBrandId = pb.Id OR p.ProductBrandId IS NULL
WHERE ( p.SucursalId = @sucursalId OR p.SucursalId IS NULL ) AND p.IsActive = 1";
IList <Products> prods;
try
{
using (var connection = new SqlConnection(SqlConn))
{
prods = connection.Query <Products, Rubro, SubRubro, Unit, ProductBrand, Products>(SqlStatement,
(prod, rubro, subrubro, unit, productBrand) =>
{
prod.Rubro = rubro;
prod.SubRubro = subrubro;
prod.Unit = unit;
prod.ProductBrand = productBrand;
return(prod);
}, new { sucursalId = sucursal }, splitOn: "Id, Id, Id, Id, Id").Distinct().ToList();
if (prods != null && prods.Count > 0)
{
var behaviours = BehavioursMapper.GetAllByProduct(connection);
foreach (var item in prods)
{
item.Behaviours = behaviours.Where(b => b.IdParent == item.Id).ToList();
}
}
}
}
catch (Exception e)
{
throw e;
}
return(prods);
}
