/// <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; }