/// <summary>
/// Checkea y descheckea todas las columnas de la matrix variedades
/// </summary>
public static void EjecutarChecking(SAPbouiCOM.Form oForm)
{
SAPbouiCOM.Button oButton = oForm.Items.Item("bt_chk").Specific;
SAPbouiCOM.Matrix oMatrix = oForm.Items.Item("matrixVar").Specific;
SAPbouiCOM.Matrix oMatrixCrt = oForm.Items.Item("matrixCrt").Specific;
oForm.Freeze(true);
// Check
if (oButton.Caption.Equals("check"))
{
for (int i = 1; i <= oMatrix.RowCount; i++)
{
SAPbouiCOM.CheckBox chk = (SAPbouiCOM.CheckBox)oMatrix.Columns.Item("co_Chk").Cells.Item(i).Specific;
chk.Checked = true;
}
DataBindCuarteles(oForm);
oButton.Caption = "uncheck";
}
// Uncheck
else
{
for (int i = 1; i <= oMatrix.RowCount; i++)
{
SAPbouiCOM.CheckBox chk = (SAPbouiCOM.CheckBox)oMatrix.Columns.Item("co_Chk").Cells.Item(i).Specific;
chk.Checked = false;
}
oMatrixCrt.Clear();
oButton.Caption = "check";
}
oForm.Freeze(false);
}
internal void populateItemsFromGR(List <Item> items)
{
grMatrix.Clear();
this.UIAPIRawForm.Freeze(true);
int rows = items.Count;
for (int i = 1; i <= rows; i++)
{
grMatrix.AddRow();
try
{
//grMatrix.SetCellWithoutValidation(i,"1", items[i - 1].ItemCode);
//SAPbouiCOM.EditText tempCell2 = (grMatrix.Columns.Item("2").Cells.Item(i).Specific as SAPbouiCOM.EditText);
grMatrix.SetCellValue("1", i, items[i - 1].ItemCode); //ItemCode
grMatrix.SetCellValue("9", i, items[i - 1].Quantity); //Item Quantity
grMatrix.SetCellValue("15", i, items[i - 1].WhsCode); //Item Whs Code
//grMatrix.SetCellWithoutValidation(i, "15", items[i - 1].WhsCode);
//(grMatrix.Columns.Item("15").Cells.Item(i).Specific as SAPbouiCOM.EditText).String = items[i - 1].WhsCode;
// grMatrix.SetCellValue("231000074", i, items[i - 1].BaseDocEntry); //Base Reference
}
catch (Exception ex)
{
Utilities.LogErrors(string.Concat("Exception Occured at GoodsReceipt.populateItemsFromGR: ", ex.ToString()));
Application.SBO_Application.MessageBox(string.Format("Error Can't Add {0}. {1}", items[i - 1].ItemCode, ex.ToString()));
}
}
this.UIAPIRawForm.Freeze(false);
}
private void ClearMatrix(string pStrDTName, SAPbouiCOM.Matrix pObjMatrix)
{
if (!this.UIAPIRawForm.DataSources.DataTables.Item(pStrDTName).IsEmpty)
{
this.UIAPIRawForm.DataSources.DataTables.Item(pStrDTName).Rows.Clear();
pObjMatrix.Clear();
}
}
/// <summary>
/// Method InsertConfig
///
/// </summary>
/// <remarks>@author RCordova-2017/08/18</remarks>
public void InsertConfig()
{
mDtInspD.Clear();
mObjMtxInspDetails.Clear();
btnAdd.Item.Enabled = true;
//btnMod.Item.Enabled = false;
btnOk.Item.Enabled = true;
lIntHeadRejCounter = Convert.ToInt32(mlstInspection.Sum(x => x.RE) + mlstInspection.Sum(x => x.NP));
}
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);
}
}
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);
}
}
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);
}
}
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);
}
}
private void LoadMatrixPeticiones(string pStrCliente = null)
{
Matrix1.Clear();
string lStrQuery;
if (EditText1.Value != "" || EditText1.Value != null)
{
if (pStrCliente == null || pStrCliente == "")
{
lStrQuery = "SELECT 'N' AS Seleccionar, cast(sum(T2.OpenInvQty) as decimal(18,2)) AS Cantidad, T2.FromWhsCod AS Almacen, T2.U_CR_Cardcode AS ClienteCod, T4.CardName AS Cliente, T2.ItemCode AS ItemCode, T3.ItemName Tipo, cast(cast(sum(T2.OpenInvQty) as decimal(18,2)) as varchar(8)) AS Trans FROM OWTQ T1 INNER JOIN WTQ1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OCRD T4 ON T4.cardcode = T2.U_CR_Cardcode INNER JOIN OITM T3 ON T3.ItemCode = T2.ItemCode WHERE T1.DocNum = '" + EditText1.Value + "' AND T2.InvntSttus = 'O' group by T2.FromWhsCod , T2.U_CR_Cardcode , T4.CardName , T2.ItemCode , T3.ItemName";
}
else
{
lStrQuery = "SELECT 'N' AS Seleccionar, cast(sum(T2.OpenInvQty) as decimal(18,2)) AS Cantidad, T2.FromWhsCod AS Almacen, T2.U_CR_Cardcode AS ClienteCod, T4.CardName AS Cliente, T2.ItemCode AS ItemCode, T3.ItemName Tipo, cast(cast(sum(T2.OpenInvQty) as decimal(18,2)) as varchar(8)) AS Trans FROM OWTQ T1 INNER JOIN WTQ1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OCRD T4 ON T4.cardcode = T2.U_CR_Cardcode INNER JOIN OITM T3 ON T3.ItemCode = T2.ItemCode WHERE T1.DocNum = '" + EditText1.Value + "' and T2.U_CR_Cardcode = '" + pStrCliente + "' AND T2.InvntSttus = 'O' group by T2.FromWhsCod , T2.U_CR_Cardcode , T4.CardName , T2.ItemCode , T3.ItemName";
}
oForm.DataSources.DataTables.Item("dtMatrix").ExecuteQuery(lStrQuery);
if (oForm.DataSources.DataTables.Item("dtMatrix").Rows.Count == 0)
{
Application.SBO_Application.StatusBar.SetText("Sin resultados.", SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Warning);
}
else
{
Matrix1.Columns.Item("Col_0").DataBind.Bind("dtMatrix", "Seleccionar");
Matrix1.Columns.Item("Col_1").DataBind.Bind("dtMatrix", "Cliente");
Matrix1.Columns.Item("Col_2").DataBind.Bind("dtMatrix", "Tipo");
Matrix1.Columns.Item("Col_3").DataBind.Bind("dtMatrix", "Almacen");
Matrix1.Columns.Item("Col_4").DataBind.Bind("dtMatrix", "Cantidad");
Matrix1.Columns.Item("Col_5").DataBind.Bind("dtMatrix", "Trans");
Matrix1.Columns.Item("Col_6").DataBind.Bind("dtMatrix", "ClienteCod");
Matrix1.Columns.Item("Col_6").Visible = false;
Matrix1.Columns.Item("Col_7").DataBind.Bind("dtMatrix", "ItemCode");
Matrix1.LoadFromDataSource();
}
Matrix1.AutoResizeColumns();
}
else
{
Application.SBO_Application.StatusBar.SetText("Es necesario seleccionar la solicitud de transferencia", SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
}
}
private void OnCustomInitialize()
{
oApp = (SAPbouiCOM.Application)Application.SBO_Application;
oCompany = (SAPbobsCOM.Company)oApp.Company.GetDICompany();
oForm = oApp.Forms.Item("edimatr");
//Start();
oMatrix = ((SAPbouiCOM.Matrix)(oForm.Items.Item("Item_2").Specific));
oDBDataSource = oForm.DataSources.DBDataSources.Item("@EDICIONESCOL");
// Ready Matrix to populate data
oMatrix.Clear();
oMatrix.AutoResizeColumns();
// Querying the DB Data source
oDBDataSource.Query();
// setting the user data source data
oMatrix.LoadFromDataSource();
oMatrix.AutoResizeColumns();
//int i;
//for (i = 1; i <= oMatrix.RowCount - 1; i++) ;
//{
// SAPbouiCOM.EditText cellID = (SAPbouiCOM.EditText)oMatrix.GetCellSpecific("numer", i);
// cellID.String = i.ToString();
//}
oForm.Visible = true;
}
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);
}
}
private void populateLog(SAPbouiCOM.Form form)
{
string sql = "";
try
{
//SAPbouiCOM.EditText customer = form.Items.Item(fidCustID).Specific;
if (form.Items.Item(mxtCCLog) == null)
{
return;
}
form.Items.Item(mxtCCLog).AffectsFormMode = false;
SAPbouiCOM.Matrix oMatrix = (SAPbouiCOM.Matrix)form.Items.Item(mxtCCLog).Specific;
oMatrix.Clear();
//oMatrix.AutoResizeColumns();
SAPbouiCOM.DataTable oDts = getDataTable(form);
sql = string.Format("select " +
" (select CAST(\"DocEntry\" AS VARCHAR) from OINV where CAST(\"DocEntry\" AS VARCHAR) = \"U_InvoiceID\") as InvoiceID," +
" (select CAST(\"DocNum\" AS VARCHAR) from OINV where CAST(\"DocEntry\" AS VARCHAR) = \"U_InvoiceID\") as InvoiceNum, " +
" (select CAST(\"DocNum\" AS VARCHAR) from ORDR where CAST(\"DocEntry\" AS VARCHAR) = \"U_OrderID\") as OrderNum, " +
" (select CAST(\"DocEntry\" AS VARCHAR) from ORDR where CAST(\"DocEntry\" AS VARCHAR) = \"U_OrderID\") as OrderID, " +
" \"U_CCAccountID\" as CCAID, " +
"\"U_PaymentID\", \"U_DownPaymentInvID\", \"U_CreditMemoID\" as CMID, \"U_custNum\", \"U_customerID\", "+
" \"U_customerName\",\"U_CardHolder\",\"U_crCardNum\",\"U_Description\",\"U_recID\",\"U_acsUrl\",\"U_authAmount\", " +
" \"U_authCode\",\"U_avsResult\",\"U_avsResultCode\",\"U_batchNum\",\"U_batchRef\",\"U_cardCodeResult\"," +
"\"U_cardCodeRCode\",\"U_cardLevelResult\", \"U_cardLevelRCode\",\"U_conversionRate\"," +
" \"U_convertedAmount\",\"U_convertedCurrency\",\"U_error\",\"U_errorCode\",\"U_isDuplicate\", " +
" \"U_payload\",\"U_profilerScore\",\"U_profilerResponse\"," +
"\"U_profilerReason\",\"U_refNum\",\"U_remainingBalance\",\"U_result\",\"U_resultCode\",\"U_status\"," +
"\"U_statusCode\", \"U_vpasResultCode\", \"U_recDate\", \"U_command\", \"U_amount\", \"DocEntry\" "+
" from \"@CCTRANS\" {0} order by \"DocEntry\" desc", strWhere);
// trace(sql);
oDts.ExecuteQuery(sql);
BindMatrix(oMatrix, "RefNum", "U_refNum");
BindMatrix(oMatrix, "PaymentID", "U_PaymentID");
BindMatrix(oMatrix, "OrderID", "OrderID");
BindMatrix(oMatrix, "InvoiceID", "InvoiceID");
BindMatrix(oMatrix, "OrderNum", "OrderNum");
BindMatrix(oMatrix, "InvoiceNum", "InvoiceNum");
BindMatrix(oMatrix, "CMID", "CMID");
BindMatrix(oMatrix, "CCAID", "CCAID");
BindMatrix(oMatrix, "custNum", "U_custNum");
BindMatrix(oMatrix, "command", "U_command");
BindMatrix(oMatrix, "Amount", "U_Amount");
BindMatrix(oMatrix, "Desc", "U_Description");
BindMatrix(oMatrix, "CardHolder", "U_CardHolder");
BindMatrix(oMatrix, "crCardNum", "U_crCardNum");
BindMatrix(oMatrix, "error", "U_error");
BindMatrix(oMatrix, "avs", "U_avsResult");
BindMatrix(oMatrix, "CardCode", "U_cardCodeResult");
BindMatrix(oMatrix, "CardLevel", "U_cardLevelResult");
BindMatrix(oMatrix, "recdate", "U_recDate");
BindMatrix(oMatrix, "ccTRANSID", "DocEntry");
oMatrix.LoadFromDataSource();
string cardcode = getCustomerID();
setFormEditVal(form, editEmail, getCustEmail(cardcode));
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
private void populateePaymentFormMatrix(string orderBy = "")
{
string sql = "";
try
{
if (oePaymentFormForm == null)
{
return;
}
SAPbouiCOM.Matrix oMatrix = (SAPbouiCOM.Matrix)oePaymentFormForm.Items.Item(matrixePaymentForm).Specific;
oMatrix.Clear();
//oMatrix.AutoResizeColumns();
string where = "";
string id = getFormItemVal(oePaymentFormForm, editCustomerID);
if (id != "")
{
where = string.Format(" and c.\"CardCode\"='{0}' ", id);
}
string grp = getFormItemVal(oePaymentFormForm, cbGroup);
if (grp != "")
{
string s = getCardCodeByGroup(grp);
if (s != "")
{
where = string.Format(" and c.\"CardCode\" in ({0}) ", s);
}
else
{
where = " and 1=0";
}
}
SAPbouiCOM.DataTable oDts = getePaymentFormMatrixDataTable();
sql = string.Format("select (a.\"DocTotal\" - a.\"PaidToDate\") as Balance " +
" , a.\"DocEntry\", a.\"DocNum\", a.\"CardCode\", b.\"U_UploadedBalance\" " +
" , b.\"U_PaidAmount\", b.\"U_Status\", b.\"U_UploadDate\", b.\"U_PaidDate\" " +
" , c.\"CardName\", c.\"E_Mail\" " +
" from OINV a INNER JOIN OCRD c on a.\"CardCode\" = c.\"CardCode\" " +
" left outer join \"@CCPAYFORMINVOICE\" b on a.\"DocNum\" = b.\"U_InvoiceID\" and c.\"CardCode\" = b.\"U_CustomerID\" " +
" where (((a.\"DocTotal\" - a.\"PaidToDate\") > 0 and a.CANCELED = 'N' and a.\"DocStatus\" <> 'C') or b.\"U_InvoiceID\" is not null) " +
" {0} ", where);
sql = sql + " order by " + orderBy + " a.\"DocEntry\" desc";
oDts.ExecuteQuery(sql);
BindMatrix(oMatrix, "DocNum", "DocNum", dtePaymentForm);
BindMatrix(oMatrix, "InvID", "DocEntry", dtePaymentForm);
BindMatrix(oMatrix, "CustID", "CardCode", dtePaymentForm);
BindMatrix(oMatrix, "Status", "U_Status", dtePaymentForm);
BindMatrix(oMatrix, "PaidDT", "U_PaidDate", dtePaymentForm);
BindMatrix(oMatrix, "UploadDT", "U_UploadDate", dtePaymentForm);
BindMatrix(oMatrix, "Balance", "Balance", dtePaymentForm);
BindMatrix(oMatrix, "AmtPaid", "U_PaidAmount", dtePaymentForm);
BindMatrix(oMatrix, "UpBal", "U_UploadedBalance", dtePaymentForm);
BindMatrix(oMatrix, "CName", "CardName", dtePaymentForm);
BindMatrix(oMatrix, "E_Mail", "E_Mail", dtePaymentForm, true);
oMatrix.LoadFromDataSource();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
/// <summary>
/// Trae los datos para grilla de cuarteles segun variedades asignadas (check)
/// </summary>
public static void DataBindCuarteles(SAPbouiCOM.Form oForm)
{
SAPbouiCOM.Matrix oMatrix = oForm.Items.Item("matrixVar").Specific;
string query = string.Empty;
List <string> Chekeados = new List <string>();
string Desc = string.Empty;
// Recorremos la matrix de variedades y obtenemos los valores con check
for (int i = 1; i <= oMatrix.RowCount; i++)
{
SAPbouiCOM.CheckBox chk = (SAPbouiCOM.CheckBox)oMatrix.Columns.Item("co_Chk").Cells.Item(i).Specific;
if (chk.Checked)
{
Chekeados.Add(oMatrix.Columns.Item("co_Cod").Cells.Item(i).Specific.Value);
Desc = oMatrix.Columns.Item("co_Desc").Cells.Item(i).Specific.Value.ToString();
}
}
// Si hay variedades chekeadas asignar cuarteles
if (Chekeados.Count > 0)
{
int index = 0;
foreach (string sCampo in Campo)
{
foreach (string var in Chekeados)
{
if (!index.Equals(0))
{
query += " UNION ";
}
switch (Conexion_SBO.m_oCompany.DbServerType)
{
case SAPbobsCOM.BoDataServerTypes.dst_HANADB:
query += @"SELECT 'Y' as ""check"", ""U_SEI_CRT"" as ""Cuartel"", ""U_SEI_VAR"" as ""Variedad"", ""U_SEI_HEC"" as ""Hec"", ""U_SEI_ESP"" as ""Esp"", ""Code"" as ""Campo"" FROM ""@SEI_DMDC"" WHERE ""Code"" = '" + sCampo + @"' AND ""U_SEI_VAR"" = '" + var + @"' AND ""U_SEI_EST""='Activo'";
break;
default:
query += @"SELECT 'Y' as check, U_SEI_CRT as Cuartel, U_SEI_VAR as Variedad, U_SEI_HEC as Hec, U_SEI_ESP as Esp, Code as Campo FROM @SEI_DMDC WHERE Code = '" + sCampo + @"' AND U_SEI_VAR = '" + var + @"' AND U_SEI_EST='Activo'";
break;
}
index++;
}
}
oForm.DataSources.DataTables.Item("CUARTELES").ExecuteQuery(query);
oMatrix = oForm.Items.Item("matrixCrt").Specific;
oMatrix.Columns.Item("co_Chk").DataBind.Bind("CUARTELES", "check");
oMatrix.Columns.Item("co_Crt").DataBind.Bind("CUARTELES", "Cuartel");
oMatrix.Columns.Item("co_Desc").DataBind.Bind("CUARTELES", "Variedad");
oMatrix.Columns.Item("co_Hec").DataBind.Bind("CUARTELES", "Hec");
oMatrix.Columns.Item("co_Esp").DataBind.Bind("CUARTELES", "Esp");
oMatrix.Columns.Item("co_Cpo").DataBind.Bind("CUARTELES", "Campo");
oMatrix.Clear();
oMatrix.LoadFromDataSource();
// Si hay variedades chekeades setear boton check variedades
SAPbouiCOM.Button oButton = oForm.Items.Item("bt_chk").Specific;
oButton.Caption = "uncheck";
}
else
{
oMatrix = oForm.Items.Item("matrixCrt").Specific;
oMatrix.Clear();
// Si no hay variedades chekeades setear boton check variedades y cuarteles
SAPbouiCOM.Button oButton = oForm.Items.Item("bt_chk").Specific;
oButton.Caption = "check";
}
}
private void ShowSendPendEmail(SAPbouiCOM.Form form)
{
string sql = "";
try
{
if (form.Items.Item(MtxSendPendingEmail) == null)
{
return;
}
DateTime FromdtValue = getdate(ed_fromDate);
DateTime TodtValue = getdate(ed_ToDate).Add(DateTime.MaxValue.TimeOfDay);
var PendingPaymentsList = ebiz.SearchEbizWebFormPendingPayments(getToken(), "", FromdtValue, TodtValue, null, 0, 100, "");
// var PaymentResp = ebiz.DeleteEbizWebFormPayment(getToken(), "");
// var ReminderResp = ebiz.ResendEbizWebFormEmail(getToken(), "");
form.Items.Item(MtxSendPendingEmail).AffectsFormMode = false;
oMatrixPendEmail = (SAPbouiCOM.Matrix)form.Items.Item(MtxSendPendingEmail).Specific;
oMatrixPendEmail.Clear();
odtSendPendingEmail = getDataTablePendEmail(form);
sql = String.Format("select top 1'N' as [clSel],ROW_NUMBER() OVER (ORDER BY [DocEntry]) AS [ID],[DocNum],[DocDate],[DocTotal],[CardCode],[CardName],IFNULL([U_EbizChargeID],'') as [EBiz],CASE WHEN IFNULL([U_EbizChargeID], '') = '' THEN 'False' ELSE 'True'END as [Sync] from ORDR where [DocStatus]='100' ").Replace("]", "\"").Replace("[", "\"");
odtSendPendingEmail.ExecuteQuery(sql);
BindMatrixPendEmail(oMatrixPendEmail, "clSel", "clSel", dtItem, true);
BindMatrixPendEmail(oMatrixPendEmail, "clNo", "ID");
BindMatrixPendEmail(oMatrixPendEmail, "DocNum", "DocNum");
BindMatrixPendEmail(oMatrixPendEmail, "DocDate", "DocDate");
BindMatrixPendEmail(oMatrixPendEmail, "CardCode", "CardCode");
BindMatrixPendEmail(oMatrixPendEmail, "Email", "CardName");
BindMatrixPendEmail(oMatrixPendEmail, "DocTotal", "DocTotal");
BindMatrixPendEmail(oMatrixPendEmail, "clEbizkey", "EBiz");
BindMatrixPendEmail(oMatrixPendEmail, "clSync", "Sync");
oMatrixPendEmail.LoadFromDataSource();
oMatrixPendEmail.Clear();
foreach (var itm in PendingPaymentsList)
{
oMatrixPendEmail.AddRow(1);
SAPbouiCOM.EditText oEdit = (SAPbouiCOM.EditText)oMatrixPendEmail.Columns.Item("CardCode").Cells.Item(oMatrixPendEmail.RowCount).Specific;
oEdit.Value = itm.CustomerId;
oEdit = (SAPbouiCOM.EditText)oMatrixPendEmail.Columns.Item("DocTotal").Cells.Item(oMatrixPendEmail.RowCount).Specific;
oEdit.Value = itm.AmountDue;
oEdit = (SAPbouiCOM.EditText)oMatrixPendEmail.Columns.Item("DocNum").Cells.Item(oMatrixPendEmail.RowCount).Specific;
oEdit.Value = itm.InvoiceNumber;
oEdit = (SAPbouiCOM.EditText)oMatrixPendEmail.Columns.Item("clEbizkey").Cells.Item(oMatrixPendEmail.RowCount).Specific;
oEdit.Value = itm.PaymentInternalId;
oEdit = (SAPbouiCOM.EditText)oMatrixPendEmail.Columns.Item("Email").Cells.Item(oMatrixPendEmail.RowCount).Specific;
oEdit.Value = itm.CustomerEmailAddress;
oEdit = (SAPbouiCOM.EditText)oMatrixPendEmail.Columns.Item("DocDate").Cells.Item(oMatrixPendEmail.RowCount).Specific;
var date = DatetimeTosapFormatDate(Convert.ToDateTime(itm.PaymentRequestDateTime));
oEdit.Value = date;
}
oMatrixPendEmail.AutoResizeColumns();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
private void populatePayOnTermMatrix(bool showResult, string batchDate = "")
{
string sql = "";
try
{
setDeliveryID();
SAPbouiCOM.Matrix oMatrix = (SAPbouiCOM.Matrix)oPayOnTermForm.Items.Item(matrixPayOnTerm).Specific;
oMatrix.Clear();
//oMatrix.AutoResizeColumns();
SAPbouiCOM.DataTable oDts = getPayOnTermMatrixDataTable();
string manualSQL = string.Format("and exists( select 1 from DLN1 l,ODLN d where l.\"DocEntry\" = d.\"DocEntry\" and CAST(l.\"BaseEntry\" AS VARCHAR) = a.\"U_OrderID\" and l.\"BaseType\" = 17 and d.\"DocNum\" in ({0})", ManualScan);
sql = "select \"U_amount\", \"U_OrderID\", \"U_InvoiceID\" \r\n" +
", \"U_DeliveryID\" as deliveryID \r\n" +
", ( select d.\"DocNum\" from ODLN d where CAST(d.\"DocEntry\" as VARCHAR) = \"U_DeliveryID\") as deliveryNo \r\n" +
", \"U_CardHolder\", \"U_avsResult\", \"U_refNum\", \"U_BatchResult\", \"U_customerID\" \r\n" +
" from \"@CCTRANS\" a , ORDR b \r\n" +
" where a.\"U_OrderID\" = CAST(b.\"DocEntry\" AS VARCHAR) and \"U_command\" in ( 'cc:authonly', 'cc:authonly-remain') and \"U_OrderID\" <> '' \r\n" +
"and b.CANCELED = 'N' and \"U_DeliveryID\" is not null \r\n" +
string.Format(" and \"U_recDate\" > TO_DATE('{0}', 'YYYYMMDD') \r\n", getDateDiff());
string orderBy = "\r\n order by \"U_OrderID\" desc";
/*
* if (!showResult)
* {
* sql = sql + " and not exists(select 1 from ORDR where CAST(\"DocEntry\" as VARCHAR) = a.\"U_OrderID\" and CANCELED = 'Y') \r\n" +
* string.Format(" and \"U_recDate\" > ADD_DAYS(CURRENT_DATE, - {0}) \r\n", getDateDiff()) +
* " and not exists(select 1 from OINV where \"DocStatus\" = 'C' and CAST(\"DocEntry\" as VARCHAR) = a.\"U_InvoiceID\") \r\n";
*
* if(cfgBatchAutoMode != "Y")
* {
* sql = sql + manualSQL;
* }
* }
* else
* {
* if (cfgBatchAutoMode == "Y")
* {
*
* sql = sql + " and \"U_recDate\" > ADD_DAYS(CURRENT_DATE, - 30) \r\n";
* }else
* sql = sql + manualSQL;
* }
*/
sql = sql + orderBy;
oDts.ExecuteQuery(sql);
//trace(sql);
BindMatrix(oMatrix, "RefNum", "U_refNum", dtPayOnTerm);
BindMatrix(oMatrix, "CardHolder", "U_CardHolder", dtPayOnTerm);
BindMatrix(oMatrix, "customerID", "U_customerID", dtPayOnTerm);
BindMatrix(oMatrix, "InvoiceID", "U_InvoiceID", dtPayOnTerm);
BindMatrix(oMatrix, "DeliveryID", "deliveryID", dtPayOnTerm);
BindMatrix(oMatrix, "DeliveryNo", "deliveryNo", dtPayOnTerm);
BindMatrix(oMatrix, "OrderID", "U_OrderID", dtPayOnTerm);
BindMatrix(oMatrix, "Amount", "U_amount", dtPayOnTerm);
BindMatrix(oMatrix, "avsResult", "U_avsResult", dtPayOnTerm);
BindMatrix(oMatrix, "Result", "U_BatchResult", dtPayOnTerm);
oMatrix.LoadFromDataSource();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
private void ShowRecPay(SAPbouiCOM.Form form)
{
string sql = "";
try
{
if (form.Items.Item(MtxRecPay) == null)
{
return;
}
DateTime FromdtValue = getdate(ed_fromDatePyR);
DateTime TodtValue = getdate(ed_ToDatePyR).Add(DateTime.MaxValue.TimeOfDay);
var filters = new SearchFilter[1];
filters[0] = new SearchFilter();
filters[0].FieldName = "PaymentInternalId";
filters[0].ComparisonOperator = "eq";
filters[0].FieldValue = "7331bcf0-4ea4-4259-a14f-3343233d295a";
var PendingPaymentsList = ebiz.SearchEbizWebFormReceivedPayments(getToken(), "", FromdtValue, TodtValue, null, 0, 100, "");
form.Items.Item(MtxRecPay).AffectsFormMode = false;
OMtxRecPay = (SAPbouiCOM.Matrix)form.Items.Item(MtxRecPay).Specific;
OMtxRecPay.Clear();
odtRecPay = GetDatatableRecPay(form);
sql = String.Format("select top 1'N' as [clSel],ROW_NUMBER() OVER (ORDER BY [DocEntry]) AS [ID],[DocNum],[DocDate],[DocTotal],[CardCode],[CardName],IFNULL([U_EbizChargeID],'') as [EBiz],CASE WHEN IFNULL([U_EbizChargeID], '') = '' THEN 'False' ELSE 'True'END as [Sync] from ORDR where [DocStatus]='100' ").Replace("]", "\"").Replace("[", "\"");
odtRecPay.ExecuteQuery(sql);
BindMtxRecPay(OMtxRecPay, "clSel", "clSel", dtItem, true);
BindMtxRecPay(OMtxRecPay, "clNo", "ID");
BindMtxRecPay(OMtxRecPay, "DocNum", "DocNum");
BindMtxRecPay(OMtxRecPay, "DocDate", "DocDate");
BindMtxRecPay(OMtxRecPay, "CardCode", "CardCode");
BindMtxRecPay(OMtxRecPay, "Email", "CardName");
BindMtxRecPay(OMtxRecPay, "DocTotal", "DocTotal");
BindMtxRecPay(OMtxRecPay, "clEbizkey", "EBiz");
BindMtxRecPay(OMtxRecPay, "clSync", "Sync");
OMtxRecPay.LoadFromDataSource();
OMtxRecPay.Clear();
foreach (var itm in PendingPaymentsList)
{
OMtxRecPay.AddRow(1);
SAPbouiCOM.EditText oEdit = (SAPbouiCOM.EditText)OMtxRecPay.Columns.Item("CardCode").Cells.Item(OMtxRecPay.RowCount).Specific;
oEdit.Value = itm.CustomerId;
oEdit = (SAPbouiCOM.EditText)OMtxRecPay.Columns.Item("DocTotal").Cells.Item(OMtxRecPay.RowCount).Specific;
oEdit.Value = itm.AmountDue;
oEdit = (SAPbouiCOM.EditText)OMtxRecPay.Columns.Item("DocNum").Cells.Item(OMtxRecPay.RowCount).Specific;
oEdit.Value = itm.InvoiceNumber;
oEdit = (SAPbouiCOM.EditText)OMtxRecPay.Columns.Item("clEbizkey").Cells.Item(OMtxRecPay.RowCount).Specific;
oEdit.Value = itm.PaymentInternalId;
oEdit = (SAPbouiCOM.EditText)OMtxRecPay.Columns.Item("Email").Cells.Item(OMtxRecPay.RowCount).Specific;
oEdit.Value = itm.CustomerEmailAddress;
oEdit = (SAPbouiCOM.EditText)OMtxRecPay.Columns.Item("DocDate").Cells.Item(OMtxRecPay.RowCount).Specific;
var date = DatetimeTosapFormatDate(Convert.ToDateTime(itm.PaymentRequestDateTime));
oEdit.Value = date;
}
OMtxRecPay.AutoResizeColumns();
}
catch (Exception ex)
{
errorLog(ex);
errorLog(sql);
}
}
