protected override string DoIt()
{
StringBuilder _sql = new StringBuilder();
MVA009Batch batch = new MVA009Batch(GetCtx(), GetRecord_ID(), Get_TrxName());
MVA009PaymentMethod _paymthd = null;
MVA009BatchLineDetails lineDetail = null;
MVA009BatchLines line = null;
//if (batch.GetVA009_GenerateLines()=="Y")
//{
// msg = Msg.GetMsg(GetCtx(), "VA009_BatchLineAlreadyCreated");
// return msg;
//}
msg = DeleteBatchLines(_sql, batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName());
if (!String.IsNullOrEmpty(msg))
{
return(msg);
}
MBankAccount _bankacc = new MBankAccount(GetCtx(), batch.GetC_BankAccount_ID(), Get_TrxName());
decimal dueamt = 0;
_sql.Clear();
_sql.Append(@"Select cp.ad_client_id, cp.ad_org_id,CI.C_Bpartner_ID, ci.c_invoice_id, cp.c_invoicepayschedule_id, cp.duedate,
cp.dueamt, cp.discountdate, cp.discountamt,cp.va009_paymentmethod_id,ci.c_currency_id , doc.DocBaseType
From C_Invoice CI inner join C_InvoicePaySchedule CP ON CI.c_invoice_id= CP.c_invoice_id INNER JOIN
C_DocType doc ON doc.C_DocType_ID = CI.C_DocType_ID Where ci.ispaid='N' AND cp.va009_ispaid='N' AND cp.C_Payment_ID IS NULL AND
CI.IsActive = 'Y' and ci.docstatus in ('CO','CL') AND cp.VA009_ExecutionStatus !='Y' AND CI.AD_Client_ID = " + batch.GetAD_Client_ID()
+ " AND CI.AD_Org_ID = " + batch.GetAD_Org_ID());
if (_C_BPartner_ID > 0)
{
_sql.Append(" and CI.C_Bpartner_ID=" + _C_BPartner_ID);
}
if (_C_invoice_ID > 0)
{
_sql.Append(" and CI.C_invoice_ID=" + _C_invoice_ID);
}
if (_paySchedule_ID > 0)
{
_sql.Append(" AND CP.C_InvoicePaySchedule_ID=" + _paySchedule_ID);
}
if (_docType > 0)
{
_sql.Append(" ANd CI.C_DocType_ID=" + _docType);
}
else
{
_sql.Append(" ANd doc.DocBaseType IN ('API' , 'ARI' , 'APC' , 'ARC') ");
}
if (_paymentMethod > 0)
{
_sql.Append(" And CP.VA009_PaymentMethod_ID=" + _paymentMethod);
_paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
_trigger = _paymthd.IsVA009_IsMandate();
}
if (_DateDoc_From != null && _DateDoc_To != null)
{
_sql.Append(" and cp.duedate BETWEEN ");
_sql.Append(GlobalVariable.TO_DATE(_DateDoc_From, true) + " AND ");
_sql.Append(GlobalVariable.TO_DATE(_DateDoc_To, true));
}
else if (_DateDoc_From != null && _DateDoc_To == null)
{
_sql.Append(" and cp.duedate >=" + GlobalVariable.TO_DATE(_DateDoc_From, true));
}
else if (_DateDoc_From == null && _DateDoc_To != null)
{
_sql.Append(" and cp.duedate <=" + GlobalVariable.TO_DATE(_DateDoc_To, true));
}
//else if (C_ConversionType_ID > 0) //commented the conversion type because while creatring invoice against Base currency, system will set currencyconversionType_ID=0
//{
// _sql.Append(" AND C_ConversionType_ID=" + C_ConversionType_ID);
//}
if (VA009_IsSameCurrency == true)
{
_sql.Append(" AND CI.C_Currency_ID =" + _bankacc.GetC_Currency_ID());
}
_sql.Append(" Order by CI.C_Bpartner_ID asc , doc.docbasetype ");
DataSet ds = new DataSet();
ds = DB.ExecuteDataset(_sql.ToString());
if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (C_ConversionType_ID == 0) //to Set Default conversion Type
{
C_ConversionType_ID = GetDefaultConversionType(_sql);
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if ((Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"])) == 0)
{
continue;
}
// if invoice is of AP Invoice and AP Credit Memo then make a single Batch line
if (docBaseType == "API" || docBaseType == "APC")
{
if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
("API" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "APC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
{
line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
}
else
{
line = null;
}
}
// if invoice is of AR Invoice and AR Credit Memo then make a single Batch line
else if (docBaseType == "ARI" || docBaseType == "ARC")
{
if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
("ARI" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "ARC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
{
line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
}
else
{
line = null;
}
}
//if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) && docBaseType == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]))
//{
// line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, null);
//}
// else
if (line == null)
{
line = new MVA009BatchLines(GetCtx(), 0, Get_TrxName());
line.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
line.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
line.SetVA009_Batch_ID(batch.GetVA009_Batch_ID());
_BPartner = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]);
docBaseType = Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]);
line.SetC_BPartner_ID(_BPartner);
if (_trigger == true)
{
_sql.Clear();
_sql.Append("Select VA009_BPMandate_id from C_BPartner Where C_BPartner_ID=" + _BPartner + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID());
DataSet ds1 = new DataSet();
ds1 = DB.ExecuteDataset(_sql.ToString());
if (ds1.Tables != null && ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
{
line.SetVA009_BPMandate_ID(Util.GetValueOfInt(ds1.Tables[0].Rows[0]["VA009_BPMandate_id"]));
}
}
if (line.Save(Get_TrxName()))
{
//line.SetProcessed(true);
line.Save(Get_TrxName());
_VA009_BatchLine_ID = line.GetVA009_BatchLines_ID();
}
else
{
Get_TrxName().Rollback();
_BPartner = 0;
_VA009_BatchLine_ID = 0;
}
}
lineDetail = new MVA009BatchLineDetails(GetCtx(), 0, Get_TrxName());
lineDetail.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
lineDetail.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
lineDetail.SetVA009_BatchLines_ID(line.GetVA009_BatchLines_ID());
lineDetail.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Invoice_ID"]));
lineDetail.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]));
lineDetail.SetDueDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DueDate"]));
lineDetail.SetC_ConversionType_ID(C_ConversionType_ID);
dueamt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"]));
Decimal DiscountAmt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]);
bool issamme = true; decimal comvertedamt = 0;
if (Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) == _bankacc.GetC_Currency_ID())
{
issamme = true;
}
else
{
issamme = false;
}
if (!issamme)
{
dueamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
if (DiscountAmt > 0)
{
DiscountAmt = MConversionRate.Convert(GetCtx(), DiscountAmt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
if (DiscountAmt == 0)
{
Get_TrxName().Rollback();
msg = Msg.GetMsg(GetCtx(), "NoCurrencyConversion");
MCurrency from = new MCurrency(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), Get_TrxName());
MCurrency to = new MCurrency(GetCtx(), Util.GetValueOfInt(_bankacc.GetC_Currency_ID()), Get_TrxName());
return(msg + from.GetISO_Code() + "," + to.GetISO_Code());
}
}
if (dueamt == 0)
{
Get_TrxName().Rollback();
msg = Msg.GetMsg(GetCtx(), "NoCurrencyConversion");
MCurrency from = new MCurrency(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), Get_TrxName());
MCurrency to = new MCurrency(GetCtx(), Util.GetValueOfInt(_bankacc.GetC_Currency_ID()), Get_TrxName());
return(msg + from.GetISO_Code() + "," + to.GetISO_Code());
}
}
if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
{
//dueamt = dueamt - (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
dueamt = dueamt - DiscountAmt;
// 145-2.88
}
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
{
lineDetail.SetDueAmt(-1 * dueamt);
comvertedamt = (-1 * dueamt);
}
else
{
lineDetail.SetDueAmt(dueamt);
comvertedamt = (dueamt);
}
if (issamme == false)
{
comvertedamt = dueamt;
//comvertedamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
lineDetail.SetC_Currency_ID(_bankacc.GetC_Currency_ID());
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
{
comvertedamt = (-1 * comvertedamt);
}
}
else
{
lineDetail.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
}
lineDetail.SetVA009_ConvertedAmt(comvertedamt);
lineDetail.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));
if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) < Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
{
lineDetail.SetDiscountDate(null);
lineDetail.SetDiscountAmt(0);
}
else if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
{
lineDetail.SetDiscountDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]));
//lineDetail.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
lineDetail.SetDiscountAmt(DiscountAmt);
}
if (!lineDetail.Save(Get_TrxName()))
{
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
//return"BatchLine Not Saved";
}
else
{
//lineDetail.SetProcessed(true);
//lineDetail.Save(Get_TrxName());
//MInvoicePaySchedule _invpay = new MInvoicePaySchedule(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]), Get_TrxName());
//_invpay.SetVA009_ExecutionStatus("Y");
//_invpay.Save(Get_TrxName());
}
}
batch.SetVA009_GenerateLines("Y");
//batch.SetProcessed(true); //Commeted by Arpit asked by Ashish Gandhi to set processed only if the Payment completion is done
batch.Save(Get_TrxName());
if (_paymentMethod != 0)
{
//_paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
batch.SetVA009_PaymentMethod_ID(_paymentMethod);
batch.SetVA009_PaymentRule(_paymthd.GetVA009_PaymentRule());
batch.SetVA009_PaymentTrigger(_paymthd.GetVA009_PaymentTrigger());
if (!batch.Save(Get_TrxName()))
{
Get_TrxName().Rollback();
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
}
}
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineCrtd"));;
}
else
{
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
};
}
public object GetMethod(int VA009_Batch_ID, Ctx ctx, Trx GetTrx)
{
//VA009_DownloadAllHO u = new VA009_DownloadAllHO();
//u.DownloadMethod(ctx, GetTrx);
MVA009Batch _batch = new MVA009Batch(ctx, VA009_Batch_ID, GetTrx);
string sql = string.Empty; string filename = string.Empty;
string foldername = string.Empty; bool module = false;
int _VA017 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA017_' AND IsActive = 'Y'", null, GetTrx));
if (_VA017 > 0)
{
foldername = Util.GetValueOfString(DB.ExecuteScalar("SELECT VA017_LocalOpFolder FROM VA017_ICICI_Payment WHERE C_BankAccount_ID=" + _batch.GetC_BankAccount_ID(), null, GetTrx));
module = true;
}
if (module == true)
{
DataSet ds = new DataSet();
sql = @"SELECT bac.accountno AS Batch_Account_NO,ICI.VA017_OutwardFilePrefix, ICI.VA017_ResponseFilePrefix, cbp.accountno AS BP_Account_No, cbp.a_name AS Account_Name,
bld.dueamt, pm.VA009_TransferCode as va009_paymentbasetype, to_date(sysdate,'DD-MON-YYYY') AS DateTrx, bank.routingno AS IFSC, usr.phone , cbp.a_email AS Account_Email,
bld.c_invoicepayschedule_id, cbp.a_street ||cbp.a_city || cbp.a_state ||cbp.a_country AS address, bl.va009_batchlines_id,
bld.va009_batchlinedetails_ID , bld.discountamt, inv.issotrx, inv.isreturntrx, bld.ad_org_id, bld.ad_client_id ,
doc.DocBaseType FROM va009_batchlinedetails bld INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bld.va009_batchlines_id
LEFT JOIN c_bp_bankaccount CBP ON cbp.c_bpartner_id=bl.c_bpartner_id INNER JOIN va009_batch b ON b.va009_batch_id =bl.va009_batch_id
INNER JOIN va009_paymentmethod pm ON pm.va009_paymentmethod_id=bld.va009_paymentmethod_id INNER JOIN c_bankaccount bac ON
bac.c_bankaccount_id=b.c_bankaccount_id LEFT JOIN C_Bank Bank ON Bank.c_bank_id=CBP.C_Bank_ID LEFT JOIN ad_user usr ON
usr.c_bpartner_id=bl.c_bpartner_id INNER JOIN c_invoice inv ON inv.c_invoice_id = bld.c_invoice_id INNER JOIN VA017_ICICI_Payment ICI ON ICI.c_bankaccount_id =b.c_bankaccount_id INNER JOIN C_DocType doc ON
doc.C_Doctype_ID= inv.C_Doctype_ID WHERE NVL(bl.c_payment_id , 0) = 0 AND NVL(bld.c_payment_id , 0) = 0 AND b.va009_batch_id=" + _batch.GetVA009_Batch_ID();
ds = null;
ds = DB.ExecuteDataset(sql);
decimal amt = 0;
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
StringBuilder stre = new StringBuilder();
string PaymentModeVal = "";
stre.Append("DebitAcNo|BeneficiaryAcNo|BeneficiaryName|Amount|PayMode|Date(DD-MMM-YYYY)|IFSC|BeneMobileNo|BeneEmail-Id|PaymnetDetail|BeneficiaryMailingAddress|CreditNarration|BatchDetailID|BankTrxID|PaymentStatus");
stre.AppendLine("");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) != 0)
{
filename = Util.GetValueOfString(ds.Tables[0].Rows[i]["VA017_OutwardFilePrefix"]) + Util.GetValueOfString(ds.Tables[0].Rows[i]["Batch_Account_NO"]);
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "API" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
{
if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) < 0)
{
amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) * -1;
}
else
{
amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]);
}
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["IFSC"]).Contains("ICIC"))
{
PaymentModeVal = "I";
}
else
{
PaymentModeVal = Util.GetValueOfString(ds.Tables[0].Rows[i]["va009_paymentbasetype"]);
}
stre.AppendLine(ds.Tables[0].Rows[i]["Batch_Account_NO"] + "|" + ds.Tables[0].Rows[i]["BP_Account_No"] +
"|" + ds.Tables[0].Rows[i]["Account_Name"] + "|" + amt +
"|" + PaymentModeVal + "|" + String.Format("{0:dd/MM/yy}", ds.Tables[0].Rows[i]["DateTrx"]) +
"|" + ds.Tables[0].Rows[i]["IFSC"] + "|" + ds.Tables[0].Rows[i]["phone"] +
"|" + ds.Tables[0].Rows[i]["Account_Email"] + "|" + ds.Tables[0].Rows[i]["c_invoicepayschedule_id"] +
"|" + ds.Tables[0].Rows[i]["address"] + "|" + ds.Tables[0].Rows[i]["va009_batchlines_id"] +
"|" + ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]);
}
else if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARI" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
{
if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) < 0)
{
amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]) * -1;
}
else
{
amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]);
}
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["IFSC"]) == "ICIC")
{
PaymentModeVal = "I";
}
else
{
PaymentModeVal = Util.GetValueOfString(ds.Tables[0].Rows[i]["va009_paymentbasetype"]);
}
stre.AppendLine(ds.Tables[0].Rows[i]["BP_Account_No"] + "|" + ds.Tables[0].Rows[i]["Batch_Account_NO"] +
"|" + ds.Tables[0].Rows[i]["Account_Name"] + "|" + amt +
"|" + PaymentModeVal + "|" + String.Format("{0:dd/MM/yy}", ds.Tables[0].Rows[i]["DateTrx"]) +
"|" + ds.Tables[0].Rows[i]["IFSC"] + "|" + ds.Tables[0].Rows[i]["phone"] +
"|" + ds.Tables[0].Rows[i]["Account_Email"] + "|" + ds.Tables[0].Rows[i]["c_invoicepayschedule_id"] +
"|" + ds.Tables[0].Rows[i]["address"] + "|" + ds.Tables[0].Rows[i]["va009_batchlines_id"] +
"|" + ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]);
}
}
}
filename += "_" + _batch.GetVA009_Batch_ID() + "_" + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + "_" + DateTime.Now.Hour + "-" + DateTime.Now.Minute + "-" + DateTime.Now.Second;
CreateLocalCSVFile(stre.ToString(), filename, foldername, module);
}
return(Msg.GetMsg(ctx, "VA009_FileCreated"));
}
else
{
return(Msg.GetMsg(ctx, "VA009_ICICIModuleNotInstalled"));
}
}
protected override void Prepare()
{
MVA009Batch _batch = new MVA009Batch(GetCtx(), GetRecord_ID(), Get_TrxName());
className = Util.GetValueOfString(DB.ExecuteScalar("SELECT pc.va009_name FROM va009_bankpaymentclass bpc INNER JOIN va009_paymentclass pc ON bpc.va009_paymentclass_id=pc.va009_paymentclass_id WHERE bpc.c_bankaccount_id=" + _batch.GetC_BankAccount_ID() + " AND bpc.ad_client_id=" + GetCtx().GetAD_Client_ID() + "", null, Get_TrxName()));
}
public string CreateJDBCDataSourceXml(Ctx ctx, string DBResourceName, int RecordID)
{
List <Document> obj = new List <Document>();
string orgName = "Org Name";
if (RecordID > 0)
{
MVA009Batch batch = new MVA009Batch(ctx, RecordID, null);
MVA009BatchLineDetails batchDetails = null;
MVA009PaymentMethod paymthd = null;
string sql = @"SELECT bd.VA009_BatchLineDetails_ID
FROM VA009_BatchLineDetails bd
INNER JOIN va009_batchlines bl
ON bl.va009_batchlines_id= bd.va009_batchlines_id
WHERE VA009_Batch_ID =" + RecordID;
DataSet dsb = DB.ExecuteDataset(sql);
if (dsb != null && dsb.Tables[0].Rows.Count > 0)
{
DataSet dsbnk = null;
for (int x = 0; x < dsb.Tables[0].Rows.Count; x++)
{
batchDetails = new MVA009BatchLineDetails(ctx, Util.GetValueOfInt(dsb.Tables[0].Rows[x]["VA009_BatchLineDetails_ID"]), null);
paymthd = new MVA009PaymentMethod(ctx, batchDetails.GetVA009_PaymentMethod_ID(), null);
Document dataobj = new Document();
dataobj.PaymentID = Util.GetValueOfInt(batchDetails.GetVA009_BatchLineDetails_ID());
dataobj.PaymentMethod = paymthd.GetVA009_Name();
dataobj.DueAmt = batchDetails.GetDueAmt();
dataobj.currency = Util.GetValueOfString(DB.ExecuteScalar("SELECT iso_code FROM c_currency WHERE c_currency_id = " + batchDetails.GetC_Currency_ID()));
dataobj.Org = Util.GetValueOfString(DB.ExecuteScalar("SELECT name FROM ad_org WHERE ad_org_id = " + batchDetails.GetAD_Org_ID()));
dsbnk = DB.ExecuteDataset("SELECT b.RoutingNo, ac.IBAN FROM C_Bank b INNER JOIN C_bankaccount ac ON b.c_bank_id= ac.c_bank_id where ac.c_bankaccount_id =" + batch.GetC_BankAccount_ID());
if (dsbnk != null && dsbnk.Tables[0].Rows.Count > 0)
{
dataobj.IBAN = Util.GetValueOfString(dsbnk.Tables[0].Rows[0]["IBAN"]);
dataobj.BIC = Util.GetValueOfString(dsbnk.Tables[0].Rows[0]["RoutingNo"]);
}
orgName = dataobj.Org;
obj.Add(dataobj);
}
}
}
//Get DataBase Details of Vienna Advantage Framework
string filePath = "";
VConnection conn = new VConnection();
conn.SetAttributes(DBConn.CreateConnectionString());
DataSet ds = new DataSet();
if (conn.Db_Type == "Oracle")
{
#region commented
//Document obj = new Document();
//obj.AccountName = "Manjot";
//obj.AccountNo = "1001";
//objJDBC.exportedWithPermissions = "false";
//objJDBC.folder = "/organizations";
//objJDBC.name = DBResourceName;
//objJDBC.version = "0";
//objJDBC.label = DBResourceName;
//objJDBC.creationDate = DateTime.Now.ToString("O");
//objJDBC.updateDate = DateTime.Now.ToString("O");
//objJDBC.driver = "tibcosoftware.jdbc.oracle.OracleDriver";
//objJDBC.connectionUrl = "jdbc:tibcosoftware:oracle://" + conn.Db_host + ";SID=" + conn.Db_name;
//objJDBC.connectionUser = conn.Db_uid;
//objJDBC.connectionPassword = conn.Db_pwd;
//objJDBC.timezone = "";
#endregion
filePath = XmlCreater(obj, true, DBResourceName, orgName);
return(filePath);
}
return(filePath);
}
protected override string DoIt()
{
batchid = GetBatchId();
if (batchid > 0)
{
StringBuilder _sql = new StringBuilder();
MVA009Batch batch = new MVA009Batch(GetCtx(), batchid, Get_TrxName());
MBankAccount _bankacc = new MBankAccount(GetCtx(), batch.GetC_BankAccount_ID(), Get_TrxName());
MVA009PaymentMethod _paymthd = null;
MVA009BatchLines line = null;
MVA009BatchLineDetails lineDetail = null;
decimal dueamt = 0;
_sql.Clear();
_sql.Append(@"Select cp.ad_client_id, cp.ad_org_id,CI.C_Bpartner_ID, ci.c_invoice_id, cp.c_invoicepayschedule_id, cp.duedate,
cp.dueamt, cp.discountdate, cp.discountamt,cp.va009_paymentmethod_id,ci.c_currency_id , doc.DocBaseType
From C_Invoice CI inner join C_InvoicePaySchedule CP ON CI.c_invoice_id= CP.c_invoice_id
INNER JOIN C_DocType doc ON doc.C_DocType_ID = CI.C_DocType_ID Where ci.ispaid='N' AND cp.va009_ispaid='N' AND cp.C_Payment_ID IS NULL
AND CI.IsActive = 'Y' and ci.docstatus in ('CO','CL') AND cp.VA009_ExecutionStatus NOT IN ( 'Y','J') AND CI.AD_Client_ID = " + GetAD_Client_ID() + " AND CI.AD_Org_ID = " + GetAD_Org_ID());
if (_C_BPartner_ID > 0)
{
_sql.Append(" and CI.C_Bpartner_ID=" + _C_BPartner_ID);
}
if (_C_invoice_ID > 0)
{
_sql.Append(" and CI.C_invoice_ID=" + _C_invoice_ID);
}
if (_paySchedule_ID > 0)
{
_sql.Append(" AND CP.C_InvoicePaySchedule_ID=" + _paySchedule_ID);
}
if (_docType > 0)
{
_sql.Append(" ANd CI.C_DocType_ID=" + _docType);
}
else
{
_sql.Append(" ANd doc.DocBaseType IN ('API' , 'ARI' , 'APC' , 'ARC') ");
}
if (_paymentMethod > 0)
{
_sql.Append(" And CP.VA009_PaymentMethod_ID=" + _paymentMethod);
_paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
_trigger = _paymthd.IsVA009_IsMandate();
}
if (_DateDoc_From != null && _DateDoc_To != null)
{
_sql.Append(" and cp.duedate BETWEEN ");
_sql.Append(GlobalVariable.TO_DATE(_DateDoc_From, true) + " AND ");
_sql.Append(GlobalVariable.TO_DATE(_DateDoc_To, true));
}
else if (_DateDoc_From != null && _DateDoc_To == null)
{
_sql.Append(" and cp.duedate >=" + GlobalVariable.TO_DATE(_DateDoc_From, true));
}
else if (_DateDoc_From == null && _DateDoc_To != null)
{
_sql.Append(" and cp.duedate <=" + GlobalVariable.TO_DATE(_DateDoc_To, true));
}
else if (C_ConversionType_ID > 0)
{
_sql.Append(" AND C_ConversionType_ID=" + C_ConversionType_ID);
}
if (VA009_IsSameCurrency == true)
{
_sql.Append(" AND CI.C_Currency_ID =" + _bankacc.GetC_Currency_ID());
}
_sql.Append(" Order by CI.C_Bpartner_ID asc , doc.docbasetype ");
DataSet ds = new DataSet();
ds = DB.ExecuteDataset(_sql.ToString(), null, Get_TrxName());
if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if ((Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"])) == 0)
{
continue;
}
// if invoice is of AP Invoice and AP Credit Memo then make a single Batch line
if (docBaseType == "API" || docBaseType == "APC")
{
if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
("API" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "APC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
{
line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
}
else
{
line = null;
}
}
// if invoice is of AR Invoice and AR Credit Memo then make a single Batch line
else if (docBaseType == "ARI" || docBaseType == "ARC")
{
if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) &&
("ARI" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "ARC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])))
{
line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName());
}
else
{
line = null;
}
}
//if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) && docBaseType == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]))
//{
// line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, null);
//}
// else
if (line == null)
{
line = new MVA009BatchLines(GetCtx(), 0, Get_TrxName());
line.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
line.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
line.SetVA009_Batch_ID(batch.GetVA009_Batch_ID());
_BPartner = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]);
docBaseType = Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]);
line.SetC_BPartner_ID(_BPartner);
if (_trigger == true)
{
_sql.Clear();
_sql.Append("Select VA009_BPMandate_id from C_BPartner Where C_BPartner_ID=" + _BPartner + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID());
DataSet ds1 = new DataSet();
ds1 = DB.ExecuteDataset(_sql.ToString(), null, Get_TrxName());
if (ds1.Tables != null && ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
{
line.SetVA009_BPMandate_ID(Util.GetValueOfInt(ds1.Tables[0].Rows[0]["VA009_BPMandate_id"]));
}
}
if (line.Save())
{
line.SetProcessed(true);
line.Save();
_VA009_BatchLine_ID = line.GetVA009_BatchLines_ID();
}
else
{
_BPartner = 0;
_VA009_BatchLine_ID = 0;
}
}
lineDetail = new MVA009BatchLineDetails(GetCtx(), 0, Get_TrxName());
lineDetail.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"]));
lineDetail.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"]));
lineDetail.SetVA009_BatchLines_ID(line.GetVA009_BatchLines_ID());
lineDetail.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Invoice_ID"]));
lineDetail.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]));
lineDetail.SetDueDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DueDate"]));
lineDetail.SetC_ConversionType_ID(C_ConversionType_ID);
dueamt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"]));
if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
{
dueamt = dueamt - (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
// 145-2.88
}
bool issamme = true; decimal comvertedamt = 0;
if (Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) == _bankacc.GetC_Currency_ID())
{
issamme = true;
}
else
{
issamme = false;
}
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
{
lineDetail.SetDueAmt(-1 * dueamt);
comvertedamt = (-1 * dueamt);
}
else
{
lineDetail.SetDueAmt(dueamt);
comvertedamt = (dueamt);
}
if (issamme == false)
{
comvertedamt = 0;
comvertedamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID());
lineDetail.SetC_Currency_ID(_bankacc.GetC_Currency_ID());
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC")
{
comvertedamt = (-1 * comvertedamt);
}
}
else
{
lineDetail.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
}
lineDetail.SetVA009_ConvertedAmt(comvertedamt);
lineDetail.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));
if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) < Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
{
lineDetail.SetDiscountDate(null);
lineDetail.SetDiscountAmt(0);
}
else if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
{
lineDetail.SetDiscountDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]));
lineDetail.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
}
if (!lineDetail.Save())
{
//return"BatchLine Not Saved";
}
else
{
lineDetail.SetProcessed(true);
MInvoicePaySchedule _invpay = new MInvoicePaySchedule(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]), Get_TrxName());
_invpay.SetVA009_ExecutionStatus("Y");
_invpay.Save();
lineDetail.Save();
}
}
batch.SetVA009_GenerateLines("Y");
batch.SetProcessed(true);
batch.Save();
if (_paymentMethod != 0)
{
//_paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName());
batch.SetVA009_PaymentMethod_ID(_paymentMethod);
batch.SetVA009_PaymentRule(_paymthd.GetVA009_PaymentRule());
batch.SetVA009_PaymentTrigger(_paymthd.GetVA009_PaymentTrigger());
batch.Save();
if (_paymthd.GetVA009_PaymentRule() == "M")
{
VA009_CreatePayments payment = new VA009_CreatePayments();
payment.DoIt(batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName(), 0);
}
else if (_paymthd.GetVA009_PaymentRule() == "E")
{
VA009_ICICI_Snorkel _Snrkl = new VA009_ICICI_Snorkel();
_Snrkl.GetMethod(batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName());
}
}
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineCrtd"));;
}
else
{
DB.ExecuteQuery("DELETE FROM VA009_Batch WHERE VA009_Batch_ID=" + batchid, null, Get_TrxName());
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
}
}
else
{
DB.ExecuteQuery("DELETE FROM VA009_Batch WHERE VA009_Batch_ID=" + batchid, null, Get_TrxName());
return(Msg.GetMsg(GetCtx(), "VA009_BatchNotCrtd"));
}
}