/// <summary>
/// Delect recordes from allocation
/// </summary>
/// <param name="hdr"></param>
/// <returns>Bool type</returns>
private Boolean Delete(MAllocationHdr hdr)
{
// _m_trx.start();
Boolean success = false;
String msg = null;
// Std Period open?
msg = DocumentEngine.IsPeriodOpen(hdr);
if (msg != null)
{
msg = "@DeleteError@" + hdr.GetDocumentNo() + ": " + msg;
log.Warning(msg);
return(false);
}
if (hdr.Delete(true, _m_trx))
{
log.Fine(hdr.ToString());
success = true;
}
if (success)
{
_m_trx.Commit();
}
else
{
_m_trx.Rollback();
}
return(success);
}
/// <summary>
/// Generate Payment Allocate As per ChequeDetails
/// </summary>
/// <param name="ctx">Context</param>
/// <param name="Record_Id">VA027_ChequeDetails_ID</param>
/// <param name="paymentId">C_Payment_ID</param>
/// <param name="trxName">trxName</param>
/// <returns>true/false</returns>
public bool GenratePaymentAllocate(Ctx ctx, int Record_Id, int paymentId, Trx trxName)
{
String sql = "SELECT * FROM VA027_Checkallocate WHERE VA027_ChequeDetails_ID=" + Record_Id;
DataSet ds = new DataSet();
MPaymentAllocate _paymentAllocate = null;
ds = DB.ExecuteDataset(sql.ToString(), null, trxName);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
_paymentAllocate = new MPaymentAllocate(ctx, 0, trxName);
_paymentAllocate.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_Client_ID"]));
_paymentAllocate.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_Org_ID"]));
_paymentAllocate.SetC_Payment_ID(paymentId);
_paymentAllocate.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Invoice_ID"]));
_paymentAllocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_ID"]));
_paymentAllocate.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["Amount"]));
_paymentAllocate.SetInvoiceAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["InvoiceAmt"]));
_paymentAllocate.SetWriteOffAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["WriteOffAmt"]));
_paymentAllocate.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
if (!_paymentAllocate.Save(trxName))
{
trxName.Rollback();
ValueNamePair pp = VLogger.RetrieveError();
log.Info("Error Occured while generating Payment Allocate : " + pp.GetValue() + " , Error Name : " + pp.GetName());
_msg = Msg.GetMsg(ctx, "PaymentNotAllocated") + ", " + (pp != null ? pp.GetName() : "");
return(false);
}
}
}
return(true);
}
/// <summary>
/// reIndex Document
/// </summary>
/// <param name="runCleanUp">clean old records</param>
/// <param name="toBeIndexed">Array of Strings to be indexed</param>
/// <param name="ctx">context</param>
/// <param name="AD_Client_ID">id</param>
/// <param name="AD_Table_ID">id</param>
/// <param name="Record_ID">id</param>
/// <param name="CM_WebProject_ID">webproject</param>
/// <param name="lastUpdated">date of last update</param>
public static void ReIndex(bool runCleanUp, String[] toBeIndexed, Ctx ctx,
int AD_Client_ID, int AD_Table_ID, int Record_ID, int CM_WebProject_ID, DateTime?lastUpdated)
{
Trx trx = Trx.Get("ReIndex_" + AD_Table_ID + "_" + Record_ID);
try {
if (!runCleanUp)
{
MIndex.CleanUp(trx, AD_Client_ID, AD_Table_ID, Record_ID);
}
for (int i = 0; i < toBeIndexed.Length; i++)
{
MIndex.RunIndex(toBeIndexed[i], ctx, trx, AD_Table_ID, Record_ID,
CM_WebProject_ID, lastUpdated);
}
trx.Commit();
//DataBase.DB.Commit (true, trx);
}
catch
{
try
{
//DataBase.DB.Rollback(true, trx);
trx.Rollback();
}
catch
{
}
}
}
private static VLogger _log = VLogger.GetVLogger(typeof(MPackage).FullName); //Arpit
public static MPackage Create(MInOut shipment, MShipper shipper, DateTime?shipDate, Trx trxName)
{
MPackage retValue = new MPackage(shipment, shipper);
if (shipDate != null)
{
retValue.SetShipDate(shipDate);
}
//Edited : Arpit Rai, 13 Sept,2017
DateTime?moveDate = Convert.ToDateTime(shipment.GetMovementDate());
String documentNo = shipment.GetDocumentNo();
retValue.SetDateAcct(DateTime.Now.Date);
retValue.SetAD_Client_ID(shipment.GetAD_Client_ID());
retValue.SetAD_Org_ID(shipment.GetAD_Org_ID());
if (!retValue.Save(trxName))
{
trxName.Rollback();
_log.Log(Level.SEVERE, "Error While Generating Package");
}
//Arpit
// Lines
MInOutLine[] lines = shipment.GetLines(false);
for (int i = 0; i < lines.Length; i++)
{
MInOutLine sLine = lines[i];
MPackageLine pLine = new MPackageLine(retValue);
//Arpit
//pLine.SetInOutLine(sLine);
//Changes in Below Method to create Lines and their values from Shipment/MR Lines to Package Lines
pLine.SetInOutLine(sLine, moveDate, documentNo,
retValue.GetAD_Client_ID(), retValue.GetAD_Org_ID());
if (!pLine.Save(trxName))
{
trxName.Rollback();
_log.Log(Level.SEVERE, "Error While Generating Package Lines");
}
//Arpit
} // lines
return(retValue);
}
/// <summary>
/// Process Remote
/// @ejb.interface-method view-type="both"
/// </summary>
/// <param name="ctx">Context</param>
/// <param name="pi">Process Info</param>
/// <returns>resulting Process Info</returns>
public ProcessInfo Process(Ctx ctx, ProcessInfo pi)
{
String className = pi.GetClassName();
log.Info(className + " - " + pi);
_processCount++;
// Get Class
// Class clazz = null; // XDoclet: no generics!
Type clazz = null;
try
{
//clazz = Class.forName (className);
clazz = Type.GetType(className);
}
catch (Exception ex)
{
log.Log(Level.WARNING, className, ex);
pi.SetSummary("ClassNotFound", true);
return(pi);
}
// Get Process
SvrProcess process = null;
try
{
//process = (SvrProcess)clazz.newInstance ();
process = (SvrProcess)Activator.CreateInstance(clazz);
}
catch (Exception ex)
{
log.Log(Level.WARNING, "Instance for " + className, ex);
pi.SetSummary("InstanceError", true);
return(pi);
}
// Start Process
Trx trx = Trx.Get("ServerPrc");
try
{
bool ok = process.StartProcess(ctx, pi, trx);
pi = process.GetProcessInfo();
trx.Commit();
trx.Close();
}
catch (Exception ex1)
{
trx.Rollback();
trx.Close();
pi.SetSummary("ProcessError=>" + ex1.Message, true);
return(pi);
}
return(pi);
}
protected override void DoWork()
{
m_summary = new StringBuilder(m_model.ToString())
.Append(" - ");
MProcess process = m_model.GetProcess();
//
try
{
// Explicitly set Environment
Ctx ctx = m_model.GetCtx();
ctx.SetAD_Client_ID(m_model.GetAD_Client_ID());
ctx.SetContext("AD_Client_ID", m_model.GetAD_Client_ID());
ctx.SetAD_Org_ID(m_model.GetAD_Org_ID());
ctx.SetContext("AD_Org_ID", m_model.GetAD_Org_ID());
ctx.SetAD_User_ID(m_model.GetUpdatedBy());
ctx.SetContext("AD_User_ID", m_model.GetUpdatedBy());
ctx.SetContext("#SalesRep_ID", m_model.GetUpdatedBy());
//
m_trx = Trx.Get("Scheduler");
String result = m_model.Execute(m_trx);
m_summary.Append(result);
m_trx.Commit();
}
catch (Exception e)
{
if (m_trx != null)
{
m_trx.Rollback();
}
log.Log(Level.WARNING, process.ToString(), e);
m_summary.Append(e.ToString());
}
if (m_trx != null)
{
m_trx.Close();
}
//
int no = m_model.DeleteLog();
m_summary.Append("Logs deleted=").Append(no);
//
MSchedulerLog pLog = new MSchedulerLog(m_model, m_summary.ToString());
pLog.SetReference("#" + (_runCount.ToString())
+ " - " + TimeUtil.FormatElapsed(VAdvantage.Classes.CommonFunctions.CovertMilliToDate(_startWork)));
pLog.Save();
}
/// <summary> Arpit
/// Delete All Batch Lines and Batch Details Lines of selected Batch
/// </summary>
/// <param name="_sql"></param>
/// <param name="batch_ID"></param>
/// <param name="ctx_"></param>
/// <param name="trx_"></param>
/// <returns> String msg if got error in deleting the records</returns>
private static String DeleteBatchLines(StringBuilder _sql, int batch_ID, Ctx ctx_, Trx trx_)
{
MVA009BatchLines bLines = null;
_sql.Clear();
_sql.Append("SELECT VA009_BatchLines_ID FROM VA009_BatchLines WHERE VA009_Batch_ID=" + batch_ID);
using (DataSet ds = DB.ExecuteDataset(_sql.ToString(), null, trx_))
{
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (Int32 i = 0; i < ds.Tables[0].Rows.Count; i++)
{
bLines = new MVA009BatchLines(ctx_, Util.GetValueOfInt(ds.Tables[0].Rows[i]["VA009_BatchLines_ID"]), trx_);
if (!bLines.Delete(false, trx_))
{
trx_.Rollback();
return(Msg.GetMsg(ctx_, "VA009_BatchLineNotCrtd"));
}
}
}
}
return("");
}
/// <summary>
/// Create version table and window based on the parent table \
/// where Maintain Version field is marked as true
/// </summary>
/// <returns> Message (String) </returns>
public string CreateVersionInfo(int AD_Column_ID, int AD_Table_ID, Trx trx)
{
_trx = trx;
_AD_Table_ID = AD_Table_ID;
_AD_Column_ID = AD_Column_ID;
bool hasMainVerCol = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + _AD_Table_ID + " AND IsActive ='Y' AND IsMaintainVersions = 'Y'", null, _trx)) > 0;
if (!hasMainVerCol)
{
hasMainVerCol = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsMaintainVersions FROM AD_Table WHERE AD_Table_ID = " + _AD_Table_ID, null, _trx)) == "Y";
}
// check whether there are any columns in the table
// marked as "Maintain Versions", then proceed else return
if (hasMainVerCol)
{
MTable tbl = new MTable(GetCtx(), _AD_Table_ID, _trx);
string VerTblName = tbl.GetTableName() + "_Ver";
// Create/Get System Elements for Version Table Columns
string retMsg = GetSystemElements(VerTblName);
if (retMsg != "")
{
return(retMsg);
}
int Ver_AD_Table_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Table_ID FROM AD_Table WHERE TableName = '" + VerTblName + "'", null, _trx));
// check whether version table is already present in system
// if not present then create table
MTable tblVer = null;
if (Ver_AD_Table_ID <= 0)
{
string tableName = tbl.GetTableName();
// create new Version table for parent table
tblVer = new MTable(GetCtx(), 0, _trx);
tbl.CopyTo(tblVer);
tblVer.SetTableName(tableName + "_Ver");
tblVer.SetName(tableName + " Ver");
tblVer.Set_Value("Export_ID", null);
tblVer.Set_Value("AD_Window_ID", null);
tblVer.SetIsDeleteable(true);
tblVer.SetDescription("Table for maintaining versions of " + tableName);
tblVer.SetHelp("Table for maintaining versions of " + tableName);
tblVer.SetIsMaintainVersions(false);
//tblVer.SetAD_Window_ID(Ver_AD_Window_ID);
if (!tblVer.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in creating Version Table";
}
log.Log(Level.SEVERE, "Version table not created :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionTblNotCreated"));
}
else
{
Ver_AD_Table_ID = tblVer.GetAD_Table_ID();
// Create Default Version Columns
retMsg = CreateDefaultVerCols(Ver_AD_Table_ID);
if (retMsg != "")
{
return(retMsg);
}
}
}
else
{
tblVer = new MTable(GetCtx(), Ver_AD_Table_ID, _trx);
// Create Default Version Columns
retMsg = CreateDefaultVerCols(Ver_AD_Table_ID);
if (retMsg != "")
{
return(retMsg);
}
}
int VerTableColID = 0;
// if Version table successfully created, then check columns, if not found then create new
if (Ver_AD_Table_ID > 0)
{
// Get all columns from Version Table
int[] ColIDs = MColumn.GetAllIDs("AD_Column", "AD_Table_ID = " + _AD_Table_ID, _trx);
bool hasCols = false;
DataSet dsDestCols = DB.ExecuteDataset("SELECT ColumnName, AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, _trx);
if (dsDestCols != null && dsDestCols.Tables[0].Rows.Count > 0)
{
hasCols = true;
}
// loop through all columns
foreach (int columnID in ColIDs)
{
bool createNew = true;
// object of Column from source table (Master Table)
MColumn sCol = new MColumn(GetCtx(), columnID, _trx);
// check if source column is not Virtual Column, proceed in that case only
if (!sCol.IsVirtualColumn())
{
DataRow[] dr = null;
if (hasCols)
{
dr = dsDestCols.Tables[0].Select("ColumnName = '" + sCol.GetColumnName() + "'");
if (dr.Length > 0)
{
createNew = false;
}
}
// Version Column object
MColumn colVer = null;
int AD_Col_ID = 0;
// if column not present in Version table then create new
if (createNew)
{
colVer = new MColumn(GetCtx(), AD_Col_ID, _trx);
}
// if column already present and user pressed sync button on same column of Master table
// then create object of existing column (in case of change in any column fields)
else if (!createNew && (_AD_Column_ID == columnID))
{
AD_Col_ID = Util.GetValueOfInt(dr[0]["AD_Column_ID"]);
colVer = new MColumn(GetCtx(), Util.GetValueOfInt(dr[0]["AD_Column_ID"]), _trx);
}
if (colVer != null)
{
sCol.CopyTo(colVer);
if (AD_Col_ID > 0)
{
colVer.SetAD_Column_ID(AD_Col_ID);
}
colVer.SetExport_ID(null);
colVer.SetAD_Table_ID(Ver_AD_Table_ID);
// set key column to false
colVer.SetIsKey(false);
// check if source column is key column
// then set Restrict Constraint and set Reference as Table Direct
if (sCol.IsKey())
{
colVer.SetConstraintType("R");
colVer.SetAD_Reference_ID(19);
}
//if (sCol.IsKey())
// colVer.SetIsParent(true);
//else
colVer.SetIsParent(false);
colVer.SetIsMaintainVersions(false);
colVer.SetIsMandatory(false);
colVer.SetIsMandatoryUI(false);
if (!colVer.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Version Column not created";
}
log.Log(Level.SEVERE, "Version Column not created :: " + sCol.GetColumnName() + " :: " + error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "VersionColNotCreated"));
}
else
{
VerTableColID = colVer.GetAD_Column_ID();
}
}
}
}
// Get one column to sync table in database from Version Table
if (VerTableColID <= 0)
{
VerTableColID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID = " + Ver_AD_Table_ID, null, _trx));
}
// Get newly Created Column
if (oldVerCol > 0)
{
VerTableColID = oldVerCol;
}
// Sync Version table in database
bool success = true;
retMsg = SyncVersionTable(tblVer, VerTableColID, out success);
// if any error and there is message in return then return and rollback transaction
if (!success && retMsg != "")
{
log.Log(Level.SEVERE, "Column not sync :: " + retMsg);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "ColumnNotSync"));
}
else
{
// if table has single key
if (tbl.IsSingleKey())
{
// get column names from parent table
string colNameStrings = GetColumnNameString(tbl.GetAD_Table_ID());
// get default columns string from Version Table columns list
string defColString = GetDefaultColString(colNameStrings);
// Insert data in version table from Master table
InsertVersionData(colNameStrings, defColString, tblVer.GetTableName());
}
// Cases where single key is not present in Master table
else
{
// Insert data in version table against Master Table
retMsg = InsertMKVersionData(tbl, tbl.GetKeyColumns(), tblVer);
if (retMsg != "")
{
log.Log(Level.SEVERE, "Data not Inserted :: " + retMsg);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "DataInsertionErrorMultikey"));
}
}
}
}
}
return(Msg.GetMsg(GetCtx(), "ProcessCompletedSuccessfully"));
}
/// <summary>
/// Process
/// </summary>
/// <returns>message</returns>
protected override String DoIt()
{
log.Info("C_BP_Group_ID=" + _C_BP_Group_ID + ", C_BPartner_ID=" + _C_BPartner_ID
+ ", DateAcct= " + _DateAcct_From + " - " + _DateAcct_To
+ ", C_AllocationHdr_ID=" + _C_AllocationHdr_ID);
_m_trx = Trx.Get("AllocReset");
int count = 0;
if (_C_AllocationHdr_ID != 0)
{
MAllocationHdr hdr = new MAllocationHdr(GetCtx(), _C_AllocationHdr_ID, _m_trx);
if (Delete(hdr))
{
count++;
}
_m_trx.Close();
return("@Deleted@ #" + count);
}
// Selection
StringBuilder sql = new StringBuilder("SELECT * FROM C_AllocationHdr ah "
+ "WHERE EXISTS (SELECT * FROM C_AllocationLine al "
+ "WHERE ah.C_AllocationHdr_ID=al.C_AllocationHdr_ID");
if (_C_BPartner_ID != 0)
{
sql.Append(" AND al.C_BPartner_ID=" + _C_BPartner_ID);
}
else if (_C_BP_Group_ID != 0 && _C_BP_Group_ID != -1)
{
sql.Append(" AND EXISTS (SELECT * FROM C_BPartner bp "
+ "WHERE bp.C_BPartner_ID=al.C_BPartner_ID AND bp.C_BP_Group_ID=" + _C_BP_Group_ID + ")");
}
else
{
sql.Append(" AND AD_Client_ID=" + GetCtx().GetAD_Client_ID());
}
if (_DateAcct_From != null)
{
sql.Append(" AND TRIM(ah.DateAcct) >=" + _DateAcct_From);
}
if (_DateAcct_To != null)
{
sql.Append(" AND TRIM(ah.DateAcct) <= " + _DateAcct_To);
}
// Do not delete Cash Trx
sql.Append(" AND al.C_CashLine_ID IS NULL)");
// Open Period
sql.Append(" AND EXISTS (SELECT * FROM C_Period p"
+ " INNER JOIN C_PeriodControl pc ON (p.C_Period_ID=pc.C_Period_ID AND pc.DocBaseType='CMA') "
+ "WHERE ah.DateAcct BETWEEN p.StartDate AND p.EndDate)");
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, _m_trx);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
Delete(new MAllocationHdr(GetCtx(), dr, _m_trx));
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString(), e);
if (idr != null)
{
idr.Close();
}
_m_trx.Rollback();
}
finally
{
dt = null;
if (idr != null)
{
idr.Close();
}
_m_trx.Close();
}
return("@Deleted@ #" + count);
}
/// <summary>
/// Is used to save data on movememt line
/// </summary>
/// <param name="mData"></param>
/// <returns></returns>
public String SaveMovementLine(List <Dictionary <string, string> > mData)
{
StringBuilder error = new StringBuilder();
bool isMoveFullContainer = false;
bool isMoveFullContainerQty = false;
Trx trx = Trx.GetTrx("Movement");
if (mData.Count > 0)
{
isMoveFullContainer = Util.GetValueOfBool(mData[0]["IsFullMoveContainer"]);
//to delete all the movement lines where MoveFullContainer is False
if (isMoveFullContainer)
{
DB.ExecuteQuery("DELETE FROM M_MovementLine WHERE M_Movement_ID = " + Util.GetValueOfInt(mData[0]["M_Movement_ID"]) + " AND MoveFullContainer= 'N' ", null, null);
}
// Lines not inserted, as movement line already has a full move container line.
if (!isMoveFullContainer)
{
if (Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT COUNT(MoveFullContainer) FROM M_MovementLine WHERE MoveFullContainer= 'Y' AND M_Movement_ID = "
+ Util.GetValueOfInt(mData[0]["M_Movement_ID"]), null, trx)) > 0)
{
trx.Close();
return(Msg.GetMsg(_ctx, "VIS_LinehaveFullContainer"));
}
}
// Get Line No
int lineNo = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(MAX(Line),0) AS DefaultValue FROM M_MovementLine WHERE M_Movement_ID="
+ Util.GetValueOfInt(mData[0]["M_Movement_ID"]), null, trx));
isMoveFullContainerQty = Util.GetValueOfBool(mData[0]["IsfullContainerQtyWise"]);
if (!isMoveFullContainer && !isMoveFullContainerQty)
{
goto moveFullContainer;
}
else if (isMoveFullContainer || isMoveFullContainerQty)
{
error.Clear();
error.Append(SaveMoveLinewithFullContainer(Util.GetValueOfInt(mData[0]["M_Movement_ID"]),
Util.GetValueOfInt(mData[0]["FromLocator"]),
Util.GetValueOfInt(mData[0]["FromContainer"]),
Util.GetValueOfInt(mData[0]["ToLocator"]),
Util.GetValueOfInt(mData[0]["ToContainer"]),
lineNo, isMoveFullContainerQty,
trx));
trx.Close();
return(error.ToString());
}
moveFullContainer:
MMovementLine moveline = null;
MProduct product = null;
int moveId = 0;
for (int i = 0; i < mData.Count; i++)
{
#region Quantity Only
MMovement move = new MMovement(_ctx, Util.GetValueOfInt(mData[i]["M_Movement_ID"]), null);
moveId = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(M_MovementLine_ID, 0) AS M_Movement_ID FROM M_MovementLine WHERE
M_Movement_ID = " + Util.GetValueOfInt(mData[i]["M_Movement_ID"]) +
@" AND M_Product_ID = " + Util.GetValueOfInt(mData[i]["M_Product_ID"]) +
@" AND NVL(M_AttributeSetInstance_ID, 0) = " + Util.GetValueOfInt(mData[i]["M_AttributeSetInstance_ID"]) +
@" AND M_Locator_ID = " + Util.GetValueOfInt(mData[i]["FromLocator"]) +
@" AND NVL(M_ProductContainer_ID, 0) = " + Util.GetValueOfInt(mData[i]["FromContainer"]) +
@" AND M_LocatorTo_ID = " + Util.GetValueOfInt(mData[i]["ToLocator"]) +
@" AND NVL(Ref_M_ProductContainerTo_ID, 0) = " + Util.GetValueOfInt(mData[i]["ToContainer"]) +
@" AND AD_Org_ID = " + move.GetAD_Org_ID()));
if (moveId > 0)
{
moveline = new MMovementLine(_ctx, moveId, trx);
}
else
{
moveline = new MMovementLine(_ctx, 0, trx);
}
if (moveId == 0)
{
#region Create new record of movement line
lineNo += 10;
moveline.SetAD_Client_ID(move.GetAD_Client_ID());
moveline.SetAD_Org_ID(move.GetAD_Org_ID());
moveline.SetM_Movement_ID(move.GetM_Movement_ID());
moveline.SetLine(lineNo);
moveline.SetM_Product_ID(Util.GetValueOfInt(mData[i]["M_Product_ID"]));
moveline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(mData[i]["M_AttributeSetInstance_ID"]));
moveline.SetC_UOM_ID(Util.GetValueOfInt(mData[i]["C_UOM_ID"]));
moveline.SetM_Locator_ID(Util.GetValueOfInt(mData[i]["FromLocator"]));
moveline.SetM_LocatorTo_ID(Util.GetValueOfInt(mData[i]["ToLocator"]));
moveline.SetM_ProductContainer_ID(Util.GetValueOfInt(mData[i]["FromContainer"]));
moveline.SetRef_M_ProductContainerTo_ID(Util.GetValueOfInt(mData[i]["ToContainer"]));
moveline.SetQtyEntered(Util.GetValueOfDecimal(mData[i]["MoveQty"]));
moveline.SetMovementQty(Util.GetValueOfDecimal(mData[i]["MoveQty"]));
moveline.SetMoveFullContainer(Util.GetValueOfBool(mData[i]["IsFullMoveContainer"]));
#endregion
}
else
{
#region Update record of movement line
moveline.SetQtyEntered(Decimal.Add(moveline.GetQtyEntered(), Util.GetValueOfDecimal(mData[i]["MoveQty"])));
moveline.SetMovementQty(Decimal.Add(moveline.GetMovementQty(), Util.GetValueOfDecimal(mData[i]["MoveQty"])));
moveline.SetMoveFullContainer(Util.GetValueOfBool(mData[i]["IsFullMoveContainer"]));
#endregion
}
if (!moveline.Save(trx))
{
#region Save error catch and rollback
product = MProduct.Get(_ctx, Util.GetValueOfInt(mData[i]["M_Product_ID"]));
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
_log.SaveError("Movement line not inserted through Move Container Form : ", pp.GetName());
if (String.IsNullOrEmpty(error.ToString()))
{
error.Append(Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
else
{
error.Append(" , " + Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
}
trx.Rollback();
#endregion
}
else
{
trx.Commit();
}
#endregion
}
}
trx.Close();
return(String.IsNullOrEmpty(error.ToString()) ? "VIS_SuccessFullyInserted" : error.ToString());
}
protected override string DoIt()
{
string status = "OK";
Trx trx = Trx.Get("CreateCalYearPeriod");
StringBuilder sql = new StringBuilder();
//GetTenantInfo
sql.Append(@"SELECT
periodstartfrommonth,
periodstartfromday,
periodendMonth,
periodendsAtDay,
period_openhistory,
period_openfuture
FROM c_acctschema
WHERE IsActive='Y'
AND IsActive='Y'
AND AD_Client_ID=" + GetCtx().GetAD_Client_ID());
DataSet ds = DB.ExecuteDataset(sql.ToString(), null);
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
return("SettingsNotFound");
}
try
{
try
{
MonthFrom = Convert.ToInt32(ds.Tables[0].Rows[0]["periodstartfrommonth"]);
}
catch { }
try
{
MonthTo = Convert.ToInt32(ds.Tables[0].Rows[0]["periodendMonth"]);
}
catch { }
//try
//{
// EntireYear = ds.Tables[0].Rows[0]["periodendMonth"].ToString().Equals("Y") ? 'Y' : 'N';
//}
//catch { }
try
{
DateFrom = Convert.ToInt32(ds.Tables[0].Rows[0]["periodstartfromday"]);
}
catch { }
try
{
DateTo = Convert.ToInt32(ds.Tables[0].Rows[0]["periodendsAtDay"]);
}
catch { }
//try
//{
// daysPast = Convert.ToInt32(ds.Tables[0].Rows[0]["period_openhistory"]);
//}
//catch { }
//try
//{
// dayFuture = Convert.ToInt32(ds.Tables[0].Rows[0]["period_openfuture"]);
//}
//catch { }
}
catch { }
string YearName = "";
bool isNextYear = false;
if (MonthFrom < MonthTo)
{
YearName = DateTime.Now.Year.ToString();
}
else
{
YearName = DateTime.Now.Year.ToString() + "-" + (DateTime.Now.Year + 1).ToString();
isNextYear = true;
}
sql.Clear();
sql.Append(@"SELECT C_Calendar_ID FROM C_Calendar
WHERE ISACTIVE='Y' AND AD_CLIENT_ID=" + GetCtx().GetAD_Client_ID() + @"
AND AD_ORG_ID=(SELECT AD_ORG_ID FROM AD_ORG WHERE NAME ='*' )");
int calendarID = 0;
try
{
calendarID = Convert.ToInt32(DB.ExecuteScalar(sql.ToString()));
}
catch { }
if (calendarID > 0)
{
sql.Clear();
sql.Append(@"SELECT C_YEAR_ID FROM C_YEAR WHERE ISACTIVE='Y' AND FiscalYear='" + YearName + "' AND C_CALENDAR_ID=" + calendarID);
int yearID = 0;
try
{
yearID = Convert.ToInt32(DB.ExecuteScalar(sql.ToString()));
}
catch { }
MYear year = new MYear(GetCtx(), yearID, trx);
year.SetC_Calendar_ID(calendarID);
year.SetFiscalYear(YearName);
year.SetIsActive(true);
year.SetAD_Org_ID(0);
if (!year.Save(trx))
{
status = "YearNotSaved";
}
if (!isNextYear)
{
for (int month = MonthFrom; month <= MonthTo; month++)
{
DateTime?start = null;
if (month == MonthFrom)
{
start = new DateTime(DateTime.Now.Year, month, DateFrom).Date;
}
else
{
start = new DateTime(DateTime.Now.Year, month, 1).Date;
}
String name = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month) + "-" + YearName;
//
int day = 0;
if (month == MonthTo)
{
day = DateTo;//TimeUtil.GetMonthLastDay(new DateTime(DateTime.Now.Year, month, DateTo)).Day;
}
else
{
day = TimeUtil.GetMonthLastDay(new DateTime(DateTime.Now.Year, month, 1)).Day;
}
DateTime end = new DateTime(DateTime.Now.Year, month, day).Date;
//
MPeriod period = new MPeriod(year, month, name, start, end);
if (!period.Save(trx)) // Creates Period Control
{
status = "PeriodNotSaved";
}
//if (EntireYear.Equals('Y'))//open Period for Entire Year
//{
// if (period.Get_ID() == 0)
// {
// continue;
// }
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
//}
//else
//{
// if (month == DateTime.Now.Month)
// {
// if (period.Get_ID() == 0)
// {
// continue;
// }
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
// }
//}
}
}
else
{
for (int month = MonthFrom; month < 13; month++)
{
DateTime?start = null;
if (month == MonthFrom)
{
start = new DateTime(DateTime.Now.Year, month, DateFrom).Date;
}
else
{
start = new DateTime(DateTime.Now.Year, month, 1).Date;
}
String name = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month) + "-" + DateTime.Now.Year.ToString();
//
int day = 0;
if (month == MonthTo)
{
day = DateTo;//TimeUtil.GetMonthLastDay(new DateTime(DateTime.Now.Year, month, DateTo)).Day;
}
else
{
day = TimeUtil.GetMonthLastDay(new DateTime(DateTime.Now.Year, month, 1)).Day;
}
DateTime end = new DateTime(DateTime.Now.Year, month, day).Date;
//
MPeriod period = new MPeriod(year, month, name, start, end);
if (!period.Save(trx)) // Creates Period Control
{
status = "PeriodNotSaved";
}
//if (EntireYear.Equals('Y'))//open Period for Entire Year
//{
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
//}
//else
//{
// if (month == DateTime.Now.Month)
// {
// if (period.Get_ID() == 0)
// {
// continue;
// }
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
// }
//}
}
for (int month = 1; month <= MonthTo; month++)
{
DateTime?start = null;
if (month == MonthFrom)
{
start = new DateTime(DateTime.Now.Year + 1, month, DateFrom).Date;
}
else
{
start = new DateTime(DateTime.Now.Year + 1, month, 1).Date;
}
String name = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month) + "-" + (DateTime.Now.Year + 1).ToString();
//
int day = 0;
if (month == MonthTo)
{
day = DateTo;// TimeUtil.GetMonthLastDay(new DateTime(DateTime.Now.Year, month, DateTo)).Day;
}
else
{
day = TimeUtil.GetMonthLastDay(new DateTime(DateTime.Now.Year + 1, month, 1)).Day;
}
DateTime end = new DateTime(DateTime.Now.Year + 1, month, day).Date;
//
MPeriod period = new MPeriod(year, month, name, start, end);
if (!period.Save(trx)) // Creates Period Control
{
status = "PeriodNotSaved";
}
//if (EntireYear.Equals('Y'))//open Period for Entire Year
//{
// if (period.Get_ID() == 0)
// {
// continue;
// }
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
//}
//else
//{
// if (month == DateTime.Now.Month)
// {
// if (period.Get_ID() == 0)
// {
// continue;
// }
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
// }
//}
}
}
}
//Open Pereiod for specified Past Days
// if (daysPast > 0)
// {
// DateTime PeriodEndDate = new DateTime(DateTime.Now.Year, MonthFrom, DateFrom);
// DateTime from = PeriodEndDate.AddDays(-daysPast);
// sql.Clear();
// sql.Append(@" SELECT C_Period_ID
// FROM C_period
// WHERE to_date(startdate,'dd-MM-yyyy') BETWEEN to_date('"+from+@"','dd-MM-yyyy') AND to_date('"+PeriodEndDate+@"','dd-MM-yyyy')
// or to_date(enddate,'dd-MM-yyyy') BETWEEN to_date('" + from + @"','dd-MM-yyyy') AND to_date('" + PeriodEndDate + @"','dd-MM-yyyy')");
// ds=null;
// ds=new DataSet();
// ds=DB.ExecuteDataset(sql.ToString());
// MPeriod period=null;
// if (ds != null)
// {
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// int periodID = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
// period = new MPeriod(GetCtx(), periodID, trx);
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
// }
// }
// }
//Open Pereiod for specified Future Days
// if (dayFuture > 0)
// {
// DateTime from=DateTime.Now;
// if (isNextYear)
// {
// from = new DateTime(DateTime.Now.Year + 1, MonthTo, DateTo);
// }
// else
// {
// from = new DateTime(DateTime.Now.Year , MonthTo, DateTo);
// }
// DateTime PeriodEndDate = from.AddDays(dayFuture);
// sql.Clear();
// sql.Append(@" SELECT C_Period_ID
// FROM C_period
// WHERE to_date(startdate,'dd-MM-yyyy') BETWEEN to_date('" + from + @"','dd-MM-yyyy') AND to_date('" + PeriodEndDate + @"','dd-MM-yyyy')
// or to_date(enddate,'dd-MM-yyyy') BETWEEN to_date('" + from + @"','dd-MM-yyyy') AND to_date('" + PeriodEndDate + @"','dd-MM-yyyy')");
// ds = null;
// ds = new DataSet();
// ds = DB.ExecuteDataset(sql.ToString());
// MPeriod period = null;
// if (ds != null)
// {
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// int periodID = Convert.ToInt32(ds.Tables[0].Rows[i][0]);
// period = new MPeriod(GetCtx(), periodID, trx);
// if (!OpenPeriod(period, trx))
// {
// status = "PeriodNotOpened";
// }
// }
// }
// }
if (status == "OK")
{
trx.Commit();
}
else
{
trx.Rollback();
}
trx.Close();
return(status);
}
public string DoIt(int recordID, Ctx ct, Trx trx, int CurrencyType_ID)
{
//Check Bank Response
// sql.Append(@"SELECT count(bd.VA009_BankResponse) FROM va009_batchlinedetails bd INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bd.va009_batchlines_id
// WHERE bl.va009_batch_id=" + recordID + " AND bd.VA009_BankResponse='IP' AND bd.AD_Client_ID = " + ct.GetAD_Client_ID() + " Group by bd.VA009_BankResponse ");
// countresponse = Util.GetValueOfInt(DB.ExecuteScalar(sql.ToString(), null,trx));
sql.Clear();
sql.Append(@"SELECT b.c_bankaccount_id, bl.c_bpartner_id, bld.c_currency_id, bld.c_invoice_id, bld.dueamt, bld.VA009_ConvertedAmt, bld.discountamt, bld.va009_batchlinedetails_ID , bl.va009_batchlines_id ,
bld.discountdate, inv.issotrx, inv.isreturntrx, bld.c_invoicepayschedule_id, bld.ad_org_id, bld.ad_client_id , doc.DocBaseType , bld.va009_paymentmethod_id , bl.VA009_DueAmount
FROM va009_batchlinedetails bld INNER JOIN va009_batchlines bl ON bl.va009_batchlines_id=bld.va009_batchlines_id
INNER JOIN va009_batch b ON b.va009_batch_id =bl.va009_batch_id INNER JOIN c_invoice inv ON inv.c_invoice_id = bld.c_invoice_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 NVL(bld.C_AllocationHdr_ID , 0) = 0 AND b.va009_batch_id =" + recordID);
if (IsBankresponse == "Y" && countresponse == 0)
{
sql.Append(" AND bld.va009_bankresponse='RE' ORDER BY bl.c_bpartner_id ASC ");
}
else if (IsBankresponse == "N")
{
sql.Append(" ORDER BY bld.va009_batchlines_id , bl.c_bpartner_id ASC ");
}
//else if (IsBankresponse == "Y" && countresponse > 0)
// return Msg.GetMsg(ct, "VA009_AllResponseNotAvailable");
DataSet ds = DB.ExecuteDataset(sql.ToString(), null, trx);
MVA009Batch _batch = new MVA009Batch(ct, recordID, trx);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
#region Consolidate = true
if (_batch.IsVA009_Consolidate() == true)
{
int c_currency_id = 0; int Bpartner_ID = 0; int C_Payment_ID = 0, batchline_id = 0, allocationHeader = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
#region Create View Allocation Header and line when the Due Amount on Batch line = 0
if (c_currency_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) &&
Bpartner_ID == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]) &&
batchline_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]) &&
Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_DueAmount"]) == 0)
{
MAllocationLine alloclne = new MAllocationLine(ct, 0, trx);
alloclne.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
alloclne.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
alloclne.SetC_AllocationHdr_ID(allocationHeader);
alloclne.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
alloclne.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
alloclne.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
alloclne.SetDateTrx(System.DateTime.Now.ToLocalTime());
alloclne.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
alloclne.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
if (!alloclne.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
// set Allocation ID on Batch Line Details
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_AllocationHdr_ID(allocationHeader);
batchLineDetails.Save();
}
}
else if (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_DueAmount"]) == 0)
{
MAllocationHdr allocHdr = new MAllocationHdr(ct, 0, trx);
allocHdr.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
allocHdr.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
allocHdr.SetDateAcct(System.DateTime.Now.ToLocalTime());
allocHdr.SetDateTrx(System.DateTime.Now.ToLocalTime());
allocHdr.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
allocHdr.SetDocStatus("DR");
allocHdr.SetDocAction("CO");
if (!allocHdr.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
if (!viewAllocationId.Contains(allocHdr.GetC_AllocationHdr_ID()))
{
viewAllocationId.Add(allocHdr.GetC_AllocationHdr_ID());
}
MAllocationLine alloclne = new MAllocationLine(ct, 0, trx);
alloclne.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
alloclne.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
alloclne.SetC_AllocationHdr_ID(allocHdr.GetC_AllocationHdr_ID());
alloclne.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
alloclne.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
alloclne.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
alloclne.SetDateTrx(System.DateTime.Now.ToLocalTime());
alloclne.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
alloclne.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
if (!alloclne.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
c_currency_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]);
Bpartner_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]);
batchline_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]);
allocationHeader = allocHdr.GetC_AllocationHdr_ID();
allocationDocumentNo += allocHdr.GetDocumentNo() + " , ";
// set Allocation ID on Batch Line
//batchLines = new MVA009BatchLines(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]), trx.trx);
//batchLineDetails.SetC_AllocationHdr_ID(allocationHeader);
//batchLines.Save();
// set Allocation ID on Batch Line Details
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_AllocationHdr_ID(allocationHeader);
batchLineDetails.Save();
}
}
}
#endregion
#region Create a new entry of payment Allocate against same payment and the condition
else if (c_currency_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) &&
Bpartner_ID == Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]) &&
batchline_id == Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]))
{
MPaymentAllocate PayAlocate = new MPaymentAllocate(ct, 0, trx);
PayAlocate.SetC_Payment_ID(C_Payment_ID);
PayAlocate.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
PayAlocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
//if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
//{
// PayAlocate.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
// PayAlocate.SetAmount(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
// PayAlocate.SetInvoiceAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
//}
//else
//{
PayAlocate.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
PayAlocate.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
PayAlocate.SetInvoiceAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]));
//}
PayAlocate.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
PayAlocate.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
PayAlocate.SetWriteOffAmt(0);
PayAlocate.SetOverUnderAmt(0);
if (!PayAlocate.Save())
{
msg = Msg.GetMsg(ct, "VA009_PymentAllocateNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_Payment_ID(C_Payment_ID);
batchLineDetails.Save();
}
}
#endregion
#region Create a new payment
else
{
MPayment _pay = new MPayment(ct, 0, trx);
int C_Doctype_ID = GetDocumnetType(Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]), ct);
_pay.SetC_DocType_ID(C_Doctype_ID);
_pay.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
_pay.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
_pay.SetDateAcct(System.DateTime.Now);
_pay.SetDateTrx(System.DateTime.Now);
_pay.SetC_BankAccount_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bankaccount_id"]));
_pay.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
_pay.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
_pay.SetC_ConversionType_ID(CurrencyType_ID);
_pay.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));
tenderType = Util.GetValueOfString(DB.ExecuteScalar(@"select VA009_PAYMENTBASETYPE from VA009_PAYMENTMETHOD where VA009_PAYMENTMETHOD_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"])));
if (tenderType == "K") // Credit Card
{
_pay.SetTenderType("C");
}
else if (tenderType == "D") // Direct Debit
{
_pay.SetTenderType("D");
}
else if (tenderType == "S") // Check
{
_pay.SetTenderType("K");
}
else if (tenderType == "T") // Direct Deposit
{
_pay.SetTenderType("A");
}
else
{
_pay.SetTenderType("A");
}
if (!_pay.Save())
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
if (!payment.Contains(_pay.GetC_Payment_ID()))
{
payment.Add(_pay.GetC_Payment_ID());
}
c_currency_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]);
Bpartner_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]);
C_Payment_ID = _pay.GetC_Payment_ID();
batchline_id = Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]);
paymentDocumentNo += _pay.GetDocumentNo() + " , ";
MPaymentAllocate PayAlocate = new MPaymentAllocate(ct, 0, trx);
PayAlocate.SetC_Payment_ID(C_Payment_ID);
PayAlocate.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
PayAlocate.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
//if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
//{
// PayAlocate.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
// PayAlocate.SetAmount(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
// PayAlocate.SetInvoiceAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
//}
//else
//{
PayAlocate.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
PayAlocate.SetAmount(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
PayAlocate.SetInvoiceAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]));
//}
PayAlocate.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
PayAlocate.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
PayAlocate.SetWriteOffAmt(0);
PayAlocate.SetOverUnderAmt(0);
if (!PayAlocate.Save())
{
msg = Msg.GetMsg(ct, "VA009_PymentAllocateNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
payment.Clear();
viewAllocationId.Clear();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_Payment_ID(_pay.GetC_Payment_ID());
batchLineDetails.Save();
batchLines = new MVA009BatchLines(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlines_id"]), trx);
batchLines.SetC_Payment_ID(_pay.GetC_Payment_ID());
batchLines.Save();
}
}
}
#endregion
}
// Complete the Consolidate Records of payment
for (int i = 0; i < payment.Count(); i++)
{
MPayment completePayment = new MPayment(ct, payment[i], trx);
if (completePayment.CompleteIt() == "CO")
{
completePayment.SetDocStatus("CO");
completePayment.SetDocAction("CL");
completePayment.Save();
}
}
// Complete the Consolidate Records of View allocation
for (int i = 0; i < viewAllocationId.Count(); i++)
{
MAllocationHdr completeAllocation = new MAllocationHdr(ct, viewAllocationId[i], trx);
if (completeAllocation.CompleteIt() == "CO")
{
completeAllocation.SetDocStatus("CO");
completeAllocation.SetDocAction("CL");
completeAllocation.Save();
}
}
}
#endregion
#region Consolidate = false
else if (_batch.IsVA009_Consolidate() == false)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MPayment _pay = new MPayment(ct, 0, trx);
int C_Doctype_ID = GetDocumnetType(Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]), ct);
_pay.SetC_DocType_ID(C_Doctype_ID);
_pay.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoice_id"]));
_pay.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_invoicepayschedule_id"]));
_pay.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]));
_pay.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]));
_pay.SetDateAcct(System.DateTime.Now);
_pay.SetDateTrx(System.DateTime.Now);
_pay.SetC_ConversionType_ID(CurrencyType_ID);
_pay.SetC_BankAccount_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bankaccount_id"]));
_pay.SetC_BPartner_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_bpartner_id"]));
//if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC")
//{
// _pay.SetDiscountAmt(-1 * Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
// _pay.SetPayAmt(-1 * Util.GetValueOfInt(ds.Tables[0].Rows[i]["dueamt"]));
//}
//else
//{
_pay.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["discountamt"]));
//_pay.SetPayAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["dueamt"]));
_pay.SetPayAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["VA009_ConvertedAmt"]));
//}
_pay.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]));
_pay.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"]));
tenderType = Util.GetValueOfString(DB.ExecuteScalar(@"select VA009_PAYMENTBASETYPE from VA009_PAYMENTMETHOD where VA009_PAYMENTMETHOD_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"])));
if (tenderType == "K") // Credit Card
{
_pay.SetTenderType("C");
}
else if (tenderType == "D") // Direct Debit
{
_pay.SetTenderType("D");
}
else if (tenderType == "S") // Check
{
_pay.SetTenderType("K");
}
else if (tenderType == "T") // Direct Deposit
{
_pay.SetTenderType("A");
}
else
{
_pay.SetTenderType("A");
}
if (!_pay.Save(trx))
{
msg = Msg.GetMsg(ct, "VA009_PymentNotSaved");
ValueNamePair ppE = VAdvantage.Logging.VLogger.RetrieveError();
SavePaymentBachLog(Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_client_id"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["ad_org_id"]),
recordID, ppE.ToString(), ct, trx);
trx.Rollback();
allocationDocumentNo = string.Empty;
paymentDocumentNo = string.Empty;
break;
}
else
{
paymentDocumentNo += _pay.GetDocumentNo() + " , ";
batchLineDetails = new MVA009BatchLineDetails(ct, Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_batchlinedetails_ID"]), trx);
batchLineDetails.SetC_Payment_ID(_pay.GetC_Payment_ID());
batchLineDetails.Save(trx);
if (_pay.CompleteIt() == "CO")
{
_pay.SetDocStatus("CO");
_pay.SetDocAction("CL");
_pay.Save(trx);
}
}
}
}
#endregion
}
else
{
return(msg = Msg.GetMsg(ct, "VA009_LinesNotAvailable"));
}
if (paymentDocumentNo != "" || allocationDocumentNo != "")
{
SaveRecordPaymentBachLog(_batch.GetAD_Client_ID(), _batch.GetAD_Org_ID(), recordID, paymentDocumentNo, allocationDocumentNo, ct, trx);
}
return(msg);
}
/** Generate Payment for Multi Cheque Details */
public string GenratePaymentLine(Ctx ctx, int Record_Id, int paymentDocumentTypeId, Trx trxName)
{
int stdprecision = 0;
Decimal surchargeAmt = Env.ZERO;
Decimal TaxAmt = Env.ZERO;
StringBuilder _sql = new StringBuilder();
MVA027PostDatedCheck _pdc = new MVA027PostDatedCheck(ctx, Record_Id, trxName);
String sql = "SELECT * FROM VA027_ChequeDetails WHERE VA027_PostDatedCheck_ID= " + Record_Id + " AND NVL(C_Payment_ID,0)=0 ORDER BY Va027_CheckDate";
DataSet _ds = new DataSet();
MPayment _payment = null;
_ds = DB.ExecuteDataset(sql.ToString(), null, trxName);
int _count = _ds.Tables[0].Rows.Count;
if (_ds != null && _ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < _ds.Tables[0].Rows.Count; i++)
{
MVA027ChequeDetails cd = new MVA027ChequeDetails(GetCtx(), Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), Get_Trx());
if (cd.IsVA027_DiscountingPDC())
{
if (Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_Payment_ID"]) == 0)
{
_sql.Clear();
_payment = new MPayment(ctx, 0, trxName);
_payment.SetAD_Client_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Client_ID"]));
_payment.SetAD_Org_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Org_ID"]));
if (c_BankAccount_ID == 0)
{
_payment.SetC_BankAccount_ID(_pdc.GetC_BankAccount_ID());
}
else
{
_payment.SetC_BankAccount_ID(c_BankAccount_ID);
}
_payment.SetDateTrx(_sysDate);
_payment.SetDateAcct(_sysDate); //cd.GetVA027_CheckDate());
_payment.SetDescription(_pdc.GetVA027_Description());
_payment.SetIsPrepayment(_pdc.IsVA027_IsPrepayment());
if (_pdc.GetC_Charge_ID() > 0)
{
_payment.SetC_Charge_ID(_pdc.GetC_Charge_ID());
}
_payment.SetVA027_DiscountingPDC(true);
if (_pdc.GetVA027_Payee() != null)
{
_payment.SetDescription(_pdc.GetVA027_Payee());
}
//added by arpit
if (_pdc.GetC_BPartner_ID() > 0)
{
_payment.SetC_BPartner_ID(_pdc.GetC_BPartner_ID());
if (_pdc.GetC_BPartner_Location_ID() > 0)
{
_payment.SetC_BPartner_Location_ID(_pdc.GetC_BPartner_Location_ID());
}
}
//end here
_payment.SetC_Tax_ID(_pdc.GetC_Tax_ID());
_payment.SetPayAmt(Math.Round(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), 2));
_payment.SetC_Currency_ID(_pdc.GetC_Currency_ID());
_payment.SetDiscountAmt(Math.Round(_pdc.GetVA027_DiscountAmt(), 2));
_payment.SetWriteOffAmt(Math.Round(_pdc.GetVA027_WriteoffAmt(), 2));
_payment.SetVA009_PaymentMethod_ID(_pdc.GetVA009_PaymentMethod_ID());
_payment.SetCheckNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_CheckNo"]));
_payment.SetCheckDate(Util.GetValueOfDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]));
_payment.SetValidMonths(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ValidMonth"]));
_payment.SetMicr(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_MICR"]));
_payment.SetAccountNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountNo"]));
_payment.SetA_Name(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountName"]));
_payment.SetPDCType(_pdc.GetPDCType());
//calculate Tax Amount
if (_pdc.GetC_Tax_ID() > 0)
{
sql = "SELECT StdPrecision FROM VA027_PostDatedCheck i INNER JOIN C_Currency c ON i.C_Currency_ID = c.C_Currency_ID " +
"WHERE VA027_PostDatedCheck_ID = " + _pdc.GetVA027_PostDatedCheck_ID();
stdprecision = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
MTax tax = new MTax(GetCtx(), _pdc.GetC_Tax_ID(), null);
if (tax.Get_ColumnIndex("Surcharge_Tax_ID") > 0 && tax.GetSurcharge_Tax_ID() > 0)
{
TaxAmt = tax.CalculateSurcharge(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision, out surchargeAmt);
}
else
{
TaxAmt = tax.CalculateTax(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision);
}
_payment.SetTaxAmount(TaxAmt);
_payment.Set_Value("SurchargeAmt", surchargeAmt);
}
//_sql.Append("select docbasetype,c_doctype_id from C_doctype where c_doctype_id=" + _pdc.GetC_DocType_ID());
//_docBaseType = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString()));
_sql.Clear();
//if (_docBaseType == "PDR")
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='ARR' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _docuType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
// _payment.SetC_DocType_ID(_docuType);
// _sql.Clear();
//}
//else
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='APP' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _documentType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString(), null, trxName));
// _payment.SetC_DocType_ID(_documentType);
// _sql.Clear();
//}
_payment.SetC_DocType_ID(paymentDocumentTypeId);
_exeStatus = _payment.GetVA009_ExecutionStatus();
_payment.SetVA009_ExecutionStatus(_exeStatus = "I");
if (_payment.Save(trxName))
{
//payment will get completed only if PaymentAllocate is generated.
if (GenratePaymentAllocate(GetCtx(), Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), _payment.GetC_Payment_ID(), Get_TrxName()))
{
_status = null;
_status = _payment.CompleteIt();
}
else
{
return("N");
}
}
else
{
return("F");
}
if (_status == "CO")
{
_payment.SetDocStatus("CO");
_payment.Set_Value("VA027_PostDatedCheck_ID", _pdc.GetVA027_PostDatedCheck_ID());
if (_payment.Save(trxName))
{
cqd = new MVA027ChequeDetails(ctx, Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), trxName);
cqd.SetC_Payment_ID(_payment.GetC_Payment_ID());
cqd.SetVA027_PaymentStatus("1");
if (!cqd.Save(trxName))
{
trxName.Rollback();
ValueNamePair pp = VLogger.RetrieveError();
log.Info("Error Saving Chequedetails : " + pp.GetValue() + " , Error Name : " + pp.GetName());
_msg = Msg.GetMsg(ctx, "ChequedetailsNotSaved") + ", " + (pp != null ? pp.GetName() : "");
return("N");
}
}
documentno += _payment.GetDocumentNo() + ",";
}
}
}
else
{
if (Convert.ToDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]) <= _sysDate)
{
if (Util.GetValueOfInt(_ds.Tables[0].Rows[i]["C_Payment_ID"]) == 0)
{
_sql.Clear();
_payment = new MPayment(ctx, 0, trxName);
_payment.SetAD_Client_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Client_ID"]));
_payment.SetAD_Org_ID(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["AD_Org_ID"]));
if (c_BankAccount_ID == 0)
{
_payment.SetC_BankAccount_ID(_pdc.GetC_BankAccount_ID());
}
else
{
_payment.SetC_BankAccount_ID(c_BankAccount_ID);
}
_payment.SetDateTrx(_sysDate);
_payment.SetDateAcct(cd.GetVA027_CheckDate());
_payment.SetDescription(_pdc.GetVA027_Description());
_payment.SetIsPrepayment(_pdc.IsVA027_IsPrepayment());
if (_pdc.GetC_Charge_ID() > 0)
{
_payment.SetC_Charge_ID(_pdc.GetC_Charge_ID());
}
if (_pdc.GetVA027_Payee() != null)
{
_payment.SetDescription(_pdc.GetVA027_Payee());
}
//added by arpit
if (_pdc.GetC_BPartner_ID() > 0)
{
_payment.SetC_BPartner_ID(_pdc.GetC_BPartner_ID());
if (_pdc.GetC_BPartner_Location_ID() > 0)
{
_payment.SetC_BPartner_Location_ID(_pdc.GetC_BPartner_Location_ID());
}
}
//end here
_payment.SetC_Tax_ID(_pdc.GetC_Tax_ID());
_payment.SetPayAmt(Math.Round(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), 2));
_payment.SetC_Currency_ID(_pdc.GetC_Currency_ID());
_payment.SetDiscountAmt(Math.Round(_pdc.GetVA027_DiscountAmt(), 2));
_payment.SetWriteOffAmt(Math.Round(_pdc.GetVA027_WriteoffAmt(), 2));
_payment.SetVA009_PaymentMethod_ID(_pdc.GetVA009_PaymentMethod_ID());
_payment.SetCheckNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_CheckNo"]));
_payment.SetCheckDate(Util.GetValueOfDateTime(_ds.Tables[0].Rows[i]["VA027_CheckDate"]));
_payment.SetValidMonths(Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ValidMonth"]));
_payment.SetMicr(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_MICR"]));
_payment.SetAccountNo(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountNo"]));
_payment.SetA_Name(Util.GetValueOfString(_ds.Tables[0].Rows[i]["VA027_AccountName"]));
_payment.SetPDCType(_pdc.GetPDCType());
//calculate Tax Amount
if (_pdc.GetC_Tax_ID() > 0)
{
sql = "SELECT StdPrecision FROM VA027_PostDatedCheck i INNER JOIN C_Currency c ON i.C_Currency_ID = c.C_Currency_ID " +
"WHERE VA027_PostDatedCheck_ID = " + _pdc.GetVA027_PostDatedCheck_ID();
stdprecision = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
MTax tax = new MTax(GetCtx(), _pdc.GetC_Tax_ID(), null);
if (tax.Get_ColumnIndex("Surcharge_Tax_ID") > 0 && tax.GetSurcharge_Tax_ID() > 0)
{
TaxAmt = tax.CalculateSurcharge(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision, out surchargeAmt);
}
else
{
TaxAmt = tax.CalculateTax(Util.GetValueOfDecimal(_ds.Tables[0].Rows[i]["VA027_ChequeAmount"]), true, stdprecision);
}
_payment.SetTaxAmount(TaxAmt);
_payment.Set_Value("SurchargeAmt", surchargeAmt);
}
//_sql.Append("select docbasetype,c_doctype_id from C_doctype where c_doctype_id=" + _pdc.GetC_DocType_ID());
//_docBaseType = Util.GetValueOfString(DB.ExecuteScalar(_sql.ToString()));
_sql.Clear();
//if (_docBaseType == "PDR")
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='ARR' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _docuType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString()));
// _payment.SetC_DocType_ID(_docuType);
// _sql.Clear();
//}
//else
//{
// _sql.Append("SELECT C_DocType_ID FROM C_DocType WHERE DocBaseType='APP' AND AD_Client_ID=" + ctx.GetAD_Client_ID());
// int _documentType = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString(), null, trxName));
// _payment.SetC_DocType_ID(_documentType);
// _sql.Clear();
//}
_payment.SetC_DocType_ID(paymentDocumentTypeId);
_exeStatus = _payment.GetVA009_ExecutionStatus();
_payment.SetVA009_ExecutionStatus(_exeStatus = "I");
if (_payment.Save(trxName))
{
//payment will get completed only if PaymentAllocate is generated.
if (GenratePaymentAllocate(GetCtx(), Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), _payment.GetC_Payment_ID(), Get_TrxName()))
{
_status = null;
_status = _payment.CompleteIt();
}
else
{
return("N");
}
}
else
{
return("F");
}
if (_status == "CO")
{
_payment.SetDocStatus("CO");
_payment.Set_Value("VA027_PostDatedCheck_ID", _pdc.GetVA027_PostDatedCheck_ID());
if (_payment.Save(trxName))
{
cqd = new MVA027ChequeDetails(ctx, Util.GetValueOfInt(_ds.Tables[0].Rows[i]["VA027_ChequeDetails_ID"]), trxName);
cqd.SetC_Payment_ID(_payment.GetC_Payment_ID());
cqd.SetVA027_PaymentStatus("1");
if (!cqd.Save(trxName))
{
trxName.Rollback();
ValueNamePair pp = VLogger.RetrieveError();
log.Info("Error Saving Chequedetails : " + pp.GetValue() + " , Error Name : " + pp.GetName());
_msg = Msg.GetMsg(ctx, "ChequedetailsNotSaved") + ", " + (pp != null ? pp.GetName() : "");
return("N");
}
}
documentno += _payment.GetDocumentNo() + ",";
}
}
else
{
return("E");
}
}
else
{
return("F");
}
}
}
}
if (Util.GetValueOfInt(DB.ExecuteScalar("SELECT Count(VA027_ChequeDetails_ID) From VA027_ChequeDetails Where VA027_PostDatedCheck_ID=" + Record_Id
+ " AND VA027_PaymentStatus!='1'", null, trxName)) == 0)
{
_pdc.SetVA027_PaymentStatus("1");
_pdc.SetVA027_PaymentGenerated(true);
_pdc.SetVA027_GeneratePayment("Y");
if (!_pdc.Save(trxName))
{
return("E");
}
}
return("Success");
}
protected override string DoIt()
{
tlLanguageID = GetRecord_ID();
if (tlLanguageID == 0)
{
return("NoLanguageFound");
}
Trx trx = Trx.Get("TRLF");
StringBuilder res = new StringBuilder();
StringBuilder sql = new StringBuilder();
////////Translate System Element
sql.Clear();
sql.Append("SELECT * FROM I_TLELEMENT_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
DataSet ds = DB.ExecuteDataset(sql.ToString());
string lang = Util.GetValueOfString(DB.ExecuteScalar("select I_TLLanguage From I_TLLanguage WHERE I_TLLAnguage_ID=" + tlLanguageID));
System.Data.SqlClient.SqlParameter[] param = null;
string desc = "";
string help = "";
if (ds != null)
{
X_I_TLElement_Trl tlEle = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlEle = new X_I_TLElement_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
sql.Clear();
param = new System.Data.SqlClient.SqlParameter[8];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlEle.GetName());
if (string.IsNullOrEmpty(tlEle.GetPrintName()))
{
param[1] = new System.Data.SqlClient.SqlParameter("@PrintName", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@PrintName", tlEle.GetPrintName());
}
if (string.IsNullOrEmpty(tlEle.GetDescription()))
{
param[2] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@description", tlEle.GetDescription());
}
help = tlEle.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[3] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[3] = new System.Data.SqlClient.SqlParameter("@help", help);
}
if (string.IsNullOrEmpty(tlEle.GetPO_Name()))
{
param[4] = new System.Data.SqlClient.SqlParameter("@POName", DBNull.Value);
}
else
{
param[4] = new System.Data.SqlClient.SqlParameter("@POName", tlEle.GetPO_Name());
}
if (string.IsNullOrEmpty(tlEle.GetPO_PrintName()))
{
param[5] = new System.Data.SqlClient.SqlParameter("@POPrintName", DBNull.Value);
}
else
{
param[5] = new System.Data.SqlClient.SqlParameter("@POPrintName", tlEle.GetPO_PrintName());
}
if (string.IsNullOrEmpty(tlEle.GetPO_Description()))
{
param[6] = new System.Data.SqlClient.SqlParameter("@POdesc", DBNull.Value);
}
else
{
param[6] = new System.Data.SqlClient.SqlParameter("@POdesc", tlEle.GetPO_Description());
}
if (string.IsNullOrEmpty(tlEle.GetPO_Help()))
{
param[7] = new System.Data.SqlClient.SqlParameter("@POhelp", DBNull.Value);
}
else
{
param[7] = new System.Data.SqlClient.SqlParameter("@POhelp", tlEle.GetPO_Help());
}
sql.Append(@"Update AD_ELEMENT_TRL
SET Name=@cname,
IsTranslated='Y',
PrintName=@PrintName,
Description=@description,
Help=@help,
PO_Name=@POName,
PO_PrintName=@POPrintName,
PO_Description=@POdesc,
PO_Help=@POhelp
WHERE AD_ELEMENT_ID=" + tlEle.GetAD_Element_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlEle.GetAD_Element_ID() + " ElementNotTranslated.");
break;
}
tlEle.SetIsTranslated(true);
tlEle.Save(trx);
//}
}
}
sql.Clear();
sql.Append("SELECT * FROM I_TLField_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLField_Trl tlField = null;
char isCentrallyMaintained = 'N';
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlField = new X_I_TLField_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
isCentrallyMaintained = 'N';
if (tlField.IsTranslateSystemElement())
{
isCentrallyMaintained = 'Y';
//Translate System Element trl
sql.Clear();
param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlField.GetName());
param[1] = new System.Data.SqlClient.SqlParameter("@PrintName", tlField.GetName());
sql.Append(@"Update AD_ELEMENT_TRL
SET Name=@cname,
PRINTNAME=@PrintName,
IsTranslated='Y'
WHERE AD_ELEMENT_ID=" + tlField.GetAD_Element_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlField.GetAD_Element_ID() + " SystemElementNotTranslated.");
break;
}
}
//else
//{
// Tarnslate Field Trl
sql.Clear();
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlField.GetName());
desc = tlField.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlField.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
sql.Append(@"Update AD_Field_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
help=@help
WHERE AD_Field_ID=" + tlField.GetAD_Field_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlField.GetAD_Field_ID() + " FieldNotTranslated.");
break;
}
sql.Clear();
sql.Append("UPDATE AD_Field SET IsCentrallyMaintained='" + isCentrallyMaintained + "' WHERE AD_Field_ID=" + tlField.GetAD_Field_ID());
DB.ExecuteQuery(sql.ToString(), null, trx);
tlField.SetIsTranslated(true);
tlField.Save(trx);
//}
}
}
/////Translate Message
sql.Clear();
sql.Append("SELECT * FROM I_TLMessage_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLMessage_Trl tlMsg = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlMsg = new X_I_TLMessage_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("@Msg", tlMsg.GetMsgText());
if (string.IsNullOrEmpty(tlMsg.GetMsgTip()))
{
param[1] = new System.Data.SqlClient.SqlParameter("@MsgTip", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@MsgTip", tlMsg.GetMsgTip());
}
sql.Clear();
sql.Append(@"Update AD_Message_TRL
SET MsgText=@Msg,
IsTranslated='Y',
MsgTip=@MsgTip
WHERE AD_Message_ID=" + tlMsg.GetAD_Message_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlMsg.GetAD_Message_ID() + " MessageNotTranslated.");
break;
}
tlMsg.SetIsTranslated(true);
tlMsg.Save(trx);
//}
}
}
///Translate WIndow
sql.Clear();
sql.Append("SELECT * FROM I_TLWindow_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLWindow_Trl tlWin = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlWin = new X_I_TLWindow_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlWin.GetName());
desc = tlWin.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlWin.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
sql.Clear();
sql.Append(@"Update AD_Window_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
Help=@help
WHERE AD_Window_ID=" + tlWin.GetAD_Window_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlWin.GetAD_Window_ID() + " WindowNotTranslated.");
break;
}
tlWin.SetIsTranslated(true);
tlWin.Save(trx);
//}
}
}
///Translate tab
sql.Clear();
sql.Append("SELECT * FROM I_TLtab_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLTab_Trl tlTab = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlTab = new X_I_TLTab_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[4];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlTab.GetName());
desc = tlTab.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlTab.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
if (string.IsNullOrEmpty(tlTab.GetCommitWarning()))
{
param[3] = new System.Data.SqlClient.SqlParameter("@cm", DBNull.Value);
}
else
{
param[3] = new System.Data.SqlClient.SqlParameter("@cm", tlTab.GetCommitWarning());
}
sql.Clear();
sql.Append(@"Update AD_Tab_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
Help=@help,
CommitWarning=@cm
WHERE AD_Tab_ID=" + tlTab.GetAD_Tab_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlTab.GetAD_Tab_ID() + " TabNotTranslated.");
break;
}
tlTab.SetIsTranslated(true);
tlTab.Save(trx);
//}
}
}
///Translate FieldGroup
sql.Clear();
sql.Append("SELECT * FROM I_TLFieldGroup_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLFieldGroup_Trl tlfg = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlfg = new X_I_TLFieldGroup_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[1];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlfg.GetName());
sql.Clear();
sql.Append(@"Update AD_FieldGroup_TRL
SET Name=@cname,
IsTranslated='Y'
WHERE AD_FieldGroup_ID=" + tlfg.GetAD_FieldGroup_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlfg.GetAD_FieldGroup_ID() + " FieldGroupNotTranslated.");
break;
}
tlfg.SetIsTranslated(true);
tlfg.Save(trx);
//}
}
}
///Translate process
sql.Clear();
sql.Append("SELECT * FROM I_TLProcess_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLProcess_Trl tlps = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlps = new X_I_TLProcess_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlps.GetName());
desc = tlps.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlps.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
sql.Clear();
sql.Append(@"Update AD_process_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
Help=@help
WHERE AD_process_ID=" + tlps.GetAD_Process_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlps.GetAD_Process_ID() + " ProcessNotTranslated.");
break;
}
tlps.SetIsTranslated(true);
tlps.Save(trx);
//}
}
}
///Translate Form
sql.Clear();
sql.Append("SELECT * FROM I_TLForm_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLForm_Trl tlps = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlps = new X_I_TLForm_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlps.GetName());
desc = tlps.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlps.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
sql.Clear();
sql.Append(@"Update AD_Form_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
Help=@help
WHERE AD_Form_ID=" + tlps.GetAD_Form_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlps.GetAD_Form_ID() + " FormNotTranslated.");
break;
}
tlps.SetIsTranslated(true);
tlps.Save(trx);
//}
}
}
///Translate task
sql.Clear();
sql.Append("SELECT * FROM I_TLTask_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLTask_Trl tlps = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlps = new X_I_TLTask_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlps.GetName());
desc = tlps.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlps.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
sql.Clear();
sql.Append(@"Update AD_Task_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
Help=@help
WHERE AD_Task_ID=" + tlps.GetAD_Task_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlps.GetAD_Task_ID() + " ProcessNotTranslated.");
break;
}
tlps.SetIsTranslated(true);
tlps.Save(trx);
//}
}
}
///Translate Workflow
sql.Clear();
sql.Append("SELECT * FROM I_TLWorkflow_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLWorkflow_Trl tlps = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlps = new X_I_TLWorkflow_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlps.GetName());
desc = tlps.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlps.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
sql.Clear();
sql.Append(@"Update AD_Workflow_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
Help=@help
WHERE AD_Workflow_ID=" + tlps.GetAD_Workflow_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlps.GetAD_Workflow_ID() + " WorkflowNotTranslated.");
break;
}
tlps.SetIsTranslated(true);
tlps.Save(trx);
//}
}
}
///Translate WF_Node
sql.Clear();
sql.Append("SELECT * FROM I_TLWF_Node_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLWF_Node_Trl tlps = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlps = new X_I_TLWF_Node_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlps.GetName());
desc = tlps.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
help = tlps.GetHelp();
if (string.IsNullOrEmpty(help))
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", DBNull.Value);
}
else
{
param[2] = new System.Data.SqlClient.SqlParameter("@help", help);
}
sql.Clear();
sql.Append(@"Update AD_WF_Node_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description,
Help=@help
WHERE AD_WF_Node_ID=" + tlps.GetAD_WF_Node_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlps.GetAD_WF_Node_ID() + " WF_NodeflowNotTranslated.");
break;
}
tlps.SetIsTranslated(true);
tlps.Save(trx);
//}
}
}
///Translate Menu
sql.Clear();
sql.Append("SELECT * FROM I_TLMenu_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLMenu_Trl tlps = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlps = new X_I_TLMenu_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlps.GetName());
desc = tlps.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
sql.Clear();
sql.Append(@"Update AD_Menu_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description
WHERE AD_Menu_ID=" + tlps.GetAD_Menu_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlps.GetAD_Menu_ID() + " MenuNotTranslated.");
break;
}
tlps.SetIsTranslated(true);
tlps.Save(trx);
//}
}
}
///Translate Ref_List
sql.Clear();
sql.Append("SELECT * FROM I_TLRef_List_trl WHERE IsContinueTranslation='Y' AND I_TLLanguage_ID=" + tlLanguageID);
ds = DB.ExecuteDataset(sql.ToString());
if (ds != null)
{
X_I_TLRef_List_Trl tlps = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
tlps = new X_I_TLRef_List_Trl(GetCtx(), ds.Tables[0].Rows[i], null);
param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("@cname", tlps.GetName());
desc = tlps.GetDescription();
if (string.IsNullOrEmpty(desc))
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", DBNull.Value);
}
else
{
param[1] = new System.Data.SqlClient.SqlParameter("@description", desc);
}
sql.Clear();
sql.Append(@"Update AD_Ref_List_TRL
SET Name=@cname,
IsTranslated='Y',
Description=@description
WHERE AD_Ref_List_ID=" + tlps.GetAD_Ref_List_ID() + " AND AD_LANGUAGE='" + lang + "'");
if (DB.ExecuteQuery(sql.ToString(), param, trx) == -1)
{
res.Append(tlps.GetAD_Ref_List_ID() + " Ref_ListNotTranslated.");
break;
}
tlps.SetIsTranslated(true);
tlps.Save(trx);
//}
}
}
if (res.Length == 0)
{
res.Append("DONE");
trx.Commit();
}
else
{
trx.Rollback();
}
trx.Close();
return(res.ToString());
//throw new NotImplementedException();
}
/// <summary>
/// Starts the process by calling the required class at run time.
/// </summary>
/// <returns>Returs ture or false on the successfull calling of the proecss</returns>
private bool StartProcess()
{
//_trx = Trx.Get("ServerProcess", true);
//log.Fine(_pi.ToString());
try
{
string className = _pi.GetClassName().Replace("org.compiere.process", "VAdvantage.Process");
className = className.Replace("org.compiere.wf", "VAdvantage.WF");
className = className.Replace("org.compiere.report", "VAdvantage.Report");
className = className.Replace("org.compiere.install", "VAdvantage.Install");
className = className.Replace("org.compiere.print", "VAdvantage.Print");
className = className.Replace("org.compiere.cmrp.process", "ViennaAdvantage.CMRP.Process");
className = className.Replace("org.compiere.cmfg.process", "ViennaAdvantage.CMFG.Process");
className = className.Replace("org.compiere.cwms.process", "ViennaAdvantage.CWMS.Process");
className = className.Replace("org.compiere.cm", "VAdvantage.CM");
/*Customization Process */
Type type = null;
string cName = _pi.GetTitle();
type = Utility.ModuleTypeConatiner.GetClassType(className, cName);
if (type == null)
{
//MessageBox.Show("no Type");
}
if (type.IsClass)
{
ProcessEngine.ProcessCall oClass = (ProcessEngine.ProcessCall)Activator.CreateInstance(type);
if (oClass == null)
{
return(false);
}
else
{
//oClass.StartProcess(_ctx, _pi, _trx);
oClass.StartProcess(_ctx, _pi, _trx);
}
if (_trx != null)
{
_trx.Commit();
//log.Fine("Commit " + _trx.ToString());
_trx.Close();
}
}
}
catch
{
if (_trx != null)
{
_trx.Rollback();
//log.Fine("Rollback " + _trx.ToString());
_trx.Close();
}
_pi.SetSummary("Error starting Class " + _pi.GetClassName().Replace("org.compiere.process", "VAdvantage.Process"), true);
//log.Log(Level.SEVERE, _pi.GetClassName(), ex);
}
return(!_pi.IsError());
}
/// <summary>
/// is used to save data in case of Full move container / full qty move
/// </summary>
/// <param name="movementId">movement header refernce</param>
/// <param name="fromLocatorId">From Locator - from where we have to move product</param>
/// <param name="fromContainerId">From Container - from which container, we have to move product</param>
/// <param name="toLocatorId">To Locator - where we are moving product</param>
/// <param name="toContainerId">To container - in which container we are moving product</param>
/// <param name="lineNo"></param>
/// <param name="isMoveFullContainerQty">Is Container also move with Product</param>
/// <param name="trx">Self created Trx</param>
/// <returns>Message : lines inserted or not</returns>
public String SaveMoveLinewithFullContainer(int movementId, int fromLocatorId, int fromContainerId, int toLocatorId, int toContainerId, int lineNo, bool isMoveFullContainerQty, Trx trx)
{
MMovement movement = new MMovement(_ctx, movementId, trx);
string childContainerId = null;
StringBuilder error = new StringBuilder();
// Get Path upto selected container
string sql = @"SELECT sys_connect_by_path(m_productcontainer_id,'->') tree
FROM m_productcontainer
WHERE m_productcontainer_id = " + fromContainerId + @"
START WITH ref_m_container_id IS NULL CONNECT BY prior m_productcontainer_id = ref_m_container_id
ORDER BY tree ";
string pathContainer = Util.GetValueOfString(DB.ExecuteScalar(sql, null, trx));
// child records with Parent Id
if (!isMoveFullContainerQty)
{
sql = @"SELECT tree, m_productcontainer_id FROM
(SELECT sys_connect_by_path(m_productcontainer_id,'->') tree , m_productcontainer_id
FROM m_productcontainer
START WITH ref_m_container_id IS NULL
CONNECT BY prior m_productcontainer_id = ref_m_container_id
ORDER BY tree
)
WHERE tree LIKE ('" + pathContainer + "%') ";
DataSet ds = DB.ExecuteDataset(sql, null, trx);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (String.IsNullOrEmpty(childContainerId))
{
childContainerId = Util.GetValueOfString(ds.Tables[0].Rows[i]["m_productcontainer_id"]);
}
else
{
childContainerId += "," + Util.GetValueOfString(ds.Tables[0].Rows[i]["m_productcontainer_id"]);
}
}
}
ds.Dispose();
}
else
{
childContainerId = fromContainerId.ToString();
}
// check is same container already moved to another container
// Ex :: p1 -> c1 and p1 -> c2
// OR also check -- is any other container is moving into exist target container
// Ex :: p1 -> c1 and p2 -> p1
if (!isMoveFullContainerQty)
{
if (!IsContainerMoved(movementId, pathContainer, childContainerId, toContainerId, trx))
{
return(Msg.GetMsg(_ctx, "VIS_AlreadyMoved"));
}
}
// not to move Parent container to its child container
if (toContainerId > 0 && childContainerId.Contains(toContainerId.ToString()))
{
//Parent cant be Move to its own child
return(Msg.GetMsg(_ctx, "VIS_cantMoveParentTochild"));
}
// Get All records of Parent Container and child container
sql = @"SELECT * FROM (
SELECT p.M_PRODUCT_ID, p.NAME, p.C_UOM_ID, u.Name AS UomName, t.M_ATTRIBUTESETINSTANCE_ID, t.M_ProductContainer_ID,
SUM(t.ContainerCurrentQty) keep (dense_rank last ORDER BY t.MovementDate, t.M_Transaction_ID) AS ContainerCurrentQty
FROM M_Transaction t
INNER JOIN M_Product p ON p.M_Product_ID = t.M_Product_ID
INNER JOIN C_UOM u ON u.C_UOM_ID = p.C_UOM_ID
WHERE t.IsActive = 'Y' AND NVL(t.M_ProductContainer_ID, 0) IN ( " + childContainerId +
@" ) AND t.MovementDate <=" + GlobalVariable.TO_DATE(movement.GetMovementDate(), true) + @"
AND t.M_Locator_ID = " + fromLocatorId + @"
AND t.AD_Client_ID = " + movement.GetAD_Client_ID() + @"
GROUP BY p.M_PRODUCT_ID, p.NAME, p.C_UOM_ID, u.Name, t.M_ATTRIBUTESETINSTANCE_ID, t.M_ProductContainer_ID
) t WHERE ContainerCurrentQty <> 0 ";
DataSet dsRecords = DB.ExecuteDataset(sql, null, trx);
if (dsRecords != null && dsRecords.Tables.Count > 0 && dsRecords.Tables[0].Rows.Count > 0)
{
int movementlineId = 0;
MMovementLine moveline = null;
MProduct product = null;
for (int i = 0; i < dsRecords.Tables[0].Rows.Count; i++)
{
movementlineId = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(M_MovementLine_ID, 0) AS M_Movement_ID FROM M_MovementLine WHERE
M_Movement_ID = " + Util.GetValueOfInt(movementId) +
@" AND M_Product_ID = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"]) +
@" AND NVL(M_AttributeSetInstance_ID, 0) = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]) +
@" AND M_Locator_ID = " + Util.GetValueOfInt(fromLocatorId) +
@" AND NVL(M_ProductContainer_ID, 0) = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]) +
@" AND M_LocatorTo_ID = " + Util.GetValueOfInt(toLocatorId) +
@" AND NVL(Ref_M_ProductContainerTo_ID, 0) = " + toContainerId +
@" AND AD_Org_ID = " + movement.GetAD_Org_ID()));
if (movementlineId > 0)
{
moveline = new MMovementLine(_ctx, movementlineId, trx);
}
else
{
moveline = new MMovementLine(_ctx, 0, trx);
}
if (movementlineId == 0)
{
#region Create new record of movement line
lineNo += 10;
moveline.SetAD_Client_ID(movement.GetAD_Client_ID());
moveline.SetAD_Org_ID(movement.GetAD_Org_ID());
moveline.SetM_Movement_ID(movement.GetM_Movement_ID());
moveline.SetLine(lineNo);
moveline.SetM_Product_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"]));
moveline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
moveline.SetC_UOM_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["C_UOM_ID"]));
moveline.SetM_Locator_ID(fromLocatorId);
moveline.SetM_LocatorTo_ID(toLocatorId);
moveline.SetM_ProductContainer_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]));
moveline.SetRef_M_ProductContainerTo_ID(toContainerId);
moveline.SetQtyEntered(Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"]));
moveline.SetMovementQty(Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"]));
moveline.SetMoveFullContainer(isMoveFullContainerQty ? false : true);
// when move full container, then only need to update IsParentMove and Target container (which represent - to which container we are moving)
// and set true value on those line which container are moving, not on its child container
if (!isMoveFullContainerQty)
{
moveline.SetIsParentMove(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]) == fromContainerId ? true : false);
moveline.SetTargetContainer_ID(fromContainerId);
}
#endregion
}
else
{
#region Update record of movement line
moveline.SetQtyEntered(Decimal.Add(moveline.GetQtyEntered(), Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"])));
moveline.SetMovementQty(Decimal.Add(moveline.GetMovementQty(), Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"])));
moveline.SetMoveFullContainer(true);
#endregion
}
if (!moveline.Save(trx))
{
#region Save error catch and rollback
product = MProduct.Get(_ctx, Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"]));
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
_log.SaveError("Movement line not inserted through Move Container Form : ", pp.GetName());
if (String.IsNullOrEmpty(error.ToString()))
{
error.Append(Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
else
{
error.Append(" , " + Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName());
}
}
trx.Rollback();
#endregion
}
else
{
trx.Commit();
}
}
}
else
{
return(Msg.GetMsg(_ctx, "VIS_ContainerhaveNoRecord"));
}
return(String.IsNullOrEmpty(error.ToString()) ? "VIS_SuccessFullyInserted" : error.ToString());
}
// // Change By Arpit to Create Parameters24th of August,2017
//On the Basis of User defined % for each quantity of Product to verify
public static void CreateParameters(List <int> _ProductList, List <int> _ProductQty, int M_MoveConfirm_ID, int VA010_QUalityPlan_ID, int CurrentQty, List <int> M_MoveConfirmLine_ID, Ctx ctx, Trx Trx_Name)
{
StringBuilder _sql = new StringBuilder();
DataSet _ds = null;
decimal _qtyPercentToVerify = 0;
decimal _qtyFrom, _qtyTo, _qtyPercent = 0;
try
{
_sql.Clear();
_sql.Append(@"SELECT NVL(VA010_PercentQtyToVerify,0)VA010_PercentQtyToVerify,
NVL(VA010_ReceiptQtyFrom,0) VA010_ReceiptQtyFrom,
NVL(VA010_ReceiptQtyTo,0) VA010_ReceiptQtyTo FROM VA010_CheckingQty
WHERE IsActive='Y' AND VA010_Qualityplan_ID=" + VA010_QUalityPlan_ID + " AND AD_Client_ID=" + ctx.GetAD_Client_ID());
_ds = DB.ExecuteDataset(_sql.ToString(), null, Trx_Name);
if (_ds != null && _ds.Tables[0].Rows.Count > 0)
{
for (Int32 k = 0; k < _ds.Tables[0].Rows.Count; k++)
{
_qtyFrom = Util.GetValueOfDecimal(_ds.Tables[0].Rows[k]["VA010_ReceiptQtyFrom"]);
_qtyTo = Util.GetValueOfDecimal(_ds.Tables[0].Rows[k]["VA010_ReceiptQtyTo"]);
_qtyPercent = Util.GetValueOfDecimal(_ds.Tables[0].Rows[k]["VA010_PercentQtyToVerify"]);
if (CurrentQty >= _qtyFrom && _qtyTo == 0)
{
_qtyPercentToVerify = _qtyPercent;
k = _ds.Tables[0].Rows.Count;
}
else if (CurrentQty >= _qtyFrom && CurrentQty <= _qtyTo)
{
_qtyPercentToVerify = _qtyPercent;
k = _ds.Tables[0].Rows.Count;
}
else
{
_qtyPercentToVerify = 100;
}
}
}
else
{
_qtyPercentToVerify = 100;
}
_sql.Clear();
_sql.Append(@"SELECT VA010_QualityParameters_ID, VA010_TestPrmtrList_ID FROM va010_AssgndParameters WHERE"
+ " VA010_QualityPlan_ID=" + VA010_QUalityPlan_ID + " AND IsActive='Y'");
_ds.Clear();
_ds = DB.ExecuteDataset(_sql.ToString(), null, Trx_Name);
int _qty = 0;
if (_ds != null)
{
if (_ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < _ProductList.Count; i++)
{
_qty = 0;
_qty = (int)Math.Round((_ProductQty[i] * _qtyPercentToVerify) / 100, MidpointRounding.AwayFromZero);
if (_qty == 0)
{
_qty = _ProductQty[i];
}
if (_qty > _ProductQty[i])
{
_qty = _ProductQty[i];
}
for (int j = 0; j < _ds.Tables[0].Rows.Count; j++)
{
//Created Table object because not to use Mclass of Quality Control Module in our Base
MTable table = MTable.Get(ctx, "VA010_MoveConfParameters");
PO pos = table.GetPO(ctx, 0, Trx_Name);
pos.Set_ValueNoCheck("M_Product_ID", Util.GetValueOfInt(_ProductList[i]));
pos.Set_ValueNoCheck("VA010_QualityParameters_ID", Util.GetValueOfInt(_ds.Tables[0].Rows[j]["VA010_QualityParameters_ID"]));
pos.Set_ValueNoCheck("M_MovementLineConfirm_ID", Util.GetValueOfInt(M_MoveConfirmLine_ID[i]));
pos.Set_ValueNoCheck("VA010_TestPrmtrList_ID", Util.GetValueOfInt(_ds.Tables[0].Rows[j]["VA010_TestPrmtrList_ID"]));
pos.Set_ValueNoCheck("VA010_QuantityToVerify", Util.GetValueOfDecimal(_qty));
pos.Set_ValueNoCheck("AD_Client_ID", ctx.GetAD_Client_ID());
pos.Set_ValueNoCheck("AD_Org_ID", ctx.GetAD_Org_ID());
if (pos.Save(Trx_Name))
{
;
}
else
{
Trx_Name.Rollback();
Trx_Name.Close();
}
}
DB.ExecuteQuery(" UPDATE M_MovementLineConfirm SET VA010_QualCheckMark ='Y' WHERE M_MovementLineConfirm_ID=" + M_MoveConfirmLine_ID[i], null, Trx_Name);
}
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, _sql.ToString(), ex);
}
finally
{
_sql.Clear();
_ds.Dispose();
_qtyPercentToVerify = _qtyFrom = _qtyTo = _qtyPercent = 0;
}
}
/// <summary>
/// Starts the process
/// </summary>
/// <param name="ctx">context</param>
/// <param name="pi">ProcessInfo object</param>
/// <returns></returns>
public bool StartProcess(Ctx ctx, ProcessInfo pi, Trx trx)
{
// Preparation
_pi = pi;
PrepareCtx(ctx);
//ctxContext = ctx == null ? Utility.Env.GetCtx() : ctx;
_trx = trx;
bool localTrx = _trx == null;
if (localTrx)
{
_trx = Trx.GetTrx("SvrProcess");
}
//trx = SqlExec.ExecuteQuery.GerServerTransaction();
String msg = null;
bool success = true;
try
{
Lock();
Prepare();
msg = DoIt();
}
catch (Exception e)
{
msg = e.Message;
if (msg == null)
{
msg = e.ToString();
}
if (e.Message != null)
{
log.Log(Level.SEVERE, msg);
}
else if (VLogMgt.IsLevelFiner())
{
log.Log(Level.WARNING, msg);
}
else
{
log.Warning(msg);
}
success = false;
}
if (localTrx && _trx != null)
{
if (success)
{
_trx.Commit();
}
else
{
_trx.Rollback();
}
_trx.Close();
_trx = null;
}
// Parse Variables
msg = Utility.Msg.ParseTranslation(ctx, msg);
_pi.SetSummary(msg, !success);
ProcessInfoUtil.SaveLogToDB(_pi);
Unlock();
return(success);
}
/// <summary>
/// Get System Elements for Default Columns
/// </summary>
/// <param name="VerTblName">Table Name</param>
public string GetSystemElements(string VerTblName)
{
// check if count in list is equal to default version columns
if (_listDefVerElements.Count == listDefVerCols.Count)
{
return("");
}
// Clear values from list
_listDefVerElements.Clear();
// check if Primary key column is present in Columns list, if not present then add Primary Key column
if (!listDefVerCols.Contains(VerTblName + "_ID"))
{
listDefVerCols.Add(VerTblName + "_ID");
}
// Create comma separated string of all default version columns
string DefSysEle = string.Join(",", listDefVerCols
.Select(x => string.Format("'{0}'", x)));
// Get System Elements and Column Names for all Version table columns
DataSet dsDefVerCols = DB.ExecuteDataset("SELECT AD_Element_ID, ColumnName FROM AD_Element WHERE ColumnName IN (" + DefSysEle + ")", null, _trx);
if (dsDefVerCols != null && dsDefVerCols.Tables[0].Rows.Count > 0)
{
// loop through all columns of version table to get System Elements
// if not found then create new
for (int i = 0; i < listDefVerCols.Count; i++)
{
DataRow[] drSysEle = dsDefVerCols.Tables[0].Select("ColumnName='" + listDefVerCols[i] + "'");
if (drSysEle.Length > 0)
{
if (!_listDefVerElements.Contains(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"])))
{
_listDefVerElements.Add(Util.GetValueOfInt(drSysEle[0]["AD_Element_ID"]));
}
if (listDefVerCols[i] == VerTblName + "_ID")
{
listDefVerRef.Add(13);
}
}
else
{
M_Element ele = new M_Element(GetCtx(), 0, _trx);
ele.SetAD_Client_ID(0);
ele.SetAD_Org_ID(0);
ele.SetName(listDefVerCols[i]);
ele.SetColumnName(listDefVerCols[i]);
ele.SetPrintName(listDefVerCols[i]);
if (!ele.Save())
{
ValueNamePair vnp = VLogger.RetrieveError();
string error = "";
if (vnp != null)
{
error = vnp.GetName();
if (error == "" && vnp.GetValue() != null)
{
error = vnp.GetValue();
}
}
if (error == "")
{
error = "Error in creating System Element";
}
log.Log(Level.SEVERE, error);
_trx.Rollback();
return(Msg.GetMsg(GetCtx(), "ElementNotSaved"));
}
else
{
_listDefVerElements.Add(ele.GetAD_Element_ID());
if (ele.GetColumnName() == VerTblName + "_ID")
{
listDefVerRef.Add(13);
}
}
}
}
}
return("");
}
/// <summary>
/// Save Data
/// </summary>
public void SavePaymentData(List <Dictionary <string, string> > rowsPayment, List <Dictionary <string, string> > rowsCash, List <Dictionary <string, string> > rowsInvoice, string currency,
bool isCash, int _C_BPartner_ID, int _windowNo, string payment, DateTime DateTrx, string applied, string discount, string writeOff, string open)
{
// fixed fields
int AD_Client_ID = ctx.GetContextAsInt(_windowNo, "AD_Client_ID");
int AD_Org_ID = ctx.GetContextAsInt(_windowNo, "AD_Org_ID");
int C_BPartner_ID = _C_BPartner_ID;
int C_Order_ID = 0;
int C_CashLine_ID = 0;
//DateTime? DateTrx = Util.GetValueOfDateTime(vdtpDateField.GetValue());
int C_Currency_ID = Convert.ToInt32(currency);
//
if (AD_Org_ID == 0)
{
//Classes.ShowMessage.Error("Org0NotAllowed", null);
return;
}
//
// log.Config("Client=" + AD_Client_ID + ", Org=" + AD_Org_ID
// + ", BPartner=" + C_BPartner_ID + ", Date=" + DateTrx);
Trx trx = Trx.Get(Trx.CreateTrxName("AL"), true);
/**
* Generation of allocations: amount/discount/writeOff
* - if there is one payment -- one line per invoice is generated
* with both the Invoice and Payment reference
* Pay=80 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#1 Inv#1
* or
* Pay=160 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#1 Inv#1
* Pay=160 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#1 Inv#2
*
* - if there are multiple payment lines -- the amounts are allocated
* starting with the first payment and payment
* Pay=60 Inv=100 Disc=10 WOff=10 => 60/10/10 Pay#1 Inv#1
* Pay=100 Inv=100 Disc=10 WOff=10 => 20/0/0 Pay#2 Inv#1
* Pay=100 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#2 Inv#2
*
* - if you apply a credit memo to an invoice
* Inv=10 Disc=0 WOff=0 => 10/0/0 Inv#1
* Inv=-10 Disc=0 WOff=0 => -10/0/0 Inv#2
*
* - if you want to write off a (partial) invoice without applying,
* enter zero in applied
* Inv=10 Disc=1 WOff=9 => 0/1/9 Inv#1
* Issues
* - you cannot write-off a payment
*/
// Payment - Loop and Add them to paymentList/amountList
try
{
#region Payment-Loop
//int pRows = vdgvPayment.ItemsSource.OfType<object>().Count();
// IList rowsPayment = vdgvPayment.ItemsSource as IList;
List <int> paymentList = new List <int>(rowsPayment.Count);
List <Decimal> amountList = new List <Decimal>(rowsPayment.Count);
Decimal paymentAppliedAmt = Env.ZERO;
for (int i = 0; i < rowsPayment.Count; i++)
{
// Payment line is selected
// bool boolValue = false;
//if (boolValue)
{
//KeyNamePair pp = (KeyNamePair)vdgvPayment.Rows[i].Cells[2].Value;
// KeyNamePair pp = (KeyNamePair)(((BindableObject)rowsPayment[i]).GetValue(2)); // Value
// Payment variables
int C_Payment_ID = Util.GetValueOfInt(rowsPayment[i]["cpaymentid"]);
paymentList.Add(C_Payment_ID);
//
// Decimal PaymentAmt = Util.GetValueOfDecimal(vdgvPayment.Rows[i].Cells[_payment].Value); // Applied Payment
Decimal PaymentAmt = Util.GetValueOfDecimal(rowsPayment[i][payment]); // Applied Payment
amountList.Add(PaymentAmt);
//
paymentAppliedAmt = Decimal.Add(paymentAppliedAmt, PaymentAmt);
//
// log.Fine("C_Payment_ID=" + C_Payment_ID
// + " - PaymentAmt=" + PaymentAmt); // + " * " + Multiplier + " = " + PaymentAmtAbs);
MPayment pay1 = new MPayment(ctx, C_Payment_ID, trx);
}
}
//log.Config("Number of Payments=" + paymentList.Count + " - Total=" + paymentAppliedAmt);
#endregion
// Invoices - Loop and generate alloctions
#region Invoice-Loop with allocation
// int iRows = vdgvInvoice.ItemsSource.OfType<object>().Count();
// IList rowsInvoice=vdgvInvoice.ItemsSource as IList;
Decimal totalAppliedAmt = Env.ZERO;
// Create Allocation - but don't save yet
MAllocationHdr alloc = new MAllocationHdr(ctx, true, // manual
DateTrx, C_Currency_ID, ctx.GetContext("#AD_User_Name"), trx);
alloc.SetAD_Org_ID(AD_Org_ID);
// For all invoices
int invoiceLines = 0;
for (int i = 0; i < rowsInvoice.Count; i++)
{
// Invoice line is selected
//if (boolValue)
{
invoiceLines++;
// KeyNamePair pp = (KeyNamePair)vdgvInvoice.Rows[i].Cells[2].Value; // Value
// KeyNamePair pp = (KeyNamePair)(((BindableObject)rowsPayment[i]).GetValue(2)); // Value
// Invoice variables
int C_Invoice_ID = Util.GetValueOfInt(rowsInvoice[i]["cinvoiceid"]);
// Decimal AppliedAmt = Util.GetValueOfDecimal(vdgvInvoice.Rows[i].Cells[_applied].Value);
Decimal AppliedAmt = Util.GetValueOfDecimal(rowsInvoice[i][applied]);
// semi-fixed fields (reset after first invoice)
// Decimal DiscountAmt = Util.GetValueOfDecimal(vdgvInvoice.Rows[i].Cells[_discount].Value);
// Decimal WriteOffAmt = Util.GetValueOfDecimal(vdgvInvoice.Rows[i].Cells[_writeOff].Value);
Decimal DiscountAmt = Util.GetValueOfDecimal(rowsInvoice[i][discount]);
Decimal WriteOffAmt = Util.GetValueOfDecimal(rowsInvoice[i][writeOff]);
// OverUnderAmt needs to be in Allocation Currency
// Decimal OverUnderAmt = Decimal.Subtract(Util.GetValueOfDecimal(vdgvInvoice.Rows[i].Cells[_open].Value),
// Decimal.Subtract(AppliedAmt, Decimal.Subtract(DiscountAmt, WriteOffAmt)));
Decimal OverUnderAmt = Decimal.Subtract(Util.GetValueOfDecimal(rowsInvoice[i][open]),
Decimal.Subtract(AppliedAmt, Decimal.Subtract(DiscountAmt, WriteOffAmt)));
// log.Config("Invoice #" + i + " - AppliedAmt=" + AppliedAmt);// + " -> " + AppliedAbs);
//Payment Settelment**********
// loop through all payments until invoice applied
int noPayments = 0;
for (int j = 0; j < paymentList.Count && Env.Signum(AppliedAmt) != 0; j++)
{
int C_Payment_ID = Util.GetValueOfInt(paymentList[j]);
Decimal PaymentAmt = Util.GetValueOfDecimal(amountList[j]);
if (Env.Signum(PaymentAmt) != 0)
{
// log.Config(".. with payment #" + j + ", Amt=" + PaymentAmt);
noPayments++;
// use Invoice Applied Amt
Decimal amount = Env.ZERO;
if ((Math.Abs(AppliedAmt)).CompareTo(Math.Abs(PaymentAmt)) > 0)
{
amount = PaymentAmt;
}
else
{
amount = AppliedAmt;
}
//log.Fine("C_Payment_ID=" + C_Payment_ID + ", C_Invoice_ID=" + C_Invoice_ID
// + ", Amount=" + amount + ", Discount=" + DiscountAmt + ", WriteOff=" + WriteOffAmt);
// Allocation Header
if (alloc.Get_ID() == 0 && !alloc.Save())
{
// log.Log(Level.SEVERE, "Allocation not created");
trx.Rollback();
trx.Close();
return;
}
// Allocation Line
MAllocationLine aLine = new MAllocationLine(alloc, amount,
DiscountAmt, WriteOffAmt, OverUnderAmt);
aLine.SetDocInfo(C_BPartner_ID, C_Order_ID, C_Invoice_ID);
//aLine.SetPaymentInfo(C_Payment_ID, C_CashLine_ID);
aLine.SetPaymentInfo(C_Payment_ID, 0);//cashline for payment allocation is zero
if (!aLine.Save())
{
// log.Log(Level.SEVERE, "Allocation Line not written - Invoice=" + C_Invoice_ID);
}
// Apply Discounts and WriteOff only first time
DiscountAmt = Env.ZERO;
WriteOffAmt = Env.ZERO;
// subtract amount from Payment/Invoice
AppliedAmt = Decimal.Subtract(AppliedAmt, amount);
//AppliedAmt = Decimal.Subtract(PaymentAmt, AppliedAmt);
PaymentAmt = Decimal.Subtract(PaymentAmt, amount);
//log.Fine("Allocation Amount=" + amount + " - Remaining Applied=" + AppliedAmt + ", Payment=" + PaymentAmt);
//amountList.set(j, PaymentAmt); // update
amountList[j] = PaymentAmt; // update//set
} // for all applied amounts
MPayment pay1 = new MPayment(ctx, C_Payment_ID, trx);
} // loop through payments for invoice
// No Payments allocated and none existing (e.g. Inv/CM)
if (noPayments == 0 && paymentList.Count == 0)
{
int C_Payment_ID = 0;
// log.Config(" ... no payment - TotalApplied=" + totalAppliedAmt);
// Create Allocation
// log.Fine("C_Payment_ID=" + C_Payment_ID + ", C_Invoice_ID=" + C_Invoice_ID
// + ", Amount=" + AppliedAmt + ", Discount=" + DiscountAmt + ", WriteOff=" + WriteOffAmt);
// Allocation Header
if (alloc.Get_ID() == 0 && !alloc.Save())
{
//log.Log(Level.SEVERE, "Allocation not created");
trx.Rollback();
trx.Close();
return;
}
// Allocation Line
MAllocationLine aLine = new MAllocationLine(alloc, AppliedAmt,
DiscountAmt, WriteOffAmt, OverUnderAmt);
aLine.SetDocInfo(C_BPartner_ID, C_Order_ID, C_Invoice_ID);
//aLine.SetPaymentInfo(C_Payment_ID, C_CashLine_ID);
aLine.SetPaymentInfo(C_Payment_ID, 0);
if (!aLine.Save(trx))
{
//Log(Level.SEVERE, "Allocation Line not written - Invoice=" + C_Invoice_ID);
}
// log.Fine("Allocation Amount=" + AppliedAmt);
MPayment pay1 = new MPayment(ctx, C_Payment_ID, trx);
}
totalAppliedAmt = Decimal.Add(totalAppliedAmt, AppliedAmt);
// log.Config("TotalRemaining=" + totalAppliedAmt);
} // invoice selected
} // invoice loop
#endregion
// Only Payments and total of 0 (e.g. Payment/Reversal)
#region Reversal Payments
if (invoiceLines == 0 && paymentList.Count > 0 &&
Env.Signum(paymentAppliedAmt) == 0)
{
for (int i = 0; i < paymentList.Count; i++)
{
int C_Payment_ID = Util.GetValueOfInt(paymentList[i]);
Decimal PaymentAmt = Util.GetValueOfDecimal(amountList[i]);
// log.Fine("Payment=" + C_Payment_ID
// + ", Amount=" + PaymentAmt);// + ", Abs=" + PaymentAbs);
// Allocation Header
if (alloc.Get_ID() == 0 && !alloc.Save())
{
// log.Log(Level.SEVERE, "Allocation not created");
trx.Rollback();
trx.Close();
return;
}
// Allocation Line
MAllocationLine aLine = new MAllocationLine(alloc, PaymentAmt,
Env.ZERO, Env.ZERO, Env.ZERO);
aLine.SetDocInfo(C_BPartner_ID, 0, 0);
aLine.SetPaymentInfo(C_Payment_ID, 0);
if (!aLine.Save(trx))
{
// log.Log(Level.SEVERE, "Allocation Line not saved - Payment=" + C_Payment_ID);
}
MPayment pay1 = new MPayment(ctx, C_Payment_ID, trx);
}
} // onlyPayments
#endregion
if (Env.Signum(totalAppliedAmt) != 0)
{
//log.Log(Level.SEVERE, "Remaining TotalAppliedAmt=" + totalAppliedAmt);
}
// Should start WF
if (alloc.Get_ID() != 0)
{
alloc.ProcessIt(DocActionVariables.ACTION_COMPLETE);
alloc.Save();
}
// Test/Set IsPaid for Invoice - requires that allocation is posted
#region Set Invoice IsPaid
for (int i = 0; i < rowsInvoice.Count; i++)
{
// Invoice line is selected
// if (boolValue)
{
//KeyNamePair pp = (KeyNamePair)vdgvInvoice.Rows[i].Cells[2].Value; // Value
// KeyNamePair pp = (KeyNamePair)((BindableObject)rowsInvoice[i]).GetValue(2); // Value
// Invoice variables
int C_Invoice_ID = Util.GetValueOfInt(rowsInvoice[i]["cinvoiceid"]);
String sql = "SELECT invoiceOpen(C_Invoice_ID, 0) "
+ "FROM C_Invoice WHERE C_Invoice_ID=@param1";
Decimal opens = Util.GetValueOfDecimal(DB.GetSQLValueBD(trx, sql, C_Invoice_ID));
if (open != null && Env.Signum(opens) == 0)
{
sql = "UPDATE C_Invoice SET IsPaid='Y' "
+ "WHERE C_Invoice_ID=" + C_Invoice_ID;
int no = DB.ExecuteQuery(sql, null, trx);
// log.Config("Invoice #" + i + " is paid");
}
else
{
// log.Config("Invoice #" + i + " is not paid - " + open);
}
}
}
#endregion
// Test/Set Payment is fully allocated
#region Set Payment Allocated
if (rowsPayment.Count > 0)
{
for (int i = 0; i < paymentList.Count; i++)
{
int C_Payment_ID = Util.GetValueOfInt(paymentList[i]);
MPayment pay = new MPayment(ctx, C_Payment_ID, trx);
if (pay.TestAllocation())
{
pay.Save();
}
string sqlGetOpenPayments = "SELECT currencyConvert(ALLOCPAYMENTAVAILABLE(C_Payment_ID) ,p.C_Currency_ID ,260,p.DateTrx ,p.C_ConversionType_ID ,p.AD_Client_ID ,p.AD_Org_ID) FROM C_Payment p Where C_Payment_ID = " + C_Payment_ID;
object result = DB.ExecuteScalar(sqlGetOpenPayments, null, trx);
Decimal?amtPayment = 0;
if (result == null || result == DBNull.Value)
{
amtPayment = -1;
}
else
{
amtPayment = Util.GetValueOfDecimal(result);
}
if (amtPayment == 0)
{
pay.SetIsAllocated(true);
}
else
{
pay.SetIsAllocated(false);
}
pay.Save();
//log.Config("Payment #" + i + (pay.IsAllocated() ? " not" : " is")
// + " fully allocated");
}
}
#endregion
paymentList.Clear();
amountList.Clear();
trx.Commit();
trx.Close();
}
catch
{
if (trx != null)
{
trx.Rollback();
trx.Close();
trx = null;
}
}
finally
{
if (trx != null)
{
trx.Rollback();
trx.Close();
trx = null;
}
}
}
protected override string DoIt()
{
string status = "OK";
string baseCurrency = DB.ExecuteScalar("Select ISO_Code from C_Currency Where C_Currency_ID=" + baseCurrencyID).ToString();
string currencySourceName = DB.ExecuteScalar("Select url from C_CurrencySource Where C_CurrencySource_ID=" + C_CurrencySource_ID).ToString();
string myCurrency = "";
int myCurrencyID = 0;
string sql = @"SELECT ISO_Code,C_Currency_ID FROM C_Currency WHERE IsActive='Y' AND ISMYCURRENCY='Y'";
DataSet ds = DB.ExecuteDataset(sql);
Trx trx = Trx.Get("CreateConVersionEnties");
try
{
if (ds != null)
{
String URL = "http://localhost/CloudService55/AccountService.asmx";
//String CloudURL = "http://cloudservice.viennaadvantage.com/AccountService.asmx";
BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.None)
{
CloseTimeout = new TimeSpan(00, 20, 00),
SendTimeout = new TimeSpan(00, 20, 00),
OpenTimeout = new TimeSpan(00, 20, 00),
ReceiveTimeout = new TimeSpan(00, 20, 00),
MaxReceivedMessageSize = int.MaxValue,
MaxBufferSize = int.MaxValue
};
int defaultconversionType = 0;
try
{
defaultconversionType = Convert.ToInt32(DB.ExecuteScalar("select c_conversiontype_id from c_conversiontype where isdefault='Y' and isactive='Y'"));
}
catch { }
MConversionRate conversion = null;
Decimal rate1 = 0;
Decimal rate2 = 0;
Decimal one = new Decimal(1.0);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCurrency = ds.Tables[0].Rows[i]["ISO_Code"].ToString();
myCurrencyID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_Currency_ID"]);
var client = new ModelLibrary.AcctService.AccountServiceSoapClient(binding, new EndpointAddress(URL));
if (!String.IsNullOrEmpty(myCurrency) &&
!String.IsNullOrEmpty(baseCurrency) &&
!String.IsNullOrEmpty(currencySourceName))
{
string result = client.GetConvertedCurrencyValue(baseCurrency, myCurrency, currencySourceName, KEY);
if (!String.IsNullOrEmpty(result))
{
conversion = new MConversionRate(GetCtx(), 0, trx);
conversion.SetAD_Org_ID(0);
conversion.SetAD_Client_ID(GetCtx().GetAD_Client_ID());
conversion.SetValidFrom(DateTime.Now);
conversion.SetValidTo(DateTime.Now);
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(myCurrencyID);
conversion.SetC_Currency_To_ID(baseCurrencyID);
//conversion.SetC_Currency_To_ID();
rate1 = Convert.ToDecimal(result);
rate2 = Utility.Env.ZERO;
one = new Decimal(1.0);
if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
{
rate2 = Decimal.Round(Decimal.Divide(one, rate1), 12); // MidpointRounding.AwayFromZero);
}
conversion.SetMultiplyRate(rate1);
conversion.SetDivideRate(rate2);
if (!conversion.Save(trx))
{
status = "ConversionRateNotsaved";
}
}
result = client.GetConvertedCurrencyValue(myCurrency, baseCurrency, currencySourceName, KEY);
if (!String.IsNullOrEmpty(result))
{
conversion = new MConversionRate(GetCtx(), 0, trx);
conversion.SetAD_Org_ID(0);
conversion.SetAD_Client_ID(GetCtx().GetAD_Client_ID());
conversion.SetValidFrom(DateTime.Now);
conversion.SetValidTo(DateTime.Now);
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(baseCurrencyID);
conversion.SetC_Currency_To_ID(myCurrencyID);
//conversion.SetC_Currency_To_ID();
rate1 = Convert.ToDecimal(result);
rate2 = Utility.Env.ZERO;
one = new Decimal(1.0);
if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
{
rate2 = Decimal.Round(Decimal.Divide(one, rate1), 12); // MidpointRounding.AwayFromZero);
}
conversion.SetMultiplyRate(rate1);
conversion.SetDivideRate(rate2);
if (!conversion.Save(trx))
{
status = "ConversionRateNotsaved";
}
}
}
}
}
}
catch (Exception ex)
{
status = ex.Message;
}
if (status.Equals("OK"))
{
trx.Commit();
}
else
{
trx.Rollback();
}
trx.Close();
return(status);
}
public string[] InsertDimensionLine(Ctx ctx, int RecordId, decimal TotalAmount, decimal LineAmount, int[] acctSchemaID, string elementTypeID, int dimensionValue, int elementID, int oldDimensionName)
{
string Sql = "";
int DimAcctTypeId;
int dimAmtLineId;
string[] LineAmountID = new string[2];
string lineID = "";
bool error = false;
Trx trx = Trx.Get("trxDim" + DateTime.Now.Millisecond);
try
{
foreach (int acct in acctSchemaID)
{
X_C_DimAmt objDimAmt = new X_C_DimAmt(ctx, RecordId, trx);
// if (objDimAmt.GetAmount() <= TotalAmount)
// {
if (CheckUpdateMaxAmount(RecordId, TotalAmount, acctSchemaID, LineAmount))
{
objDimAmt.SetAmount(TotalAmount);
//objDimAmt.SetAD_Table_ID(AD_TableId);
// objDimAmt.SetRecord_ID(AD_RecordID);
if (!objDimAmt.Save(trx))
{
error = true;
goto ErrorCheck;
}
}
// }
LineAmountID[0] = Convert.ToString(objDimAmt.GetC_DimAmt_ID());
RecordId = objDimAmt.GetC_DimAmt_ID();
if (acct != -1)
{
// RecordId = objDimAmt.GetC_DimAmt_ID();
Sql = "select nvl(c_dimamtaccttype_ID,0) from c_dimamtaccttype where c_dimamt_id=" + RecordId + " and c_acctschema_ID=" + acct + "";
DimAcctTypeId = Convert.ToInt32(DB.ExecuteScalar(Sql));
Sql = "select nvl((sum(cd.amount)),0) as Amount from c_dimamtline cd inner join c_dimamtaccttype ct on cd.c_dimamt_id=ct.c_dimamt_id " +
" and cd.c_dimamtaccttype_id=ct.c_dimamtaccttype_id " +
" where cd.c_dimamt_id=" + RecordId + " and ct.c_dimamtaccttype_id=" + DimAcctTypeId + "";
decimal TotoalDimAmount = Convert.ToDecimal(DB.ExecuteScalar(Sql));
if (LineAmount != -1)
{
TotoalDimAmount += LineAmount;
}
X_C_DimAmtAcctType objDimAcctType = new X_C_DimAmtAcctType(ctx, DimAcctTypeId, trx);
objDimAcctType.SetC_DimAmt_ID(objDimAmt.GetC_DimAmt_ID());
if (LineAmount != -1)
{
objDimAcctType.SetC_AcctSchema_ID(acct);
objDimAcctType.SetElementType(elementTypeID);
}
if (LineAmount != -1 || TotoalDimAmount != 0)
{
objDimAcctType.SetTotalDimLineAmout(TotoalDimAmount);
if (!objDimAcctType.Save(trx))
{
error = true;
goto ErrorCheck;
}
}
if (LineAmount != -1)
{
Sql = "select nvl(c_dimamtline_id,0) from c_dimamtline where c_Dimamt_ID=" + RecordId + " and c_dimamtaccttype_id=" + objDimAcctType.GetC_DimAmtAcctType_ID() + "";
if (elementTypeID == "AC")
{
Sql += " and c_elementvalue_id=" + oldDimensionName;
}//Account
else if (elementTypeID == "AY")
{
Sql += " and c_activity_id=" + oldDimensionName;
} //Activity
else if (elementTypeID == "BP")
{
Sql += " and c_BPartner_ID=" + oldDimensionName;
} //BPartner
else if (elementTypeID == "LF" || elementTypeID == "LT")
{
Sql += " and c_location_ID=" + oldDimensionName;
} //Location From//Location To
else if (elementTypeID == "MC")
{
Sql += " and c_Campaign_ID=" + oldDimensionName;
} //Campaign
else if (elementTypeID == "OO" || elementTypeID == "OT")
{
Sql += " and Org_ID=" + oldDimensionName;
} //Organization//Org Trx
else if (elementTypeID == "PJ")
{
Sql += " and c_Project_id=" + oldDimensionName;
} //Project
else if (elementTypeID == "PR")
{
Sql += " and M_Product_Id=" + oldDimensionName;
} //Product
else if (elementTypeID == "SR")
{
Sql += " and c_SalesRegion_Id=" + oldDimensionName;
} //Sales Region
else if (elementTypeID == "U1" || elementTypeID == "U2")
{
Sql += " and c_elementvalue_id=" + oldDimensionName;
}//User List 1//User List 2
else if (elementTypeID == "X1" || elementTypeID == "X2" || elementTypeID == "X3" || elementTypeID == "X4" || elementTypeID == "X5" || elementTypeID == "X6" ||
elementTypeID == "X7" || elementTypeID == "X8" || elementTypeID == "X9")
{
Sql += " and AD_Column_ID=" + oldDimensionName;
} //User Element 1 to User Element 9
dimAmtLineId = Convert.ToInt32(DB.ExecuteScalar(Sql));
X_C_DimAmtLine objDimAmtLine = new X_C_DimAmtLine(ctx, dimAmtLineId, trx);
if (dimAmtLineId != 0)
{
objDimAcctType.SetTotalDimLineAmout(objDimAcctType.GetTotalDimLineAmout() - objDimAmtLine.GetAmount());
if (!objDimAcctType.Save(trx))
{
error = true;
goto ErrorCheck;
}
}
objDimAmtLine.SetC_DimAmt_ID(objDimAmt.GetC_DimAmt_ID());
objDimAmtLine.SetC_DimAmtAcctType_ID(objDimAcctType.GetC_DimAmtAcctType_ID());
objDimAmtLine.SetAmount(LineAmount);
if (elementTypeID == "AC")
{
objDimAmtLine.SetC_Element_ID(elementID);
objDimAmtLine.SetC_ElementValue_ID(dimensionValue);
}//Account
else if (elementTypeID == "AY")
{
objDimAmtLine.SetC_Activity_ID(dimensionValue);
} //Activity
else if (elementTypeID == "BP")
{
objDimAmtLine.SetC_BPartner_ID(dimensionValue);
} //BPartner
else if (elementTypeID == "LF" || elementTypeID == "LT")
{
objDimAmtLine.SetC_Location_ID(dimensionValue);
} //Location From//Location To
else if (elementTypeID == "MC")
{
objDimAmtLine.SetC_Campaign_ID(dimensionValue);
} //Campaign
else if (elementTypeID == "OO" || elementTypeID == "OT")
{
objDimAmtLine.SetOrg_ID(dimensionValue);
} //Organization//Org Trx
else if (elementTypeID == "PJ")
{
objDimAmtLine.SetC_Project_ID(dimensionValue);
} //Project
else if (elementTypeID == "PR")
{
objDimAmtLine.SetM_Product_ID(dimensionValue);
} //Product
else if (elementTypeID == "SA")
{
} //Sub Account
else if (elementTypeID == "SR")
{
objDimAmtLine.SetC_SalesRegion_ID(dimensionValue);
} //Sales Region
else if (elementTypeID == "U1" || elementTypeID == "U2")
{
objDimAmtLine.SetC_Element_ID(elementID);
objDimAmtLine.SetC_ElementValue_ID(dimensionValue);
}//User List 1//User List 2
else if (elementTypeID == "X1" || elementTypeID == "X2" || elementTypeID == "X3" || elementTypeID == "X4" || elementTypeID == "X5" || elementTypeID == "X6" ||
elementTypeID == "X7" || elementTypeID == "X8" || elementTypeID == "X9")
{
objDimAmtLine.SetAD_Column_ID(dimensionValue);
} //User Element 1 to User Element 9
if (!objDimAmtLine.Save(trx))
{
error = true;
goto ErrorCheck;
}
if (lineID == "")
{
lineID += Convert.ToString(objDimAmtLine.GetC_DimAmtLine_ID());
}
else
{
lineID += "," + Convert.ToString(objDimAmtLine.GetC_DimAmtLine_ID());
}
}
}
else
{
break;
}
ErrorCheck:
if (error)
{
break;
}
}
}
catch (Exception e)
{
error = true;
}
finally
{
if (error)
{
LineAmountID[0] = ""; LineAmountID[1] = "";
trx.Rollback();
log.Warning("Some error occured while saving Dimension");
}
else
{
LineAmountID[1] = lineID;
trx.Commit();
}
}
return(LineAmountID);
}
public string SaveCashData(List <Dictionary <string, string> > paymentData, List <Dictionary <string, string> > rowsCash, List <Dictionary <string, string> > rowsInvoice, string currency,
bool isCash, int _C_BPartner_ID, int _windowNo, string payment, DateTime DateTrx, string applied, string discount, string writeOff, string open)
{
//if (_noInvoices + _noCashLines == 0)
// return "";
int C_Currency_ID = Convert.ToInt32(currency);
// fixed fields
int AD_Client_ID = ctx.GetContextAsInt(_windowNo, "AD_Client_ID");
int AD_Org_ID = ctx.GetContextAsInt(_windowNo, "AD_Org_ID");
int C_BPartner_ID = _C_BPartner_ID;
int C_Order_ID = 0;
int C_CashLine_ID = 0;
//
if (AD_Org_ID == 0)
{
//Classes.ShowMessage.Error("Org0NotAllowed", null);
return("");
}
//
// log.Config("Client=" + AD_Client_ID + ", Org=" + AD_Org_ID
// + ", BPartner=" + C_BPartner_ID + ", Date=" + DateTrx);
Trx trx = Trx.Get(Trx.CreateTrxName("AL"), true);
/**
* Generation of allocations: amount/discount/writeOff
* - if there is one payment -- one line per invoice is generated
* with both the Invoice and Payment reference
* Pay=80 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#1 Inv#1
* or
* Pay=160 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#1 Inv#1
* Pay=160 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#1 Inv#2
*
* - if there are multiple payment lines -- the amounts are allocated
* starting with the first payment and payment
* Pay=60 Inv=100 Disc=10 WOff=10 => 60/10/10 Pay#1 Inv#1
* Pay=100 Inv=100 Disc=10 WOff=10 => 20/0/0 Pay#2 Inv#1
* Pay=100 Inv=100 Disc=10 WOff=10 => 80/10/10 Pay#2 Inv#2
*
* - if you apply a credit memo to an invoice
* Inv=10 Disc=0 WOff=0 => 10/0/0 Inv#1
* Inv=-10 Disc=0 WOff=0 => -10/0/0 Inv#2
*
* - if you want to write off a (partial) invoice without applying,
* enter zero in applied
* Inv=10 Disc=1 WOff=9 => 0/1/9 Inv#1
* Issues
* - you cannot write-off a payment
*/
// CashLines - Loop and Add them to cashList/CashAmountList
#region CashLines-Loop
// int cRows = vdgvCashLines.RowCount;
// IList rowsCash = vdgvCashLine.ItemsSource as IList;
List <int> cashList = new List <int>(rowsCash.Count);
List <Decimal> CashAmtList = new List <Decimal>(rowsCash.Count);
Decimal cashAppliedAmt = Env.ZERO;
for (int i = 0; i < rowsCash.Count; i++)
{
// Payment line is selected
bool boolValue = false;
bool flag = false;
// if (boolValue)
{
// Payment variables
C_CashLine_ID = Util.GetValueOfInt(rowsCash[i]["ccashlineid"]);
cashList.Add(C_CashLine_ID);
//
//Decimal PaymentAmt = Util.GetValueOfDecimal(((BindableObject)rowsCash[i]).GetValue(_payment)); // Applied Payment
Decimal PaymentAmt = Util.GetValueOfDecimal(rowsCash[i][payment]); // Applied Payment
CashAmtList.Add(PaymentAmt);
//
cashAppliedAmt = Decimal.Add(cashAppliedAmt, PaymentAmt);
//
// log.Fine("C_CashLine_ID=" + C_CashLine_ID
// + " - PaymentAmt=" + PaymentAmt); // + " * " + Multiplier + " = " + PaymentAmtAbs);
}
}
//log.Config("Number of Cashlines=" + cashList.Count + " - Total=" + cashAppliedAmt);
#endregion
// Invoices - Loop and generate alloctions
#region Invoice-Loop with allocation
// int iRows = vdgvInvoice.RowCount;
// IList rowsInvoice = vdgvInvoice.ItemsSource as IList;
Decimal totalAppliedAmt = Env.ZERO;
// Create Allocation - but don't save yet
MAllocationHdr alloc = new MAllocationHdr(ctx, true, // manual
DateTrx, C_Currency_ID, ctx.GetContext("#AD_User_Name"), trx);
alloc.SetAD_Org_ID(AD_Org_ID);
// For all invoices
int invoiceLines = 0;
//for (int i = 0; i < rowsCash.Count; i++)
for (int i = 0; i < rowsInvoice.Count; i++)
{
// Invoice line is selected
bool boolValue = false;
bool flag = false;
// if (boolValue)
{
invoiceLines++;
// Invoice variables
/// int C_Invoice_ID = Util.GetValueOfInt(((BindableObject)rowsInvoice[i]).GetValue("C_INVOICE_ID"));
int C_Invoice_ID = Util.GetValueOfInt(rowsInvoice[i]["cinvoiceid"]);
Decimal AppliedAmt = Util.GetValueOfDecimal(rowsInvoice[i][applied]);
// semi-fixed fields (reset after first invoice)
Decimal DiscountAmt = Util.GetValueOfDecimal(rowsInvoice[i][discount]);
Decimal WriteOffAmt = Util.GetValueOfDecimal(rowsInvoice[i][writeOff]);
// OverUnderAmt needs to be in Allocation Currency
Decimal OverUnderAmt = Decimal.Subtract(Util.GetValueOfDecimal(rowsInvoice[i][open]),
Decimal.Subtract(AppliedAmt, Decimal.Subtract(DiscountAmt, WriteOffAmt)));
//log.Config("Invoice #" + i + " - AppliedAmt=" + AppliedAmt);// + " -> " + AppliedAbs);
//CashLines settelment************
// loop through all payments until invoice applied
int noCashlines = 0;
for (int j = 0; j < cashList.Count && Env.Signum(AppliedAmt) != 0; j++)
{
C_CashLine_ID = Util.GetValueOfInt(cashList[j]);
Decimal PaymentAmt = Util.GetValueOfDecimal(CashAmtList[j]);
if (Env.Signum(PaymentAmt) != 0)
{
//log.Config(".. with payment #" + j + ", Amt=" + PaymentAmt);
noCashlines++;
// use Invoice Applied Amt
Decimal amount = AppliedAmt;
//log.Fine("C_CashLine_ID=" + C_CashLine_ID + ", C_Invoice_ID=" + C_Invoice_ID
// + ", Amount=" + amount + ", Discount=" + DiscountAmt + ", WriteOff=" + WriteOffAmt);
// Allocation Header
if (alloc.Get_ID() == 0 && !alloc.Save())
{
// log.Log(Level.SEVERE, "Allocation not created");
trx.Rollback();
trx.Close();
return("");
}
// Allocation Line
MAllocationLine aLine = new MAllocationLine(alloc, amount,
DiscountAmt, WriteOffAmt, OverUnderAmt);
aLine.SetDocInfo(C_BPartner_ID, C_Order_ID, C_Invoice_ID);
aLine.SetPaymentInfo(0, C_CashLine_ID);//payment for payment allocation is zero
if (!aLine.Save())
{
// log.Log(Level.SEVERE, "Allocation Line not written - Invoice=" + C_Invoice_ID);
// Apply Discounts and WriteOff only first time
DiscountAmt = Env.ZERO;
}
WriteOffAmt = Env.ZERO;
// subtract amount from Payment/Invoice
AppliedAmt = Decimal.Subtract(AppliedAmt, amount);
//AppliedAmt = Decimal.Subtract(PaymentAmt, AppliedAmt);
PaymentAmt = Decimal.Subtract(PaymentAmt, amount);
//log.Fine("Allocation Amount=" + amount + " - Remaining Applied=" + AppliedAmt + ", Payment=" + PaymentAmt);
//amountList.set(j, PaymentAmt); // update
if (CashAmtList.Count > 0)
{
MCashLine cline = new MCashLine(ctx, C_CashLine_ID, null);
cline.SetAmount(Decimal.Subtract(cline.GetAmount(), CashAmtList[j]));
if (!cline.Save())
{
// log.SaveError("AmountIsNotUpdated" + C_CashLine_ID.ToString(), "");
}
CashAmtList[j] = PaymentAmt; // update//set
}
} // for all applied amounts
} // loop through Cash for invoice(Charge)
// No Cashlines allocated and none existing
if (rowsCash.Count > 0)
{
if (noCashlines == 0 && cashList.Count == 0)
{
C_CashLine_ID = 0;
//log.Config(" ... no CashLines - TotalApplied=" + totalAppliedAmt);
// Create Allocation
// log.Fine("C_CashLine_ID=" + C_CashLine_ID + ", C_Invoice_ID=" + C_Invoice_ID
// + ", Amount=" + AppliedAmt + ", Discount=" + DiscountAmt + ", WriteOff=" + WriteOffAmt);
// Allocation Header
if (alloc.Get_ID() == 0 && !alloc.Save())
{
//log.Log(Level.SEVERE, "Allocation not created");
trx.Rollback();
trx.Close();
return("");
}
// Allocation Line
MAllocationLine aLine = new MAllocationLine(alloc, AppliedAmt,
DiscountAmt, WriteOffAmt, OverUnderAmt);
aLine.SetDocInfo(C_BPartner_ID, C_Order_ID, C_Invoice_ID);
aLine.SetPaymentInfo(0, C_CashLine_ID);
if (!aLine.Save(trx))
{
}
// log.Log(Level.SEVERE, "Allocation Line not written - Invoice=" + C_Invoice_ID);
//log.Fine("Allocation Amount=" + AppliedAmt);
}
}
totalAppliedAmt = Decimal.Add(totalAppliedAmt, AppliedAmt);
//log.Config("TotalRemaining=" + totalAppliedAmt);
} // invoice selected
} // invoice loop
#endregion
if (Env.Signum(totalAppliedAmt) != 0)
{
//log.Log(Level.SEVERE, "Remaining TotalAppliedAmt=" + totalAppliedAmt);
// Should start WF
if (alloc.Get_ID() != 0)
{
alloc.ProcessIt(DocActionVariables.ACTION_COMPLETE);
alloc.Save();
}
}
// Test/Set IsPaid for Invoice - requires that allocation is posted
#region Set Invoice IsPaid
for (int i = 0; i < rowsInvoice.Count; i++)
{
bool boolValue = false;
// Invoice line is selected
bool flag = false;
//Dispatcher.BeginInvoke(delegate
//{
// boolValue = GetBoolValue(vdgvInvoice, i, 0);
// flag = true;
// SetBusy(false);
//});
//while (!flag)
//{
// System.Threading.Thread.Sleep(1);
//}
// if (boolValue)
{
//KeyNamePair pp = (KeyNamePair)vdgvInvoice.Rows[i].Cells[2].Value; // Value
//KeyNamePair pp = (KeyNamePair)((BindableObject)rowsInvoice[i]).GetValue(2); // Value
// Invoice variables
int C_Invoice_ID = Util.GetValueOfInt(rowsInvoice[i]["cinvoiceid"]);
String sql = "SELECT invoiceOpen(C_Invoice_ID, 0) "
+ "FROM C_Invoice WHERE C_Invoice_ID=@param1";
Decimal opens = Util.GetValueOfDecimal(DB.GetSQLValueBD(trx, sql, C_Invoice_ID));
if (open != null && Env.Signum(opens) == 0)
{
sql = "UPDATE C_Invoice SET IsPaid='Y' "
+ "WHERE C_Invoice_ID=" + C_Invoice_ID;
int no = DB.ExecuteQuery(sql, null, trx);
// log.Config("Invoice #" + i + " is paid");
}
else
{
// log.Config("Invoice #" + i + " is not paid - " + open);
}
}
}
#endregion
// Test/Set CashLine is fully allocated
#region Set CashLine Allocated
if (rowsCash.Count > 0)
{
for (int i = 0; i < cashList.Count; i++)
{
C_CashLine_ID = Util.GetValueOfInt(cashList[i]);
MCashLine cash = new MCashLine(ctx, C_CashLine_ID, trx);
if (cash.GetAmount() == 0)
{
cash.SetIsAllocated(true);
cash.Save();
}
// log.Config("Cash #" + i + (cash.IsAllocated() ? " not" : " is")
// + " fully allocated");
}
}
#endregion
cashList.Clear();
CashAmtList.Clear();
trx.Commit();
trx.Close();
return("");
}
/// <summary>
/// Process
/// </summary>
/// <returns>message</returns>
protected override String DoIt()
{
int viewNum = 0;
//get SQLs
//InputStream iin = null;
StreamReader inn = null;
String targetViewName = null;
_AD_Table_ID = (GetProcessInfo() != null) ? GetRecord_ID() : 0;
if (_AD_Table_ID > 0)
{
MTable targetTable = MTable.Get(Env.GetCtx(), _AD_Table_ID);
targetViewName = targetTable.GetTableName();
entityType = targetTable.GetEntityType();
}
try
{
inn = new StreamReader(SQLfile);// FileInputStream(SQLfile);
}
catch (Exception e)
{
log.Severe(e.Message);
throw new Exception("SQL file error. file name = " + SQLfile);
}
if (inn == null)
{
throw new Exception("SQL file error. file name = " + SQLfile);
}
List <String> commands = ViewUtil.ReadSqlFromFile(SQLfile);
if (commands == null)
{
throw new Exception("SQL file error. file name = " + SQLfile);
}
//put SQL into AD
// Initialization
String tableName = null;
List <String> colName = new List <String>();
List <String> colSQL = new List <String>();
List <String> colType = new List <String>();
Trx myTrx = Trx.Get("ImportView");
String Acommand = null;
// endSQL:
try
{
foreach (String command in commands)
{
Acommand = command;
bool colNameDone = false;
int ir = command.IndexOf('\t');
while (ir > 0)
{
Acommand = Replace(Acommand, '\t', ' ');
ir = command.IndexOf('\t');
}
// Not care about "exit"
if (command.Equals("exit") || command.Equals("EXIT"))
{
// endSQL;
break;
}
if (command.StartsWith("DROP VIEW "))
{
continue;
}
tableName = null;
colName.Clear();
int iView = command.IndexOf(" VIEW ");
//eachSQL:
if (command.StartsWith("CREATE") && iView > 0)
{
int iSel = command.IndexOf("SELECT ");
String tc = command.Substring(iView + 6, iSel);
int ileft = tc.IndexOf('(');
int iright = tc.IndexOf(')');
if (ileft > 0)
{
tableName = tc.Substring(0, ileft);
tc = tc.Substring(ileft + 1, iright);
String[] colNames = tc.Split(new Char[] { ',' });// tc.Substring(ileft + 1, iright).Split(",");
foreach (String cn in colNames)
{
colName.Add(Trim(cn));
}
colNameDone = true;
}
else
{
tableName = tc.Substring(0, tc.IndexOf(" AS "));
}
if (tableName != null)
{
//jz String trim() doesn't remove tail space tableName.trim();
tableName = Trim(tableName);
}
if (tableName == null || tableName.Length == 0)
{
log.Severe("No view name from the SQL: " + command);
continue;
}
if (_AD_Table_ID != 0 && !(targetViewName.Equals(tableName) || targetViewName.Equals(tableName.ToUpper())))
{
log.Fine("Skipping view " + targetViewName);
continue;
}
//int itc = tc.substring(tc.length()-1).hashCode();
//insert into/update ad_table for each view
MTable mt = MTable.Get(Env.GetCtx(), tableName);
if (mt != null && !mt.IsView())
{
log.Severe("Duplicated view name with an existing table for the SQL: " + command);
//myTrx.rollback();
// eachSQL;
break;
}
if (mt == null)
//mt.delete(true, null);
{
mt = new MTable(Env.GetCtx(), 0, null);
//mt.delete(true, myTrx.getTrxName());
//mt = new MTable(Env.getCtx(), 0, myTrx.getTrxName());
//MTable mt = MTable.get(Env.getCtx(), 0);
mt.SetTableName(tableName);
//mt.setAD_Org_ID(0);
//mt.setAD_Client_ID(0); also updatedby, createdby. jz: all default is 0 in PO
mt.SetAccessLevel(X_AD_Table.ACCESSLEVEL_ClientPlusOrganization);
mt.SetEntityType(entityType);
mt.SetIsActive(true);
mt.SetIsView(true);
mt.SetName("View_" + tableName);
mt.SetLoadSeq(900);
mt.SetImportTable(null);
if (!mt.Save())
{
log.Severe("Unable to insert into AD_Table for the SQL: " + command);
//myTrx.rollback();
//eachSQL;
break;
}
log.Info("Add " + tableName + " into AD_Table for the SQL: " + command);
}
//clean view components and their columns
String vcdel = "DELETE FROM AD_ViewComponent WHERE (AD_Table_ID, AD_Client_ID) IN (SELECT AD_Table_ID, AD_Client_ID FROM AD_Table WHERE TableName = '" + tableName + "')";
try
{
DataBase.DB.ExecuteQuery(vcdel, null);
}
catch (Exception e)
{
log.Log(Level.SEVERE, vcdel, e);
}
//insert into ad_viewComponent for each union part
Acommand = command.Substring(iSel);
//String[] selects =command.Split(new char[]{','});//UNION'},0); // currently does not handle other set operators (e.g. INTERSECT)
Regex reg = new Regex(Acommand);
String[] selects = reg.Split("UNION");
for (int i = 0; i < selects.Length; i++)
{
int iFrom = FindNext(selects[i], " FROM ");
if (iFrom < 0)
{
log.Severe("No from clause from the SQL: " + command);
//eachSQL;
break;
}
colSQL.Clear();
colType.Clear();
int isel = selects[i].IndexOf("SELECT ");
String colstr = selects[i].Substring(isel + 7, iFrom);
colstr = colstr + ",";// concat(",");
int iComma = FindNext(colstr, ",");
if (iComma == 0)
{
log.Severe("No view column from the SQL: " + command);
///eachSQL;
break;
}
int iPrevComma = -1;
while (iComma != iPrevComma)
{
String column = colstr.Substring(iPrevComma + 1, iComma);
int iAS = column.LastIndexOf(" AS ");
String cs = null;
if (iAS > 0)
{
if (i == 0 && !colNameDone)
{
colName.Add(column.Substring(iAS + 4, column.Length).Trim());
}
cs = column.Substring(0, iAS);
//colSQL.add(cols[j].substring(0, iAS));
}
else
{
cs = column;
if (i == 0 && !colNameDone)
{
int iDot = column.IndexOf('.');
String cn = column;
if (iDot > 0)
{
cn = column.Substring(iDot + 1, column.Length).Trim();
}
colName.Add(cn);
}
}
cs = cs.Trim();
if (cs.Equals("NULLIF(1,1)") || cs.Equals("nullif(1,1)"))
{
cs = null;
colType.Add("I");
}
else if (cs.Equals("NULLIF('A','A')") || cs.Equals("nullif('A','A')"))
{
cs = null;
colType.Add("V");
}
else
{
colType.Add(null);
}
colSQL.Add(cs);
iPrevComma = iComma;
if (iPrevComma + 1 < colstr.Length)
{
iComma = iPrevComma + 1 + FindNext(colstr.Substring(iPrevComma + 1), ",");
}
}// while (iComma != iPrevComma)
String from = selects[i].Substring(iFrom + 1, selects[i].Length);
int iWH = from.IndexOf(" WHERE ");
String where = null;
String others = null;
int iGROUP = -1;
int iORDER = -1;
if (iWH > 0)
{
where = from.Substring(iWH + 1, from.Length);
from = from.Substring(0, iWH);
iGROUP = where.IndexOf(" GROUP BY ");
iORDER = where.IndexOf(" ORDER BY ");
if (iORDER > 0 && iGROUP == -1)
{
iGROUP = iORDER;
}
if (iGROUP > 0)
{
others = where.Substring(iGROUP + 1, where.Length);
where = where.Substring(0, iGROUP);
}
}
else
{
iGROUP = from.IndexOf(" GROUP BY ");
iORDER = from.IndexOf(" ORDER BY ");
if (iORDER > 0 && iGROUP == -1)
{
iGROUP = iORDER;
}
if (iGROUP > 0)
{
others = from.Substring(iGROUP + 1, from.Length);
from = from.Substring(0, iGROUP);
}
}
if (from == null || from.Length == 0)
{
log.Severe("No from clause from the SQL: " + command);
//myTrx.rollback();
// eachSQL;
break;
}
//insert into AD_ViewComponent
//MViewComponent mvc = new MViewComponent(Env.getCtx(), 0, myTrx.getTrxName());
MViewComponent mvc = new MViewComponent(Env.GetCtx(), 0, null);
mvc.SetName("VC_" + tableName);
mvc.SetAD_Table_ID(mt.Get_ID());
mvc.SetSeqNo((i + 1) * 10);
mvc.SetIsActive(true);
mvc.SetEntityType(entityType);
//mvc.setAD_Org_ID(0);
//mvc.setReferenced_Table_ID(mt.get_ID());
String from1 = from.Substring(5);
from1 = Trim(from1);
int rtix = from1.IndexOf(' ');
if (rtix < 0)
{
rtix = from1.Length;
}
String refTab = from1.Substring(0, rtix);
refTab = Trim(refTab);
MTable rt = MTable.Get(Env.GetCtx(), refTab);
if (rt != null)
{
mvc.SetReferenced_Table_ID(rt.Get_ID());
}
else
{
mvc.SetReferenced_Table_ID(0);
}
mvc.SetFromClause(from);
mvc.SetWhereClause(where);
mvc.SetOtherClause(others);
if (!mvc.Save())
{
log.Severe("unable to create view component " + i + ": " + command);
//myTrx.rollback();
//eachSQL;
break;
}
//insert into AD_ViewColumn
MViewColumn mvcol = null;
for (int j = 0; j < colName.Count; j++)
{
//mvcol = new MViewColumn(Env.getCtx(), 0, myTrx.getTrxName());
mvcol = new MViewColumn(Env.GetCtx(), 0, null);
//mvcol.setAD_Org_ID(0);
mvcol.SetAD_ViewComponent_ID(mvc.Get_ID());
mvcol.SetIsActive(true);
mvcol.SetEntityType(entityType);
log.Info("Importing View " + tableName + "(i,j) = (" + i + ", " + j + ")");
mvcol.SetDBDataType(colType[j]); //.get(j));
mvcol.SetColumnName(colName[j]); //.get(j));
mvcol.SetColumnSQL(colSQL[j]); //.get(j));
if (!mvcol.Save())
{
log.Severe("unable to create view component " + i + " column: " + colName[j] + " in " + command);
//myTrx.rollback();
break; //eachSQL;
}
}
}//for selects
//myTrx.commit();
log.Info("Impored view: " + tableName);
}//handle create view
else
{
log.Warning("Ignore non create view SQL: " + command);
continue;
}
viewNum++;
} // for (String command : commands)
}
catch (Exception e)
{
log.Severe("Error at importing view SQL: " + Acommand + " \n " + e);
}
finally
{
if (myTrx != null && myTrx.IsActive())
{
myTrx.Rollback();
myTrx.Close();
}
}
if (_AD_Table_ID > 0)
{
if (viewNum == 0)
{
return("Not able to import view " + targetViewName + " from " + SQLfile);
}
else
{
return("Created view " + targetViewName);
}
}
return("Imported View #" + viewNum);
}
