private void BuscarDatos()
{
try
{
string sql = "";
if (EditText1.Value.Trim().Length > 0)
{
sql = @" exec [SBO_COMERCIAL].[dbo].[SpEmpleadosTodosBuscar] '" + EditText1.Value.Trim() + "'";
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = EditText1.Value.Trim();
}
else
{
sql = @" exec [SBO_COMERCIAL].[dbo].[SpEmpleadosTodosBuscar] ''";
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = "°#TODOS LOS REGISTROS#°";
}
}
catch (Exception)
{
}
}
private void Button0_ClickAfter(object sboObject, SAPbouiCOM.SBOItemEventArg pVal)
{
try
{
string sql = "";
if (EditText0.Value.Trim().Length > 0)
{
oUDS = oForm.DataSources.UserDataSources.Item("UD_1");
if (CheckBox0.Checked)
{
sql = @"select Project as 'Proyecto', DocNum as 'Numero Pedido', CardName as 'Cliente Final', CreateDate as 'Fecha Creacion'
from ORDR
where Series = 27 and ( DocNum = " + EditText0.Value.Trim() + " or Project = '" + EditText0.Value.Trim() + @"')
order by Project, CreateDate";
}
else
{
sql = @"select Project as 'Proyecto', DocNum as 'Numero Pedido', CardName as 'Cliente Final', CreateDate as 'Fecha Creacion'
from ORDR
where Series = 27 and SlpCode = " + oUDS.ValueEx + " and ( DocNum = " + EditText0.Value.Trim() + " or Project = '" + EditText0.Value.Trim() + @"')
order by Project, CreateDate";
}
DT_SQL.ExecuteQuery(sql);
DT_PCV.CopyFrom(DT_SQL);
Grid0.Columns.Item(2).Width = 200;
Ultima_Busqueda = EditText0.Value.Trim();
}
else
{
oUDS = oForm.DataSources.UserDataSources.Item("UD_1");
if (CheckBox0.Checked)
{
sql = @"select Project as 'Proyecto', DocNum as 'Numero Pedido', CardName as 'Cliente Final', CreateDate as 'Fecha Creacion'
from ORDR
where Series = 27
order by Project, CreateDate";
}
else
{
sql = @"select Project as 'Proyecto', DocNum as 'Numero Pedido', CardName as 'Cliente Final', CreateDate as 'Fecha Creacion'
from ORDR
where Series = 27 and SlpCode = " + oUDS.ValueEx + @"
order by Project, CreateDate";
}
DT_SQL.ExecuteQuery(sql);
DT_PCV.CopyFrom(DT_SQL);
Grid0.Columns.Item(2).Width = 200;
Ultima_Busqueda = "°#TODOS LOS REGISTROS#°";
}
}
catch (Exception)
{
}
}
private void BuscarDatos()
{
try
{
string sql = "";
if (EditText0.Value.Trim().Length > 0)
{
sql = @" SELECT PrjCode as Codigo ,PrjName as Nombre FROM [" + Program.sBDComercial.Trim() + @"].DBO.OPRJ
where PrjCode in (select cast(U_PrjCode as nvarchar) from [" + Program.sBDComercial.Trim() + @"].DBO.[@ZINFOP] )
and (PrjName like '%" + EditText0.Value.Trim() + "%' or PrjCode like '%" + EditText0.Value.Trim() + "%')";
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = EditText0.Value.Trim();
}
else
{
sql = @" SELECT PrjCode as Codigo ,PrjName as Nombre FROM [" + Program.sBDComercial.Trim() + @"].DBO.OPRJ
where PrjCode in (select cast(U_PrjCode as nvarchar) from [" + Program.sBDComercial.Trim() + @"].DBO.[@ZINFOP] )";
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = "°#TODOS LOS REGISTROS#°";
}
}
catch (Exception)
{
}
}
private void BuscarDatos()
{
try
{
string sql = "";
string Condi = CheckBox0.Checked ? "Y" : "N";
if (EditText0.Value.Trim().Length > 0)
{
sql = @"SELECT
ORCT.DocNum AS 'Numero Pago'
,ORCT.TaxDate as 'Fecha PR'
--,ORCT.CardName as 'Cliente'
,ORCT.Comments as 'Info Detallada'
,IIF(ORCT.NoDocSum > 0, ORCT.NoDocSum, ORCT.DocTotal) as 'Total del Documento'
,ORCT.JrnlMemo as 'Comentarios'
,LEFT(ORCT.Ref1,2)+'-'+SUBSTRING(ORCT.Ref1,3,2)+'-'+RIGHT(ORCT.Ref1,4) as 'Referencia 1'
,ORCT.CreateDate as 'Fecha Creacion'
FROM
[" + Program.sBDComercial.Trim() + @"].DBO.ORCT
WHERE
ORCT.Canceled = '" + Condi + @"'
and (CAST(DocNum as nvarchar(20)) like '%" + EditText0.Value.Trim() + @"%'
or CAST(Comments as nvarchar(MAX)) like '%" + EditText0.Value.Trim() + @"%'
or CAST(JrnlMemo as nvarchar(MAX)) like '%" + EditText0.Value.Trim() + @"%')
ORDER BY
1" ;
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = EditText0.Value.Trim();
}
else
{
sql = @"SELECT
ORCT.DocNum AS 'Numero Pago'
,ORCT.TaxDate as 'Fecha PR'
--,ORCT.CardName as 'Cliente'
,ORCT.Comments as 'Info Detallada'
,IIF(ORCT.NoDocSum > 0, ORCT.NoDocSum, ORCT.DocTotal) as 'Total del Documento'
,ORCT.JrnlMemo as 'Comentarios'
,LEFT(ORCT.Ref1,2)+'-'+SUBSTRING(ORCT.Ref1,3,2)+'-'+RIGHT(ORCT.Ref1,4) as 'Referencia 1'
,ORCT.CreateDate as 'Fecha Creacion'
FROM
[" + Program.sBDComercial.Trim() + @"].DBO.ORCT
WHERE
ORCT.Canceled = '" + Condi + @"'
ORDER BY
1" ;
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = "°#TODOS LOS REGISTROS#°";
}
}
catch (Exception)
{
}
}
private void OnCustomInitialize()
{
this.Grid0 = ((SAPbouiCOM.Grid)(this.GetItem("Item_4").Specific));
this.Grid0.ValidateAfter += new SAPbouiCOM._IGridEvents_ValidateAfterEventHandler(this.Grid0_ValidateAfter);
this.Grid0.Item.FromPane = 1;
this.Grid0.Item.ToPane = 1;
var sql = @"select * from SBO_COMERCIAL.dbo.[@ZPARAMFA1]";
DT_SQL.ExecuteQuery(sql);
Grid0.DataTable = DT_SQL;
}
private void Form_ResizeAfter(SAPbouiCOM.SBOItemEventArg pVal)
{
oUDS = oForm.DataSources.UserDataSources.Item("UD_1");
sOrigf = oUDS.ValueEx;
string sql;
switch (sOrigf)
{
case "ComisionesA":
oForm.Title = "Agregar Vendedor a Comisiones";
sql = @"SELECT
SlpCode as 'Codigo'
,Memo as 'Inicial'
,SlpName as 'Nombre'
FROM
OSLP
WHERE
Active = 'Y'
AND SlpCode != -1
AND SlpCode NOT IN ( SELECT DISTINCT V.SlpCode
FROM OSLP V join [@Z_COMI_VEND] P ON V.SlpCode = P.[Id Vend]
WHERE V.Active = 'Y'
AND V.SlpCode != -1
AND Periodo = '" + NPeriodo.NombrePeriodo + @"')
ORDER BY
1" ;
DT_SQL.ExecuteQuery(sql);
break;
case "ComisionesR":
oForm.Title = "Agregar Vendedor a Comisiones";
sql = @"SELECT
SlpCode as 'Codigo'
,Memo as 'Inicial'
,SlpName as 'Nombre'
FROM
OSLP
WHERE
Active = 'Y'
AND SlpCode != -1
AND SlpCode NOT IN ( SELECT DISTINCT V.SlpCode
FROM OSLP V join ORDR P ON V.SlpCode = P.SlpCode
WHERE V.Active = 'Y' AND V.SlpCode != -1 AND series = 27
AND P.CreateDate BETWEEN '" + NPeriodo.FechaInicio.ToString("MM-dd-yyyy") +
@"' AND '" + NPeriodo.FechaFin.ToString("MM-dd-yyyy") + @"')
ORDER BY
1" ;
DT_SQL.ExecuteQuery(sql);
break;
}
}
private void Form_ResizeAfter(SAPbouiCOM.SBOItemEventArg pVal)
{
try
{
oUDS = oForm.DataSources.UserDataSources.Item("UD_1");
string sql = @"select Project as 'Proyecto', DocNum as 'Numero Pedido', CardName as 'Cliente Final', CreateDate as 'Fecha Creacion'
from ORDR
where Series = 27 and SlpCode = " + oUDS.ValueEx + @"
order by Project, CreateDate";
DT_SQL.ExecuteQuery(sql);
DT_PCV.CopyFrom(DT_SQL);
Grid0.Columns.Item(2).Width = 200;
oUDS = oForm.DataSources.UserDataSources.Item("UD_2");
StaticText1.Caption = "VENDEDOR: " + oUDS.ValueEx;
}
catch (Exception)
{
}
}
private void OnCustomInitialize()
{
try
{
oForm = Application.SBO_Application.Forms.Item(this.UIAPIRawForm.UniqueID);
string sql = @"SELECT
SlpCode as 'Codigo'
,Memo as 'Inicial'
,SlpName as 'Nombre'
FROM
OSLP
WHERE
Active = 'Y'
AND SlpCode != -1
ORDER BY
1" ;
DT_SQL.ExecuteQuery(sql);
oForm.ReportType = "Modal";
}
catch (Exception) { }
}
private void Importar_Anexos_Desde_Origen()
{
try
{
oForm.Freeze(true);
SAPbouiCOM.DBDataSource source = oForm.DataSources.DBDataSources.Item("OPCH");
string sDocEntry = source.GetValue("DocEntry", source.Offset);
int iAtcEntry = int.TryParse(source.GetValue("AtcEntry", source.Offset), out iAtcEntry) ? iAtcEntry : 0;
//SAPbobsCOM.Documents oPurchaseOrders = (SAPbobsCOM.Documents)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders);
//oPurchaseOrders.GetByKey(Convert.ToInt32(sDocEntry));
string sql = "select distinct BaseRef,BaseType,BaseEntry from PCH1 where docentry = " + sDocEntry;
DT_SQL.ExecuteQuery(sql);
if (!DT_SQL.IsEmpty)
{
for (int i = 0; i < DT_SQL.Rows.Count; i++)
{
SAPbobsCOM.Recordset oRecordset = (SAPbobsCOM.Recordset)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
SAPbouiCOM.Matrix oMatrix = null;
SAPbouiCOM.EditText oEdit = null;
string sBaseType = DT_SQL.GetValue("BaseType", i).ToString();
string sBaseRef = DT_SQL.GetValue("BaseRef", i).ToString();
string sBaseEntry = DT_SQL.GetValue("BaseEntry", i).ToString();
string sTable = "";
switch (sBaseType)
{
case "20": //Orden Compra
sql = "select distinct BaseRef,BaseType,BaseEntry from PDN1 where docentry = " + sBaseEntry;
DT_SQL3.ExecuteQuery(sql);
if (!DT_SQL3.IsEmpty)
{
sBaseType = DT_SQL3.GetValue("BaseType", 0).ToString();
if (sBaseType == "22")
{
sTable = "OPOR";
sBaseRef = DT_SQL3.GetValue("BaseRef", 0).ToString();
}
}
break;
case "22": //Orden Compra
sTable = "OPOR";
break;
case "1470000113": //Solicitud de Compra
sTable = "OPRQ";
break;
}
if (sTable.Trim().Length > 0)
{
sql = "SELECT AtcEntry FROM " + sTable + " WHERE AtcEntry is not null AND DocNum = " + sBaseRef;
DT_SQL2.ExecuteQuery(sql);
if (!DT_SQL2.IsEmpty)
{
SAPbobsCOM.Attachments2 oAtt = (SAPbobsCOM.Attachments2)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oAttachments2);
oAtt.GetByKey(Convert.ToInt32(DT_SQL2.GetValue("AtcEntry", 0)));
//SAPbobsCOM.Attachments2 oAttN = (SAPbobsCOM.Attachments2)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oAttachments2);
//if (iAtcEntry > 0)
//{
// oAttN.GetByKey(iAtcEntry);
//}
oMatrix = (SAPbouiCOM.Matrix)oForm.Items.Item("1320002138").Specific;
int rowNum = oMatrix.RowCount + 1;
sql = @"select AttachPath from OADP";
oRecordset.DoQuery(sql);
string RutaDestino = oRecordset.Fields.Item(0).Value.ToString();
for (int x = 0; x < oAtt.Lines.Count; x++)
{
sql = @"SELECT RTRIM(CAST(trgtPath as nvarchar(200)))+'\'+FileName+'.'+FileExt from ATC1 where AbsEntry = " + DT_SQL2.GetValue("AtcEntry", 0) + " AND Line = " + (x + 1).ToString();
oRecordset.DoQuery(sql);
string RutaServ = oRecordset.Fields.Item(0).Value.ToString();
oMatrix.AddRow();
oEdit = (SAPbouiCOM.EditText)oMatrix.Columns.Item("1320000002").Cells.Item(rowNum).Specific;
oEdit.Active = true;
oEdit.Item.Enabled = true;
oEdit.String = RutaDestino;
oEdit = (SAPbouiCOM.EditText)oMatrix.Columns.Item("1320000003").Cells.Item(rowNum).Specific;
oEdit.Active = true;
oEdit.Item.Enabled = true;
oEdit.String = System.IO.Path.GetDirectoryName(RutaServ);
oEdit = (SAPbouiCOM.EditText)oMatrix.Columns.Item("1320000004").Cells.Item(rowNum).Specific;
oEdit.Active = true;
oEdit.Item.Enabled = true;
oEdit.String = System.IO.Path.GetFileNameWithoutExtension(RutaServ);
oEdit = (SAPbouiCOM.EditText)oMatrix.Columns.Item("1320000005").Cells.Item(rowNum).Specific;
oEdit.Active = true;
oEdit.Item.Enabled = true;
oEdit.String = System.IO.Path.GetExtension(RutaServ).Substring(1);
oEdit = (SAPbouiCOM.EditText)oMatrix.Columns.Item("1320000007").Cells.Item(rowNum).Specific;
oEdit.Active = true;
oEdit.Item.Enabled = true;
oEdit.String = DateTime.Now.ToShortDateString();
oEdit = (SAPbouiCOM.EditText)oMatrix.Columns.Item("1320000001").Cells.Item(rowNum).Specific;
oEdit.Active = true;
oEdit.Item.Enabled = true;
oEdit.String = rowNum.ToString();
//Se vuelve a cargar la primera fila para que no avance el scroll horizontal al final.
oEdit = (SAPbouiCOM.EditText)oMatrix.Columns.Item("1320000002").Cells.Item(rowNum).Specific;
oEdit.Active = true;
oEdit.Item.Enabled = true;
oEdit.String = RutaDestino;
rowNum += 1;
//oAtt.Lines.SetCurrentLine(x);
//oAttN.Lines.Add();
//oAttN.Lines.FileName = System.IO.Path.GetFileNameWithoutExtension(RutaServ);
//oAttN.Lines.FileExtension = System.IO.Path.GetExtension(RutaServ).Substring(1);
//oAttN.Lines.SourcePath = System.IO.Path.GetDirectoryName(RutaServ);
//oAttN.Lines.Override = SAPbobsCOM.BoYesNoEnum.tYES;
//if (iAtcEntry > 0)
//{
// if (oAttN.Update() != 0)
// throw new Exception(oCompany.GetLastErrorDescription());
//}
//else
//{
// int iAttEntry = -1;
// if (oAttN.Add() == 0)
// {
// iAttEntry = int.Parse(oCompany.GetNewObjectKey());
// //Assign the attachment to the GR object (GR is my SAPbobsCOM.Documents object)
// oPurchaseOrders.AttachmentEntry = iAttEntry;
// }
//}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRecordset);
}
}
}
}
//System.Runtime.InteropServices.Marshal.ReleaseComObject(oPurchaseOrders);
//oPurchaseOrders = null;
GC.Collect();
}
catch (Exception)
{
}
finally
{
oForm.Freeze(false);
}
}
private void Form_DataAddAfter(ref SAPbouiCOM.BusinessObjectInfo pVal)
{
SAPbouiCOM.DBDataSource oDBDataSource = oForm.DataSources.DBDataSources.Item("OIGE");
SAPbouiCOM.DBDataSource oDBDataSourceLines = oForm.DataSources.DBDataSources.Item("IGE1");
oEdit = (SAPbouiCOM.EditText)oForm.Items.Item("7").Specific;
int sDocNum = Convert.ToInt32(oDBDataSource.GetValue("DocNum", oDBDataSource.Offset));
int sDocEntry = Convert.ToInt32(oDBDataSource.GetValue("DocEntry", oDBDataSource.Offset));
string sql = "";
if (((SAPbouiCOM.EditText)oForm.Items.Item("Item_0").Specific).Value.Trim() != "")
{
try
{
int nDocOrd = Convert.ToInt32(oDBDataSource.GetValue("U_Solicitud_Compra", oDBDataSource.Offset).ToString().Substring(4, 9)); //;Convert.ToInt32(DT_SQL.GetValue("U_Solicitud_Compra", 0).ToString().Substring(4,9));
sql = @"UPDATE D SET BaseLine = ImportLog , BaseEntry = S.DocEntry
from OIGE S JOIN IGE1 D ON S.DocEntry = D.DocEntry
where docnum = " + sDocNum;
DT_SQL.ExecuteQuery(sql);
sql = @"UPDATE D SET OpenQty = IIF(SAL.Entregado IS NULL, D.Quantity ,(D.Quantity - SAL.Entregado)),
OpenCreQty = IIF(SAL.Entregado IS NULL, D.Quantity ,(D.Quantity - SAL.Entregado)),
LineStatus = IIF((D.Quantity - SAL.Entregado) = 0,'C','O')
FROM OPRQ P JOIN PRQ1 D ON P.DocEntry = D.DocEntry
LEFT JOIN (select BaseRef,BaseLine,sum(D.Quantity) as Entregado
from OIGE S JOIN IGE1 D ON S.DocEntry = D.DocEntry
where BaseRef is not null
GROUP BY BaseRef,BaseLine) SAL ON P.DocNum = SAL.BaseRef AND D.LineNum = SAL.BaseLine
WHERE P.DocNum = " + nDocOrd;
DT_SQL.ExecuteQuery(sql);
sql = @"UPDATE T0 SET DocStatus = IIF(Cerradas = Lineas,'C','O')
FROM OPRQ T0
JOIN (select P.DocEntry,SUM(IIF(LineStatus = 'C',1,0)) as Cerradas,COUNT(D.LineNum) as Lineas
FROM OPRQ P JOIN PRQ1 D ON P.DocEntry = D.DocEntry
WHERE P.DocNum = " + nDocOrd + @"
GROUP BY P.DocEntry) T1 ON T0.DocEntry = T1.DocEntry
WHERE docnum = " + nDocOrd;
DT_SQL.ExecuteQuery(sql);
//SAPbobsCOM.Documents oInventoryGenExit;
//SAPbobsCOM.Document_Lines oInventoryGenExitLines;
//Get InventoryGenExit Object
//oInventoryGenExit = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryGenExit);
//if (oInventoryGenExit.GetByKey(sDocEntry))
//{
// oInventoryGenExitLines = oInventoryGenExit.Lines;
// for (int i = 0; i < oInventoryGenExitLines.Count; i++)
// {
// oInventoryGenExitLines.SetCurrentLine(i);
// oInventoryGenExitLines.BaseLine = Convert.ToInt32(oDBDataSourceLines.GetValue("ImportLog", i));
// oInventoryGenExitLines.BaseEntry = Convert.ToInt32(oDBDataSourceLines.GetValue("DocEntry", i));
// }
// if (oInventoryGenExit.Update() != 0)
// {
// int a = oCompany.GetLastErrorCode();
// string b = oCompany.GetLastErrorDescription();
// }
//}
//Documents oPurchaseRequest = (Documents)oCompany.GetBusinessObject(BoObjectTypes.oPurchaseRequest);
//int nCantArtClose = 0;
//if (oPurchaseRequest.GetByKey(nDocOrd))
//{
// for (int i = 0; i < oPurchaseRequest.Lines.Count; i++)
// {
// oPurchaseRequest.Lines.SetCurrentLine(i);
// if (oPurchaseRequest.Lines.RemainingOpenQuantity == 0)
// {
// oPurchaseRequest.Lines.LineStatus = SAPbobsCOM.BoStatus.bost_Close;
// nCantArtClose += 1;
// }
// }
// if (nCantArtClose == oPurchaseRequest.Lines.Count)
// oPurchaseRequest.Close();
// int a = oPurchaseRequest.Update();
//}
}
catch (Exception)
{
}
}
}