using Dapper; using Microsoft.AspNetCore.JsonPatch; using MisIngredientesVue.Core.Exceptions; using MisIngredientesVue.Core.Extensions; using MisIngredientesVue.Core.Models; using SixLabors.ImageSharp.Formats.Png; using SixLabors.ImageSharp.Processing; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; namespace MisIngredientesVue.Core.Services { public interface IClientsService { Task> GetAllClients(string tenantId); Task InsertClient(string tenantId, int createdBy, Client client); Task> GetClients(string tenantId, int skip, int take, string sortBy, string sortOrder, string q, int includeDeleted = 0); Task PatchClient(string tenantId, int modifiedBy, int id, JsonPatchDocument changes); Task GetClient(string tenantId, int id); Task GetGeneralData(string tenantId); Task UpdateGeneralData(string tenantId, ClientGeneralData data, byte[] fileImage = null); Task UpdateClient(string tenantId, int updatedBy, Client client); Task DeleteClients(string tenantId, int deletedById, int[] clients); Task> QuickSearchClients(string tenantId, string q); Task> QuickSearchBrands(string tenantId, string q); Task> QuickSearchBranches(string tenantId, string q); Task> GetBrandBranches(string tenantId, int clientId, int brandId); Task> GetDistinctBranches(string tenantId); Task> GetDistinctClients(string tenantId); Task> GetBrands(string tenantId, int id); Task> GetBrands(string tenantId); Task> GetBranches(string tenantId); Task> GetBrandsAndBranches(string tenantId); Task> GetRfcs(string tenantId); Task AddRfc(string tenantId, int createdById, ClientTaxPayer rfc); Task EditRfc(string tenantId, int id, int updatedById, ClientTaxPayer rfc); Task AddBrandToCurrentClient(string tenantId, int createdById, ClientBrand brand); Task EditBrandFromCurrentClient(string tenantId, int brandId, int updatedById, ClientBrand brand); Task AddBranchToBrand(string tenantId, int clientBrandId, int createdById, ClientBranch branch); Task EditBranchFromBrand(string tenantId, int clientBrandId, int clientBranchId, int updatedById, ClientBranch branch); Task GetProductCount(string tenantId, int[] supplierIds); Task> GetBranchesByBrandIds(string tenantId, int[] brandIds); } public class ClientsService : IClientsService { private readonly IRepository _repository; private readonly IFileStorageService _fileStorageService; private readonly ISettingsService _settingsService; public ClientsService(IRepository repository, IFileStorageService fileStorageService, ISettingsService settingsService) { _repository = repository; _fileStorageService = fileStorageService; _settingsService = settingsService; } public async Task DeleteClients(string tenantId, int deletedById, int[] clients) { await _repository.Query(async (db) => { var strBuilder = new StringBuilder(); for (var i = 0; i < clients.Length; i++) { var clientId = clients[i]; //strBuilder.AppendLine(_repository.GetDeleteQuery // (tenantId, x => x.ClientId.Equals(clientId), ref dynamicParameters)); //strBuilder.AppendLine(_repository.GetDeleteQuery // (tenantId, x => x.ClientId.Equals(clientId), ref dynamicParameters)); //strBuilder.AppendLine(_repository.GetDeleteQuery // (tenantId, x => x.ClientId.Equals(clientId), ref dynamicParameters)); //strBuilder.AppendLine(_repository.GetDeleteQuery // (tenantId, x => x.Id.Equals(clientId), ref dynamicParameters)); strBuilder.Append($@" _count = (SELECT COUNT(*) FROM orders WHERE clientid = {clientId} AND tenantId = '{tenantId}'); IF (_count = 0) THEN DELETE FROM clientbranches WHERE (clientbranches.ClientId = {clientId}) AND clientbranches.TenantId = '{tenantId}' AND clientbranches.IsActive = True; DELETE FROM clienttaxpayers WHERE (clienttaxpayers.ClientId = {clientId}) AND clienttaxpayers.TenantId = '{tenantId}' AND clienttaxpayers.IsActive = True; DELETE FROM clientbrands WHERE (clientbrands.ClientId = {clientId}) AND clientbrands.TenantId = '{tenantId}' AND clientbrands.IsActive = True; UPDATE clients SET isactive = false, modifiedbyid = {deletedById} WHERE (clients.Id = {clientId}) AND clients.TenantId = '{tenantId}' AND clients.IsActive = True; ELSE UPDATE clientbranches SET IsActive = False WHERE (clientbranches.ClientId = {clientId}) AND clientbranches.TenantId = '{tenantId}' AND clientbranches.IsActive = True; UPDATE clienttaxpayers SET IsActive = False WHERE (clienttaxpayers.ClientId = {clientId}) AND clienttaxpayers.TenantId = '{tenantId}' AND clienttaxpayers.IsActive = True; UPDATE clientbrands SET IsActive = False WHERE (clientbrands.ClientId = {clientId}) AND clientbrands.TenantId = '{tenantId}' AND clientbrands.IsActive = True; UPDATE clients SET IsActive = False WHERE (clients.Id = {clientId}) AND clients.TenantId = '{tenantId}' AND clients.IsActive = True; END IF; "); } var query = $@" DO $$ DECLARE _count int; BEGIN {strBuilder} END; $$; "; //Console.WriteLine(query); await db.QueryAsync(query); return ""; }); } public async Task> GetAllClients(string tenantId) { return await _repository.FindAll(tenantId); } public async Task> GetBrandBranches(string tenantId, int clientId, int brandId) { var resultsQuery = $"SELECT * FROM {_repository.GetTableName()} WHERE ClientId = @clientId AND ClientBrandId = @brandId AND TenantId = @tenantId"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, new { clientId, brandId, tenantId }); }); } public async Task> GetBrands(string tenantId, int id) { var resultsQuery = @$"SELECT clientbrands.* FROM clientbrands LEFT JOIN clients ON clientbrands.clientid = clients.id LEFT JOIN client_suppliers ON client_suppliers.client_tenantid = clients.tenantid WHERE ClientId = @id AND (clientbrands.tenantId = @tenantId OR supplier_tenantid = @tenantId)"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, new { id, tenantId }); }); } public async Task GetClient(string tenantId, int id) { return await _repository.Query(async (db) => { var query = $@"SELECT clients.*, clientmintotals.mintotal, clientbrands.*, clientbranches.*, clienttaxpayers.* FROM clients LEFT JOIN clientmintotals ON clientmintotals.clientid = clients.id LEFT JOIN public.clientbrands ON public.clientbrands.clientid = public.clients.id LEFT JOIN public.clientbranches ON public.clientbranches.clientid = public.clients.id LEFT JOIN public.clienttaxpayers ON public.clienttaxpayers.clientid = public.clients.id LEFT JOIN client_suppliers ON client_suppliers.client_tenantid = clients.tenantid WHERE public.clients.id = @id AND (public.clients.tenantId = @tenantId OR supplier_tenantid = @tenantId)"; Client client = null; await db.QueryAsync(query, (c, cb, cbr, ctp) => { if (client == null) { client = c; client.Branches = new List(); client.Brands = new List(); client.TaxPayers = new List(); } if (cbr != null && !client.Branches.Any(x => x.Id == cbr.Id)) client.Branches.Add(cbr); if (cb != null && !client.Brands.Any(x => x.Id == cb.Id)) client.Brands.Add(cb); if (ctp != null && !client.TaxPayers.Any(x => x.Id == ctp.Id)) client.TaxPayers.Add(ctp); return client; }, new { id, tenantId }); return client; }); } public async Task GetGeneralData(string tenantId) { return await _repository.Query(async (db) => { var query = $@"SELECT * FROM public.clients WHERE public.clients.tenantid = @tenantId"; return await db.QuerySingleAsync(query, new { tenantId }); }); } private string GetHashSHA1(byte[] data) { using (var sha1 = new System.Security.Cryptography.SHA1CryptoServiceProvider()) { return string.Concat(sha1.ComputeHash(data).Select(x => x.ToString("X2"))); } } public async Task UpdateGeneralData(string tenantId, ClientGeneralData data, byte[] fileImage = null) { return await _repository.Query(async (db) => { var imageSrc = ""; if (fileImage != null) { using (SixLabors.ImageSharp.Image image = SixLabors.ImageSharp.Image.Load(fileImage)) { int width = 440; int height = 150; image.Mutate(x => x.Resize(new ResizeOptions() { Size = new SixLabors.ImageSharp.Size(width, height), Mode = ResizeMode.Max, Sampler = KnownResamplers.Lanczos3 })); using (var outputImage = new SixLabors.ImageSharp.Image (440, 150)) { outputImage.Mutate(o => o .DrawImage(image, new SixLabors.ImageSharp.Point((440 / 2) - (image.Width / 2), (150 / 2) - (image.Height / 2)), 1f) ); using (var outStream = new MemoryStream()) { outputImage.Save(outStream, new PngEncoder()); fileImage = outStream.ToArray(); } } } await _fileStorageService.SaveFile(tenantId + ".png", "companylogosmi", fileImage); imageSrc = $"/api/account/logo/{tenantId}?t={GetHashSHA1(fileImage)}"; } if (!string.IsNullOrEmpty(imageSrc)) { await _settingsService.SaveSettings(tenantId, new List() { new ClientSetting() { Name = ClientSettingType.CompanyLogoUrl, Value = imageSrc } }); } var query = $@"UPDATE public.clients SET Name = @name, Website = @website, Phone = @phone, Email = @email WHERE public.clients.tenantid = @tenantId"; await db.QueryAsync(query, new { tenantId, name = data.Name, website = data.Website, phone = data.Phone, email = data.Email }); return imageSrc; }); } public async Task> GetClients(string tenantId, int skip, int take, string sortBy, string sortOrder, string q, int includeDeleted = 0) { //1 //return await _repository.Find(tenantId, x => x.Name.Contains(q), skip, take, sortBy, sortOrder); //2 //Expression> filterExpression = (x) => true; //var where = ""; //var dynamicParams = _repository.GetWhereQueryFromExpression(tenantId, filterExpression, out where); //var joins = @" LEFT JOIN users AS u ON u.Id = clients.CreatedById // LEFT JOIN users AS u2 ON u2.Id = clients.ModifiedById"; //if (!string.IsNullOrEmpty(q)) //{ // joins += @" // LEFT JOIN clientbrands ON clientbrands.clientid = clients.id // LEFT JOIN clientbranches ON clientbranches.clientid = clients.id // LEFT JOIN clienttaxpayers ON clienttaxpayers.clientid = clients.id"; // where += @" AND (clients.name ~* @term OR clients.contact ~* @term OR clients.phone ~* @term OR clients.email ~* @term OR // clientbranches.name ~* @term OR clientbranches.manager ~* @term OR clientbranches.manageremail ~* @term OR // clientbranches.managerphone ~* @term OR clientbranches.shippingaddress ~* @term OR clientbranches.shippingdetails ~* @term OR // clientbrands.name ~* @term OR clientbrands.code ~* @term OR clientbrands.contact ~* @term OR clientbrands.contactemail ~* @term OR // clientbrands.contactphone ~* @term OR clientbrands.name ~* @term OR // clienttaxpayers.taxpayeridno ~* @term OR clienttaxpayers.legalname ~* @term OR clienttaxpayers.street ~* @term OR // clienttaxpayers.intnumber ~* @term OR clienttaxpayers.extnumber ~* @term OR clienttaxpayers.neighborhood ~* @term OR // clienttaxpayers.municipality ~* @term OR clienttaxpayers.state ~* @term OR clienttaxpayers.zipcode ~* @term OR // clienttaxpayers.email ~* @term // )"; // dynamicParams.Add("term", q.PrepareQuery()); //} //return await _repository.CustomFind(tenantId, // @$"SELECT DISTINCT public.clients.*, u.*, u2.* FROM public.clients {joins} {where}", // $"SELECT DISTINCT COUNT(*) as total FROM public.clients {joins} {where}", dynamicParams, //skip, take, sortBy, sortOrder, //(async (db, query, dparams) => //{ // Console.Write(query); // var clients = await db.QueryAsync // (query, (cl, u, u2) => // { // return cl; // }, dparams); // return clients; //}), //(async (db, query, dparams) => //{ // return await db.QueryFirstAsync(query, dparams); //})); return await _repository.Query(async (db) => { if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder)) sortOrder = "DESC"; if (string.IsNullOrEmpty(sortBy)) sortBy = "clients.name"; sortOrder = sortOrder.ToUpper(); if (sortOrder != "ASC" && sortOrder != "DESC") sortOrder = "DESC"; if (sortBy == "modifiedById") sortBy = "clients.modifiedbyid"; if (sortBy == "modified") sortBy = "clients.modified"; if (sortBy == "createdById") sortBy = "clients.createdbyid"; if (sortBy == "created") sortBy = "clients.created"; if (!(new[] { "clients.created", "clients.createdById", "clients.modified", "clients.modifiedById" }.Contains(sortBy))) sortBy = "clients.name"; var includeDeletedQuery = "AND public.clients.isactive = true"; if (includeDeleted != 0) includeDeletedQuery = ""; var whereQuery = ""; var term = q.PrepareQueryNoAccents(); if (!string.IsNullOrEmpty(q)) { whereQuery = "AND clients.tags ~* @term"; } var query = $@"SELECT public.clients.*, u.*, u2.* FROM public.clients LEFT JOIN users AS u ON u.Id = clients.CreatedById LEFT JOIN users AS u2 ON u2.Id = clients.ModifiedById LEFT JOIN client_suppliers ON client_suppliers.client_tenantid = clients.tenantid WHERE (public.clients.tenantId = @tenantId OR supplier_tenantid = @tenantId) {includeDeletedQuery} {whereQuery} ORDER BY {sortBy} {sortOrder} LIMIT {take} OFFSET {skip} "; var countQuery = $@"SELECT COUNT(*) FROM clients LEFT JOIN client_suppliers ON client_suppliers.client_tenantid = clients.tenantid WHERE (tenantId = @tenantId OR supplier_tenantid = @tenantId) {includeDeletedQuery} {whereQuery}"; var clients = new List(); await db.QueryAsync(query, (c, u, u2) => { var client = clients.FirstOrDefault(x => x.Id == c.Id); if (client == null) { clients.Add(c); client = c; } client.CreatedBy = u; client.ModifiedBy = u; return client; }, new { tenantId, sortBy, term }); var count = await db.QueryFirstAsync(countQuery, new { tenantId, sortBy, term }); return new FindResults() { Results = clients.ToArray(), Total = count }; }); } public async Task> GetDistinctBranches(string tenantId) { return await _repository.GetDistinct(tenantId, x => x.Id, x => x.Name); } public async Task> GetDistinctClients(string tenantId) { return await _repository.GetDistinct(tenantId, x => x.Id, x => x.Name); } public async Task InsertClient(string tenantId, int createdBy, Client client) { return await _repository.Query(async (db) => { var dynamicParameters = new DynamicParameters(); var tags = new HashSet(); tags.Add(client.Name); tags.Add(client.Email); tags.Add(client.Contact); tags.Add(client.Phone); var insertClientQuery = _repository.GetInsertQuery(tenantId, createdBy, client, ref dynamicParameters, "Id"); var clientId = db.QueryFirst(insertClientQuery, dynamicParameters); var strBuilder = new StringBuilder(); Dictionary brandsDictionary = new Dictionary(); //Marcas foreach (var brand in client.Brands) { tags.Add(brand.Code); tags.Add(brand.Name); tags.Add(brand.Contact); tags.Add(brand.ContactEmail); tags.Add(brand.ContactPhone); brand.ClientId = clientId; brand.TenantId = tenantId; var brandDynamicParameters = new DynamicParameters(); var insertBrandQuery = _repository.GetInsertQuery(tenantId, createdBy, brand, ref brandDynamicParameters, "Id"); var requestBrandId = brand.Id; var brandId = db.QueryFirst(insertBrandQuery, brandDynamicParameters); brand.Id = brandId; brandsDictionary.Add(requestBrandId, brandId); } Dictionary taxPayersDictionary = new Dictionary(); //RFCs foreach (var taxPayer in client.TaxPayers) { tags.Add(taxPayer.LegalName); tags.Add(taxPayer.Email); tags.Add(taxPayer.ExtNumber); tags.Add(taxPayer.IntNumber); tags.Add(taxPayer.Municipality); tags.Add(taxPayer.Neighborhood); tags.Add(taxPayer.Street); tags.Add(taxPayer.State); tags.Add(taxPayer.TaxPayerIdNo); tags.Add(taxPayer.ZipCode); taxPayer.ClientId = clientId; taxPayer.TenantId = tenantId; var taxPayerDynamicParameters = new DynamicParameters(); var insertTaxPayerQuery = _repository.GetInsertQuery(tenantId, createdBy, taxPayer, ref taxPayerDynamicParameters, "Id"); var requestTaxPayerId = taxPayer.Id; var taxPayerId = db.QueryFirst(insertTaxPayerQuery, taxPayerDynamicParameters); taxPayer.Id = taxPayerId; taxPayersDictionary.Add(requestTaxPayerId, taxPayerId); } //Sucursales foreach (var branch in client.Branches) { tags.Add(branch.Name); tags.Add(branch.ManagerEmail); tags.Add(branch.Manager); tags.Add(branch.ManagerPhone); tags.Add(branch.NumInt); tags.Add(branch.ShippingAddress); tags.Add(branch.ShippingDetails); tags.Add(branch.ZipCode); if (branch.TaxPayerId.HasValue) branch.TaxPayerId = taxPayersDictionary.ContainsKey(branch.TaxPayerId.Value) ? (int?)taxPayersDictionary[branch.TaxPayerId.Value] : null; if (branch.ClientBrandId.HasValue) branch.ClientBrandId = brandsDictionary.ContainsKey(branch.ClientBrandId.Value) ? (int?)brandsDictionary[branch.ClientBrandId.Value] : null; branch.ClientId = clientId; branch.TenantId = tenantId; strBuilder.Append(_repository.GetInsertQuery(tenantId, createdBy, branch, ref dynamicParameters, "id")); } client.Tags = string.Join(" ", tags).PrepareTags(); dynamicParameters.Add("allTags", client.Tags); strBuilder.Append($" UPDATE clients SET tags = @allTags WHERE id = {clientId};"); _repository.PrintQuery(strBuilder.ToString(), dynamicParameters); using (var multi = await db.QueryMultipleAsync(strBuilder.ToString(), dynamicParameters)) { foreach (var branch in client.Branches) { branch.Id = multi.Read().First(); } } client.Id = clientId; return client; }); } public async Task PatchClient(string tenantId, int modifiedBy, int id, JsonPatchDocument changes) { await _repository.Patch(tenantId, modifiedBy, id, changes, false); } public async Task> QuickSearchClients(string tenantId, string q) { q = q?.PrepareQuery(); var resultsQuery = @$"SELECT * FROM clients LEFT JOIN client_suppliers ON client_suppliers.client_tenantid = clients.tenantid {(!string.IsNullOrEmpty(q) ? $"WHERE Name ~* @q AND (tenantId = @tenantId OR supplier_tenantid = @tenantId) AND clients.isactive = true" : "WHERE (tenantId = @tenantId OR supplier_tenantid = @tenantId) AND clients.isactive = true")} LIMIT 20"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, new { q, tenantId }); }); } public async Task> QuickSearchBranches(string tenantId, string q) { q = q?.PrepareQuery(); var resultsQuery = $"SELECT * FROM {_repository.GetTableName()} {(!string.IsNullOrEmpty(q) ? $"WHERE Name ~* @q AND TenantId = @tenantId" : "TenantId = @tenantId")} LIMIT 20"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, new { q, tenantId }); }); } public async Task> QuickSearchBrands(string tenantId, string q) { q = q?.PrepareQuery(); var resultsQuery = $"SELECT * FROM {_repository.GetTableName()} {(!string.IsNullOrEmpty(q) ? $"WHERE Name ~* @q AND TenantId = @tenantId" : "TenantId = @tenantId")} LIMIT 20"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, new { q, tenantId }); }); } public async Task UpdateClient(string tenantId, int modifiedBy, Client client) { await _repository.Query(async (db) => { var existingClient = (await db.QueryFirstOrDefaultAsync( $"SELECT id, tenantId FROM {_repository.GetTableName()} WHERE id = @id", new { Id = client.Id })); if(existingClient.TenantId != tenantId) { //Si el cliente es externo, el proveedor sólo puede editar su pedido mínimo if (client.MinTotal.HasValue) { var upsertQuery = @"INSERT INTO clientmintotals(clientid, mintotal, tenantid) VALUES (@clientId, @minTotal, @tenantId) ON CONFLICT (tenantid, clientid) DO UPDATE SET mintotal = @minTotal;"; await db.QueryAsync(upsertQuery, new { tenantId, clientId = existingClient.Id, minTotal = client.MinTotal }); } else { var deleteQuery = @"DELETE FROM clientmintotals WHERE clientId = @clientId AND tenantId = @tenantId;"; await db.QueryAsync(deleteQuery, new { tenantId, clientId = existingClient.Id }); } return null; } //El total mínimo de pedido se guarda en otra tabla if (client.MinTotal.HasValue) { var upsertQuery2 = @"INSERT INTO clientmintotals(clientid, mintotal, tenantid) VALUES (@clientId, @minTotal, @tenantId) ON CONFLICT (tenantid, clientid) DO UPDATE SET mintotal = @minTotal;"; await db.QueryAsync(upsertQuery2, new { tenantId, clientId = existingClient.Id, minTotal = client.MinTotal }); } else { var deleteQuery2 = @"DELETE FROM clientmintotals WHERE clientId = @clientId AND tenantId = @tenantId;"; await db.QueryAsync(deleteQuery2, new { tenantId, clientId = existingClient.Id }); } var dynamicParameters = new DynamicParameters(); var clientId = client.Id; var strBuilder = new StringBuilder(); var tags = new HashSet(); tags.Add(client.Name); tags.Add(client.Email); tags.Add(client.Contact); tags.Add(client.Phone); //RFCs var existingTaxPayers = (await db.QueryAsync( $"SELECT * FROM {_repository.GetTableName()} WHERE ClientId = @clientId AND TenantId = @tenantId", new { tenantId, clientId })).ToList(); var taxPayersDictionary = new Dictionary(); foreach (var taxPayer in client.TaxPayers) { tags.Add(taxPayer.LegalName); tags.Add(taxPayer.Email); tags.Add(taxPayer.ExtNumber); tags.Add(taxPayer.IntNumber); tags.Add(taxPayer.Municipality); tags.Add(taxPayer.Neighborhood); tags.Add(taxPayer.Street); tags.Add(taxPayer.State); tags.Add(taxPayer.TaxPayerIdNo); tags.Add(taxPayer.ZipCode); var existingTaxPayer = existingTaxPayers.FirstOrDefault(x => x.Id == taxPayer.Id); taxPayer.ClientId = clientId; taxPayer.TenantId = tenantId; if (existingTaxPayer != null) { var existingTaxPayerId = existingTaxPayer.Id; existingTaxPayers.Remove(existingTaxPayer); strBuilder.AppendLine(_repository.GetUpdateQuery(tenantId, modifiedBy, x => x.Id == existingTaxPayerId, taxPayer, ref dynamicParameters)); } else { var taxPayerDynamicParameters = new DynamicParameters(); var insertTaxPayerQuery = _repository.GetInsertQuery(tenantId, modifiedBy, taxPayer, ref taxPayerDynamicParameters, "Id"); var requestTaxPayerId = taxPayer.Id; var taxPayerId = db.QueryFirst(insertTaxPayerQuery, taxPayerDynamicParameters); taxPayer.Id = taxPayerId; taxPayersDictionary.Add(requestTaxPayerId, taxPayerId); } } //Marcas var brandsDictionary = new Dictionary(); var existingBrands = (await db.QueryAsync( $"SELECT * FROM {_repository.GetTableName()} WHERE ClientId = @clientId AND TenantId = @tenantId", new { tenantId, clientId })).ToList(); foreach (var brand in client.Brands) { tags.Add(brand.Code); tags.Add(brand.Name); tags.Add(brand.Contact); tags.Add(brand.ContactEmail); tags.Add(brand.ContactPhone); var existingBrand = existingBrands.FirstOrDefault(x => x.Id == brand.Id); brand.ClientId = clientId; brand.TenantId = tenantId; if (existingBrand != null) { var existingBrandId = existingBrand.Id; existingBrands.Remove(existingBrand); strBuilder.AppendLine(_repository.GetUpdateQuery(tenantId, modifiedBy, x => x.Id == existingBrandId, brand, ref dynamicParameters)); } else { var brandDynamicParameters = new DynamicParameters(); var insertBrandQuery = _repository.GetInsertQuery(tenantId, modifiedBy, brand, ref brandDynamicParameters, "Id"); var requestBrandId = brand.Id; var brandId = db.QueryFirst(insertBrandQuery, brandDynamicParameters); brand.Id = brandId; brandsDictionary.Add(requestBrandId, brandId); } } //Sucursales var existingBranches = (await db.QueryAsync( $"SELECT * FROM {_repository.GetTableName()} WHERE ClientId = @clientId AND TenantId = @tenantId", new { tenantId, clientId })).ToList(); foreach (var branch in client.Branches) { tags.Add(branch.Name); tags.Add(branch.ManagerEmail); tags.Add(branch.Manager); tags.Add(branch.ManagerPhone); tags.Add(branch.NumInt); tags.Add(branch.ShippingAddress); tags.Add(branch.ShippingDetails); tags.Add(branch.ZipCode); var existingBranch = existingBranches.FirstOrDefault(x => x.Id == branch.Id); branch.ClientId = clientId; branch.TenantId = tenantId; if (branch.TaxPayerId.HasValue) branch.TaxPayerId = taxPayersDictionary.ContainsKey(branch.TaxPayerId.Value) ? (int?)taxPayersDictionary[branch.TaxPayerId.Value] : (branch.TaxPayerId == 0 ? null : branch.TaxPayerId); if (branch.ClientBrandId.HasValue) branch.ClientBrandId = brandsDictionary.ContainsKey(branch.ClientBrandId.Value) ? (int?)brandsDictionary[branch.ClientBrandId.Value] : (branch.ClientBrandId == 0 ? null : branch.ClientBrandId); if (existingBranch != null) { var existingBranchId = existingBranch.Id; existingBranches.Remove(existingBranch); strBuilder.AppendLine(_repository.GetUpdateQuery(tenantId, modifiedBy, x => x.Id == existingBranchId, branch, ref dynamicParameters)); } else { strBuilder.Append(_repository.GetInsertQuery(tenantId, modifiedBy, branch, ref dynamicParameters)); } } //---------- client.Tags = string.Join(" ", tags).PrepareTags(); client.TenantId = tenantId; strBuilder.AppendLine(_repository.GetUpdateQuery(tenantId, modifiedBy, x => x.Id == clientId, client, ref dynamicParameters)); foreach (var taxPayer in existingTaxPayers) { strBuilder.AppendLine(_repository.GetDeleteQuery(tenantId, taxPayer, ref dynamicParameters)); } foreach (var brand in existingBrands) { strBuilder.AppendLine(_repository.GetDeleteQuery(tenantId, brand, ref dynamicParameters)); } foreach (var branch in existingBranches) { strBuilder.AppendLine(_repository.GetDeleteQuery(tenantId, branch, ref dynamicParameters)); } _repository.PrintQuery(strBuilder.ToString(), dynamicParameters); return await db.QueryMultipleAsync(strBuilder.ToString(), dynamicParameters); }); } public async Task> GetBrands(string tenantId) { var resultsQuery = $"SELECT * FROM {_repository.GetTableName()} WHERE TenantId = @tenantId"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, new { tenantId }); }); } public async Task> GetBranches(string tenantId) { var resultsQuery = $@"SELECT clientbranches.*, clientbrands.* FROM clientbranches LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid WHERE clientbranches.tenantId = @tenantId"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, (cb, cbr) => { if (cb != null) { cb.ClientBrand = cbr; } return cb; }, new { tenantId }); }); } public async Task> GetRfcs(string tenantId) { var resultsQuery = $"SELECT * FROM {_repository.GetTableName()} WHERE TenantId = @tenantId"; return await _repository.Query(async (dbConnection) => { return await dbConnection.QueryAsync(resultsQuery, new { tenantId }); }); } public async Task> GetBrandsAndBranches(string tenantId) { var query = @"SELECT clientbrands.id, clientbrands.name, clientbranches.id, clientbranches.name FROM clientbrands LEFT JOIN clientbranches ON clientbranches.clientbrandid = clientbrands.id WHERE clientbrands.tenantid = @tenantId"; return await _repository.Query(async (db) => { var brands = new List(); await db.QueryAsync (query, (cbrand, cbranch) => { var brand = brands.FirstOrDefault(x => x.Id == cbrand.Id); if (brand == null) { brands.Add(cbrand); brand = cbrand; } brand.Branches = brand.Branches ?? new List(); if(cbranch != null) brand.Branches.Add(cbranch); return brand; }, new { tenantId }); return brands; }); } public async Task AddBrandToCurrentClient(string tenantId, int createdById, ClientBrand brand) { await _repository.Query(async (dbConnection) => { var today = DateTime.UtcNow; var query = $@" INSERT INTO clientbrands (clientid, name, code, contactuserid, contact, contactemail, contactphone, sendinvoice, tenantid, createdbyid, created, modifiedById, modified, isactive) VALUES((SELECT id FROM clients WHERE tenantId = @tenantId), @name, @code, @contactUserId, @contact, @contactEmail, @contactPhone, @sendInvoice, @tenantId, @createdById, @created, @modifiedById, @modified, @isActive)"; await dbConnection.QueryAsync(query, new { name = brand.Name, code = brand.Code, contact = brand.Contact, contactUserId = brand.ContactUserId, contactEmail = brand.ContactEmail, contactPhone = brand.ContactPhone, sendInvoice = brand.SendInvoice, isActive = true, created = today, createdById, modified = today, modifiedById = createdById, tenantId }); return ""; }); } public async Task EditBrandFromCurrentClient(string tenantId, int brandId, int updatedById, ClientBrand brand) { await _repository.Query(async (dbConnection) => { var today = DateTime.UtcNow; var query = $@" UPDATE clientbrands SET name = @name, code = @code, contactUserId = @contactUserId, contact = @contact, contactemail = @contactEmail, contactphone = @contactPhone, sendinvoice = @sendInvoice, modifiedById = @modifiedById, modified = @modified WHERE id = @brandId AND tenantId = @tenantId"; await dbConnection.QueryAsync(query, new { brandId, name = brand.Name, code = brand.Code, contactUserId = brand.ContactUserId, contact = brand.Contact, contactEmail = brand.ContactEmail, contactPhone = brand.ContactPhone, sendInvoice = brand.SendInvoice, modified = today, modifiedById = updatedById, tenantId }); return ""; }); } public async Task AddBranchToBrand(string tenantId, int clientBrandId, int createdById, ClientBranch branch) { await _repository.Query(async (dbConnection) => { var today = DateTime.UtcNow; var query = $@" INSERT INTO clientbranches (clientid, name, manageruserid, manager, manageremail, managerphone, shippingaddress, shippingdetails, sendinvoice, taxpayerid, clientbrandid, zipcode, lat, lng, numint, tenantid, createdbyid, created, modifiedbyid, modified, isactive) VALUES ((SELECT id FROM clients WHERE tenantId = @tenantId), @name, @managerUserId, @manager, @managerEmail, @managerPhone, @shippingAddress, @shippingDetails, @sendInvoice, @taxPayerId, @clientBrandId, @zipCode, @lat, @lng, @numInt, @tenantId, @createdById, @created, @modifiedById, @modified, @isActive)"; await dbConnection.QueryAsync(query, new { name = branch.Name, managerUserId = branch.ManagerUserId, manager = branch.Manager, managerEmail = branch.ManagerEmail, managerPhone = branch.ManagerPhone, shippingAddress = branch.ShippingAddress, shippingDetails = branch.ShippingDetails, taxPayerId = branch.TaxPayerId, clientBrandId, zipCode = branch.ZipCode, lat = branch.Lat, lng = branch.Lng, numInt = branch.NumInt, sendInvoice = branch.SendInvoice, isActive = true, created = today, createdById, modified = today, modifiedById = createdById, tenantId }); return ""; }); } public async Task EditBranchFromBrand(string tenantId, int clientBrandId, int clientBranchId, int updatedById, ClientBranch branch) { await _repository.Query(async (dbConnection) => { var today = DateTime.UtcNow; var query = $@" UPDATE clientbranches SET name = @name, manageruserid = @managerUserId, manager = @manager, manageremail = @managerEmail, managerphone = @managerPhone, shippingaddress = @shippingAddress, shippingdetails = @shippingDetails, sendinvoice = @sendInvoice, taxpayerid = @taxPayerId, zipcode = @zipCode, lat = @lat, lng = @lng, numint = @numInt, modifiedbyid = @modifiedById, modified = @modified WHERE tenantId = @tenantId AND id = @clientBranchId AND clientbrandid = @clientBrandId"; await dbConnection.QueryAsync(query, new { clientBranchId, name = branch.Name, managerUserId = branch.ManagerUserId, manager = branch.Manager, managerEmail = branch.ManagerEmail, managerPhone = branch.ManagerPhone, shippingAddress = branch.ShippingAddress, shippingDetails = branch.ShippingDetails, taxPayerId = branch.TaxPayerId, clientBrandId, zipCode = branch.ZipCode, lat = branch.Lat, lng = branch.Lng, numInt = branch.NumInt, sendInvoice = branch.SendInvoice, isActive = true, modified = today, modifiedById = updatedById, tenantId }); return ""; }); } public async Task AddRfc(string tenantId, int createdById, ClientTaxPayer rfc) { return await _repository.Query(async (dbConnection) => { var today = DateTime.UtcNow; var query = $@" INSERT INTO clienttaxpayers (clientid, taxpayeridno, legalname, street, intnumber, extnumber, neighborhood, municipality, state, zipcode, email, paymentform, paymentmethod, usocfdi, sendinvoice, billingManager, billingManagerUserId, billingManagerPhone, billingManagerEmail, tenantid, createdbyid, created, modified, modifiedById, isactive) VALUES ((SELECT id FROM clients WHERE tenantId = @tenantId), @taxPayerIdNo, @legalName, @street, @intNumber, @extNumber, @neighborhood, @municipality, @state, @zipCode, @email, @paymentForm, @paymentMethod, @usoCfdi, @sendInvoice, @billingManager, @billingManagerUserId, @billingManagerPhone, @billingManagerEmail, @tenantId, @createdById, @created, @modified, @modifiedById, @isActive) RETURNING id "; var id = await dbConnection.QueryFirstAsync(query, new { billingManagerUserId = rfc.BillingManagerUserId, billingManager = rfc.BillingManager, billingManagerPhone = rfc.BillingManagerPhone, billingManagerEmail = rfc.BillingManagerEmail, taxPayerIdNo = rfc.TaxPayerIdNo, legalName = rfc.LegalName, street = rfc.Street, intNumber = rfc.IntNumber, extNumber = rfc.ExtNumber, neighborhood = rfc.Neighborhood, municipality = rfc.Municipality, state = rfc.State, zipCode = rfc.ZipCode, email = rfc.Email, paymentForm = rfc.PaymentForm, paymentMethod = rfc.PaymentMethod, usoCfdi = rfc.UsoCfdi, sendInvoice = rfc.SendInvoice, isActive = true, created = today, createdById, modified = today, modifiedById = createdById, tenantId }); rfc.Id = id; return rfc; }); } public async Task EditRfc(string tenantId, int id, int updatedById, ClientTaxPayer rfc) { await _repository.Query(async (dbConnection) => { var today = DateTime.UtcNow; var query = $@" UPDATE clienttaxpayers SET taxpayeridno = @taxPayerIdNo, legalname = @legalName, street = @street, intnumber = @intNumber, extnumber = @extNumber, neighborhood = @neighborhood, municipality = @municipality, state = @state, zipcode = @zipCode, email = @email, paymentform = @paymentForm, paymentmethod = @paymentMethod, usocfdi = @usoCfdi, billingManager = @billingManager, billingManagerUserId = @billingManagerUserId, billingManagerPhone = @billingManagerPhone, billingManagerEmail = @billingManagerEmail, sendinvoice = @sendInvoice, modified = @modified, modifiedById = @modifiedById WHERE id = @id AND tenantId = @tenantId"; await dbConnection.QueryAsync(query, new { id, billingManagerUserId = rfc.BillingManagerUserId, billingManager = rfc.BillingManager, billingManagerPhone = rfc.BillingManagerPhone, billingManagerEmail = rfc.BillingManagerEmail, taxPayerIdNo = rfc.TaxPayerIdNo, legalName = rfc.LegalName, street = rfc.Street, intNumber = rfc.IntNumber, extNumber = rfc.ExtNumber, neighborhood = rfc.Neighborhood, municipality = rfc.Municipality, state = rfc.State, zipCode = rfc.ZipCode, email = rfc.Email, paymentForm = rfc.PaymentForm, paymentMethod = rfc.PaymentMethod, usoCfdi = rfc.UsoCfdi, sendInvoice = rfc.SendInvoice, modified = today, modifiedById = updatedById, tenantId }); return ""; }); } public async Task GetProductCount(string tenantId, int[] supplierIds) { return await _repository.Query(async (dbConnection) => { var query = $@"SELECT count(*) as ProductsCount FROM public.products LEFT JOIN ordertemplateproducts ON ordertemplateproducts.productid = public.products.id LEFT JOIN ordertemplates ON ordertemplateproducts.ordertemplateid = ordertemplates.id WHERE ordertemplates.tenantId IN (SELECT tenantId from suppliers WHERE id IN ({string.Join(",", supplierIds.Select(x => $"{x}"))})) AND clientid = (SELECT id from clients WHERE tenantid = @tenantId)"; return await dbConnection.QueryFirstAsync(query, new { tenantId }); }); } public async Task> GetBranchesByBrandIds(string tenantId, int[] brandIds) { return await _repository.Query(async (dbConnection) => { //WARNING: Usar el tenantId aquí var query = $@"SELECT * FROM clientbranches WHERE clientbrandid IN ({string.Join(",", brandIds.Select(x => $"{x}"))})"; return await dbConnection.QueryAsync(query); }); } } }