/// <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
{
}
}
}
/// <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();
}
} // prepare
/// <summary>
/// Process
/// </summary>
/// <returns></returns>
protected override String DoIt()
{
if (p_AD_Table_ID == 0)
{
throw new Exception("@NotFound@ @AD_Table_ID@ " + p_AD_Table_ID);
}
log.Info("EntityType=" + p_EntityType
+ ", AllTables=" + p_AllTables
+ ", AD_Table_ID=" + p_AD_Table_ID);
Trx trx = Trx.Get("getDatabaseMetaData");
DatabaseMetaData md = new DatabaseMetaData();
string catalog = "";
string schema = DataBase.DB.GetSchema();
if (p_AllTables)
{
AddTable(md, catalog, schema);
}
else
{
MTable table = new MTable(GetCtx(), p_AD_Table_ID, Get_Trx());
if ((table == null) || (table.Get_ID() == 0))
{
throw new Exception("@NotFound@ @AD_Table_ID@ " + p_AD_Table_ID);
}
log.Info(table.GetTableName() + ", EntityType=" + p_EntityType);
String tableName = table.GetTableName();
//tableName = tableName.ToUpper();
DataSet rs = md.GetColumns(catalog, schema, tableName.ToUpper());
AddTableColumn(GetCtx(), rs, table, p_EntityType);
SubTableUtil.CheckStandardColumns(table, p_EntityType);
}
md.Dispose();
trx.Close();
return("#" + m_count);
}
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);
}
} // generate
/// <summary>
/// Generate Delta and History Table
/// </summary>
/// <returns></returns>
private bool GenerateTable()
{
// Table
m_derivedTable = MTable.Get(m_ctx, m_dTableName);
if (m_derivedTable == null)
{
m_derivedTable = new MTable(m_ctx, 0, null);
}
PO.CopyValues(m_baseTable, m_derivedTable);
m_derivedTable.SetTableName(m_dTableName);
m_derivedTable.SetName(m_derivedTable.GetName() + " SubTable");
m_derivedTable.SetSubTableType(m_derivedTableType);
m_derivedTable.SetBase_Table_ID(m_baseTable.GetAD_Table_ID());
if (!m_derivedTable.Save())
{
throw new Exception("Cannot save " + m_dTableName);
}
MColumn[] dCols = SyncMColumns(true);
// Sync Columns in Database
List <MColumn> list = new List <MColumn>(dCols.Length);
foreach (MColumn element in dCols)
{
list.Add(element);
}
Trx trx = Trx.Get("getDatabaseMetaData");
//
String catalog = "";
String schema = DataBase.DB.GetSchema();
String tableName = m_dTableName;
tableName = tableName.ToUpper();
int noColumns = 0;
//
DataSet rs = null;
using (DatabaseMetaData md = new DatabaseMetaData())
{
rs = md.GetColumns(catalog, schema, tableName);
}
for (int rscount = 0; rscount <= rs.Tables[0].Rows.Count - 1; rscount++)
{
noColumns++;
String columnName = rs.Tables[0].Rows[rscount]["COLUMN_NAME"].ToString();
bool found = false;
for (int i = 0; i < list.Count; i++)
{
MColumn dCol = list[i];
if (columnName.Equals(dCol.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
String sql = dCol.GetSQLModify(m_derivedTable, false);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
found = true;
list.Remove(list[i]);
break;
}
}
if (!found)
{
String sql = "ALTER TABLE " + m_dTableName + " DROP COLUMN " + columnName;
DataBase.DB.ExecuteQuery(sql, null);
}
}
//rs.close();
trx.Close();
// No Columns
if (noColumns == 0)
{
String sql = m_derivedTable.GetSQLCreate();
return(DataBase.DB.ExecuteUpdateMultiple(sql, false, null) >= 0);
}
// New Columns
for (int i = 0; i < list.Count(); i++)
{
MColumn dCol = list[i];
if (dCol.IsVirtualColumn())
{
continue;
}
String sql = dCol.GetSQLAdd(m_derivedTable);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
}
return(true);
} // generateTable
/// <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);
}
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[] 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);
}
/// <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;
}
}
}
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("");
}
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>
/// 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);
}