//[Authorize]
public async Task <IActionResult> GetPriceListProductsWms()
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery("Select \"ListName\", \"ListNum\" From OPLN");
JToken pricelists = context.XMLTOJSON(oRecSet.GetAsXML())["OPLN"];
List <PriceListOutput> priceListOuput = pricelists.ToObject <List <PriceListOutput> >();
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok(priceListOuput));
}
public async Task <IActionResult> Get()
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery(@"
Select
""WhsCode"",
""WhsName""
From OWHS");
oRecSet.MoveFirst();
JToken warehouseList = context.XMLTOJSON(oRecSet.GetAsXML())["OWHS"];
return(Ok(warehouseList));
}
public async Task <IActionResult> GetList(int id)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery($"Select \"Fax\" From OSLP Where \"SlpCode\" = {id}");
oRecSet.MoveFirst();
string warehouses = context.XMLTOJSON(oRecSet.GetAsXML())["OSLP"][0]["Fax"].ToString();
warehouses = warehouses.Trim();
if (warehouses.Equals(""))
{
warehouses = "'S01', 'S06', 'S07', 'S10', 'S12', 'S13', 'S15', 'S24', 'S36', 'S47', 'S55', 'S59', 'S62','S63'";
}
else
{
warehouses = warehouses.ToUpper();
warehouses = "'" + warehouses + "'";
warehouses = warehouses.Replace(" ", "");
warehouses = warehouses.Replace(",", "','");
}
oRecSet.DoQuery(@"
Select
warehouse.""WhsCode"",
warehouse.""WhsName"",
serie.""Series""
From OWHS warehouse
LEFT JOIN NNM1 serie ON serie.""SeriesName"" = warehouse.""WhsCode""
Where serie.""ObjectCode"" = 17 AND warehouse.""WhsCode"" in (" + warehouses + ") ");
oRecSet.MoveFirst();
JToken warehouseList = context.XMLTOJSON(oRecSet.GetAsXML())["OWHS"];
return(Ok(warehouseList));
}
public async Task <IActionResult> Get()
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.SBObob SBO = (SAPbobsCOM.SBObob)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge);
try {
SAPbobsCOM.Recordset oRecSet = SBO.GetCurrencyRate("USD", DateTime.Today);
JToken temp = context.XMLTOJSON(oRecSet.GetAsXML())["Recordset"][0];
CurrencyRateDetail CurrencyOutput = temp.ToObject <CurrencyRateDetail>();
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok(CurrencyOutput));
} catch (Exception ex) {
return(Conflict(ex.Message));
}
}
public async Task <IActionResult> GetList()
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery(@"
Select
warehouse.""WhsCode"",
warehouse.""WhsName"",
serie.""Series""
From OWHS warehouse
LEFT JOIN NNM1 serie ON serie.""SeriesName"" = warehouse.""WhsCode""
Where serie.""ObjectCode"" = 17 AND warehouse.""WhsCode"" in ('S01', 'S06', 'S07', 'S10', 'S12', 'S13', 'S15', 'S24', 'S36','S47', 'S55', 'S59', 'S62','S63') ");
oRecSet.MoveFirst();
JToken warehouseList = context.XMLTOJSON(oRecSet.GetAsXML())["OWHS"];
return(Ok(warehouseList));
}
public async Task <IActionResult> GetListToSellRetail()
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery(@"
Select
warehouse.""WhsCode"",
warehouse.""WhsName"",
serie.""Series""
From OWHS warehouse
LEFT JOIN NNM1 serie ON serie.""SeriesName"" = warehouse.""WhsCode""
Where serie.""ObjectCode"" = 17"); // AND warehouse.""WhsCode"" in ('S01', 'S06', 'S07', 'S10', 'S12', 'S13', 'S15', 'S24', 'S36', 'S55') ");
JToken warehouseList = context.XMLTOJSON(oRecSet.GetAsXML())["OWHS"];
List <WarehouseWithSerie> warehouseWithSeries = warehouseList.ToObject <List <WarehouseWithSerie> >();
oRecSet = null;
warehouseList = null;
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok(warehouseWithSeries));
}
public IActionResult GetProductosTrimestre([FromRoute] string CardCode)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
JToken invoice;
oRecSet.DoQuery($@"
select T0.""ItemCode"" from ""INV1"" T0,
""OINV"" T1 where
T0.""DocDate"" >= ADD_DAYS(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), -90)
AND T0.""DocDate"" <= CURRENT_DATE
and T0.""BaseCard"" = '{CardCode}'
AND T1.""CANCELED"" != 'C'
AND T0.""DocEntry"" = T1.""DocEntry""
GROUP BY T0.""ItemCode"",T0.""BaseCard""
ORDER BY T0.""BaseCard""");
if (oRecSet.RecordCount == 0)
{
return(Ok()); // Handle no Existing Invoice
}
invoice = context.XMLTOJSON(oRecSet.GetAsXML())["INV1"];
return(Ok(invoice));
}
public async Task <IActionResult> GetReception(int id)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery(@"
Select
""DocEntry"",
""DocNum"",
""DocStatus"",
""ToWhsCode"",
""Filler""
From OWTQ WHERE ""DocNum"" = " + id);
int rc = oRecSet.RecordCount;
if (rc == 0)
{
return(NotFound());
}
JToken request = context.XMLTOJSON(oRecSet.GetAsXML());
request["AdmInfo"]?.Parent.Remove();
request["OWTQ"] = request["OWTQ"][0];
if (request["OWTQ"]["DocStatus"].ToString() != "O")
{
return(BadRequest("Documento Cerrado"));
}
oRecSet.DoQuery(@"
Select
""LineStatus"",
""LineNum"",
""ItemCode"",
""Dscription"",
""UseBaseUn"",
""UomEntry"",
""WhsCode"",
""UomCode"",
""FromWhsCod"",
""OpenInvQty"",
""OpenQty""
From WTQ1 WHERE ""DocEntry"" = " + request["OWTQ"]["DocEntry"]);
request["WTQ1"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTQ1"];
foreach (var pro in request["WTQ1"])
{
oRecSet.DoQuery(@"
Select
""ItemCode"",
""ItemName"",
""QryGroup7"",
""QryGroup41"",
""QryGroup42"",
""QryGroup44"",
""QryGroup45"",
""ManBtchNum"",
""U_IL_PesMax"",
""U_IL_PesMin"",
""U_IL_PesProm"",
""U_IL_TipPes"",
""NumInSale"",
""NumInBuy""
From OITM Where ""ItemCode"" = '" + pro["ItemCode"] + "'");
oRecSet.MoveFirst();
pro["Detail"] = context.XMLTOJSON(oRecSet.GetAsXML())["OITM"][0];
oRecSet.DoQuery(@"
Select
""BcdEntry"",
""BcdCode"",
""BcdName"",
""ItemCode"",
""UomEntry""
From OBCD Where ""ItemCode"" = '" + pro["ItemCode"] + "'");
oRecSet.MoveFirst();
pro["CodeBars"] = context.XMLTOJSON(oRecSet.GetAsXML())["OBCD"];
}
return(Ok(request));
}
public async Task <IActionResult> Post([FromBody] DeliveryOld value)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Documents order = (SAPbobsCOM.Documents)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
SAPbobsCOM.Documents delivery = (SAPbobsCOM.Documents)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes);
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
if (order.GetByKey(value.order))
{
delivery.CardCode = order.CardCode;
delivery.DocDate = DateTime.Now;
delivery.DocDueDate = DateTime.Now;
oRecSet.DoQuery(@"
Select
serie1.""SeriesName"",
serie1.""Series"",
serie1.""ObjectCode"",
serie2.""SeriesName""as s1,
serie2.""Series"" as s2,
serie2.""ObjectCode"" as s3
From NNM1 serie1
JOIN NNM1 serie2 ON serie1.""SeriesName"" = serie2.""SeriesName""
Where serie1.""ObjectCode"" = 15 AND serie2.""Series"" = '" + order.Series + "'");
oRecSet.MoveFirst();
delivery.Series = context.XMLTOJSON(oRecSet.GetAsXML())["NNM1"][0]["Series"].ToObject <int>();
for (int i = 0; i < value.products.Count; i++)
{
//delivery.Lines.ItemCode = value.products[i].ItemCode;
//delivery.Lines.Quantity = value.products[i].Count;
//delivery.Lines.UoMEntry = value.products[i].UoMEntry;
//delivery.Lines.WarehouseCode = value.products[i].WarehouseCode;
delivery.Lines.BaseEntry = order.DocEntry;
delivery.Lines.BaseLine = value.products[i].Line;
delivery.Lines.BaseType = 17;
delivery.Lines.Quantity = value.products[i].Count;
for (int j = 0; j < value.products[i].batch.Count; j++)
{
delivery.Lines.BatchNumbers.BaseLineNumber = delivery.Lines.LineNum;
delivery.Lines.BatchNumbers.BatchNumber = value.products[i].batch[j].name;
delivery.Lines.BatchNumbers.Quantity = value.products[i].batch[j].quantity;
delivery.Lines.BatchNumbers.Add();
}
delivery.Lines.Add();
}
//delivery.Comments = "Test";
int result = delivery.Add();
if (result == 0)
{
return(Ok(new { value }));
}
else
{
string error = context.oCompany.GetLastErrorDescription();
return(BadRequest(new { error }));
}
}
return(BadRequest(new { error = "No Existe Documento" }));
}
//[Authorize]
public async Task <IActionResult> Post([FromBody] PurchaseOrderDelivery value)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Documents purchaseOrder = (SAPbobsCOM.Documents)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders);
SAPbobsCOM.Documents purchaseOrderdelivery = (SAPbobsCOM.Documents)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes);
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
if (purchaseOrder.GetByKey(value.order))
{
if ((String)purchaseOrder.UserFields.Fields.Item("U_IL_Pedimento").Value == String.Empty)
{
purchaseOrder.UserFields.Fields.Item("U_IL_Pedimento").Value = value.pedimento;
purchaseOrder.Update();
}
purchaseOrderdelivery.CardCode = purchaseOrder.CardCode;
purchaseOrderdelivery.DocDate = DateTime.Now;
purchaseOrderdelivery.DocDueDate = DateTime.Now;
if (purchaseOrder.DocRate != 1)
{
purchaseOrderdelivery.DocRate = purchaseOrder.DocRate;
}
oRecSet.DoQuery(@"
Select
count(*) as count
From PDN1
Where ""BaseEntry"" = " + purchaseOrder.DocEntry + "");
oRecSet.MoveFirst();
int count = context.XMLTOJSON(oRecSet.GetAsXML())["PDN1"][0]["COUNT"].ToObject <int>();
if (count == 0)
{
for (int i = 0; i < purchaseOrder.Expenses.Count; i++)
{
purchaseOrder.Expenses.SetCurrentLine(i);
if (purchaseOrder.Expenses.LineTotal != 0)
{
purchaseOrderdelivery.Expenses.ExpenseCode = purchaseOrder.Expenses.ExpenseCode;
if (purchaseOrder.DocCurrency == "MXN")
{
purchaseOrderdelivery.Expenses.LineTotal = purchaseOrder.Expenses.LineTotal;
}
else
{
purchaseOrderdelivery.Expenses.LineTotal = purchaseOrder.Expenses.LineTotalFC;
}
purchaseOrderdelivery.Expenses.TaxCode = purchaseOrder.Expenses.TaxCode;
purchaseOrderdelivery.Expenses.Remarks = purchaseOrder.Expenses.Remarks;
purchaseOrderdelivery.Expenses.VatGroup = purchaseOrder.Expenses.VatGroup;
purchaseOrderdelivery.Expenses.WTLiable = SAPbobsCOM.BoYesNoEnum.tNO;
purchaseOrderdelivery.Expenses.DistributionMethod = purchaseOrder.Expenses.DistributionMethod;
purchaseOrderdelivery.Expenses.Add();
}
}
}
for (int i = 0; i < value.products.Count; i++)
{
//purchaseOrderdelivery.Lines.ItemCode = value.products[i].ItemCode;
//purchaseOrderdelivery.Lines.Quantity = value.products[i].Count;
// purchaseOrderdelivery.Lines.UoMEntry = value.products[i].UoMEntry;
// purchaseOrderdelivery.Lines.WarehouseCode = value.products[i].WarehouseCode;
purchaseOrderdelivery.Lines.BaseEntry = purchaseOrder.DocEntry;
purchaseOrderdelivery.Lines.BaseLine = value.products[i].Line;
purchaseOrderdelivery.Lines.BaseType = 22;
if (value.products[i].UoMEntry == 116 || value.products[i].UoMEntry == 196)
{
purchaseOrder.Lines.SetCurrentLine(value.products[i].Line);
if (value.products[i].Group == 43)
{
oRecSet.DoQuery(@"
Select
""NumInBuy"",
""IUoMEntry""
From OITM Where ""ItemCode"" = '" + value.products[i].ItemCode + "'");
oRecSet.MoveFirst();
double price = context.XMLTOJSON(oRecSet.GetAsXML())["OITM"][0]["NumInBuy"].ToObject <double>();
purchaseOrderdelivery.Lines.UoMEntry = context.XMLTOJSON(oRecSet.GetAsXML())["OITM"][0]["IUoMEntry"].ToObject <int>();
purchaseOrderdelivery.Lines.UnitPrice = purchaseOrder.Lines.UnitPrice / price;
}
else
{
purchaseOrderdelivery.Lines.UoMEntry = 185;
purchaseOrderdelivery.Lines.UnitPrice = purchaseOrder.Lines.UnitPrice * 2.20462;
}
//Console.WriteLine(value.products[i].ItemType);
//if (value.products[i].ItemType == "F")
//{
// Console.WriteLine(purchaseOrder.Lines.Quantity);
// Console.WriteLine(purchaseOrder.Lines.UnitPrice);
// Console.WriteLine(purchaseOrder.Lines.LineTotal);
// Console.WriteLine(purchaseOrder.Lines.RowTotalFC);
// if (purchaseOrder.Lines.Currency == "USD") {
// purchaseOrderdelivery.Lines.DiscountPercent = ((value.products[i].Count * Math.Round(purchaseOrder.Lines.UnitPrice * 2.20462, 2)) * 100 / purchaseOrder.Lines.RowTotalFC) - 100;
// } else {
// purchaseOrderdelivery.Lines.DiscountPercent = ((value.products[i].Count * Math.Round(purchaseOrder.Lines.UnitPrice * 2.20462, 2)) * 100 / purchaseOrder.Lines.LineTotal) - 100;
// }
// //purchaseOrderdelivery.Lines.LineTotal = purchaseOrder.Lines.LineTotal;
// //purchaseOrderdelivery.Lines.RowTotalFC = purchaseOrder.Lines.RowTotalFC;
// //Console.WriteLine(purchaseOrderdelivery.Lines.LineTotal);
//}
}
purchaseOrderdelivery.Lines.Quantity = value.products[i].Count;
for (int j = 0; j < value.products[i].batch.Count; j++)
{
purchaseOrderdelivery.Lines.BatchNumbers.BatchNumber = value.products[i].batch[j].name;
purchaseOrderdelivery.Lines.BatchNumbers.Quantity = value.products[i].batch[j].quantity;
purchaseOrderdelivery.Lines.BatchNumbers.ManufacturerSerialNumber = value.products[i].batch[j].pedimento;
purchaseOrderdelivery.Lines.BatchNumbers.InternalSerialNumber = value.products[i].batch[j].attr1;
purchaseOrderdelivery.Lines.BatchNumbers.ExpiryDate = value.products[i].batch[j].expirationDate.Date;
purchaseOrderdelivery.Lines.BatchNumbers.UserFields.Fields.Item("U_IL_CodBar").Value = value.products[i].batch[j].code;
//purchaseOrderdelivery.Lines.BatchNumbers.UserFields.Fields.Item("U_CodBarCj").Value = value.products[i].batch[j].code;
purchaseOrderdelivery.Lines.BatchNumbers.Add();
}
purchaseOrderdelivery.Lines.Add();
}
int result = purchaseOrderdelivery.Add();
if (result == 0)
{
return(Ok(new { value }));
}
else
{
string error = context.oCompany.GetLastErrorDescription();
return(BadRequest(new { error }));
}
}
return(BadRequest("No Existe Documento"));
}
//[Authorize]
public async Task <IActionResult> GetReturn(int DocEntry)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery(@"
Select
""DocEntry"",
""DocNum"",
""DocStatus"",
""CardName"",
""CardCode""
From OPDN WHERE ""DocEntry"" = " + DocEntry);
if (oRecSet.RecordCount == 0)
{
return(NotFound("No exite Documento"));
}
JToken purchaseDelivery = context.XMLTOJSON(oRecSet.GetAsXML())["OPDN"][0];
if (purchaseDelivery["DocStatus"].ToString() != "O")
{
return(BadRequest("Documento Cerrado"));
}
oRecSet.DoQuery(@"
Select
""LineStatus"",
""LineNum"",
""ItemCode"",
""Dscription"",
""UomEntry"",
""WhsCode"",
""UomCode"",
""OpenInvQty"",
""OpenQty""
From PDN1 WHERE ""DocEntry"" = " + DocEntry);
purchaseDelivery["PDN1"] = context.XMLTOJSON(oRecSet.GetAsXML())["PDN1"];
foreach (JToken product in purchaseDelivery["PDN1"])
{
oRecSet.DoQuery(@"
Select
""ItemCode"",
""ItemName"",
""QryGroup2"",
""QryGroup7"",
""QryGroup41"",
""QryGroup43"",
""QryGroup44"",
""QryGroup45"",
""ManBtchNum"",
""U_IL_PesMax"",
""U_IL_PesMin"",
""U_IL_PesProm"",
""U_IL_TipPes"",
""NumInSale"",
""NumInBuy""
From OITM Where ""ItemCode"" = '" + product["ItemCode"] + "'");
oRecSet.MoveFirst();
product["Detail"] = context.XMLTOJSON(oRecSet.GetAsXML())["OITM"][0];
//oRecSet.DoQuery(@"
// Select
// ""BcdEntry"",
// ""BcdCode"",
// ""BcdName"",
// ""ItemCode"",
// ""UomEntry""
// From OBCD Where ""ItemCode"" = '" + pro["ItemCode"] + "'");
//oRecSet.MoveFirst();
//pro["CodeBars"] = context.XMLTOJSON(oRecSet.GetAsXML())["OBCD"];
}
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok(purchaseDelivery));
}
public async Task <IActionResult> GetSearch([FromBody] SearchRequest request)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
List <string> where = new List <string>();
if (request.columns[0].search.value != String.Empty)
{
where.Add($"LOWER(purchaseDelivery.\"DocNum\") Like LOWER('%{request.columns[0].search.value}%')");
}
if (request.columns[1].search.value != String.Empty)
{
where.Add($"LOWER(contact.\"CardFName\") Like LOWER('%{request.columns[1].search.value}%')");
}
if (request.columns[2].search.value != String.Empty)
{
where.Add($"LOWER(contact.\"CardName\") Like LOWER('%{request.columns[2].search.value}%')");
}
if (request.columns[3].search.value != String.Empty)
{
where.Add($"LOWER(warehouse.\"WhsName\") Like LOWER('%{request.columns[3].search.value}%')");
}
if (request.columns[4].search.value != String.Empty)
{
List <string> whereOR = new List <string>();
if ("Abierto".Contains(request.columns[4].search.value, StringComparison.CurrentCultureIgnoreCase))
{
whereOR.Add(@"purchaseDelivery.""DocStatus"" = 'O' ");
}
if ("Cerrado".Contains(request.columns[4].search.value, StringComparison.CurrentCultureIgnoreCase))
{
whereOR.Add(@"purchaseDelivery.""DocStatus"" = 'C' ");
}
if ("Cancelado".Contains(request.columns[4].search.value, StringComparison.CurrentCultureIgnoreCase))
{
whereOR.Add(@"purchaseDelivery.""CANCELED"" = 'Y' ");
}
string whereORClause = "(" + String.Join(" OR ", whereOR) + ")";
where.Add(whereORClause);
}
if (request.columns[5].search.value != String.Empty)
{
where.Add($"to_char(to_date(SUBSTRING(purchaseDelivery.\"DocDate\", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') Like '%{request.columns[5].search.value}%'");
}
string orderby = "";
if (request.order[0].column == 0)
{
orderby = $" ORDER BY purchaseDelivery.\"DocNum\" {request.order[0].dir}";
}
else if (request.order[0].column == 1)
{
orderby = $" ORDER BY contact.\"CardFName\" {request.order[0].dir}";
}
else if (request.order[0].column == 2)
{
orderby = $" ORDER BY contact.\"CardName\" {request.order[0].dir}";
}
else if (request.order[0].column == 3)
{
orderby = $" ORDER BY warehouse.\"WhsName\" {request.order[0].dir}";
}
else if (request.order[0].column == 4)
{
orderby = $" ORDER BY purchaseDelivery.\"DocStatus\" {request.order[0].dir}";
}
else if (request.order[0].column == 5)
{
orderby = $" ORDER BY purchaseDelivery.\"DocDate\" {request.order[0].dir}";
}
else
{
orderby = $" ORDER BY purchaseDelivery.\"DocNum\" DESC";
}
string whereClause = String.Join(" AND ", where);
string query = @"
Select
purchaseDelivery.""DocEntry"",
purchaseDelivery.""DocNum"",
to_char(to_date(SUBSTRING(purchaseDelivery.""DocDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDate"",
(case when purchaseDelivery.""CANCELED"" = 'Y' then 'Cancelado'
when purchaseDelivery.""DocStatus"" = 'O' then 'Abierto'
when purchaseDelivery.""DocStatus"" = 'C' then 'Cerrado'
else purchaseDelivery.""DocStatus"" end) AS ""DocStatus"",
purchaseDelivery.""CardName"",
contact.""CardFName"",
warehouse.""WhsName""
From OPDN purchaseDelivery
LEFT JOIN NNM1 serie ON purchaseDelivery.""Series"" = serie.""Series""
LEFT JOIN OWHS warehouse ON serie.""SeriesName"" = warehouse.""WhsCode""
LEFT JOIN OCRD contact ON purchaseDelivery.""CardCode"" = contact.""CardCode"" ";
if (where.Count != 0)
{
query += "Where " + whereClause;
}
query += orderby;
query += " LIMIT " + request.length + " OFFSET " + request.start + "";
oRecSet.DoQuery(query);
oRecSet.MoveFirst();
List <PurchaseOrderSearchDetail> orders = context.XMLTOJSON(oRecSet.GetAsXML())["OPDN"].ToObject <List <PurchaseOrderSearchDetail> >();
string queryCount = @"
Select
Count (*) as COUNT
From OPDN purchaseDelivery
LEFT JOIN NNM1 serie ON purchaseDelivery.""Series"" = serie.""Series""
LEFT JOIN OWHS warehouse ON serie.""SeriesName"" = warehouse.""WhsCode""
LEFT JOIN OCRD contact ON purchaseDelivery.""CardCode"" = contact.""CardCode"" ";
if (where.Count != 0)
{
queryCount += "Where " + whereClause;
}
oRecSet.DoQuery(queryCount);
oRecSet.MoveFirst();
int COUNT = context.XMLTOJSON(oRecSet.GetAsXML())["OPDN"][0]["COUNT"].ToObject <int>();
PurchaseOrderSearchResponse respose = new PurchaseOrderSearchResponse {
data = orders,
draw = request.Draw,
recordsFiltered = COUNT,
recordsTotal = COUNT,
};
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok(respose));
}
public async Task <IActionResult> GetWMSDetail(int DocEntry)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
PurchaseDeliveryDetail purchaseDeliveryDetail;
JToken purchaseDelivery;
string DocCur;
oRecSet.DoQuery(@"
Select
purchaseDelivery.""DocEntry"",
purchaseDelivery.""DocNum"",
purchaseDelivery.""DocCur"",
(case when purchaseDelivery.""DocCur"" = 'USD' then purchaseDelivery.""DocTotalFC""
else purchaseDelivery.""DocTotal"" end) AS ""Total"",
to_char(to_date(SUBSTRING(purchaseDelivery.""DocDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDate"",
to_char(to_date(SUBSTRING(purchaseDelivery.""DocDueDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDueDate"",
to_char(to_date(SUBSTRING(purchaseDelivery.""CancelDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""CancelDate"",
(case when purchaseDelivery.""CANCELED"" = 'Y' then 'Cancelado'
when purchaseDelivery.""DocStatus"" = 'O' then 'Abierto'
when purchaseDelivery.""DocStatus"" = 'C' then 'Cerrado'
else purchaseDelivery.""DocStatus"" end) AS ""DocStatus"",
purchaseDelivery.""Comments"",
contact.""CardCode"",
contact.""CardName"",
contact.""CardFName"",
warehouse.""WhsName""
From OPDN purchaseDelivery
LEFT JOIN NNM1 serie ON purchaseDelivery.""Series"" = serie.""Series""
LEFT JOIN OWHS warehouse ON serie.""SeriesName"" = warehouse.""WhsCode""
LEFT JOIN OCRD contact ON purchaseDelivery.""CardCode"" = contact.""CardCode""
WHERE purchaseDelivery.""DocEntry"" = '" + DocEntry + "'");
if (oRecSet.RecordCount == 0)
{
return(NotFound("No Existe Documento"));
}
purchaseDelivery = context.XMLTOJSON(oRecSet.GetAsXML())["OPDN"][0];
DocCur = purchaseDelivery["DocCur"].ToString();
oRecSet.DoQuery(@"
Select
""ItemCode"",
""Dscription"",
""Price"",
""Currency"",
""Quantity"",
""UomCode"",
""InvQty"",
""UomCode2"",
(case when '" + DocCur + @"' = 'USD' then ""TotalFrgn""
else ""LineTotal"" end) AS ""Total""
From PDN1
WHERE ""DocEntry"" = '" + DocEntry + "'");
oRecSet.MoveFirst();
purchaseDelivery["PurchaseDeliveryRows"] = context.XMLTOJSON(oRecSet.GetAsXML())["PDN1"];
purchaseDeliveryDetail = purchaseDelivery.ToObject <PurchaseDeliveryDetail>();
purchaseDelivery = null;
oRecSet = null;
DocCur = null;
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok(purchaseDeliveryDetail));
}
//[Authorize]
public async Task <IActionResult> Get(string CodeBar)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery($@"
Select
""BcdEntry"",
""BcdCode"",
""BcdName"",
""ItemCode"",
""UomEntry""
From OBCD Where ""BcdCode"" = '{CodeBar}';");
if (oRecSet.RecordCount == 0)
{
return(NoContent());
}
string ItemCode = context.XMLTOJSON(oRecSet.GetAsXML())["OBCD"][0]["ItemCode"].ToString();
oRecSet.DoQuery($@"
Select
""ItemCode"",
""ItemName"",
""QryGroup7"",
""QryGroup41"",
""ManBtchNum"",
""U_IL_PesMax"",
""U_IL_PesMin"",
""U_IL_PesProm"",
""U_IL_TipPes"",
""NumInSale"",
""NumInBuy""
From OITM Where ""ItemCode"" = '{ItemCode}';");
oRecSet.MoveFirst();
JToken Detail = context.XMLTOJSON(oRecSet.GetAsXML())["OITM"][0];
oRecSet.DoQuery($@"
Select
""BcdEntry"",
""BcdCode"",
""BcdName"",
""ItemCode"",
""UomEntry""
From OBCD Where ""ItemCode"" = '{ItemCode}';");
oRecSet.MoveFirst();
JToken CodeBars = context.XMLTOJSON(oRecSet.GetAsXML())["OBCD"];
oRecSet.DoQuery($@"
Select
header.""UgpCode"",
header.""BaseUom"",
baseUOM.""UomCode"" as baseUOM,
detail.""UomEntry"",
UOM.""UomCode"",
detail.""BaseQty""
From OUGP header
JOIN UGP1 detail ON header.""UgpEntry"" = detail.""UgpEntry""
JOIN OUOM baseUOM ON header.""BaseUom"" = baseUOM.""UomEntry""
JOIN OUOM UOM ON detail.""UomEntry"" = UOM.""UomEntry""
Where header.""UgpCode"" = '{ItemCode}';");
oRecSet.MoveFirst();
JToken uom = context.XMLTOJSON(oRecSet.GetAsXML())["OUGP"];
return(Ok(new { Detail, CodeBars, uom }));
}
//[Authorize]
public async Task <IActionResult> PostDelivery([FromBody] Delivery value)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Documents order = (SAPbobsCOM.Documents)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
int DeliveryDocumentCount = value.DeliveryRows.Select(row => row.DeliveryRowDetailList).Select(Detail => Detail.Count).Max();
SAPbobsCOM.Documents[] deliveryList = new SAPbobsCOM.Documents[DeliveryDocumentCount];
for (int i = 0; i < deliveryList.Length; i++)
{
deliveryList[i] = (SAPbobsCOM.Documents)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes);
}
if (!order.GetByKey(value.DocEntry))
{
return(NoContent());
}
oRecSet.DoQuery($@"
Select
serie1.""SeriesName"",
serie1.""Series"",
serie1.""ObjectCode"",
serie2.""SeriesName""as s1,
serie2.""Series"" as s2,
serie2.""ObjectCode"" as s3
From NNM1 serie1
JOIN NNM1 serie2 ON serie1.""SeriesName"" = serie2.""SeriesName""
Where serie1.""ObjectCode"" = 15 AND serie2.""Series"" = '{order.Series}';");
if (oRecSet.RecordCount == 0)
{
return(BadRequest("Error Con la Sucursal"));
}
int Serie = context.XMLTOJSON(oRecSet.GetAsXML())["NNM1"][0]["Series"].ToObject <int>();
for (int i = 0; i < deliveryList.Length; i++)
{
deliveryList[i].CardCode = order.CardCode;
deliveryList[i].DocDate = DateTime.Now;
deliveryList[i].DocDueDate = DateTime.Now;
deliveryList[i].Series = Serie;
}
for (int i = 0; i < value.DeliveryRows.Count; i++)
{
for (int j = 0; j < value.DeliveryRows[i].DeliveryRowDetailList.Count; j++)
{
deliveryList[j].Lines.BaseEntry = order.DocEntry;
deliveryList[j].Lines.BaseLine = value.DeliveryRows[i].LineNum;
deliveryList[j].Lines.UoMEntry = value.DeliveryRows[i].DeliveryRowDetailList[j].UomEntry;
deliveryList[j].Lines.BaseType = (int)SAPbobsCOM.BoAPARDocumentTypes.bodt_Order;
deliveryList[j].Lines.Quantity = value.DeliveryRows[i].DeliveryRowDetailList[j].Count;
for (int k = 0; k < value.DeliveryRows[i].DeliveryRowDetailList[j].BatchList.Count; k++)
{
deliveryList[j].Lines.BatchNumbers.BaseLineNumber = deliveryList[j].Lines.LineNum;
deliveryList[j].Lines.BatchNumbers.BatchNumber = value.DeliveryRows[i].DeliveryRowDetailList[j].BatchList[k].Code;
deliveryList[j].Lines.BatchNumbers.Quantity = value.DeliveryRows[i].DeliveryRowDetailList[j].BatchList[k].Quantity;
deliveryList[j].Lines.BatchNumbers.Add();
}
deliveryList[j].Lines.Add();
}
}
StringBuilder Errors = new StringBuilder();
for (int i = 0; i < deliveryList.Length; i++)
{
if (deliveryList[i].Add() != 0)
{
Errors.AppendLine($"Documento Numero: {i}");
Errors.AppendLine(context.oCompany.GetLastErrorDescription());
}
}
if (Errors.Length != 0)
{
string error = Errors.ToString();
return(BadRequest(error));
}
//Force Garbage Collector. Recommendation by InterLatin Dude. SDK Problem with memory.
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok());
}
public async Task <IActionResult> GetClients([FromBody] SearchRequest request)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
List <string> where = new List <string>();
if (request.columns[0].search.value != String.Empty)
{
where.Add($"LOWER(\"CardCode\") Like LOWER('%{request.columns[0].search.value}%')");
}
if (request.columns[1].search.value != String.Empty)
{
where.Add($"LOWER(\"CardFName\") Like LOWER('%{request.columns[1].search.value}%')");
}
if (request.columns[2].search.value != String.Empty)
{
where.Add($"LOWER(\"CardName\") Like LOWER('%{request.columns[2].search.value}%')");
}
string orderby = "";
if (request.order[0].column == 0)
{
orderby = $" ORDER BY \"CardCode\" {request.order[0].dir}";
}
else if (request.order[0].column == 1)
{
orderby = $" ORDER BY \"CardFName\" {request.order[0].dir}";
}
else if (request.order[0].column == 2)
{
orderby = $" ORDER BY \"CardName\" {request.order[0].dir}";
}
else
{
orderby = $" ORDER BY \"CardCode\" DESC";
}
string whereClause = String.Join(" AND ", where);
string query = @"
Select ""CardCode"", ""CardName"", ""CardFName""
From OCRD Where ""CardType"" = 'C' AND ""CardCode"" NOT LIKE '%-D'";
if (where.Count != 0)
{
query += " AND " + whereClause;
}
query += orderby;
query += " LIMIT " + request.length + " OFFSET " + request.start + "";
oRecSet.DoQuery(query);
oRecSet.MoveFirst();
var orders = context.XMLTOJSON(oRecSet.GetAsXML())["OCRD"].ToObject <List <ClientSearchDetail> >();
string queryCount = @"
Select
Count (*) as COUNT
From OCRD Where ""CardType"" = 'C' AND ""CardCode"" NOT LIKE '%-D' ";
if (where.Count != 0)
{
queryCount += " AND " + whereClause;
}
oRecSet.DoQuery(queryCount);
oRecSet.MoveFirst();
int COUNT = context.XMLTOJSON(oRecSet.GetAsXML())["OCRD"][0]["COUNT"].ToObject <int>();
ClientSearchResponse respose = new ClientSearchResponse {
data = orders,
draw = request.Draw,
recordsFiltered = COUNT,
recordsTotal = COUNT,
};
return(Ok(respose));
}
public async Task <IActionResult> GetSearch([FromBody] SearchRequest request)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
List <string> where = new List <string>();
if (request.columns[0].search.value != String.Empty)
{
where.Add($"LOWER(\"DocNum\") Like LOWER('%{request.columns[0].search.value}%')");
}
if (request.columns[1].search.value != String.Empty)
{
where.Add($"LOWER(\"Filler\") Like LOWER('%{request.columns[1].search.value}%')");
}
if (request.columns[2].search.value != String.Empty)
{
where.Add($"LOWER(\"ToWhsCode\") Like LOWER('%{request.columns[2].search.value}%')");
}
if (request.columns[3].search.value != String.Empty)
{
where.Add($"to_char(to_date(SUBSTRING(\"DocDate\", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') Like '%{request.columns[3].search.value}%'");
}
string orderby = "";
if (request.order[0].column == 0)
{
orderby = $" ORDER BY \"DocNum\" {request.order[0].dir}";
}
else if (request.order[0].column == 1)
{
orderby = $" ORDER BY \"Filler\" {request.order[0].dir}";
}
else if (request.order[0].column == 2)
{
orderby = $" ORDER BY \"ToWhsCode\" {request.order[0].dir}";
}
else if (request.order[0].column == 3)
{
orderby = $" ORDER BY \"DocDate\" {request.order[0].dir}";
}
else
{
orderby = $" ORDER BY \"DocNum\" DESC";
}
string whereClause = String.Join(" AND ", where);
string query = @"
Select
""DocEntry"",
""DocNum"",
to_char(to_date(SUBSTRING(""DocDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDate"",
""ToWhsCode"",
""Filler""
From OWTR ";
if (where.Count != 0)
{
query += "Where " + whereClause;
}
query += orderby;
query += " LIMIT " + request.length + " OFFSET " + request.start + "";
oRecSet.DoQuery(query);
oRecSet.MoveFirst();
List <TransferSearchDetail> orders = context.XMLTOJSON(oRecSet.GetAsXML())["OWTR"].ToObject <List <TransferSearchDetail> >();
string queryCount = @"Select Count (*) as COUNT From OWTR ";
if (where.Count != 0)
{
queryCount += "Where " + whereClause;
}
oRecSet.DoQuery(queryCount);
oRecSet.MoveFirst();
int COUNT = context.XMLTOJSON(oRecSet.GetAsXML())["OWTR"][0]["COUNT"].ToObject <int>();
TransferSearchResponse respose = new TransferSearchResponse {
data = orders,
draw = request.Draw,
recordsFiltered = COUNT,
recordsTotal = COUNT,
};
return(Ok(respose));
}
public async Task <IActionResult> GetDeliveryTransferRequest(uint DocNum)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery($@"
Select
""DocEntry"",
""DocNum"",
""DocStatus"",
""ToWhsCode"",
""Filler""
From OWTQ WHERE ""DocNum"" = {DocNum};");
if (oRecSet.RecordCount == 0)
{
return(NoContent());
}
JToken request = context.XMLTOJSON(oRecSet.GetAsXML())["OWTQ"][0];
if (request["DocStatus"].ToString() != "O")
{
return(BadRequest("Documento Cerrado"));
}
oRecSet.DoQuery($@"
Select
""LineStatus"",
""LineNum"",
Line.""ItemCode"",
""FromWhsCod"",
""UomEntry"",
""WhsCode"",
""UomCode"",
""OpenInvQty"",
""OpenQty"",
""ItemName"",
""QryGroup44"",
""QryGroup45"",
""ManBtchNum"",
""U_IL_PesMax"",
""U_IL_PesMin"",
""U_IL_PesProm"",
""U_IL_TipPes"",
""NumInSale""
From WTQ1 as Line
JOIN OITM as Detail on Detail.""ItemCode"" = Line.""ItemCode""
WHERE Line.""DocEntry"" = {request["DocEntry"]};");
request["Lines"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTQ1"];
foreach (var line in request["Lines"])
{
oRecSet.DoQuery($@"
Select ""BcdCode""
From OBCD Where ""ItemCode"" = '{line["ItemCode"]}';");
var temp = context.XMLTOJSON(oRecSet.GetAsXML())["OBCD"].Select(Q => (string)Q["BcdCode"]);
line["CodeBars"] = JArray.FromObject(temp);
oRecSet.DoQuery($@"
Select
header.""BaseUom"" as ""BaseEntry"",
baseUOM.""UomCode"" as ""BaseUom"",
detail.""UomEntry"",
UOM.""UomCode"",
detail.""BaseQty""
From OUGP header
JOIN UGP1 detail ON header.""UgpEntry"" = detail.""UgpEntry""
JOIN OUOM baseUOM ON header.""BaseUom"" = baseUOM.""UomEntry""
JOIN OUOM UOM ON detail.""UomEntry"" = UOM.""UomEntry""
Where header.""UgpCode"" = '{line["ItemCode"]}';");
line["Uoms"] = context.XMLTOJSON(oRecSet.GetAsXML())["OUGP"];
}
TransferDeliveryOutput output = request.ToObject <TransferDeliveryOutput>();
return(Ok(output));
}
public async Task <IActionResult> GetWMSDetail(int id, string doctype)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
if (!doctype.Equals("DocEntry") && !doctype.Equals("DocNum"))
{
return(BadRequest(new { error = "Doc Type to Search Invalid" }));
}
oRecSet.DoQuery(@"
SELECT
document.""DocEntry"",
document.""DocNum"",
to_char(to_date(SUBSTRING(document.""DocDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDate"",
(case when document.""CANCELED"" = 'Y' then 'Cancelado'
when document.""DocStatus"" = 'O' then 'Abierto'
when document.""DocStatus"" = 'C' then 'Cerrado'
else document.""DocStatus"" end) AS ""DocStatus"",
document.""Filler"",
whsOrigin.""WhsName"" as ""WhsNameOrigin"",
whsDest.""WhsName"" as ""WhsNameDest""
From OWTQ document
JOIN OWHS whsOrigin ON document.""Filler"" = whsOrigin.""WhsCode""
JOIN OWHS whsDest ON document.""ToWhsCode"" = whsDest.""WhsCode""
WHERE """ + doctype + @""" = " + id);
if (oRecSet.RecordCount == 0)
{
return(NotFound("No Existe Documento"));
}
JToken transfer = context.XMLTOJSON(oRecSet.GetAsXML())["OWTQ"][0];
int DocEntry;
int DocNum;
if (doctype.Equals("DocEntry"))
{
DocEntry = id;
DocNum = transfer["DocNum"].ToObject <int>();
}
else
{
DocEntry = transfer["DocEntry"].ToObject <int>();
DocNum = id;
}
oRecSet.DoQuery(@"
SELECT
""LineNum"",
(case when ""LineStatus"" = 'O' then 'Abierto'
when ""LineStatus"" = 'C' then 'Cerrado'
else ""LineStatus"" end) AS ""LineStatus"",
""ItemCode"",
""Dscription"",
""Quantity"",
""UomCode""
FROM WTQ1 WHERE ""DocEntry"" = " + DocEntry);
transfer["Lines"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTQ1"];
// Inventory Transfers
oRecSet.DoQuery(@"
SELECT
""DocEntry"",
""DocNum"",
to_char(to_date(SUBSTRING(""DocDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDate"",
to_char(to_date(SUBSTRING(""DocDueDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDueDate"",
""ToWhsCode"",
""Filler""
FROM OWTR
WHERE ""DocEntry"" in (SELECT ""DocEntry"" FROM WTR1 WHERE ""BaseEntry"" = " + DocEntry + ")");
if (oRecSet.RecordCount != 0)
{
transfer["TransferList"] = context.XMLTOJSON(oRecSet.GetAsXML())["OWTR"];
oRecSet.DoQuery(@"
SELECT
""DocEntry"",
""ItemCode"",
""Dscription"",
""Quantity"",
""UomCode""
FROM WTR1
WHERE ""BaseEntry"" = " + DocEntry);
transfer["TransferLines"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTR1"];
List <string> DocEntryListToGetBarCodes = new List <string>();
foreach (JToken transfers in transfer["TransferList"])
{
DocEntryListToGetBarCodes.Add(transfers["DocEntry"].ToString());
}
string DocEntriesToGetBarCode = String.Join(",", DocEntryListToGetBarCodes);
oRecSet.DoQuery(@"
SELECT
""ItemCode"",
""BatchNum"",
""LineNum"",
""BaseEntry"",
""Quantity""
From IBT1 WHERE ""BaseEntry"" in (" + DocEntriesToGetBarCode + @") AND ""WhsCode"" = '" + transfer["Filler"].ToString() + "'");
transfer["BarCodes"] = context.XMLTOJSON(oRecSet.GetAsXML())["IBT1"];
//foreach (JToken transferDetail in transfer["TransfersDetail"])
//{
// oRecSet.DoQuery(@"SELECT * From IBT1 WHERE ""ItemCode"" = '" + transferDetail["ItemCode"] + @"' AND ""BaseEntry"" = " + transferDetail["DocEntry"] + @" AND ""WhsCode"" = '" + transferDetail["FromWhsCod"] + "'");
// transferDetail["Codes"] = context.XMLTOJSON(oRecSet.GetAsXML())["IBT1"];
//}
oRecSet.DoQuery(@"
SELECT
""DocNum"",
""DocEntry"",
to_char(to_date(SUBSTRING(""DocDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDate"",
(case when ""CANCELED"" = 'Y' then 'Cancelado'
when ""DocStatus"" = 'O' then 'Abierto'
when ""DocStatus"" = 'C' then 'Cerrado'
else ""DocStatus"" end) AS ""DocStatus""
From OWTQ WHERE ""U_SO1_02NUMRECEPCION"" = " + DocNum);
if (oRecSet.RecordCount != 0)
{
transfer["TransferRequestCopyList"] = context.XMLTOJSON(oRecSet.GetAsXML())["OWTQ"];
oRecSet.DoQuery(@"
SELECT
""DocEntry"",
""ItemCode"",
""Quantity"",
""UomCode""
FROM WTQ1 WHERE ""DocEntry"" in (SELECT ""DocEntry"" From OWTQ WHERE ""U_SO1_02NUMRECEPCION"" = " + DocNum + ")");
transfer["TransferRequestCopyLines"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTQ1"];
}
}
return(Ok(transfer));
}
public async Task <IActionResult> GetInventoryTransferProtheus(string WHS, string InitialDate, string EndDate)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery($@"
SELECT
RIGHT(T1.""WhsCode"",2) AS ""1"",
CASE
WHEN T1.""Quantity"" > 0 THEN
CASE
WHEN T1.""Quantity"" * T1.""LineTotal"" > 0 THEN
'003'
ELSE
'002'
END
ELSE
CASE
WHEN T1.""Quantity"" * T1.""LineTotal"" < 0 THEN
'601'
ELSE
'501'
END
END AS ""2"",
RIGHT(T1.""ItemCode"", 7) AS ""3"",
T1.""Quantity"" * T4.""NumInSale"" AS ""4"",
'01' AS ""5"",
CONCAT(T2.""DocNum"", LPAD(T1.""LineNum"", 2, '00')) AS ""6"",
TO_VARCHAR(T2.""DocDate"", 'DD/MM/YYYY') as ""7"",
T1.""LineTotal"" * IFNULL(NULLIF(T1.""Rate"", 0), 1) AS ""11"",
'Transferencia SAP' AS ""12""
FROM
WTR1 T1 INNER JOIN
OWTR T2
ON T1.""DocEntry"" = T2.""DocEntry"" INNER JOIN
NNM1 T3
ON T2.""Series"" = T3.""Series"" INNER JOIN
OITM T4
ON T4.""ItemCode"" = T1.""ItemCode""
WHERE
-- T3.""SeriesName"" IN('S02', 'S03', 'S04', 'S08', 'S09', 'S11', 'S14', 'S21', 'S23', 'S24', 'S28', 'S32', 'S34', 'S38', 'S44', 'S46', 'S51', 'S52', 'S57', 'S61') AND – Esta linea es solo poner en comentarios
T3.""SeriesName"" = '{WHS}' AND
T2.""DocDate"" >= '{InitialDate}' AND
T2.""DocDate"" <= '{EndDate}'
ORDER BY
T3.""SeriesName""");
if (oRecSet.RecordCount == 0)
{
return(NoContent());
}
JToken Transferencia = context.XMLTOJSON(oRecSet.GetAsXML())["WTR1"];
//Force Garbage Collector. Recommendation by InterLatin Dude. SDK Problem with memory.
GC.Collect();
GC.WaitForPendingFinalizers();
return(Ok(Transferencia));
}
public async Task <IActionResult> Post([FromBody] TransferOld value)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.StockTransfer request = (SAPbobsCOM.StockTransfer)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryTransferRequest);
SAPbobsCOM.StockTransfer transfer = (SAPbobsCOM.StockTransfer)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer);
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
if (request.GetByKey(value.order))
{
transfer.DocDate = DateTime.Now;
oRecSet.DoQuery(@"
Select
serie1.""SeriesName"",
serie1.""Series"",
serie1.""ObjectCode"",
serie2.""SeriesName""as s1,
serie2.""Series"" as s2,
serie2.""ObjectCode"" as s3
From NNM1 serie1
JOIN NNM1 serie2 ON serie1.""SeriesName"" = serie2.""SeriesName""
Where serie1.""ObjectCode"" = 67 AND serie2.""Series"" = '" + request.Series + "'");
oRecSet.MoveFirst();
transfer.Series = context.XMLTOJSON(oRecSet.GetAsXML())["NNM1"][0]["Series"].ToObject <int>();
for (int i = 0; i < value.products.Count; i++)
{
//transfer.Lines.ItemCode = value.products[i].ItemCode;
//transfer.Lines.Quantity = value.products[i].Count;
//transfer.Lines.UoMEntry = value.products[i].UoMEntry;
//transfer.Lines.FromWarehouseCode = "S01";
// transfer.Lines.WarehouseCode = value.products[i].WarehouseCode;
transfer.Lines.BaseEntry = request.DocEntry;
transfer.Lines.BaseLine = value.products[i].Line;
transfer.Lines.Quantity = value.products[i].Count;
transfer.Lines.BaseType = SAPbobsCOM.InvBaseDocTypeEnum.InventoryTransferRequest;
transfer.Lines.UserFields.Fields.Item("U_Tarima").Value = value.products[i].Pallet;
for (int j = 0; j < value.products[i].batch.Count; j++)
{
//transfer.Lines.BatchNumbers.BaseLineNumber = transfer.Lines.LineNum;
transfer.Lines.BatchNumbers.BatchNumber = value.products[i].batch[j].name;
transfer.Lines.BatchNumbers.Quantity = value.products[i].batch[j].quantity;
transfer.Lines.BatchNumbers.Add();
}
transfer.Lines.Add();
}
int result = transfer.Add();
if (result == 0)
{
if (request.Lines.FromWarehouseCode == request.FromWarehouse)
{
request.GetByKey(value.order);
//for (int i = 0; i < request.Lines.Count; i++)
//{
// request.Lines.SetCurrentLine(i);
// if (request.Lines.RemainingOpenQuantity != 0) {
// request.Lines.Quantity = request.Lines.Quantity - request.Lines.RemainingOpenQuantity;
// }
//}
//int result3 = request.Update();
//if (result3 != 0)
//{
// string error = context.oCompany.GetLastErrorDescription();
// return BadRequest(new {id = 1, error });
//}
try
{
SAPbobsCOM.StockTransfer newRequest = (SAPbobsCOM.StockTransfer)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryTransferRequest);
newRequest.FromWarehouse = request.FromWarehouse;
newRequest.ToWarehouse = request.ToWarehouse;
newRequest.Series = request.Series;
newRequest.UserFields.Fields.Item("U_SO1_02NUMRECEPCION").Value = request.DocNum.ToString();
for (int i = 0; i < value.products.Count; i++)
{
//request.Lines.SetCurrentLine(value.products[i].Line);
newRequest.Lines.ItemCode = value.products[i].ItemCode;
//newRequest.Lines.UoMEntry = request.Lines.UoMEntry;
newRequest.Lines.UoMEntry = value.products[i].UoMEntry;
//newRequest.Lines.UseBaseUnits = request.Lines.UseBaseUnits;
newRequest.Lines.UseBaseUnits = value.products[i].UseBaseUnits;
newRequest.Lines.Quantity = value.products[i].Count;
newRequest.Lines.FromWarehouseCode = request.Lines.WarehouseCode;
newRequest.Lines.WarehouseCode = request.ToWarehouse;
newRequest.Lines.Add();
}
int result2 = newRequest.Add();
if (result2 != 0)
{
string error = context.oCompany.GetLastErrorDescription();
Console.WriteLine(2);
Console.WriteLine(error);
Console.WriteLine(value);
Console.WriteLine(context.XMLTOJSON(newRequest.GetAsXML()));
return(BadRequest(new { id = 2, error, value, va = context.XMLTOJSON(newRequest.GetAsXML()) }));
}
return(Ok(context.oCompany.GetNewObjectKey()));
}
catch (Exception ex)
{
Console.WriteLine(6);
Console.WriteLine(ex);
Console.WriteLine(value);
return(BadRequest(new { id = 5, ex.Message, value }));
}
}
}
else
{
string error = context.oCompany.GetLastErrorDescription();
return(BadRequest(new { id = 3, error }));
}
return(Ok(new { value }));
}
return(BadRequest(new { error = "No Existe Documento" }));
}
public async Task <IActionResult> GetDetail(int id, string doctype)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
if (!doctype.Equals("DocEntry") && !doctype.Equals("DocNum"))
{
return(BadRequest(new { error = "Doc Type to Search Invalid" }));
}
oRecSet.DoQuery(@"
SELECT
""DocEntry"",
""DocNum"",
""CANCELED"",
""DocStatus"",
""DocDate"",
""Filler"",
""ToWhsCode""
From OWTQ WHERE """ + doctype + @""" = " + id);
if (oRecSet.RecordCount == 0)
{
return(NotFound("No Existe Documento"));
}
JToken transfer = JToken.Parse("{}");
transfer["OWTQ"] = context.XMLTOJSON(oRecSet.GetAsXML())["OWTQ"][0];
int docentry = transfer["OWTQ"]["DocEntry"].ToObject <int>();
int docnum = transfer["OWTQ"]["DocNum"].ToObject <int>();
//oRecSet.DoQuery(@"
// SELECT
// *
// From IBT1 Where ""WhsCode"" = 'S01' AND ""ItemCode"" = 'A0305243'");
//transfer["IBT1"] = context.XMLTOJSON(oRecSet.GetAsXML())["IBT1"];
// Inventory Transfer Request Rows
//oRecSet.DoQuery(@"
// SELECT
// ""DocEntry"",
// ""LineNum"",
// ""LineStatus"",
// ""ItemCode"",
// ""Dscription"",
// ""Quantity"",
// ""OpenQty"",
// ""WhsCode"",
// ""UomCode""
// FROM WTQ1 WHERE ""DocEntry"" = " + docentry);
oRecSet.DoQuery(@"
SELECT
""LineNum"",
""LineStatus"",
""ItemCode"",
""Dscription"",
""Quantity"",
""UomCode""
FROM WTQ1 WHERE ""DocEntry"" = " + docentry);
transfer["WTQ1"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTQ1"];
// Inventory Transfer
oRecSet.DoQuery(@"
SELECT
""DocEntry"",
""DocNum"",
""DocDate"",
""DocDueDate"",
""ToWhsCode"",
""Filler""
FROM OWTR
WHERE ""DocEntry"" in (SELECT ""DocEntry"" FROM WTR1 WHERE ""BaseEntry"" = " + docentry + ")");
if (oRecSet.RecordCount != 0)
{
transfer["Transfers"] = context.XMLTOJSON(oRecSet.GetAsXML())["OWTR"];
oRecSet.DoQuery(@"
SELECT
""DocEntry"",
""ItemCode"",
""Dscription"",
""Quantity"",
""UomCode""
FROM WTR1
WHERE ""BaseEntry"" = " + docentry);
transfer["TransfersDetail"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTR1"];
string docEntrys = "";
foreach (JToken transfers in transfer["Transfers"])
{
docEntrys += transfers["DocEntry"] + ",";
}
docEntrys = docEntrys.Substring(0, docEntrys.Length - 1);
oRecSet.DoQuery(@"
SELECT
""ItemCode"",
""BatchNum"",
""LineNum"",
""BaseEntry"",
""Quantity""
From IBT1 WHERE ""BaseEntry"" in (" + docEntrys + @") AND ""WhsCode"" = '" + transfer["OWTQ"]["Filler"].ToString() + "'");
transfer["Codes"] = context.XMLTOJSON(oRecSet.GetAsXML())["IBT1"];
//foreach (JToken transferDetail in transfer["TransfersDetail"])
//{
// oRecSet.DoQuery(@"SELECT * From IBT1 WHERE ""ItemCode"" = '" + transferDetail["ItemCode"] + @"' AND ""BaseEntry"" = " + transferDetail["DocEntry"] + @" AND ""WhsCode"" = '" + transferDetail["FromWhsCod"] + "'");
// transferDetail["Codes"] = context.XMLTOJSON(oRecSet.GetAsXML())["IBT1"];
//}
oRecSet.DoQuery(@"SELECT ""DocNum"", ""DocEntry"", ""DocDate"" From OWTQ WHERE ""U_SO1_02NUMRECEPCION"" = " + docnum);
if (oRecSet.RecordCount != 0)
{
transfer["Requests"] = context.XMLTOJSON(oRecSet.GetAsXML())["OWTQ"];
oRecSet.DoQuery(@"
SELECT
""DocEntry"",
""ItemCode"",
""Quantity"",
""UomCode""
FROM WTQ1 WHERE ""DocEntry"" in (SELECT ""DocEntry"" From OWTQ WHERE ""U_SO1_02NUMRECEPCION"" = " + docnum + ")");
transfer["RequestsDetail"] = context.XMLTOJSON(oRecSet.GetAsXML())["WTQ1"];
}
}
return(Ok(transfer));
}
public async Task <IActionResult> GetDetail(int DocEntry)
{
SAPContext context = HttpContext.RequestServices.GetService(typeof(SAPContext)) as SAPContext;
SAPbobsCOM.Recordset oRecSet = (SAPbobsCOM.Recordset)context.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecSet.DoQuery($@"
SELECT
ord.""DocEntry"",
ord.""DocNum"",
to_char(to_date(SUBSTRING(ord.""DocDueDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDueDate"",
to_char(to_date(SUBSTRING(ord.""DocDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""DocDate"",
to_char(to_date(SUBSTRING(ord.""CancelDate"", 0, 10), 'YYYY-MM-DD'), 'DD-MM-YYYY') as ""CancelDate"",
(case when ord.""CANCELED"" = 'Y' then 'Cancelado'
when ord.""DocStatus"" = 'O' then 'Abierto'
when ord.""DocStatus"" = 'C' then 'Cerrado'
else ord.""DocStatus"" end) AS ""DocStatus"",
ord.""DocTime"",
ord.""Address"",
ord.""Address2"",
ord.""DocCur"",
ord.""Comments"",
ord.""DocTotal"",
ord.""DocTotalFC"",
ord.""DocRate"",
payment.""PymntGroup"",
contact.""CardName"",
contact.""CardCode"",
contact.""CardFName"",
contact.""ListNum"",
employee.""SlpCode"",
employee.""SlpName"",
warehouse.""WhsCode"",
warehouse.""WhsName""
FROM ODLN ord
LEFT JOIN NNM1 series ON series.""Series"" = ord.""Series""
LEFT JOIN OWHS warehouse ON warehouse.""WhsCode"" = series.""SeriesName""
LEFT JOIN OSLP employee ON employee.""SlpCode"" = ord.""SlpCode""
LEFT JOIN OCTG payment ON payment.""GroupNum"" = ord.""GroupNum""
LEFT JOIN OCRD contact ON contact.""CardCode"" = ord.""CardCode""
WHERE ord.""DocEntry"" = '{DocEntry}';");
if (oRecSet.RecordCount == 0)
{
return(NoContent());
}
JToken temp = context.XMLTOJSON(oRecSet.GetAsXML())["ODLN"][0];
oRecSet.DoQuery($@"
Select
""LineNum"",
""ItemCode"",
""Dscription"",
""Price"",
""Currency"",
""Quantity"",
""UomCode"",
""InvQty"",
""OpenQty"",
""UomEntry"",
""UomCode2"",
""LineTotal"",
""U_CjsPsVr"",
""TotalFrgn"",
""Rate""
From DLN1 Where ""DocEntry"" = '{DocEntry}';");
temp["DLN1"] = context.XMLTOJSON(oRecSet.GetAsXML())["DLN1"];
return(Ok(temp));
}
