private void BuscarDatos()
{
try
{
string sql = "";
if (EditText0.Value.Trim().Length > 0)
{
sql = @" exec [SBO_COMERCIAL].[dbo].[SpEmpleadosTodosBuscar] '" + EditText0.Value.Trim() + "'";
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = EditText0.Value.Trim();
}
else
{
sql = @" exec [SBO_COMERCIAL].[dbo].[SpEmpleadosTodosBuscar] ''";
DT_SQL.ExecuteQuery(sql);
Ultima_Busqueda = "°#TODOS LOS REGISTROS#°";
}
}
catch (Exception)
{
}
}
private static void Cargar_Datos_Matrix()
{
try
{
//oForm = Application.SBO_Application.Forms.ActiveForm;
oDTTable = oForm.DataSources.DataTables.Item("DT_SQL1");
SAPbouiCOM.DBDataSource source = oForm.DataSources.DBDataSources.Item("@ZDFER");
oForm.Freeze(true);
string sql = "SELECT * FROM [@ZDFER] ORDER BY U_Fecha";
oDTTable.ExecuteQuery(sql);
oMatrix = (SAPbouiCOM.Matrix)oForm.Items.Item("3").Specific;
oMatrix.FlushToDataSource();
source.Clear();
oMatrix.Clear();
for (int i = 0; i < oDTTable.Rows.Count; i++)
{
source.InsertRecord(source.Size);
source.Offset = source.Size - 1;
source.SetValue("Code", source.Size - 1, Convert.ToString(oDTTable.GetValue("Code", i)));
//DateTime fecha = (DateTime)oDTTable.GetValue("U_Fecha", i);
source.SetValue("U_Fecha", source.Size - 1, Convert.ToDateTime(oDTTable.GetValue("U_Fecha", i)).ToString("yyyyMMdd"));
source.SetValue("U_Descripcion", source.Size - 1, Convert.ToString(oDTTable.GetValue("U_Descripcion", i)));
source.SetValue("DocEntry", source.Size - 1, Convert.ToString(oDTTable.GetValue("DocEntry", i)));
}
sql = "SELECT MAX(CAST(Code as int)) + 1 as Proximo FROM [@ZDFER]";
oDTTable.ExecuteQuery(sql);
source.InsertRecord(source.Size);
source.Offset = source.Size - 1;
source.SetValue("Code", source.Size - 1, Convert.ToString(oDTTable.GetValue("Proximo", 0)));
source.SetValue("DocEntry", source.Size - 1, Convert.ToString(oDTTable.GetValue("Proximo", 0)));
oMatrix.LoadFromDataSource();
oMatrix.AutoResizeColumns();
}
catch (Exception)
{
}
finally
{
oForm.Freeze(false);
}
}
public SAPbouiCOM.DataTable GenerarEncabezado(SAPbouiCOM.DataTable DT_SQL, SAPbouiCOM.DataTable DT_HEAD)
{
try
{
string FechaPago = DT_SQL.GetValue(0, 0).ToString();
string CantidadPagos = DT_SQL.GetValue(0, 1).ToString();
string MontoPagos = DT_SQL.GetValue(0, 2).ToString();//.Replace(".","");
string[] SeparaTotal = MontoPagos.Split('.');
string MontoEntero = SeparaTotal[0];
string MontoDecimal = SeparaTotal.Count() == 1 ? "00" : MontoPagos.Split('.')[1];
string sql = @"EXEC [dbo].[Min_Bancos_Generar_Encabezado_Archivo_PagoTXT]
@U_FechaPago = N'" + FechaPago + @"',
@U_CantidadPago = N'" + CantidadPagos + @"',
@U_MontoTotal = N'" + MontoEntero + MontoDecimal + "'";
DT_HEAD.ExecuteQuery(sql);
//Hay que establecer la columana de tipo TEXT para que puedan agregarse posteriormente los rgistros de detalle
string RC = DT_HEAD.GetValue(0, 0).ToString().Trim();
DT_HEAD.Clear();
DT_HEAD.Columns.Add("Info", SAPbouiCOM.BoFieldsType.ft_Text);
DT_HEAD.Rows.Add(1);
DT_HEAD.SetValue("Info", 0, RC);
//DT_HEAD.SetValue("Info", 0, "RC00354803021120160000100000000016184000578509689910000968991000 [email protected]");
}
catch (Exception) {}
return(DT_HEAD);
}
private void ItemLoad()
{
//1.¬Ýsample
SubMain.AddChooseFromList(f17, "2", "cfl2", "CardType", "C");
SubMain.AddChooseFromList(f17, "17", "cfl17", "", "");
//define ds
f17.DataSources.UserDataSources.Add("txCardCode", SAPbouiCOM.BoDataType.dt_SHORT_TEXT, 254);
f17.DataSources.UserDataSources.Add("txEntry", SAPbouiCOM.BoDataType.dt_SHORT_TEXT, 254);
f17.DataSources.UserDataSources.Add("txDate", SAPbouiCOM.BoDataType.dt_DATE, 254);
//bind ds
txCardCode.DataBind.SetBound(true, "", "txCardCode");
txEntry.DataBind.SetBound(true, "", "txEntry");
txDate.DataBind.SetBound(true, "", "txDate");
txCardCode.ChooseFromListUID = "cfl2";
txCardCode.ChooseFromListAlias = "CardCode";
txEntry.ChooseFromListUID = "cfl17";
txEntry.ChooseFromListAlias = "DocEntry";
oDTtmp.ExecuteQuery("select WhsCode,WhsName from owhs");
for (int i = 0; i < oDTtmp.Rows.Count; i++)
{
cbWH.ValidValues.Add(oDTtmp.GetValue(0, i).ToString(), oDTtmp.GetValue(1, i).ToString());
}
//
}
public static void IdentificarAccesoUsuario(SAPbouiCOM.DataTable oDataTable)
{
string Acceso = "";
if (!Conexion.oCompany.Connected)
{
Conexion.Conectar_Aplicacion();
}
string sAliasUsuActual = Convert.ToString(Conexion.oCompany.UserName);
string sBDComercial = "SBO_COMERCIAL";
//SAPbouiCOM.DataTable DT_SQL = new SAPbouiCOM.DataTable();
string sPSql = "SELECT * FROM [" + sBDComercial + "].dbo.[OUSR] where USER_Code ='" + sAliasUsuActual + "'";
try
{
oDataTable.ExecuteQuery(sPSql);
Acceso = Convert.ToString(oDataTable.GetValue("U_Hab_Fec_Proy", 0));
Habilitado = Acceso == "Si" ? true : false;
ConsultaUsuario = true;
}
catch
{
// item does not exist
Habilitado = false;
}
}
// query and populate matrix
private void populateMatrix()
{
try
{
String strQuery = String.Format(TWM_KDS_AddOn.Src.Resource.Queries.TWM_GET_PO_MATRIX, _txtVendor.String, _txtDTFRM.Value, _txtDTTO.Value);
_dt_VendorMatrix = _oForm.DataSources.DataTables.Item("dt_VendorfromOPOR");
_dt_VendorMatrix.ExecuteQuery(strQuery);
if (_dt_VendorMatrix.Rows.Count > 0)
{
_mat_Vendor.Columns.Item("col_0").DataBind.Bind("dt_VendorfromOPOR", "Row");
_mat_Vendor.Columns.Item("colVCC").DataBind.Bind("dt_VendorfromOPOR", "CardCode");
_mat_Vendor.Columns.Item("colVCN").DataBind.Bind("dt_VendorfromOPOR", "CardName");
_mat_Vendor.Columns.Item("colCHK").DataBind.Bind("dt_VendorfromOPOR", "Check");
_mat_Vendor.Columns.Item("colSONO").DataBind.Bind("dt_VendorfromOPOR", "DocNum");
_mat_Vendor.Columns.Item("colPONO").DataBind.Bind("dt_VendorfromOPOR", "NumAtCard");
_mat_Vendor.Columns.Item("colDOCET").DataBind.Bind("dt_VendorfromOPOR", "DocEntry");
_mat_Vendor.LoadFromDataSource();
_mat_Vendor.AutoResizeColumns();
}
}
catch (Exception ex) { eCommon.SBO_Application.StatusBar.SetText(ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error); }
}
private void populateGrid()
{
try
{
_grid_Inv = _oForm.Items.Item("grid_Inv").Specific as SAPbouiCOM.Grid;
String strQuery = String.Format(TWM_KDS_AddOn.Src.Resource.Queries.TWM_GET_INV, _oForm.DataSources.UserDataSources.Item("txtDocEn").ValueEx.ToString());
_dt_grid_Inv.ExecuteQuery(strQuery);
// if no results from query delete first row which is empty
if (_grid_Inv.DataTable.GetValue("Doc No.", 0).ToString().Length < 1)
{
_grid_Inv.DataTable.Rows.Remove(0);
}
_grid_Inv.Columns.Item("DocEntry").Visible = false;
_grid_Inv.Columns.Item("LineNum").Visible = false;
_grid_Inv.Columns.Item("Doc No.").Editable = false;
_grid_Inv.Columns.Item("Item Code").Editable = false;
_grid_Inv.Columns.Item("BP Catalog No.").Editable = false;
_grid_Inv.Columns.Item("Description").Editable = false;
_grid_Inv.Columns.Item("Cust.PO No.").Editable = false;
_grid_Inv.Columns.Item("Part No.").Editable = false;
_grid_Inv.AutoResizeColumns();
// link button for itemNo
SAPbouiCOM.EditTextColumn oEditColItemNo;
oEditColItemNo = ((SAPbouiCOM.EditTextColumn)(_grid_Inv.Columns.Item("Item Code")));
oEditColItemNo.LinkedObjectType = "4";
}
catch (Exception Ex) { eCommon.SBO_Application.StatusBar.SetText(Ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error); }
}
public static void Cargar_ComboBox(SAPbouiCOM.ComboBox oComboBox, SAPbouiCOM.DataTable oDataTable, string Query, int ValorValue, int ValorDescription, bool ValorVacio)
{
try
{
//oForm = Application.SBO_Application.Forms.Item(FormId) ;
//oComboBox = (SAPbouiCOM.ComboBox) oForm.Items.Item(ComboID).Specific;
//oDTTable = oForm.DataSources.DataTables.Item(DataTableID) ;
oDataTable.ExecuteQuery(Query);
if (ValorVacio) // Agrega primera linea de ComboBox Vacia
{
oComboBox.ValidValues.Add("", "");
}
for (int i = 0; i <= oDataTable.Rows.Count - 1; i++)
{
oComboBox.ValidValues.Add(Convert.ToString(oDataTable.GetValue(ValorValue, i)), Convert.ToString(oDataTable.GetValue(ValorDescription, i)));
}
}
catch (Exception)
{
throw;
}
}
private void populateRBillingMatrix(string orderBy = "", string where = "")
{
string sql = "";
try
{
if (oRBillingForm == null)
{
return;
}
SAPbouiCOM.Matrix oMatrix = (SAPbouiCOM.Matrix)oRBillingForm.Items.Item(matrixRBilling).Specific;
oMatrix.Clear();
//oMatrix.AutoResizeColumns();
SAPbouiCOM.DataTable oDts = getRBillingMatrixDataTable();
sql = string.Format("select \"DocEntry\" as jobID, \r\n" +
"(select \"DocEntry\" from ORDR where CAST(\"DocEntry\" AS VARCHAR) = \"U_OrderID\") as OrderID, \r\n" +
"\"U_Description\", \r\n" +
"\"U_Result\", \r\n" +
"\"U_CustomerID\", \r\n" +
"\"U_Frequency\", \r\n" +
"\"U_StartDate\", \r\n" +
"\"U_EndDate\", \r\n" +
"\"U_NextRunDate\", \r\n" +
"\"U_LastRunDate\", \r\n" +
"\"U_CancelledDate\", \r\n" +
"\"U_Amount\", \r\n" +
"(select \"CardName\" from OCRD where \"CardCode\" = \"U_CustomerID\") as CustomerName, \r\n" +
"(select \"DocEntry\" from OINV where \"CardCode\" = \"U_CustomerID\" and CAST(\"DocNum\" as VARCHAR) = \"U_InvoiceID\") as InvDocNum \r\n" +
"from \"@CCJOB\" \r\n" +
"where (\"U_Cancelled\"<>'Y' OR \"U_Cancelled\" is NULL) {0} order By {1} \"U_NextRunDate\" ", where, orderBy);
//trace(sql);
oDts.ExecuteQuery(sql);
BindMatrix(oMatrix, "jobID", "jobID", dtRBilling);
BindMatrix(oMatrix, "CustName", "CustomerName", dtRBilling);
BindMatrix(oMatrix, "InvID", "InvDocNum", dtRBilling);
BindMatrix(oMatrix, "CustomerID", "U_CustomerID", dtRBilling);
BindMatrix(oMatrix, "Desc", "U_Description", dtRBilling);
BindMatrix(oMatrix, "Frequency", "U_Frequency", dtRBilling);
BindMatrix(oMatrix, "StartDate", "U_StartDate", dtRBilling);
BindMatrix(oMatrix, "EndDate", "U_EndDate", dtRBilling);
BindMatrix(oMatrix, "NextRun", "U_NextRunDate", dtRBilling);
BindMatrix(oMatrix, "LastRun", "U_LastRunDate", dtRBilling);
BindMatrix(oMatrix, "Result", "U_Result", dtRBilling);
//BindMatrix(oMatrix, "CancelDate", "CancelledDate", dtRBilling);
BindMatrix(oMatrix, "OrderID", "OrderID", dtRBilling);
BindMatrix(oMatrix, "Amount", "U_Amount", dtRBilling);
oMatrix.LoadFromDataSource();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
public SAPbouiCOM.DataTable BuscarDatosAutorizacion(SAPbouiCOM.DataTable DT_SQL, string ObjType, string sDocNum)
{
try
{
string sql = "SELECT * FROM [@ZAUTORI] WHERE U_ObjType = '" + ObjType + "' AND U_DocNum = ISNULL(" + sDocNum + ",0) ORDER BY DocEntry";
DT_SQL.ExecuteQuery(sql);
}
catch (Exception) {}
return(DT_SQL);
}
private void Button0_PressedAfter(object sboObject, SAPbouiCOM.SBOItemEventArg pVal)
{
//Cargar_Datos_Matrix();
oDTTable = oForm.DataSources.DataTables.Item("DT_SQL1");
string sql = "DELETE FROM [@ZDFER] WHERE U_Fecha is null";
oDTTable.ExecuteQuery(sql);
Cargar_Datos_Matrix();
}
public SAPbouiCOM.DataTable ConsultarFacturas(SAPbouiCOM.DataTable DT_SQL, string sPago, string TipoTabla)
{
try
{
string sql = "";
switch (TipoTabla)
{
case "D":
sql = @"select
DocEntry as 'Numero Interno'
,DocNum as 'Numero Documento'
,CardName as 'Proveedor'
,CreateDate as 'Fecha Creacion'
,REPLACE(PARSENAME(CONVERT(VARCHAR,CAST(DocTotal AS MONEY),1),2),',','.') as 'Total'
from
OPCH
where
CANCELED = 'N'
AND DocEntry IN (SELECT
T1.DocEntry
FROM
OVPM T0 JOIN VPM2 T1 ON T0.DocEntry = T1.DocNum
WHERE
T1.InvType = 18 and T1.ObjType = 46 and T0.DocEntry = " + sPago + ")";
break;
case "B":
sql = @"select
DocEntry as 'Numero Interno'
,DocNum as 'Numero Documento'
,CardName as 'Proveedor'
,CreateDate as 'Fecha Creacion'
,REPLACE(PARSENAME(CONVERT(VARCHAR,CAST(DocTotal AS MONEY),1),2),',','.') as 'Total'
from
OPCH
where
CANCELED = 'N'
AND DocEntry IN (SELECT
T1.DocEntry
FROM
OPDF T0 JOIN PDF2 T1 ON T0.DocEntry = T1.DocNum
WHERE
T0.Canceled = 'N' AND T0.DocType = 'S'
AND T0.TrsfrSum > 0 AND T0.ObjType = 46
AND T1.InvType = 18 AND T1.ObjType = 46 AND T0.DocEntry = " + sPago + ")";
break;
}
DT_SQL.ExecuteQuery(sql);
}
catch (Exception) {}
return(DT_SQL);
}
private void loadGrid(string cmd)
{
// Dim cmd As String = "SELECT WOR1.LineNum+1 as 列號碼, WOR1.ItemCode as 子階項目號碼 ,OITM.ItemName as 項目名稱, cast(WOR1.PlannedQty as decimal(18,2))) as 應發數量,cast(WOR1.IssuedQty as decimal(18,2))) as 已發數量, cast(OITW.OnHand as decimal(18,2))) as 庫存量, cast(OITW.OnHand - OITW.IsCommited as decimal(18,2))) AS 可用量,WOR1.wareHouse as 倉庫 " _
//& " from WOR1 LEFT OUTER JOIN OITM ON WOR1.ItemCode = OITM.ItemCode LEFT OUTER JOIN OITW ON WOR1.ItemCode = OITW.ItemCode AND WOR1.wareHouse =OITW.WhsCode " _
// & " where WOR1.DocEntry = N'{0}'"
//string cmd = " SELECT top 50 * from OCRD";
//cmd = string.Format(cmd, docNum);
oDTGrid.ExecuteQuery(cmd);
oSAP.setGridReadonly(ref oGrid);
formatSubGridColumn();
}
private void ShowExportSO(SAPbouiCOM.Form form, String Where = "")
{
string sql = "";
try
{
if (form.Items.Item(MtxExportSO) == null)
{
return;
}
form.Items.Item(MtxExportSO).AffectsFormMode = false;
oMatrixExportSO = (SAPbouiCOM.Matrix)form.Items.Item(MtxExportSO).Specific;
oMatrixExportSO.Clear();
oDTExportSO = getDataTableExportSo(form);
if (Where.Equals(""))
{
sql = String.Format(" select 'N' as [clSel[,ROW_NUMBER() OVER (ORDER BY R.[DocEntry[) AS [ID[,R.[DocEntry[,R.[DocNum[,R.[DocDate[,R.[DocTotal[,R.[CardCode[,R.[CardName[, IFNULL(R.[U_EbizChargeID[, '') as [EBiz[, 'False' as [Sync[ , (select Max([U_Status[) from[@CCSOLOG[where[U_DocNum[= Cast(R.[DocNum[ as nvarchar(20)))as [Status[ , (select Min([U_CreateDt[) from[@CCSOLOG[where[U_DocNum[= Cast(R.[DocNum[ as nvarchar(20)) )as [UploadDt[ , (select Max([U_UpdateDt[) from[@CCSOLOG[where[U_DocNum[= Cast(R.[DocNum[ as nvarchar(20)) )as [TransDt[ from ORDR R where R.[DocStatus[= 'O' and IFNULL(R.[U_EbizChargeMarkPayment[, '') <> 'Paid' and R.\"DocDate\" =current_date ").Replace("]", "\"").Replace("[", "\"");
}
else
{
var subQ = " ,( select Max([U_Status[) from [@CCSOLOG[ where [U_DocNum[=Cast(RDR.[DocNum[ as nvarchar(20)))as [Status[ ,(select Min([U_CreateDt[) from [@CCSOLOG[where[U_DocNum[= Cast(RDR.[DocNum[ as nvarchar(20)) )as [UploadDt[ , (select Max([U_UpdateDt[) from [@CCSOLOG[ where [U_DocNum[= Cast(RDR.[DocNum[ as nvarchar(20)) )as [TransDt[";
sql = String.Format("select 'N' as ]clSel],ROW_NUMBER() OVER (ORDER BY RDR.[DocEntry]) AS ]ID],RDR.[DocEntry[,RDR.[DocNum],RDR.[DocDate],RDR.[DocTotal],RDR.[CardCode], RDR.[CardName], IFNULL(RDR.[U_EbizChargeID], '') as ]EBiz], 'False' as ]Sync] {1} from ORDR RDR inner join OCRD C on C.[CardCode] = RDR.[CardCode] inner join OCRG CRG on C.[GroupCode] = CRG.[GroupCode] INNER JOIN OCTG CTG ON C.[GroupNum] = CTG.[GroupNum] where RDR.[DocStatus]='O' and IFNULL(RDR.[U_EbizChargeMarkPayment],'')<>'Paid' {0}", Where, subQ).Replace("[", "\"").Replace("]", "\"");
}
oDTExportSO.ExecuteQuery(sql);
BindMatrixExportSO(oMatrixExportSO, "clSel", "clSel", dtItem, true);
BindMatrixExportSO(oMatrixExportSO, "clNo", "ID");
BindMatrixExportSO(oMatrixExportSO, "DocNum", "DocNum");
BindMatrixExportSO(oMatrixExportSO, "DocEntry", "DocEntry");
BindMatrixExportSO(oMatrixExportSO, "DocDate", "DocDate");
BindMatrixExportSO(oMatrixExportSO, "CardCode", "CardCode");
BindMatrixExportSO(oMatrixExportSO, "CardName", "CardName");
BindMatrixExportSO(oMatrixExportSO, "DocTotal", "DocTotal");
BindMatrixExportSO(oMatrixExportSO, "clSync", "Sync");
BindMatrixExportSO(oMatrixExportSO, "Status", "Status");
BindMatrixExportSO(oMatrixExportSO, "UploadDt", "UploadDt");
BindMatrixExportSO(oMatrixExportSO, "TransDt", "TransDt");
oMatrixExportSO.LoadFromDataSource();
oMatrixExportSO.AutoResizeColumns();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
public SAPbouiCOM.DataTable ListarHistoricoPagosTXT(int CodigoHistorico, SAPbouiCOM.DataTable DT_Resultado)
{
try
{
string sp = @"[Min_Bancos_Consultar_Historico_PagosTXT]
@Codigo = " + CodigoHistorico.ToString() + "";
DT_Resultado.ExecuteQuery(sp);
}
catch
{
DT_Resultado = null;
}
return(DT_Resultado);
}
public SAPbouiCOM.DataTable ConsultarOrdenes(SAPbouiCOM.DataTable DT_SQL, string sPago, string TipoTabla)
{
try
{
string sql = "";
switch (TipoTabla)
{
case "D":
sql = @"SELECT DISTINCT
T4.DocEntry as 'Numero Interno'
,T4.DocNum as 'Numero Documento'
,T4.CardName as 'Proveedor'
,T4.CreateDate as 'Fecha Creacion'
,REPLACE(PARSENAME(CONVERT(VARCHAR,CAST(T4.DocTotal AS MONEY),1),2),',','.') as 'Total'
FROM
(select distinct docentry as 'Factura', BaseEntry as 'Entrega', BaseType from PCH1 where BaseType = 20) T0
JOIN (select distinct DocNum as 'Pago', DocEntry as 'Factura' from VPM2 where InvType = 18) T1 ON T0.Factura = T1.Factura
JOIN OPDN T2 ON T0.Entrega = T2.DocEntry
JOIN PDN1 T3 ON T0.Entrega = T3.DocEntry
JOIN OPOR T4 ON T3.BaseEntry = T4.DocEntry
WHERE
T0.BaseType = 20 AND T2.CANCELED = 'N'
AND T3.BaseType = 22 AND T1.Pago = " + sPago + "";
break;
case "B":
sql = @" SELECT DISTINCT
T4.DocEntry as 'Numero Interno'
,T4.DocNum as 'Numero Documento'
,T4.CardName as 'Proveedor'
,T4.CreateDate as 'Fecha Creacion'
,REPLACE(PARSENAME(CONVERT(VARCHAR,CAST(T4.DocTotal AS MONEY),1),2),',','.') as 'Total'
FROM
(select distinct docentry as 'Factura', BaseEntry as 'Entrega', BaseType from PCH1 where BaseType = 20) T0
JOIN (select distinct DocNum as 'Pago', DocEntry as 'Factura' from PDF2 where InvType = 18) T1 ON T0.Factura = T1.Factura
JOIN OPDN T2 ON T0.Entrega = T2.DocEntry
JOIN PDN1 T3 ON T0.Entrega = T3.DocEntry
JOIN OPOR T4 ON T3.BaseEntry = T4.DocEntry
WHERE
T0.BaseType = 20 AND T2.CANCELED = 'N'
AND T3.BaseType = 22 AND T1.Pago = " + sPago + "";
break;
}
DT_SQL.ExecuteQuery(sql);
}
catch (Exception) { }
return(DT_SQL);
}
public SAPbouiCOM.DataTable ListarHistoricoTXT(DHistoricoTXT HistoricoTXT, SAPbouiCOM.DataTable DT_Resultado)
{
try
{
string sp = @"Min_Bancos_Consultar_Historico_ArchivosTXT
@FechaDesde = N'" + HistoricoTXT.U_Fecha.ToString("MM/dd/yyyy") + @"',
@FechaHasta = N'" + HistoricoTXT.U_FechaEstado.ToString("MM/dd/yyyy") + "'";
DT_Resultado.ExecuteQuery(sp);
}
catch
{
DT_Resultado = null;
}
return(DT_Resultado);
}
public SAPbouiCOM.DataTable Listar(DPagosTranferencia Pagos_Transferencia, SAPbouiCOM.DataTable DT_Resultado)
{
try
{
string sp = @"Min_Bancos_Consultar_Pagos_Depositos_Para_TXT
@FechaDesde = N'" + Pagos_Transferencia.FechaIni.ToString("MM/dd/yyyy") + @"',
@FechaHasta = N'" + Pagos_Transferencia.FechaFin.ToString("MM/dd/yyyy") + "'";
DT_Resultado.ExecuteQuery(sp);
}
catch
{
DT_Resultado = null;
}
return(DT_Resultado);
}
public SAPbouiCOM.DataTable Listar(DPagosAutorizados Pagos_Autorizados, SAPbouiCOM.DataTable DT_Resultado)
{
try
{
string sp = @"EXEC [dbo].[Min_Bancos_Consultar_Pagos_Depositos_Autorizados]
@FechaDesde = N'" + Pagos_Autorizados.FechaIni.ToString("MM/dd/yyyy") + @"',
@FechaHasta = N'" + Pagos_Autorizados.FechaFin.ToString("MM/dd/yyyy") + "'";
DT_Resultado.ExecuteQuery(sp);
}
catch
{
DT_Resultado = null;
}
return(DT_Resultado);
}
private void PopulateBatchesGrid(int rowIndex)
{
string itemCode = dtDocuments.GetValue("ItemCode", rowIndex).ToString();
string whsCode = dtDocuments.GetValue("WhsCode", rowIndex).ToString();
var query = string.Format(Query, itemCode, whsCode);
dtBatches.ExecuteQuery(query);
gvBatches.SetGridHeaderIndex();
SAPbouiCOM.EditTextColumn gColumn = gvBatches.Columns.Item("ItemCode") as SAPbouiCOM.EditTextColumn;
gColumn.LinkedObjectType = "4";
gColumn = gvBatches.Columns.Item("WhsCode") as SAPbouiCOM.EditTextColumn;
gColumn.LinkedObjectType = "64";
}
/// <summary>
/// Enlaza matrix con datos de la BD
/// </summary>
public static void BindMatrix(SAPbouiCOM.Matrix oMatrix, SAPbouiCOM.DataTable dt)
{
String Query = null;
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
Query = "SELECT \"DocEntry\", \"DocNum\", CASE \"FolioPref\" WHEN '33' THEN 'Factura Electrónica' WHEN '34' THEN 'Factura Exenta Electrónica' END AS \"FolioPref\", \"CardName\", \"FolioNum\", \"DocDate\", \"DocTotal\", \"U_SEI_FEBOSID\" FROM OPCH WHERE \"U_SEI_CONTADO\" = 'N'";
break;
default:
Query = "SELECT DocEntry, DocNum, CASE FolioPref WHEN '33' THEN 'Factura Electrónica' WHEN '34' THEN 'Factura Exenta Electrónica' END AS FolioPref, CardName, FolioNum, DocDate, DocTotal, U_SEI_FEBOSID FROM OPCH WHERE U_SEI_CONTADO = 'N'";
break;
}
dt.ExecuteQuery(Query);
oMatrix.LoadFromDataSource();
}
public void SBO_ItemEvent(string FormUID, ref SAPbouiCOM.ItemEvent pVal, out bool BubbleEvent)
{
BubbleEvent = true;
//cfl
SubMain.GetCFL_EDIT(FormUID, ref pVal, out BubbleEvent, "txCardCode", "cfl2");
SubMain.GetCFL_EDIT(FormUID, ref pVal, out BubbleEvent, "txEntry", "cfl17");
if (pVal.Before_Action == false)
{
return;
}
try
{
if (FormUID == FUID)
{
if (pVal.ItemUID == "10")
{
if (pVal.EventType == SAPbouiCOM.BoEventTypes.et_CLICK)
{
try
{
f17.Freeze(true);
string cmd = GetCmd();
oDT.ExecuteQuery(cmd);
InitGridCol();
}
catch (Exception ex)
{
}
finally
{
f17.Freeze(false);
}
}
}
}
}
catch (Exception ex)
{
SubMain.SBO_Application.MessageBox(ex.Message, 1, "Ok", "", "");
}
}
public SAPbouiCOM.DataTable Listar(DPagosPendientes Pagos_Pendientes, SAPbouiCOM.DataTable DT_Resultado)
{
//SAPbouiCOM.DataTable DT_Resultado = new SAPbouiCOM.DataTable();
try
{
string sp = @"Min_Bancos_Consultar_Pagos_Depositos_Por_Autorizar
@FechaDesde = N'" + Pagos_Pendientes.FechaIni.ToString("MM/dd/yyyy") + @"',
@FechaHasta = N'" + Pagos_Pendientes.FechaFin.ToString("MM/dd/yyyy") + "'";
DT_Resultado.ExecuteQuery(sp);
}
catch
{
DT_Resultado = null;
}
return(DT_Resultado);
}
private void populateGrid(String check)
{
try
{
int[] SelectedRows = eCommon.DataTableIndexOf(_dt_CST, "Check", "Y");
StringBuilder oSB = new StringBuilder();
foreach (int Row in SelectedRows)
{
oSB.Append(String.Format("{0},", _dt_CST.GetValue("DocEntry", Row)));
}
String strQuery = String.Format(TWM_KDS_AddOn.Src.Resource.Queries.TWM_GET_SO_GRID, oSB.Append("0").ToString(), check);
_dt_LST.ExecuteQuery(strQuery);
// if no results from query delete first row which is empty
if (_grid_CST.DataTable.GetValue("ItemNo", 0).ToString().Length < 1)
{
_grid_CST.DataTable.Rows.Remove(0);
}
_grid_CST.Columns.Item(0).Type = SAPbouiCOM.BoGridColumnType.gct_CheckBox;
_grid_CST.Columns.Item("ItemNo").Editable = false;
_grid_CST.Columns.Item("BP Catalog No").Editable = false;
_grid_CST.Columns.Item("Item Description").Editable = false;
_grid_CST.Columns.Item("Total Amount").Editable = false;
_grid_CST.Columns.Item("Warehouse").Editable = false;
_grid_CST.Columns.Item("Delivery Date").Editable = false;
_grid_CST.Columns.Item("Doc_Type").Visible = false;
_grid_CST.Columns.Item("Doc_Entry").Visible = false;
_grid_CST.Columns.Item("Line_Num").Visible = false;
// link button for itemNo
SAPbouiCOM.EditTextColumn oEditColItemNo;
oEditColItemNo = ((SAPbouiCOM.EditTextColumn)(_grid_CST.Columns.Item("ItemNo")));
oEditColItemNo.LinkedObjectType = "4";
// link button for WareHouse
SAPbouiCOM.EditTextColumn oEditColWarehouse;
oEditColWarehouse = ((SAPbouiCOM.EditTextColumn)(_grid_CST.Columns.Item("Warehouse")));
oEditColWarehouse.LinkedObjectType = "64";
updateFields();
}
catch (Exception Ex) { eCommon.SBO_Application.StatusBar.SetText(Ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error); }
}
private void populateLog(SAPbouiCOM.Form form)
{
string sql = "";
try
{
//SAPbouiCOM.EditText customer = form.Items.Item(fidCustID).Specific;
SAPbouiCOM.Matrix oMatrix = form.Items.Item(mxtCCLog).Specific;
oMatrix.Clear();
//oMatrix.AutoResizeColumns();
SAPbouiCOM.DataTable oDts = getDataTable(form);
sql = string.Format("select top " + GetConfig("maxSelectRow") + " InvoiceID, CCAccountID as CCAID, OrderID, PaymentID, DownPaymentInvoiceID, CreditMemoID as CMID, custNum, customerID, customerName,CardHolder,crCardNum,Description,recID,acsUrl,authAmount,authCode,avsResult,avsResultCode, batchNum,batchRef,cardCodeResult," +
"cardCodeResultCode,cardLevelResult, cardLevelResultCode,conversionRate,convertedAmount,convertedAmountCurrency,error,errorCode,isDuplicate,payload,profilerScore,profilerResponse," +
"profilerReason,refNum,remainingBalance,result,resultCode, status, statusCode, vpasResultCode, recDate, Command, Amount from CCTRANS {0} order by recDate desc", strWhere);
oDts.ExecuteQuery(sql);
BindMatrix(oMatrix, "RefNum", "refNum");
BindMatrix(oMatrix, "PaymentID", "PaymentID");
BindMatrix(oMatrix, "OrderID", "OrderID");
BindMatrix(oMatrix, "InvoiceID", "InvoiceID");
BindMatrix(oMatrix, "CMID", "CMID");
BindMatrix(oMatrix, "CCAID", "CCAID");
BindMatrix(oMatrix, "custNum", "custNum");
BindMatrix(oMatrix, "command", "Command");
BindMatrix(oMatrix, "Amount", "Amount");
BindMatrix(oMatrix, "Desc", "Description");
BindMatrix(oMatrix, "CardHolder", "CardHolder");
BindMatrix(oMatrix, "crCardNum", "crCardNum");
BindMatrix(oMatrix, "error", "error");
BindMatrix(oMatrix, "avs", "avsResult");
BindMatrix(oMatrix, "CardCode", "cardCodeResult");
BindMatrix(oMatrix, "CardLevel", "cardLevelResult");
BindMatrix(oMatrix, "recdate", "recDate");
oMatrix.LoadFromDataSource();
}catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
//System.Runtime.InteropServices.Marshal.ReleaseComObject(oDT);
public static void DoQueryDT(string cmd, ref SAPbouiCOM.DataTable oDT)
{
//SAPbouiCOM.DataTable oDT = null;
//string sDocNum = ((SAPbouiCOM.EditText)SBO_Application.Forms.ActiveForm.Items.Item("18").Specific).Value;
//try
//{
// oDT = SBO_Application.Forms.ActiveForm.DataSources.DataTables.Add("tmp");
//}
//catch
//{
// oDT = SBO_Application.Forms.ActiveForm.DataSources.DataTables.Item("tmp");
//}
//SAPbouiCOM.DataTable oDT= oForm.DataSources.DataTables.Add("tmp");
//string cmd = "select cardcode from ocrd where CardType<>'S'";
oDT.ExecuteQuery(cmd);
//oDT.GetValue("ProfitCode", i).ToString();
//this.cboBadItem.ValidValues.Add(oDT.GetValue("Code", i).ToString(), oDT.GetValue("Name", i).ToString());
}
public String returnDepartment(SAPbouiCOM.Grid grid)
{
temporaryID = base.setRandom();
form = (SAPbouiCOM.Form)SAPbouiCOM.Framework.Application.SBO_Application.Forms.ActiveForm;
dataTable = form.DataSources.DataTables.Add(temporaryID.ToString());
temporaryID++;
dataTable.ExecuteQuery("SELECT\n" +
" t2.\"Code\"\n" +
"FROM\n" +
"USR5 T0 \n" +
"INNER JOIN OUSR T1 ON T0.\"UserCode\" = t1.USER_CODE\n" +
"INNER JOIN OUDP t2 ON t1.\"Department\" = t2.\"Code\"\n" +
"INNER JOIN M_CONNECTIONS MC ON MC.logical_connection_id = CURRENT_CONNECTION AND T0.\"ProcessID\" = MC.CLIENT_PID \n" +
"AND T0.\"SessionID\" = CURRENT_CONNECTION");
grid.DataTable = dataTable;
String currentDepartment = grid.DataTable.GetValue(0, 0).ToString();
//dataTable = form.DataSources.DataTables.Add(base.setRandom().ToString());
return(currentDepartment);
}
public SAPbouiCOM.DataTable GenerarEncabezadoFormato610(SAPbouiCOM.DataTable DT_SQL, SAPbouiCOM.DataTable DT_RESULT)
{
try
{
string FechaPago = DT_SQL.GetValue(0, 0).ToString();
string CantidadPagos = DT_SQL.GetValue(0, 1).ToString();
string CantidadDocu = DT_SQL.GetValue(0, 3).ToString();;
string MontoPagos = DT_SQL.GetValue(0, 2).ToString();//.Replace(".","");
string[] SeparaTotal = MontoPagos.Split('.');
string MontoEntero = SeparaTotal[0];
string MontoDecimal = SeparaTotal.Count() == 1 ? "00" : MontoPagos.Split('.')[1];
string sql = @"EXEC [dbo].[Min_Bancos_Generar_Encabezado_Archivo_PagoTXT_Formato610]
@U_CantidadPago = N'" + CantidadPagos + @"',
@U_CantidadDocu = N'" + CantidadDocu + @"',
@U_MontoTotal = N'" + MontoEntero + MontoDecimal + "'";
DT_RESULT.ExecuteQuery(sql);
}
catch (Exception) { }
return(DT_RESULT);
}
private void ShowExportItem(SAPbouiCOM.Form form)
{
string sql = "";
try
{
if (form.Items.Item(MtxItem) == null)
{
return;
}
form.Items.Item(MtxItem).AffectsFormMode = false;
oMatrixItemExport = (SAPbouiCOM.Matrix)form.Items.Item(MtxItem).Specific;
oMatrixItemExport.Clear();
oDTItemExport = getDataTableItemMaster(form);
sql = String.Format("select 'N' as [clSel],ROW_NUMBER() OVER (ORDER BY [DocEntry]) AS [ID],[ItemCode],[ItemName],IFNULL([U_EbizChargeID],'') as [EBiz],CASE WHEN IFNULL([U_EbizChargeID], '') = '' THEN 'False' ELSE 'True'END as [Sync] from OITM ").Replace("]", "\"").Replace("[", "\"");
oDTItemExport.ExecuteQuery(sql);
BindMatrixItem(oMatrixItemExport, "clSel", "clSel", dtItem, true);
BindMatrixItem(oMatrixItemExport, "clNo", "ID");
BindMatrixItem(oMatrixItemExport, "ItemCode", "ItemCode");
BindMatrixItem(oMatrixItemExport, "ItemName", "ItemName");
// BindMatrixItem(oMatrixItemExport, "clEbizkey", "EBiz");
BindMatrixItem(oMatrixItemExport, "clSync", "Sync");
oMatrixItemExport.LoadFromDataSource();
oMatrixItemExport.AutoResizeColumns();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
private void ShowExportBp(SAPbouiCOM.Form form, String DefaultCheck = "N")
{
string sql = "";
try
{
if (form.Items.Item(MtxBPExport) == null)
{
return;
}
form.Items.Item(MtxBPExport).AffectsFormMode = false;
oMatrixBpExport = (SAPbouiCOM.Matrix)form.Items.Item(MtxBPExport).Specific;
oMatrixBpExport.Clear();
oDTBpExport = getDataTableBP(form);
sql = String.Format("SELECT '{0}' as [sel],ROW_NUMBER() OVER (ORDER BY T0.[CardCode]) AS [ID],T0.[CardCode] as [c], T0.[CardName],T0.[Balance],T0.[E_Mail], IFNULL(T0.[U_EbizChargeID],'') as [EBiz],CASE WHEN IFNULL(T0.[U_EbizChargeID], '') = '' THEN 'False' ELSE 'True'END as [Sync] FROM OCRD T0 WHERE T0.[CardType] = 'C'", DefaultCheck).Replace("]", "\"").Replace("[", "\"");
oDTBpExport.ExecuteQuery(sql);
BindMatrixBP(oMatrixBpExport, "clSel", "sel", dtBPExport, true);
BindMatrixBP(oMatrixBpExport, "clNo", "ID");
BindMatrixBP(oMatrixBpExport, "colCard", "c");
BindMatrixBP(oMatrixBpExport, "CardName", "CardName");
BindMatrixBP(oMatrixBpExport, "Balance", "Balance");
BindMatrixBP(oMatrixBpExport, "E_Mail", "E_Mail");
// BindMatrixBP(oMatrixBpExport, "clEbizkey", "EBiz");
BindMatrixBP(oMatrixBpExport, "clSync", "Sync");
oMatrixBpExport.LoadFromDataSource();
oMatrixBpExport.AutoResizeColumns();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}