/// <summary>
        /// 
        /// </summary>
        /// <param name="mktgDocType"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="fieldFilter">S = SlpCode - C = CardCode</param>
        /// <param name="slp_card_Code">SlpCode/CardCode</param>
        /// <returns></returns>
        public List<LightMarketingDocument> GetList(SapDocumentType mktgDocType, DateTime startDate, DateTime endDate, char fieldFilter, string slp_card_Code)
        {
            StringBuilder oSQL = new StringBuilder();
            List<LightMarketingDocument> documentList = new List<LightMarketingDocument>();
            List<LightMarketingDocumentLine> documentsLinesList = new List<LightMarketingDocumentLine>();

            #region Header query
            oSQL.Append("Select a.DocEntry, a.DocNum, a.CardCode, a.CardName, a.DocDate, a.DocDueDate, a.DocStatus ");

            switch (mktgDocType)
            {
                case SapDocumentType.SalesInvoice:
                    oSQL.Append("FROM OINV a ");
                    break;
                case SapDocumentType.SalesCreditNote:
                    oSQL.Append("FROM ORIN a ");
                    break;
                case SapDocumentType.SalesDelivery:
                    oSQL.Append("FROM ODLN a ");
                    break;
                case SapDocumentType.SalesReturn:
                    oSQL.Append("FROM ORDN a ");
                    break;
                case SapDocumentType.SalesOrder:
                    oSQL.Append("FROM ORDR a ");
                    break;
                case SapDocumentType.PurchaseInvoice:
                    oSQL.Append("FROM OPCH a ");
                    break;
                case SapDocumentType.PurchaseCreditNote:
                    oSQL.Append("FROM ORPC a ");
                    break;
                case SapDocumentType.PurchaseDelivery:
                    oSQL.Append("FROM OPDN a ");
                    break;
                case SapDocumentType.PurchaseReturn:
                    oSQL.Append("FROM ORPD a ");
                    break;
                case SapDocumentType.PurchaseOrder:
                    oSQL.Append("FROM OPOR a ");
                    break;
                case SapDocumentType.Quotation:
                    oSQL.Append("FROM OQUT a ");
                    break;
                default:
                    break;
            }

            if (fieldFilter == 'S')
                oSQL.Append(string.Format("Where a.slpCode = '{0}' ", slp_card_Code));
            else
                oSQL.Append(string.Format("Where a.cardCode = '{0}' ", slp_card_Code));

            oSQL.Append(string.Format("and a.DocDate between '{0}' and '{1}'", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd")));


            DbCommand dbCommand = this.dataBase.GetSqlStringCommand(oSQL.ToString());
            #endregion

            #region Get Header Data
            using (this.reader = this.dataBase.ExecuteReader(dbCommand))
            {
                while (this.reader.Read())
                {
                    LightMarketingDocument document = new LightMarketingDocument();
                    document.docEntry = this.reader.IsDBNull(0) ? 0 : Convert.ToInt32(this.reader.GetValue(0).ToString());
                    document.docNum = this.reader.IsDBNull(1) ? 0 : Convert.ToInt32(this.reader.GetValue(1).ToString());
                    document.cardCode = this.reader.IsDBNull(2) ? "" : this.reader.GetValue(2).ToString();
                    document.cardName = this.reader.IsDBNull(3) ? "" : this.reader.GetValue(3).ToString();
                    document.docDate = this.reader.IsDBNull(4) ? DateTime.Now : Convert.ToDateTime(this.reader.GetValue(4).ToString());
                    document.docDueDate = this.reader.IsDBNull(5) ? DateTime.Now : Convert.ToDateTime(this.reader.GetValue(5).ToString());
                    document.docStatus = this.reader.IsDBNull(6) ? "" : this.reader.GetValue(6).ToString();

                    documentList.Add(document);
                }
            }
            #endregion

            return documentList;
        }
        /// <summary>
        /// Consulta un socio de negocios en SAP Business One
        /// </summary>
        /// <param name="cardCode">Codigo de socio de negocio</param>
        /// <returns>Socio con la información</returns>
        public LightMarketingDocument GetSingle(SapDocumentType mktgDocType, string docNum)
        {
            StringBuilder oSQL = new StringBuilder();
            LightMarketingDocument document = new LightMarketingDocument();

            #region Header query
            oSQL.Append("Select a.DocEntry, a.DocNum, a.CardCode, a.CardName, a.DocDate, a.DocDueDate, a.DocStatus ");

            switch (mktgDocType)
            {
                case SapDocumentType.SalesInvoice:
                    oSQL.Append("FROM OINV a ");
                    break;
                case SapDocumentType.SalesCreditNote:
                    oSQL.Append("FROM ORIN a ");
                    break;
                case SapDocumentType.SalesDelivery:
                    oSQL.Append("FROM ODLN a ");
                    break;
                case SapDocumentType.SalesReturn:
                    oSQL.Append("FROM ORDN a ");
                    break;
                case SapDocumentType.SalesOrder:
                    oSQL.Append("FROM ORDR a ");
                    break;
                case SapDocumentType.PurchaseInvoice:
                    oSQL.Append("FROM OPCH a ");
                    break;
                case SapDocumentType.PurchaseCreditNote:
                    oSQL.Append("FROM ORPC a ");
                    break;
                case SapDocumentType.PurchaseDelivery:
                    oSQL.Append("FROM OPDN a ");
                    break;
                case SapDocumentType.PurchaseReturn:
                    oSQL.Append("FROM ORPD a ");
                    break;
                case SapDocumentType.PurchaseOrder:
                    oSQL.Append("FROM OPOR a ");
                    break;
                case SapDocumentType.Quotation:
                    oSQL.Append("FROM OQUT a ");
                    break;
                default:
                    break;
            }

            oSQL.Append("Where a.DocNum = @docNum");

            DbCommand dbCommand = this.dataBase.GetSqlStringCommand(oSQL.ToString());
            this.dataBase.AddInParameter(dbCommand, "docNum", DbType.Int32, docNum);
            #endregion

            #region Get Header Data
            using (this.reader = this.dataBase.ExecuteReader(dbCommand))
            {
                while (this.reader.Read())
                {

                    document.docEntry = this.reader.IsDBNull(0) ? 0 : Convert.ToInt32(this.reader.GetValue(0).ToString());
                    document.docNum = this.reader.IsDBNull(1) ? 0 : Convert.ToInt32(this.reader.GetValue(1).ToString());
                    document.cardCode = this.reader.IsDBNull(2) ? "" : this.reader.GetValue(2).ToString();
                    document.cardName = this.reader.IsDBNull(3) ? "" : this.reader.GetValue(3).ToString();
                    document.docDate = this.reader.IsDBNull(4) ? DateTime.Now : Convert.ToDateTime(this.reader.GetValue(4).ToString());
                    document.docDueDate = this.reader.IsDBNull(5) ? DateTime.Now : Convert.ToDateTime(this.reader.GetValue(5).ToString());
                    document.docStatus = this.reader.IsDBNull(6) ? "" : this.reader.GetValue(6).ToString();
                }
            }
            #endregion

            #region Lines

            #region Lines Query
            oSQL.Clear();

            oSQL.Append("Select a.DocEntry, b.ItemCode, c.ItemName, b.Quantity, b.Price, b.Quantity*b.Price Total ");

            switch (mktgDocType)
            {
                case SapDocumentType.SalesInvoice:
                    oSQL.Append("From OINV a inner join INV1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.SalesCreditNote:
                    oSQL.Append("From ORIN a inner join RIN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.SalesDelivery:
                    oSQL.Append("From ODLN a inner join DLN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.SalesReturn:
                    oSQL.Append("From ORDN a inner join RDN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.SalesOrder:
                    oSQL.Append("From ORDR a inner join RDR1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.PurchaseInvoice:
                    oSQL.Append("From OPCH a inner join PCH1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.PurchaseCreditNote:
                    oSQL.Append("From ORPC a inner join RPC1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.PurchaseDelivery:
                    oSQL.Append("From OPDN a inner join PDN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.PurchaseReturn:
                    oSQL.Append("From ORPD a inner join RPD1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                case SapDocumentType.PurchaseOrder:
                    oSQL.Append("From OPOR a inner join POR1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
                    break;
                default:
                    break;
            }

            oSQL.Append("Where a.DocNum = @docNum");

            DbCommand dbCommandLines = this.dataBase.GetSqlStringCommand(oSQL.ToString());
            this.dataBase.AddInParameter(dbCommandLines, "docNum", DbType.Int32, docNum);

            #endregion

            #region Get Lines Data
            using (this.reader = this.dataBase.ExecuteReader(dbCommandLines))
            {
                while (this.reader.Read())
                {
                    LightMarketingDocumentLine documentLine = new LightMarketingDocumentLine();
                    documentLine.docEntry = this.reader.IsDBNull(0) ? 0 : Convert.ToInt32(this.reader.GetValue(0).ToString());
                    documentLine.itemCode = this.reader.IsDBNull(1) ? "" : this.reader.GetValue(1).ToString();
                    documentLine.itemName = this.reader.IsDBNull(2) ? "" : this.reader.GetValue(2).ToString();
                    documentLine.quantity = this.reader.IsDBNull(3) ? 0 : Convert.ToDouble(this.reader.GetValue(3).ToString());
                    documentLine.price = this.reader.IsDBNull(4) ? 0 : Convert.ToDouble(this.reader.GetValue(4).ToString());
                    documentLine.total = this.reader.IsDBNull(5) ? 0 : Convert.ToDouble(this.reader.GetValue(5).ToString());

                    document.lines.Add(documentLine);
                }
            }
            #endregion

            #endregion
            return document;
        }
        public List<LightMarketingDocument> GetList(SapDocumentType mktgDocType, DateTime startDate, DateTime endDate, string cardCode)
        {
            StringBuilder oSQL = new StringBuilder();
            List<LightMarketingDocument> documentList = new List<LightMarketingDocument>();
            List<LightMarketingDocumentLine> documentsLinesList = new List<LightMarketingDocumentLine>();

            #region Header query
            oSQL.Append("Select a.DocEntry, a.DocNum, a.CardCode, a.CardName, a.DocDate, a.DocDueDate, a.DocStatus ");

            switch (mktgDocType)
            {
                case SapDocumentType.SalesInvoice:
                    oSQL.Append("FROM OINV a ");
                    break;
                case SapDocumentType.SalesCreditNote:
                    oSQL.Append("FROM ORIN a ");
                    break;
                case SapDocumentType.SalesDelivery:
                    oSQL.Append("FROM ODLN a ");
                    break;
                case SapDocumentType.SalesReturn:
                    oSQL.Append("FROM ORDN a ");
                    break;
                case SapDocumentType.SalesOrder:
                    oSQL.Append("FROM ORDR a ");
                    break;
                case SapDocumentType.PurchaseInvoice:
                    oSQL.Append("FROM OPCH a ");
                    break;
                case SapDocumentType.PurchaseCreditNote:
                    oSQL.Append("FROM ORPC a ");
                    break;
                case SapDocumentType.PurchaseDelivery:
                    oSQL.Append("FROM OPDN a ");
                    break;
                case SapDocumentType.PurchaseReturn:
                    oSQL.Append("FROM ORPD a ");
                    break;
                case SapDocumentType.PurchaseOrder:
                    oSQL.Append("FROM OPOR a ");
                    break;
                case SapDocumentType.Quotation:
                    oSQL.Append("FROM OQUT a ");
                    break;
                default:
                    break;
            }

            oSQL.Append("Where a.cardCode = @cardCode ");
            oSQL.Append("and a.DocDate between @starDate and @endDate");

            DbCommand dbCommand = this.dataBase.GetSqlStringCommand(oSQL.ToString());
            this.dataBase.AddInParameter(dbCommand, "cardCode", DbType.String, cardCode);
            this.dataBase.AddInParameter(dbCommand, "starDate", DbType.Date, startDate);
            this.dataBase.AddInParameter(dbCommand, "endDate", DbType.Date, endDate);
            #endregion

            #region Get Header Data
            using (this.reader = this.dataBase.ExecuteReader(dbCommand))
            {
                while (this.reader.Read())
                {
                    LightMarketingDocument document = new LightMarketingDocument();
                    document.docEntry = this.reader.IsDBNull(0) ? 0 : Convert.ToInt32(this.reader.GetValue(0).ToString());
                    document.docNum = this.reader.IsDBNull(1) ? 0 : Convert.ToInt32(this.reader.GetValue(1).ToString());
                    document.cardCode = this.reader.IsDBNull(2) ? "" : this.reader.GetValue(2).ToString();
                    document.cardName = this.reader.IsDBNull(3) ? "" : this.reader.GetValue(3).ToString();
                    document.docDate = this.reader.IsDBNull(4) ? DateTime.Now : Convert.ToDateTime(this.reader.GetValue(4).ToString());
                    document.docDueDate = this.reader.IsDBNull(5) ? DateTime.Now : Convert.ToDateTime(this.reader.GetValue(5).ToString());
                    document.docStatus = this.reader.IsDBNull(6) ? "" : this.reader.GetValue(6).ToString();

                    documentList.Add(document);
                }
            }
            #endregion

            #region Lines
            //#region Lines Query
            //oSQL.Clear();

            //oSQL.Append("Select a.DocEntry, b.ItemCode, c.ItemName, b.Quantity, b.Price, b.Quantity*b.Price Total");

            //switch (mktgDocType)
            //{
            //    case SapDocumentType.SalesInvoice:
            //        oSQL.Append("From OINV a inner join INV1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.SalesCreditNote:
            //        oSQL.Append("From ORIN a inner join RIN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.SalesDelivery:
            //        oSQL.Append("From ODLN a inner join DLN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.SalesReturn:
            //        oSQL.Append("From ORDN a inner join RDN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.SalesOrder:
            //        oSQL.Append("From ORDR a inner join RDR1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.PurchaseInvoice:
            //        oSQL.Append("From OPCH a inner join PCH1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.PurchaseCreditNote:
            //        oSQL.Append("From ORPC a inner join RPC1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.PurchaseDelivery:
            //        oSQL.Append("From OPDN a inner join PDN1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.PurchaseReturn:
            //        oSQL.Append("From ORPD a inner join RPD1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    case SapDocumentType.PurchaseOrder:
            //        oSQL.Append("From OPOR a inner join POR1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode ");
            //        break;
            //    default:
            //        break;
            //}

            //oSQL.Append("Where a.cardCode = @cardCode ");
            //oSQL.Append("and a.DocDate between @starDate and @endDate");

            //DbCommand dbCommandLines = this.dataBase.GetSqlStringCommand(oSQL.ToString());
            //this.dataBase.AddInParameter(dbCommandLines, "cardCode", DbType.String, cardCode);
            //this.dataBase.AddInParameter(dbCommandLines, "starDate", DbType.Date, startDate);
            //this.dataBase.AddInParameter(dbCommandLines, "endDate", DbType.Date, endDate);

            //#endregion

            //#region Get Lines Data
            //using (this.reader = this.dataBase.ExecuteReader(dbCommandLines))
            //{
            //    while (this.reader.Read())
            //    {
            //        LightMarketingDocumentLine documentLine = new LightMarketingDocumentLine();
            //        documentLine.docEntry = this.reader.IsDBNull(0) ? 0 : Convert.ToInt32(this.reader.GetValue(0).ToString());
            //        documentLine.itemCode = this.reader.IsDBNull(1) ? "" : this.reader.GetValue(1).ToString();
            //        documentLine.itemName = this.reader.IsDBNull(2) ? "" : this.reader.GetValue(2).ToString();
            //        documentLine.quantity = this.reader.IsDBNull(3) ? 0 : Convert.ToDouble(this.reader.GetValue(3).ToString());
            //        documentLine.price = this.reader.IsDBNull(4) ? 0 : Convert.ToDouble(this.reader.GetValue(4).ToString());
            //        documentLine.total = this.reader.IsDBNull(5) ? 0 : Convert.ToDouble(this.reader.GetValue(5).ToString());

            //        documentsLinesList.Add(documentLine);
            //    }
            //}
            //#endregion

            //#region Put each line into respective order
            //foreach (LightMarketingDocument order in documentList)
            //    order.lines.AddRange(documentsLinesList.Where(x => x.docEntry.Equals(order.docEntry)).ToList());
            //#endregion
            #endregion

            return documentList;
        }