context: using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.SqlClient;
  using System.Configuration;
  using System.Linq;
  using POSClient.Base;
  using POSClient.Entities;
  using POSClient;
  using POSClient.POCOS;
  using System.Xml;
  
  namespace POSClient.Entities
  {
     /// <summary>
     /// User custom methods for SalesDap
     /// </summary>
     partial class SalesDap
     {
  
        public void MarcarCorteZHecho(DateTime selectedDate)
        {
           Execute<Sales>("Update Sales Set IncluidoEnCorteZ=1 Where SaleDate=@SaleDate", new { SaleDate = selectedDate.ToString("yyyy-MM-dd") });
        }
  
        public void MarcarCorteDeTurnoHecho(DateTime selectedDate, int cajeroID)
        {
           Execute<Sales>("Update Sales Set IncluidoEnCorteDeTurno=1 Where SaleDate=@SaleDate And CajeroID=@CajeroID", new { SaleDate = selectedDate.ToString("yyyy-MM-dd"), CajeroID = cajeroID });
        }
  
        public decimal VentasParaCorteZ(DateTime selectedDate)
        {
           decimal decRetorno = 0;
           try
           {
              var retorno = Query<decimal>(POSClient.Properties.Resources.CorteTotalQuery, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
              if (retorno.Any())
              {
                 decRetorno = retorno.Sum();
                 return decRetorno;
              }
              else return 0;
           }
           catch
           {
              return 0;
           }
        }
  
        public decimal VentasParaReImpresionCorteZ(DateTime selectedDate)
        {
            decimal decRetorno = 0;
            try
            {
                var retorno = Query<decimal>(POSClient.Properties.Resources.CorteTotalQueryReImpresionCorteZ, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
                if (retorno.Any())
                {
                    decRetorno = retorno.Sum();
                    return decRetorno;
                }
                else return 0;
            }
            catch
            {
                return 0;
            }
        }
  
        public decimal VentasParaCortePorCajero(DateTime selectedDate, int cajeroID)
        {
           decimal decRetorno = 0;
           try
           {
              var retorno = Query<decimal>(POSClient.Properties.Resources.CorteTotalQueryPorCajero, new { SaleDate = selectedDate.ToString("yyyy-MM-dd"), CajeroID = cajeroID }).ToList();
              if (retorno.Any())
              {
                 decRetorno = retorno.Sum();
                 return decRetorno;
              }
              else return 0;
           }
           catch
           {
              return 0;
           }
        }
  
        public decimal GetByDate(DateTime selectedDate, int cajeroID)
        {
           decimal decRetorno = 0;
           try
           {
              var retorno = Query<decimal>(POSClient.Properties.Resources.CorteTotalQuery, new { SaleDate = selectedDate.ToString("yyyy-MM-dd"), CajeroID = cajeroID }).ToList();
              if (retorno.Any())
              {
                 decRetorno = retorno.Sum();
                 return decRetorno;
              }
              else return 0;
           }
           catch
           {
              return 0;
           }
        }
         //TODO: Terminar ventas canceladas
         /*public IList<Sales> GetCancelledSalesByDate(DateTime selectedDate)
        {
  
        }*/
  
        public IList<VentasPorProductoDTO> GetProductSalesParaCortePorTurno(DateTime selectedDate, int cajeroID)
        {
  
           var retorno = Query<VentasPorProductoDTO>(POSClient.Properties.Resources.CorteProductosParaCorteDeTurno, new { SaleDate = selectedDate.ToString("yyyy-MM-dd"), CajeroID = cajeroID }).ToList();
           return retorno;
        }
  
        public IList<VentasPorProductoDTO> GetProductSalesParaCorteZ(DateTime selectedDate)
        {
  
           var retorno = Query<VentasPorProductoDTO>(POSClient.Properties.Resources.CorteProductosParaCorteZ, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
           return retorno;
        }
        public IList<VentasPorProductoDTO> GetProductSalesParaReImpresionCorteZ(DateTime selectedDate)
        {
  
            var retorno = Query<VentasPorProductoDTO>(POSClient.Properties.Resources.CorteProductosParaReImpresionCorteZ, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
            return retorno;
        }
  
        public IList<VentasPorProductoDTO> GetProductSalesByDate(DateTime selectedDate, int cajeroID)
        {
  
           var retorno = Query<VentasPorProductoDTO>(POSClient.Properties.Resources.CorteProductosQuery, new { SaleDate = selectedDate.ToString("yyyy-MM-dd"), CajeroID = cajeroID }).ToList();
           return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorVendedorParaCorteZ(DateTime selectedDate)
        {
           String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                 "on s.VendedorID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate and s.IncluidoEnCorteZ=0 Group by u.NombreCompleto";
           var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
           return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorVendedorParaReImpresionCorteZ(DateTime selectedDate)
        {
            String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                  "on s.VendedorID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate Group by u.NombreCompleto";
            var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
            return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorVendedorParaCorteDeTurno(DateTime selectedDate, int cajeroID)
        {
           String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                 "on s.VendedorID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate and s.IncluidoEnCorteDeTurno=0 and s.CajeroID=@CajeroID Group by u.NombreCompleto";
           var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd"), CajeroID=cajeroID }).ToList();
           return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorVendedorPorDia(DateTime selectedDate)
        {
           String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                 "on s.VendedorID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate Group by u.NombreCompleto";
           var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
           return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorCajeroParaCorteDeTurno(DateTime selectedDate, int cajeroID)
        {
           String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                 "on s.CajeroID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate and s.IncluidoEnCorteDeTurno=0 and s.CajeroID=@CajeroID Group by u.NombreCompleto";
           var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd"), CajeroID=cajeroID }).ToList();
           return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorCajeroParaCorteZ(DateTime selectedDate)
        {
           String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                 "on s.CajeroID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate and s.IncluidoEnCorteZ=0 Group by u.NombreCompleto";
           var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
           return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorCajeroParaReImpresionCorteZ(DateTime selectedDate)
        {
            String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                  "on s.CajeroID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate Group by u.NombreCompleto";
            var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
            return retorno;
        }
  
        public IList<VentasPorPersonaDTO> TraerVentasPorCajeroPorDia(DateTime selectedDate)
        {
           String sQLStatement = "Select u.NombreCompleto as Nombre, FORMAT(sum(s.TotalAfterDiscount), 'C', 'en-us') as Vendido from Sales as s join Usuario as u " +
                                 "on s.CajeroID = u.ID where coalesce(s.Cancelled,0) = 0 and s.SaleDate = @SaleDate Group by u.NombreCompleto";
           var retorno = Query<VentasPorPersonaDTO>(sQLStatement, new { SaleDate = selectedDate.ToString("yyyy-MM-dd") }).ToList();
           return retorno;
        }
  
        public FoliosDTO GetNewFolios()
        {
           string serie = "";
           serie = System.Configuration.ConfigurationManager.AppSettings["SerieTerminal"];
           var results = Query<FoliosDTO>("GetNewFolios", new { Serie = serie }, commandType: CommandType.StoredProcedure).ToList();
           if (results.Any())
              return results.FirstOrDefault();
           else
              return new FoliosDTO();
        }
  
        public Sales GetByFolioGlobal(int folioGlobal)
        {
           var returnValue = Query<Sales>("Select * from Sales where SaleNumber=@SaleNumber", new { SaleNumber = folioGlobal }).ToList();
           if (returnValue.Any())
              return returnValue.FirstOrDefault();
           else
              return new Sales();
        }
  
        public List<TicketPreview> GetSaleTicketPreviewByID(int saleID)
        {
           const string queryStatement = "Select 	p.Description as Producto	, d.Quantity as Cantidad	, FORMAT(d.Price, 'C', 'en-us') as PrecioUnitario	, d.Total	, d.ProductId	, d.PriceLevelId as PriceIndex	, d.Price as PriceValue	, p.Code" +
                                         ", d.DiscountPercentage	, d.DiscountTotal	, d.SubTotalBeforeDiscount	, d.IVABeforeDiscount	, d.TotalBeforeDiscount	From SaleDetails as d inner join Products as p on p.Id = d.ProductId" +
                                         " Where d.SaleId = @SaleID";
           var resultsToReturn = Query<TicketPreview>(queryStatement, new { SaleID = saleID }).ToList();
           return resultsToReturn;
        }
  
        public XmlDocument GetInvoiceXml(int saleId)
        {
           var retorno = Query<XmlDocument>("GETINVOICEXML", new { SALEID = saleId }, commandType: CommandType.StoredProcedure).FirstOrDefault();
  
gt:          return retorno ?? new XmlDocument();