} // getWarehouses
/* HTML5 */
/// <summary>
/// Load Preferences into Context for selected client.
/// <para>
/// Sets Org info in context and loads relevant field from
/// - AD_Client/Info,
/// - C_AcctSchema,
/// - C_AcctSchema_Elements
/// - AD_Preference
/// </para>
/// Assumes that the context is set for #AD_Client_ID, ##AD_User_ID, #AD_Role_ID
/// </summary>
/// <param name="org">org information</param>
/// <param name="warehouse">optional warehouse information</param>
/// <param name="timestamp">optional date</param>
/// <param name="printerName">optional printer info</param>
/// <returns>AD_Message of error (NoValidAcctInfo) or ""</returns>
public String LoadPreferences(string date, String printerName)
{
if (m_ctx.GetContext("#AD_Client_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Client_ID");
}
if (m_ctx.GetContext("##AD_User_ID").Length == 0)
{
throw new Exception("Missing Context ##AD_User_ID");
}
if (m_ctx.GetContext("#AD_Role_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Role_ID");
}
string dateS = m_ctx.GetContext("#Date");
DateTime dt = DateTime.Now;
long today = CommonFunctions.CurrentTimeMillis();
if (DateTime.TryParse(dateS, out dt))
{
today = CommonFunctions.CurrentTimeMillis(dt);
}
m_ctx.SetContext("#Date", today.ToString());
// Load User/Role Infos
MUser user = MUser.Get(m_ctx, m_ctx.GetAD_User_ID());
MUserPreference preference = user.GetPreference();
MRole role = MRole.GetDefault(m_ctx);
// Optional Printer
if (printerName == null)
{
printerName = "";
}
if (printerName.Length == 0 && preference.GetPrinterName() != null)
{
printerName = preference.GetPrinterName();
}
m_ctx.SetPrinterName(printerName);
if (preference.GetPrinterName() == null && printerName.Length > 0)
{
preference.SetPrinterName(printerName);
}
// Other
m_ctx.SetAutoCommit(preference.IsAutoCommit());
m_ctx.SetAutoNew(Ini.IsPropertyBool(Ini.P_A_NEW));
if (role.IsShowAcct())
{
m_ctx.SetContext("#ShowAcct", preference.IsShowAcct());
}
else
{
m_ctx.SetContext("#ShowAcct", "N");
}
m_ctx.SetContext("#ShowTrl", preference.IsShowTrl());
m_ctx.SetContext("#ShowAdvanced", preference.IsShowAdvanced());
String retValue = "";
int AD_Client_ID = m_ctx.GetAD_Client_ID();
// int AD_Org_ID = org.getKey();
// int AD_User_ID = Env.getAD_User_ID (m_ctx);
int AD_Role_ID = m_ctx.GetAD_Role_ID();
// Other Settings
m_ctx.SetContext("#YYYY", "Y");
// Report Page Size Element
m_ctx.SetContext("#REPORT_PAGE_SIZE", "500");
String sql = "SELECT NAME, VALUE FROM AD_SysConfig WHERE NAME = 'REPORT_PAGE_SIZE'";
IDataReader dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
if (!string.IsNullOrEmpty(dr[1].ToString()))
{
Regex regex = new Regex(@"^[1-9]\d*$");
if (regex.IsMatch(dr[1].ToString()))
{
m_ctx.SetContext("#REPORT_PAGE_SIZE", (dr[1].ToString()));
}
}
}
dr.Close();
// Bulk Report Download
m_ctx.SetContext("#BULK_REPORT_DOWNLOAD", "N");
sql = "SELECT NAME, VALUE FROM AD_SysConfig WHERE NAME = 'BULK_REPORT_DOWNLOAD'";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
if (!string.IsNullOrEmpty(dr[1].ToString()))
{
Regex regex = new Regex(@"Y|N");
if (regex.IsMatch(dr[1].ToString()))
{
m_ctx.SetContext("#BULK_REPORT_DOWNLOAD", (dr[1].ToString()));
}
}
}
dr.Close();
// Set Default Value of System Config in Context
sql = "SELECT NAME, VALUE FROM AD_SysConfig WHERE ISACTIVE = 'Y' AND NAME NOT IN ('REPORT_PAGE_SIZE' , 'BULK_REPORT_DOWNLOAD')";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
if (!string.IsNullOrEmpty(dr[1].ToString()))
{
m_ctx.SetContext("#" + dr[0].ToString(), (dr[1].ToString()));
}
}
dr.Close();
// AccountSchema Info (first)
sql = "SELECT a.C_AcctSchema_ID, a.C_Currency_ID, a.HasAlias, c.ISO_Code, c.StdPrecision "
+ "FROM C_AcctSchema a"
+ " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)"
+ " INNER JOIN C_Currency c ON (a.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ci.AD_Client_ID='" + AD_Client_ID + "'";
dr = null;
try
{
int C_AcctSchema_ID = 0;
dr = DataBase.DB.ExecuteReader(sql);
if (!dr.Read())
{
// No Warning for System
if (AD_Role_ID != 0)
{
retValue = "NoValidAcctInfo";
}
}
else
{
// Accounting Info
C_AcctSchema_ID = Utility.Util.GetValueOfInt(dr[0].ToString());
m_ctx.SetContext("$C_AcctSchema_ID", C_AcctSchema_ID);
m_ctx.SetContext("$C_Currency_ID", Utility.Util.GetValueOfInt(dr[1].ToString()));
m_ctx.SetContext("$HasAlias", dr[2].ToString());
m_ctx.SetContext("$CurrencyISO", dr[3].ToString());
m_ctx.SetStdPrecision(Utility.Util.GetValueOfInt(dr[4].ToString()));
}
dr.Close();
// Accounting Elements
sql = "SELECT ElementType "
+ "FROM C_AcctSchema_Element "
+ "WHERE C_AcctSchema_ID='" + C_AcctSchema_ID + "'"
+ " AND IsActive='Y'";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
m_ctx.SetContext("$Element_" + dr["ElementType"].ToString(), "Y");
}
dr.Close();
// This reads all relevant window neutral defaults
// overwriting superseeded ones. Window specific is read in Maintain
sql = "SELECT Attribute, Value, AD_Window_ID "
+ "FROM AD_Preference "
+ "WHERE AD_Client_ID IN (0, @#AD_Client_ID@)"
+ " AND AD_Org_ID IN (0, @#AD_Org_ID@)"
+ " AND (AD_User_ID IS NULL OR AD_User_ID=0 OR AD_User_ID=@##AD_User_ID@)"
+ " AND IsActive='Y' "
+ "ORDER BY Attribute, AD_Client_ID, AD_User_ID DESC, AD_Org_ID";
// the last one overwrites - System - Client - User - Org - Window
sql = Utility.Env.ParseContext(m_ctx, 0, sql, false);
if (sql.Length == 0)
{
}
else
{
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
string AD_Window_ID = dr[2].ToString();
String at = "";
if (string.IsNullOrEmpty(AD_Window_ID))
{
at = "P|" + dr[0].ToString();
}
else
{
at = "P" + AD_Window_ID + "|" + dr[0].ToString();
}
String va = dr[1].ToString();
m_ctx.SetContext(at, va);
}
dr.Close();
}
// Default Values
sql = "SELECT t.TableName, c.ColumnName "
+ "FROM AD_Column c "
+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) "
+ "WHERE c.IsKey='Y' AND t.IsActive='Y'"
+ " AND EXISTS (SELECT * FROM AD_Column cc "
+ " WHERE ColumnName = 'IsDefault' AND t.AD_Table_ID=cc.AD_Table_ID AND cc.IsActive='Y')";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
LoadDefault(dr[0].ToString(), dr[1].ToString());
}
dr.Close();
}
catch
{
if (dr != null)
{
dr.Close();
}
}
//Ini.SaveProperties(Ini.IsClient());
// Country
m_ctx.SetContext("#C_Country_ID", MCountry.GetDefault(m_ctx).GetC_Country_ID());
m_ctx.SetShowClientOrg(Ini.IsShowClientOrg() ? "Y" : "N");
m_ctx.SetShowMiniGrid(Ini.GetProperty(Ini.P_Show_Mini_Grid));
return(retValue);
} // loadPreferences
/// <SUMmary>
/// Team Forecast Products
/// </SUMmary>
/// <returns>No of lines created</returns>
private int TeamForecastProduct()
{
sql = @"SELECT fl.M_Product_ID,fl.M_AttributeSetInstance_ID,fl.qtyentered,fl.BaseQty,f.C_Forecast_ID,
C_ForecastLine_ID,f.C_Period_ID,fl.C_UOM_ID,NVL(pricestd,0) AS Price,f.C_Currency_ID
FROM C_Forecast f " +
" INNER JOIN C_Forecastline fl ON fl.c_forecast_id = f.c_forecast_id " +
" WHERE f.c_period_id = " + C_Period_ID + " AND f.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND f.isactive = 'Y' AND f.processed = 'Y'" +
" AND C_ForecastLine_ID NOT IN (SELECT C_ForecastLine_ID FROM VA073_MasterForecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(fl.M_Product_ID,0)>0 ";
sql = MRole.GetDefault(mf.GetCtx()).AddAccessSQL(sql, "C_Forecast", true, true); // fully qualified - RO
dsForecast = new DataSet();
dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsForecast.Tables[0].Rows.Count; i++)
{
//Create MasterForecastLine
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, 0, 0, 0, 0, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Forecast_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Period_ID"]), mfLine.GetC_UOM_ID(),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["qtyentered"]),
ConvertedAmt, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]) + " " + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]));
}
}
else
{
//Update quantities AND Price at Product line
Count++;
LineNo += 10;
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundForecast"));
}
return(Count);
}
/// <summary>
/// Get match data for Order, Invoice and Ship/Receipt on Matching PO - invoice form.
/// </summary>
/// <param name="ctx"></param>
/// <param name="displayMATCH_INVOICEs"></param>
/// <param name="chkIsReturnTrxProps"></param>
/// <param name="displayMATCH_ORDERs"></param>
/// <param name="matchToTypeMATCH_SHIPMENTs"></param>
/// <param name="matchedsss"></param>
/// <param name="chkSameBPartnerss"></param>
/// <param name="chkSameProductss"></param>
/// <param name="chkSameQtyss"></param>
/// <param name="from_ss"></param>
/// <param name="fromIfs"></param>
/// <param name="to_Dats"></param>
/// <param name="matchToTypes"></param>
/// <param name="MATCH_SHIPMENTs"></param>
/// <param name="MATCH_ORDERs"></param>
/// <param name="onlyProduct_ss"></param>
/// <param name="onlyVendor_ss"></param>
/// <param name="MatchToID"></param>
/// <returns>List of Property Class "GetTableLoadVmatch"</returns>
public List <GetTableLoadVmatch> GetTableLoadVmatch(Ctx ctx, string displayMATCH_INVOICEs, bool chkIsReturnTrxProps, string displayMATCH_ORDERs,
string matchToTypeMATCH_SHIPMENTs, bool matchedsss, string chkSameBPartnerss,
string chkSameProductss, string chkSameQtyss, string from_ss, string fromIfs, string to_Dats, string matchToTypes,
string MATCH_SHIPMENTs, string MATCH_ORDERs, string onlyProduct_ss, string onlyVendor_ss, string MatchToID)
{
List <GetTableLoadVmatch> objj = new List <GetTableLoadVmatch>();
StringBuilder _sql = new StringBuilder();
string sql = "";
var _groupBy = "";
var matched = matchedsss;
DataSet ds = null;
if (displayMATCH_INVOICEs != "")
{
_sql.Append(@"SELECT hdr.C_Invoice_ID AS IDD,hdr.DocumentNo AS DocNum, hdr.DateInvoiced AS Dates, bp.Name AS BPNames,hdr.C_BPartner_ID AS BPartner_ID,
lin.Line AS Lines,lin.C_InvoiceLine_ID AS lineK, p.Name as Product,lin.M_Product_ID AS productk, lin.QtyInvoiced AS qty,SUM(NVL(mi.Qty,0)) AS MATCH
FROM C_Invoice hdr INNER JOIN C_BPartner bp ON (hdr.C_BPartner_ID=bp.C_BPartner_ID) INNER JOIN C_InvoiceLine lin ON (hdr.C_Invoice_ID=lin.C_Invoice_ID)
INNER JOIN M_Product p ON (lin.M_Product_ID=p.M_Product_ID) INNER JOIN C_DocType dt ON (hdr.C_DocType_ID=dt.C_DocType_ID and dt.DocBaseType in ('API','APC')
AND dt.IsReturnTrx = " + (chkIsReturnTrxProps ? "'Y')" : "'N')") + @" FULL JOIN M_MatchInv mi ON (lin.C_InvoiceLine_ID=mi.C_InvoiceLine_ID)
WHERE hdr.DocStatus IN ('CO','CL')" + (matched && MatchToID != "" ? " AND lin.M_InOutLine_ID = " + MatchToID : ""));
_groupBy = " GROUP BY hdr.C_Invoice_ID,hdr.DocumentNo,hdr.DateInvoiced,bp.Name,hdr.C_BPartner_ID,"
+ " lin.Line,lin.C_InvoiceLine_ID,p.Name,lin.M_Product_ID,lin.QtyInvoiced "
+ "HAVING "
+ (matched ? "0" : "lin.QtyInvoiced")
+ "<>SUM(NVL(mi.Qty,0)) ORDER BY hdr.DocumentNo";
}
else if (displayMATCH_ORDERs != "")
{
_sql.Append(@"SELECT hdr.C_Order_ID AS IDD,hdr.DocumentNo AS Docnum, hdr.DateOrdered AS Dates, bp.Name AS BPNames,hdr.C_BPartner_ID AS Bpartner_Id,
lin.Line AS Lines,lin.C_OrderLine_ID AS Linek, p.Name as Product,lin.M_Product_ID AS Productk,lin.QtyOrdered AS qty,SUM(COALESCE(mo.Qty,0)) AS MATCH
FROM C_Order hdr INNER JOIN C_BPartner bp ON (hdr.C_BPartner_ID=bp.C_BPartner_ID) INNER JOIN C_OrderLine lin ON (hdr.C_Order_ID=lin.C_Order_ID)
INNER JOIN M_Product p ON (lin.M_Product_ID=p.M_Product_ID) INNER JOIN C_DocType dt ON (hdr.C_DocType_ID=dt.C_DocType_ID AND dt.DocBaseType='POO'
AND dt.isReturnTrx = " + (chkIsReturnTrxProps ? "'Y')" : "'N')") + @" FULL JOIN M_MatchPO mo ON (lin.C_OrderLine_ID=mo.C_OrderLine_ID)
WHERE hdr.DocStatus IN ('CO','CL')" + (matched && MatchToID != "" ? " AND mo.M_InOutLine_ID = " + MatchToID : ""));
//Conneted this condition because of partialy received qty from MR In case of Purcahse Order : Done by Manjot issue assigned by Puneet and Mukesh Sir
//mo."
// + (matchToTypes == MATCH_SHIPMENTs ? "M_InOutLine_ID" : "C_InvoiceLine_ID")
// + (matched ? " IS NOT NULL" : " IS NULL")
// + " AND
_groupBy = " GROUP BY hdr.C_Order_ID,hdr.DocumentNo,hdr.DateOrdered,bp.Name,hdr.C_BPartner_ID,"
+ " lin.Line,lin.C_OrderLine_ID,p.Name,lin.M_Product_ID,lin.QtyOrdered "
+ "HAVING "
+ (matched ? "0" : "lin.QtyOrdered")
+ "<>SUM(COALESCE(mo.Qty,0)) ORDER BY hdr.DocumentNo";
}
else // Shipment
{
_sql.Append(@"SELECT hdr.M_InOut_ID AS IDD,hdr.DocumentNo AS Docnum, hdr.MovementDate AS Dates, bp.Name AS BPNames,hdr.C_BPartner_ID AS Bpartner_Id,
lin.Line AS Lines,lin.M_InOutLine_ID AS Linek, p.Name as Product,lin.M_Product_ID AS Productk, lin.MovementQty AS qty,SUM(NVL(m.Qty,0)) AS MATCH
FROM M_InOut hdr INNER JOIN C_BPartner bp ON (hdr.C_BPartner_ID=bp.C_BPartner_ID) INNER JOIN M_InOutLine lin ON (hdr.M_InOut_ID=lin.M_InOut_ID)
INNER JOIN M_Product p ON (lin.M_Product_ID=p.M_Product_ID) INNER JOIN C_DocType dt ON (hdr.C_DocType_ID = dt.C_DocType_ID AND dt.DocBaseType='MMR'
AND dt.isReturnTrx = " + (chkIsReturnTrxProps ? "'Y')" : "'N')") + " FULL JOIN " + (matchToTypes == MATCH_ORDERs ? "M_MatchPO" : "M_MatchInv")
+ @" m ON (lin.M_InOutLine_ID=m.M_InOutLine_ID) WHERE hdr.DocStatus IN ('CO','CL')" + (matched && MatchToID != "" ? (matchToTypes == MATCH_ORDERs ? " AND m.C_OrderLine_ID = "
+ MatchToID : " AND m.C_InvoiceLine_ID = " + MatchToID) : ""));
_groupBy = " GROUP BY hdr.M_InOut_ID,hdr.DocumentNo,hdr.MovementDate,bp.Name,hdr.C_BPartner_ID,"
+ " lin.Line,lin.M_InOutLine_ID,p.Name,lin.M_Product_ID,lin.MovementQty "
+ "HAVING "
+ (matched ? "0" : "lin.MovementQty")
+ "<>SUM(NVL(m.Qty,0)) ORDER BY hdr.DocumentNo";
}
if (onlyProduct_ss != "")
{
_sql.Append(onlyProduct_ss);
}
if (onlyVendor_ss != "")
{
_sql.Append(onlyVendor_ss);
}
if (from_ss != "")
{
_sql.Append(from_ss);
}
else if (fromIfs != "")
{
_sql.Append(fromIfs);
}
else if (to_Dats != "")
{
_sql.Append(to_Dats);
}
if (chkSameBPartnerss != "")
{
_sql.Append(chkSameBPartnerss);
}
if (chkSameProductss != "")
{
_sql.Append(chkSameProductss);
}
if (chkSameQtyss != "")
{
_sql.Append(chkSameQtyss);
}
sql = MRole.GetDefault(ctx).AddAccessSQL(_sql.ToString(), "hdr", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO) + _groupBy;
ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
GetTableLoadVmatch obj = new GetTableLoadVmatch();
obj._ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["IDD"]);
obj.DocNo = Util.GetValueOfString(ds.Tables[0].Rows[i]["Docnum"]);
obj.Date = Convert.ToDateTime(ds.Tables[0].Rows[i]["Dates"]);
obj.CBPartnerID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["Bpartner_Id"]);
obj.CBPartnerIDK = Util.GetValueOfString(ds.Tables[0].Rows[i]["BPNames"]);
obj.Line = Util.GetValueOfString(ds.Tables[0].Rows[i]["Lines"]);
obj.Line_K = Util.GetValueOfString(ds.Tables[0].Rows[i]["Linek"]);
obj.MProductID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["Productk"]);
obj.MProductIDK = Util.GetValueOfString(ds.Tables[0].Rows[i]["Product"]);
obj.Qty = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["qty"]);
obj.Matched = Util.GetValueOfString(ds.Tables[0].Rows[i]["MATCH"]);
objj.Add(obj);
}
}
return(objj);
}
/// <summary>
/// Query
/// </summary>
/// <param name="ctx"></param>
/// <param name="whereClause">the SQL where clause (w/o the WHERE)</param>
/// <param name="orderClause"></param>
/// <param name="pageNo"></param>
public void Query(Ctx ctx, String whereClause, String orderClause, int pageNo = 0)
{
RColumn rc = null;
// Create SQL
StringBuilder sql = new StringBuilder("SELECT ");
int size = cols.Count;
for (int i = 0; i < size; i++)
{
rc = (RColumn)cols[i];//.get(i);
if (i > 0)
{
sql.Append(",");
}
sql.Append(rc.GetColSQL());
}
sql.Append(" FROM ").Append(_TableName).Append(" ").Append(RModel.TABLE_ALIAS);
if (whereClause != null && whereClause.Length > 0)
{
sql.Append(" WHERE ").Append(whereClause);
}
String finalSQL = MRole.GetDefault(ctx, false).AddAccessSQL(
sql.ToString(), RModel.TABLE_ALIAS, MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
if (orderClause != null && orderClause.Length > 0)
{
finalSQL += " ORDER BY " + orderClause;
}
log.Fine(finalSQL);
// FillData
int index = 0; // rowset index
_rows.Clear();
//IDataReader idr = null;
try
{
//Statement stmt = DataBase.createStatement();
//ResultSet rs = stmt.executeQuery(finalSQL);
//idr = DataBase.DB.ExecuteReader(finalSQL, null, null);
DataSet ds = DB.GetDatabase().ExecuteDatasetPaging(finalSQL, pageNo, 50, 0);
#region Commented
//while (idr.Read())
//{
// List<Object> row = new List<Object>(size);
// //index = 1;
// index = 0;
// // Columns
// for (int i = 0; i < size; i++)
// {
// rc = (RColumn)cols[i];
// // Get ID
// if (rc.IsIDcol())
// {
// //row.add(new KeyNamePair(rs.getInt(index++), rs.getString(index++)));
// row.Add(new KeyNamePair(Utility.Util.GetValueOfInt(idr[index++]), Utility.Util.GetValueOfString(idr[index++])));
// }
// // Null check
// else if (idr[index] == null)
// {
// index++;
// row.Add(null);
// }
// else if (rc.GetColClass() == typeof(String))
// {
// row.Add(Utility.Util.GetValueOfString(idr[index++]));
// }
// else if (rc.GetColClass() == typeof(Decimal))
// {
// row.Add(Utility.Util.GetValueOfDecimal(idr[index++]));
// }
// else if (rc.GetColClass() == typeof(Double))
// {
// row.Add(Utility.Util.GetValueOfDouble(idr[index++]));
// }
// else if (rc.GetColClass() == typeof(int))
// {
// row.Add(Utility.Util.GetValueOfInt(idr[index++]));
// }
// else if (rc.GetColClass() == typeof(DateTime))
// {
// row.Add(Utility.Util.GetValueOfDateTime(idr[index++]));
// }
// else if (rc.GetColClass() == typeof(Boolean))
// {
// row.Add(Utility.Util.GetValueOfBool("Y".Equals(idr[index++])));
// }
// else // should not happen
// {
// row.Add(Utility.Util.GetValueOfString(idr[index++]));
// }
// }
// _rows.Add(row);
//}
//idr.Close();
#endregion
//used for instead of While and used DataSet instead of DataReader
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
List <Object> row = new List <Object>(size);
//index = 1;
index = 0;
// Columns
for (int i = 0; i < size; i++)
{
rc = (RColumn)cols[i];
// Get ID
if (rc.IsIDcol())
{
//row.add(new KeyNamePair(rs.getInt(index++), rs.getString(index++)));
row.Add(new KeyNamePair(Utility.Util.GetValueOfInt(ds.Tables[0].Rows[j][index++]), Utility.Util.GetValueOfString(ds.Tables[0].Rows[j][index++])));
}
// Null check
else if (ds.Tables[0].Rows[j][index] == null)
{
index++;
row.Add(null);
}
else if (rc.GetColClass() == typeof(String))
{
row.Add(Utility.Util.GetValueOfString(ds.Tables[0].Rows[j][index++]));
}
else if (rc.GetColClass() == typeof(Decimal))
{
row.Add(Utility.Util.GetValueOfDecimal(ds.Tables[0].Rows[j][index++]));
}
else if (rc.GetColClass() == typeof(Double))
{
row.Add(Utility.Util.GetValueOfDouble(ds.Tables[0].Rows[j][index++]));
}
else if (rc.GetColClass() == typeof(int))
{
row.Add(Utility.Util.GetValueOfInt(ds.Tables[0].Rows[j][index++]));
}
else if (rc.GetColClass() == typeof(DateTime))
{
row.Add(Utility.Util.GetValueOfDateTime(ds.Tables[0].Rows[j][index++]));
}
else if (rc.GetColClass() == typeof(Boolean))
{
row.Add(Utility.Util.GetValueOfBool("Y".Equals(ds.Tables[0].Rows[j][index++])));
}
else // should not happen
{
row.Add(Utility.Util.GetValueOfString(ds.Tables[0].Rows[j][index++]));
}
}
_rows.Add(row);
}
}
catch (Exception e)
{
//if (idr != null)
//{
// idr.Close();
// idr = null;
//}
if (index == 0)
{
log.Log(Level.SEVERE, finalSQL, e);
}
else
{
log.Log(Level.SEVERE, "Index=" + index + "," + rc, e);
}
//e.printStackTrace();
}
Process();
}
/// <summary>
/// Get All the groups of current client. If a group is assigned to current role then it will be checked otherwise unchecked.
/// </summary>
/// <param name="AD_Role_ID"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <GroupInfo> GetGroupInfo(int AD_Role_ID, string name)
{
List <GroupInfo> gInfo = new List <GroupInfo>();
int groupWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='Group Rights'", null, null));
if (MRole.GetDefault(ctx).GetWindowAccess(groupWindowID) == null || !(bool)MRole.GetDefault(ctx).GetWindowAccess(groupWindowID))
{
return(gInfo);
}
string sql = @"SELECT Name, AD_GroupInfo_ID FROM AD_GroupINfo WHERE IsActive='Y'";
if (!string.IsNullOrEmpty(name))
{
sql += " AND upper(Name) like ('%" + name.ToUpper() + "%')";
}
sql += " ORDER BY upper(name) ";
DataSet ds = DB.ExecuteDataset(sql); // get All Groups.
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
GroupInfo roleInfo = new GroupInfo();
roleInfo.AD_Group_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_GroupInfo_ID"]);
roleInfo.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
roleInfo.IsAssignedToUser = false;
roleInfo.GroupWindowID = groupWindowID;
gInfo.Add(roleInfo);
}
}
sql = "select ad_role_group.AD_GroupInfo_ID,ad_role_group.IsActive from ad_role_group join AD_GroupInfo on ad_role_group.AD_GroupInfo_ID=AD_GroupInfo.AD_GroupInfo_ID WHERE ad_role_group.AD_Role_ID=" + AD_Role_ID;
if (!string.IsNullOrEmpty(name))
{
sql += " AND upper(AD_GroupInfo.Name) like ('%" + name.ToUpper() + "%')";
}
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_GroupInfo", true, false);
DataSet dsURoles = DB.ExecuteDataset(sql); // Get All groups that are assigned to current Role...
if (dsURoles != null && dsURoles.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsURoles.Tables[0].Rows.Count; i++)
{
GroupInfo ginofs = gInfo.Where(a => a.AD_Group_ID == Convert.ToInt32(dsURoles.Tables[0].Rows[i]["AD_GroupInfo_ID"])).FirstOrDefault();
if (ginofs != null)
{
if (dsURoles.Tables[0].Rows[i]["IsActive"].ToString().Equals("Y")) // if a group is assigned to current role, then show it as checked
{
ginofs.IsAssignedToUser = true;
}
else
{
ginofs.IsAssignedToUser = false;
}
}
}
}
gInfo = gInfo.OrderBy(a => !a.IsAssignedToUser).ToList(); // Show assigned Users on the Top.
return(gInfo);
}
/// <summary>
/// Get Information of group like windows, forms, processes, workflows it contained.
/// </summary>
/// <param name="groupID"></param>
/// <returns></returns>
public GroupChildInfo GetGroupInfo(int groupID)
{
GroupChildInfo gInfo = new GroupChildInfo();
string sql = "SELECT Name, Description from AD_GroupInfo WHERE AD_GroupInfo_ID=" + groupID;
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_GroupInfo", true, false);
DataSet ds = DB.ExecuteDataset(sql);
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
return(gInfo);
}
if (ds.Tables[0].Rows[0]["Name"] != null)
{
gInfo.GroupName = ds.Tables[0].Rows[0]["Name"].ToString();
}
if (ds.Tables[0].Rows[0]["Description"] != null)
{
gInfo.Description = ds.Tables[0].Rows[0]["Description"].ToString();
}
sql = @"SELECT AD_WIndow.Name FROM AD_Group_Window JOIN AD_WIndow
ON AD_Group_Window.AD_Window_ID=AD_Window.AD_Window_ID
WHERE AD_Group_Window.IsActive='Y' AND AD_Group_Window.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_WIndow.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder windows = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (windows.Length > 0)
{
windows.Append(", ");
}
windows.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.WindowName = windows.ToString();
sql = @"SELECT AD_Form.Name FROM AD_Group_Form JOIN AD_Form
ON AD_Group_Form.AD_Form_ID=AD_Form.AD_Form_ID
WHERE AD_Group_Form.IsActive='Y' AND AD_Group_Form.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_Form.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder forms = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (forms.Length > 0)
{
forms.Append(", ");
}
forms.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.FormName = forms.ToString();
sql = @"SELECT AD_Process.Name FROM AD_Group_Process JOIN AD_Process
ON AD_Group_Process.AD_Process_ID=AD_Process.AD_Process_ID
WHERE AD_Group_Process.IsActive='Y' AND AD_Group_Process.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_Process.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder processes = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (processes.Length > 0)
{
processes.Append(", ");
}
processes.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.ProcessName = processes.ToString();
sql = @"SELECT AD_workflow.Name FROM AD_Group_workflow JOIN AD_workflow
ON AD_Group_workflow.AD_workflow_ID=AD_workflow.AD_workflow_ID
WHERE AD_Group_workflow.IsActive='Y' AND AD_Group_workflow.AD_GroupInfo_ID=" + groupID + " ORDER BY AD_workflow.Name";
ds = DB.ExecuteDataset(sql);
StringBuilder workflows = new StringBuilder();
if (ds == null || ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (workflows.Length > 0)
{
workflows.Append(", ");
}
workflows.Append(ds.Tables[0].Rows[i]["Name"].ToString());
}
}
gInfo.WorkflowName = workflows.ToString();
return(gInfo);
}
/// <summary>
/// function will accept columnName and Ids selected. Will Fetch information from Default tree hierarchy and get child records accordingly.
/// </summary>
/// <param name="_ctx"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <param name="result"></param>
/// <returns>Name of orgs separated bY commas, and IDS in Reference Object</returns>
private static string GetRecursiveParameterValue(Ctx _ctx, string columnName, string value, ref string result, bool ShowChildOfSelected)
{
string tableName = columnName.Substring(0, columnName.Length - 3);
string[] values = value.Split(',');
String eSql = "";
string result1 = "";
StringBuilder finalResult = new StringBuilder();
StringBuilder nonSummaryResult = new StringBuilder();
if (values.Length > 0)
{
// Get Default Heirarchy
string sqla = @"SELECT PA_HIERARCHY_id FROM PA_Hierarchy WHERE ISACTIVE ='Y'
ORDER BY ISDEFAULT DESC ,PA_HIERARCHY_id ASC";
sqla = MRole.GetDefault(_ctx).AddAccessSQL(sqla, "PA_Hierarchy", true, true);
object ID = DB.ExecuteScalar(sqla);
int _PA_Hierarchy_ID = 0;
if (ID != null && ID != DBNull.Value)
{
_PA_Hierarchy_ID = Util.GetValueOfInt(ID);
}
Language _language = Language.GetLanguage(_ctx.GetAD_Language());
//Get Query to fetch identifier value from table based on column selected. it will be used to display identifires on for parameter in report.
eSql = VLookUpFactory.GetLookup_TableDirEmbed(_language, columnName, columnName.Substring(0, columnName.Length - 3));
for (int i = 0; i < values.Length; i++)
{
if (!string.IsNullOrEmpty(values[i]))
{
//try
//{
string sqlCheckSummary = "SELECT IsSummary FROM " + tableName + " WHERE " + columnName + "=" + values[i];
object val = DB.ExecuteScalar(sqlCheckSummary);
if (val != null && val != DBNull.Value)
{
if (val.ToString().Equals("N")) // If non-summary is selected then add it string and continue to next ID
{
if (nonSummaryResult.Length > 0)
{
nonSummaryResult.Append("," + values[i]);
}
else
{
nonSummaryResult.Append(values[i]);
}
continue;
}
}
//}
//catch
//{
// result = "";
// continue;
//}
// Fetch child records from tree hierarchy based on ID selected.
if (columnName.Equals("AD_Org_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Organization, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_BPartner_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_BPartner, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("M_Product_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Product, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_Project_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Project, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("AD_OrgTrx_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_OrgTrx, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("Account_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Account, Convert.ToInt32(values[i]));
}
else if (columnName.Equals("C_Campaign_ID", StringComparison.OrdinalIgnoreCase))
{
result1 = MReportTree.GetWhereClause(_ctx, _PA_Hierarchy_ID, MAcctSchemaElement.ELEMENTTYPE_Campaign, Convert.ToInt32(values[i]));
}
if (result1.IndexOf("(") > -1)
{
result1 = result1.Substring(result1.IndexOf("(") + 1);
result1 = result1.Substring(0, result1.IndexOf(")"));
}
else
{
result1 = result1.Substring(result1.IndexOf("=") + 1);
}
//create list of sleected IDs in stringbuilder
if (result1 != values[i] && result1.Length > 0)
{
if (finalResult.Length > 0)
{
finalResult.Append("," + result1);
}
else
{
finalResult.Append(result1);
}
}
}
}
}
StringBuilder identifiedsName = new StringBuilder();
if (finalResult.Length > 0)
{
if (finalResult.ToString().IndexOf(",") > -1)
{
eSql = eSql + " AND " + columnName + " IN (" + finalResult.ToString() + ")";
}
else
{
eSql = eSql + " AND " + columnName + " = " + finalResult.ToString();
}
//eSql = eSql + " AND " + result1;
//if (!string.IsNullOrEmpty(finalResult.ToString()))
//{
// result = result + "," + finalResult.ToString();
//}
DataSet dsIdeintifiers = DB.ExecuteDataset(eSql);
if (ShowChildOfSelected && (dsIdeintifiers != null && dsIdeintifiers.Tables[0].Rows.Count > 0))
{
for (int s = 0; s < dsIdeintifiers.Tables[0].Rows.Count; s++)
{
if (identifiedsName.Length > 0)
{
identifiedsName.Append(",");
}
identifiedsName.Append(dsIdeintifiers.Tables[0].Rows[s][0]);
}
}
}
if (nonSummaryResult.Length > 0 || finalResult.Length > 0)
{
if (nonSummaryResult.Length > 0)
{
result = nonSummaryResult.ToString();
}
if (finalResult.Length > 0)
{
if (result.Length > 0)
{
result += ",";
}
result += finalResult.ToString();
}
}
if (identifiedsName != null)
{
return(identifiedsName.ToString());
}
return("");
}
/// <summary>
/// Create Change Log only if table is logged
/// </summary>
/// <param name="TrxName">transaction name</param>
/// <param name="AD_ChangeLog_ID">0 for new change log</param>
/// <param name="AD_Table_ID">table</param>
/// <param name="AD_Column_ID">column</param>
/// <param name="keyInfo">key value(s)</param>
/// <param name="AD_Client_ID">client</param>
/// <param name="AD_Org_ID">org</param>
/// <param name="OldValue">old</param>
/// <param name="NewValue">new</param>
/// <param name="tableName"></param>
/// <param name="type"></param>
/// <returns>change log or null</returns>
public MChangeLog ChangeLog(Trx trx, int AD_ChangeLog_ID,
int AD_Table_ID, int AD_Column_ID, Object keyInfo,
int AD_Client_ID, int AD_Org_ID,
Object oldValue, Object newValue,
String tableName, String type)
{
// Null handling
if (oldValue == null && newValue == null)
{
return(null);
}
// Equal Value
if (oldValue != null && newValue != null && oldValue.Equals(newValue))
{
return(null);
}
// No Log
if (MChangeLog.IsNotLogged(AD_Table_ID, tableName, AD_Column_ID, type))
{
return(null);
}
// Role Logging
MRole role = MRole.GetDefault(GetCtx(), false);
// Do we need to log
if (_webStoreSession || // log if WebStore
MChangeLog.IsLogged(AD_Table_ID, type) || // im/explicit log
(role != null && role.IsChangeLog())) // Role Logging
{
;
}
else
{
return(null);
}
//
log.Finest("AD_ChangeLog_ID=" + AD_ChangeLog_ID
+ ", AD_Session_ID=" + GetAD_Session_ID()
+ ", AD_Table_ID=" + AD_Table_ID + ", AD_Column_ID=" + AD_Column_ID
+ ": " + oldValue + " -> " + newValue);
//Boolean success = false;
try
{
String trxName = null;
if (trx != null)
{
trxName = trx.GetTrxName();
}
MChangeLog cl = new MChangeLog(GetCtx(),
AD_ChangeLog_ID, trxName, GetAD_Session_ID(),
AD_Table_ID, AD_Column_ID, keyInfo, AD_Client_ID, AD_Org_ID,
oldValue, newValue);
if (cl.Save())
{
return(cl);
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, "AD_ChangeLog_ID=" + AD_ChangeLog_ID
+ ", AD_Session_ID=" + GetAD_Session_ID()
+ ", AD_Table_ID=" + AD_Table_ID + ", AD_Column_ID=" + AD_Column_ID, e);
return(null);
}
log.Log(Level.SEVERE, "AD_ChangeLog_ID=" + AD_ChangeLog_ID
+ ", AD_Session_ID=" + GetAD_Session_ID()
+ ", AD_Table_ID=" + AD_Table_ID + ", AD_Column_ID=" + AD_Column_ID);
return(null);
}
/**************************************************************************
* Get Assignments for timeframe.
* <pre>
* - Resource is Active and Available
* - Resource UnAvailability
* - NonBusinessDay
* - ResourceType Available
* </pre>
* @param S_Resource_ID resource
* @param start_Date start date
* @param end_Date optional end date, need to provide qty to calculate it
* @param qty optional qty in ResourceType UOM - ignored, if end date is not null
* @param getAll if true return all errors
* @param trxName transaction
* @return Array of existing Assigments or null - if free
*/
//@SuppressWarnings("unchecked")
public MAssignmentSlot[] GetAssignmentSlots(int S_Resource_ID,
DateTime?start_Date, DateTime?end_Date,
Decimal?qty, bool getAll, Trx trxName)
{
log.Config(start_Date.ToString());
if (_S_Resource_ID != S_Resource_ID)
{
GetBaseInfo(S_Resource_ID);
}
//
List <MAssignmentSlot> list = new List <MAssignmentSlot>();
MAssignmentSlot ma = null;
if (!_isAvailable)
{
ma = new MAssignmentSlot(EARLIEST, LATEST,
Msg.GetMsg(_ctx, "ResourceNotAvailable"), "", MAssignmentSlot.STATUS_NotAvailable);
if (!getAll)
{
return new MAssignmentSlot[] { ma }
}
;
list.Add(ma);
}
_startDate = start_Date;
_endDate = end_Date;
if (_endDate == null)
{
_endDate = MUOMConversion.GetEndDate(_ctx, _startDate, _C_UOM_ID, qty);
}
log.Fine("- EndDate=" + _endDate);
// Resource Unavailability -------------------------------------------
// log.fine( "- Unavailability -");
String sql = "SELECT Description, DateFrom, DateTo "
+ "FROM S_ResourceUnavailable "
+ "WHERE S_Resource_ID=@1" // #1
+ " AND DateTo >= @2" // #2 start
+ " AND DateFrom <= @3" // #3 end
+ " AND IsActive='Y'";
IDataReader dr = null;
System.Data.SqlClient.SqlParameter[] param = null;
try
{
// log.fine( sql, "ID=" + S_Resource_ID + ", Start=" + m_startDate + ", End=" + m_endDate);
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@1", _S_Resource_ID);
param[1] = new System.Data.SqlClient.SqlParameter("@2", _startDate);
param[2] = new System.Data.SqlClient.SqlParameter("@3", _endDate);
dr = DataBase.DB.ExecuteReader(sql, param, trxName);
while (dr.Read())
{
ma = new MAssignmentSlot(TimeUtil.GetDay(dr.GetDateTime(1)),
TimeUtil.GetNextDay(dr.GetDateTime(2)), // user entered date need to convert to not including end time
Msg.GetMsg(_ctx, "ResourceUnAvailable"), dr.GetString(1),
MAssignmentSlot.STATUS_UnAvailable);
// log.fine( "- Unavailable", ma);
if (getAll)
{
CreateDaySlot(list, ma);
}
else
{
list.Add(ma);
}
}
dr.Close();
dr = null;
param = null;
}
catch (Exception e)
{
if (dr != null)
{
dr.Close();
}
dr = null;
param = null;
log.Log(Level.SEVERE, sql, e);
ma = new MAssignmentSlot(EARLIEST, LATEST,
Msg.GetMsg(_ctx, "ResourceUnAvailable"), e.ToString(),
MAssignmentSlot.STATUS_UnAvailable);
}
if (ma != null && !getAll)
{
return new MAssignmentSlot[] { ma }
}
;
// NonBusinessDay ----------------------------------------------------
// log.fine( "- NonBusinessDay -");
// "WHERE TRUNC(Date1) BETWEEN TRUNC(?) AND TRUNC(?)" causes
// ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
sql = MRole.GetDefault(_ctx, false).AddAccessSQL(
"SELECT Name, Date1 FROM C_NonBusinessDay "
+ "WHERE TRUNC(Date1,'DD') BETWEEN @1 AND @2",
"C_NonBusinessDay", false, false); // not qualified - RO
try
{
DateTime?startDay = TimeUtil.GetDay(_startDate);
DateTime?endDay = TimeUtil.GetDay(_endDate);
// log.fine( sql, "Start=" + startDay + ", End=" + endDay);
param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("@1", startDay);
param[1] = new System.Data.SqlClient.SqlParameter("@2", endDay);
dr = DataBase.DB.ExecuteReader(sql, param, trxName);
while (dr.Read())
{
ma = new MAssignmentSlot(TimeUtil.GetDay(dr.GetDateTime(1)),
TimeUtil.GetNextDay(dr.GetDateTime(1)), // user entered date need to convert to not including end time
Msg.GetMsg(_ctx, "NonBusinessDay"), dr.GetString(0),
MAssignmentSlot.STATUS_NonBusinessDay);
log.Finer("- NonBusinessDay " + ma);
list.Add(ma);
}
dr.Close();
dr = null;
param = null;
}
catch (Exception e)
{
if (dr != null)
{
dr.Close();
dr = null;
}
param = null;
log.Log(Level.SEVERE, sql, e);
ma = new MAssignmentSlot(EARLIEST, LATEST,
Msg.GetMsg(_ctx, "NonBusinessDay"), e.ToString(),
MAssignmentSlot.STATUS_NonBusinessDay);
}
if (ma != null && !getAll)
{
return new MAssignmentSlot[] { ma }
}
;
// ResourceType Available --------------------------------------------
// log.fine( "- ResourceTypeAvailability -");
sql = "SELECT Name, IsTimeSlot,TimeSlotStart,TimeSlotEnd, " // 1..4
+ "IsDateSlot,OnMonday,OnTuesday,OnWednesday," // 5..8
+ "OnThursday,OnFriday,OnSaturday,OnSunday " // 9..12
+ "FROM S_ResourceType "
+ "WHERE S_ResourceType_ID=@1";
try
{
param = new System.Data.SqlClient.SqlParameter[1];
param[0] = new System.Data.SqlClient.SqlParameter("@1", _S_ResourceType_ID);
dr = DataBase.DB.ExecuteReader(sql, param, trxName);
if (dr.Read())
{
_typeName = dr.GetString(0);
// TimeSlot
if ("Y".Equals(dr.GetString(1)))
{
_slotStartTime = TimeUtil.GetDayTime(_startDate, dr.GetDateTime(2));
_slotEndTime = TimeUtil.GetDayTime(_endDate, dr.GetDateTime(3));
if (TimeUtil.InRange(_startDate, _endDate, _slotStartTime, _slotEndTime))
{
ma = new MAssignmentSlot(_slotStartTime, _slotEndTime,
Msg.GetMsg(_ctx, "ResourceNotInSlotTime"), _typeName,
MAssignmentSlot.STATUS_NotInSlotTime);
if (getAll)
{
CreateTimeSlot(list,
dr.GetDateTime(2), dr.GetDateTime(3));
}
}
} // TimeSlot
// DaySlot
if ("Y".Equals(dr.GetString(4)))
{
if (TimeUtil.InRange(_startDate, _endDate,
"Y".Equals(dr.GetString(5)), "Y".Equals(dr.GetString(6)), // Mo..Tu
"Y".Equals(dr.GetString(7)), "Y".Equals(dr.GetString(8)), "Y".Equals(dr.GetString(9)), // We..Fr
"Y".Equals(dr.GetString(10)), "Y".Equals(dr.GetString(11))))
{
ma = new MAssignmentSlot(_startDate, _endDate,
Msg.GetMsg(_ctx, "ResourceNotInSlotDay"), _typeName,
MAssignmentSlot.STATUS_NotInSlotDay);
if (getAll)
{
CreateDaySlot(list,
"Y".Equals(dr.GetString(5)), "Y".Equals(dr.GetString(6)), // Mo..Tu
"Y".Equals(dr.GetString(7)), "Y".Equals(dr.GetString(8)), "Y".Equals(dr.GetString(9)), // We..Fr
"Y".Equals(dr.GetString(10)), "Y".Equals(dr.GetString(11)));
}
}
} // DaySlot
}
dr.Close();
dr = null;
param = null;
}
catch (Exception e)
{
if (dr != null)
{
dr.Close();
dr = null;
}
param = null;
log.Log(Level.SEVERE, sql, e);
ma = new MAssignmentSlot(EARLIEST, LATEST,
Msg.GetMsg(_ctx, "ResourceNotInSlotDay"), e.ToString(),
MAssignmentSlot.STATUS_NonBusinessDay);
}
if (ma != null && !getAll)
{
return new MAssignmentSlot[] { ma }
}
;
// Assignments -------------------------------------------------------
sql = "SELECT S_ResourceAssignment_ID "
+ "FROM S_ResourceAssignment "
+ "WHERE S_Resource_ID=@1" // #1
+ " AND AssignDateTo >= @2" // #2 start
+ " AND AssignDateFrom <= @3" // #3 end
+ " AND IsActive='Y'";
try
{
param = new System.Data.SqlClient.SqlParameter[3];
param[0] = new System.Data.SqlClient.SqlParameter("@1", _S_Resource_ID);
param[1] = new System.Data.SqlClient.SqlParameter("@2", _startDate);
param[2] = new System.Data.SqlClient.SqlParameter("@3", _endDate);
dr = DataBase.DB.ExecuteReader(sql, param, trxName);
while (dr.Read())
{
MResourceAssignment mAssignment =
new MResourceAssignment(_ctx, Utility.Util.GetValueOfInt(dr[0]), trxName);
ma = new MAssignmentSlot(mAssignment);
if (!getAll)
{
break;
}
list.Add(ma);
}
dr.Close();
dr = null;
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql, e);
ma = new MAssignmentSlot(EARLIEST, LATEST,
Msg.Translate(_ctx, "S_R"), e.ToString(),
MAssignmentSlot.STATUS_NotConfirmed);
}
if (ma != null && !getAll)
{
return new MAssignmentSlot[] { ma }
}
;
/*********************************************************************/
// fill m_timeSlots (required for layout)
CreateTimeSlots();
// Clean list - date range
List <MAssignmentSlot> clean = new List <MAssignmentSlot>(list.Count);
for (int i = 0; i < list.Count; i++)
{
MAssignmentSlot mas = (MAssignmentSlot)list[i];
if ((mas.GetStartTime().Equals(_startDate) || mas.GetStartTime() > _startDate) &&
(mas.GetEndTime().Equals(_endDate)) || mas.GetEndTime() < _endDate)
{
clean.Add(mas);
}
}
// Delete Unavailability TimeSlots when all day assigments exist
MAssignmentSlot[] sorted = new MAssignmentSlot[clean.Count];
sorted = clean.ToArray();
Array.Sort(sorted); // sorted by start/end date
list.Clear(); // used as day list
clean.Clear(); // cleaned days
DateTime?sortedDay = null;
for (int i = 0; i < sorted.Length; i++)
{
if (sortedDay == null)
{
sortedDay = TimeUtil.GetDay(sorted[i].GetStartTime());
}
if (sortedDay.Equals(TimeUtil.GetDay(sorted[i].GetStartTime())))
{
list.Add(sorted[i]);
}
else
{
// process info list -> clean
LayoutSlots(list, clean);
// prepare next
list.Clear();
list.Add(sorted[i]);
sortedDay = TimeUtil.GetDay(sorted[i].GetStartTime());
}
}
// process info list -> clean
LayoutSlots(list, clean);
// Return
MAssignmentSlot[] retValue = new MAssignmentSlot[clean.Count];
retValue = clean.ToArray();
Array.Sort(retValue); // sorted by start/end date
return(retValue);
} // getAssignmentSlots
/// <summary>
///
/// </summary>
/// <param name="contSchedule"></param>
private void GenerateInvoice(VAdvantage.Model.X_C_ContractSchedule contSchedule)
{
if (contSchedule.IsActive())
{
int res = 0;
sql = "SELECT MIN(C_DOCTYPE_ID) FROM C_DocType"
+ @" WHERE DOCBASETYPE='ARI'
AND ISACTIVE ='Y'";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_DocType", true, true);
int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_TrxName()));
// sql = "select noofdays from c_frequency where c_frequency_id = " + cont.GetC_Frequency_ID();
// Decimal? days = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
Decimal?price = null;
if (!cont.IsCancel())
{
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
}
else
{
sql = "UPDATE C_Contract SET RenewalType = null WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
int res2 = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
if (contSchedule.GetEndDate() <= cont.GetCancellationDate())
{
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
}
else
{
sql = "SELECT MAX(C_ContractSchedule_ID) FROM C_ContractSchedule WHERE NVL(C_INVOICE_ID,0) > 0 AND C_Contract_ID = " + cont.GetC_Contract_ID();
int c_contractschedule_id = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_TrxName()));
if (c_contractschedule_id != 0)
{
string date = cont.GetCancellationDate().Value.ToString("dd-MMM-yyyy");
// int contsch = Util.GetValueOfInt(contSchedule.GetC_ContractSchedule_ID()) - 1;
sql = "SELECT daysbetween('" + date + "', EndDate) FROM C_ContractSchedule WHERE C_ContractSchedule_ID= " + c_contractschedule_id;
Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_TrxName()));
// price = Decimal.Multiply(cont.GetPriceEntered(), diffDays.Value);
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
sql = "UPDATE C_ContractSchedule SET IsActive = 'N' WHERE EndDate > '" + date + "' AND C_Contract_ID = " + cont.GetC_Contract_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
}
else
{
sql = "SELECT daysbetween(CancellationDate, StartDate) FROM C_Contract WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
Decimal?diffDays = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_TrxName()));
//price = Decimal.Multiply(Decimal.Divide(cont.GetPriceEntered(), days.Value), diffDays.Value);
price = Decimal.Multiply(cont.GetPriceEntered(), cont.GetQtyEntered());
if (diffDays > 0)
{
sql = "UPDATE C_ContractSchedule SET IsActive = 'N' WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
}
else
{
sql = "UPDATE C_ContractSchedule SET IsActive = 'N' WHERE C_Contract_ID = " + cont.GetC_Contract_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
}
// sql = "update c_contractschedule set isactive = 'N' where enddate > '" + System.DateTime.Now + "' and c_contract_id = " + cont.GetC_Contract_ID();
}
}
}
price = Decimal.Round(price.Value, 2, MidpointRounding.AwayFromZero);
inv = new VAdvantage.Model.MInvoice(GetCtx(), 0, Get_TrxName());
inv.SetAD_Client_ID(cont.GetAD_Client_ID());
inv.SetAD_Org_ID(cont.GetAD_Org_ID());
inv.SetC_BPartner_ID(cont.GetC_BPartner_ID());
if (Util.GetValueOfInt(cont.GetC_Order_ID()) != 0)
{
inv.SetC_Order_ID(cont.GetC_Order_ID());
}
inv.SetC_DocType_ID(C_DocType_ID);
inv.SetC_DocTypeTarget_ID(C_DocType_ID);
inv.SetC_BPartner_Location_ID(cont.GetBill_Location_ID());
inv.SetC_Currency_ID(cont.GetC_Currency_ID());
inv.SetC_PaymentTerm_ID(cont.GetC_PaymentTerm_ID());
inv.SetC_Campaign_ID(cont.GetC_Campaign_ID());
inv.SetIsSOTrx(true);
inv.SetM_PriceList_ID(cont.GetM_PriceList_ID());
inv.SetSalesRep_ID(cont.GetSalesRep_ID());
inv.SetC_Contract_ID(cont.GetC_Contract_ID());
if (!inv.Save())
{
//Neha----If Invoice not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
}
else
{
VAdvantage.Model.MInvoiceLine invLine = new VAdvantage.Model.MInvoiceLine(GetCtx(), 0, Get_TrxName());
invLine.SetAD_Client_ID(inv.GetAD_Client_ID());
invLine.SetAD_Org_ID(inv.GetAD_Org_ID());
invLine.SetC_Campaign_ID(inv.GetC_Campaign_ID());
invLine.SetC_Invoice_ID(inv.GetC_Invoice_ID());
invLine.SetC_UOM_ID(cont.GetC_UOM_ID());
invLine.SetM_Product_ID(cont.GetM_Product_ID());
// Added by Vivek on 21/11/2017 asigned by Pradeep
invLine.SetM_AttributeSetInstance_ID(cont.GetM_AttributeSetInstance_ID());
if (Util.GetValueOfInt(cont.GetC_OrderLine_ID()) != 0)
{
invLine.SetC_OrderLine_ID(cont.GetC_OrderLine_ID());
}
invLine.SetC_Tax_ID(cont.GetC_Tax_ID());
invLine.SetQty(cont.GetQtyEntered());
invLine.SetQtyEntered(cont.GetQtyEntered());
// invLine.SetQtyInvoiced(1);
// invLine.SetPrice(price.Value);
invLine.SetPriceActual(cont.GetPriceEntered());
invLine.SetPriceEntered(cont.GetPriceEntered());
// invLine.SetPriceLimit(price);
invLine.SetPriceList(cont.GetPriceEntered());
if (!invLine.Save())
{
//Neha----If Invoice Line not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice Line");
}
}
string comp = inv.CompleteIt();
//Neha---If Invoice Completed then Set Document Action, Document Status and Contract on Invoice Header---11 Sep,2018
if (comp == "CO")
{
inv.SetDocAction("CL");
inv.SetDocStatus("CO");
//Neha---Set C_Contract_ID on C_invoice table using MClass object--11 Sep,2018
inv.SetC_Contract_ID(cont.GetC_Contract_ID());
if (!inv.Save())
{
//Neha----If Invoice not saved then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot save Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot save Invoice");
}
}
else
{
//Neha----If Invoice not completed then will show the exception---11 Sep,2018
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Cannot complete the Invoice. " + pp.GetName());
}
throw new ArgumentException("Cannot complete the Invoice");
}
#region Commented Code
//Neha---Set C_Contract_ID on C_invoice table using MClass object--11 Sep,2018
//sql = "UPDATE c_invoice SET C_Contract_ID = " + cont.GetC_Contract_ID() + " WHERE c_invoice_id = " + inv.GetC_Invoice_ID();
//res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
//Neha---taxAmt not used in this class----11 Sep,2018
//sql = "SELECT SUM(taxamt) FROM c_invoicetax WHERE c_invoice_id = " + inv.GetC_Invoice_ID();
//Decimal? taxAmt = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
# endregion
sql = "UPDATE C_ContractSchedule SET C_Invoice_ID = " + inv.GetC_Invoice_ID() + ", Processed = 'Y' WHERE C_ContractSchedule_ID = " + contSchedule.GetC_ContractSchedule_ID();
// sql = "update c_contractschedule set c_invoice_id = " + inv.GetC_Invoice_ID() + ", processed = 'Y', TotalAmt = " + inv.GetTotalLines() + ", taxamt = " + taxAmt + ", grandtotal = " + inv.GetGrandTotal() + " where c_contractschedule_id = " + contSchedule.GetC_ContractSchedule_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName()));
//Neha---Append Document No. in sb----12 Sep,2018
sb.Append(inv.GetDocumentNo() + ", ");
//_count++;
}
/// <summary>
/// set node properties
/// </summary>
/// <param name="Node_ID">node key id</param>
/// <param name="Parent_ID">parent node id</param>
/// <param name="seqNo">seq no</param>
/// <param name="onBar">node in favorite tree</param>
/// <returns>VTreeNode </returns>
private VTreeNode GetNodeDetail(int Node_ID, int Parent_ID, int seqNo, bool onBar)
{
int AD_Window_ID = 0;
int AD_Process_ID = 0;
int AD_Form_ID = 0;
int AD_Workflow_ID = 0;
int AD_Task_ID = 0;
int AD_Workbench_ID = 0;
VTreeNode retValue = null;
string strColumnName = "";
if (treeType == TreeType.MM)
{
strColumnName = "Ad_Menu_Id";
}
else
{
strColumnName = "Ad_ORG_ID";
}
// Serch For Node details
for (int i = 0; i < dt.Rows.Count; i++)
{
int node1 = Utility.Util.GetValueOfInt(dt.Rows[i][0].ToString());
if (Node_ID != node1) // search for correct one
{
continue;
}
DataRow dr = dt.Rows[i];
int node = Utility.Util.GetValueOfInt(dr[0].ToString());
int index = 1;
string name = dr[index++].ToString();
string description = dr[index++].ToString();
bool isSummary = "Y".Equals(dr[index++].ToString());
string actionColor = "";
// Menu only
//if (getTreeType().equals(TREETYPE_Menu) && !isSummary)
if (!isSummary)
{
bool?blnAccess = null;
if (treeType == TreeType.MM)
{
actionColor = dr[index++].ToString();
AD_Window_ID = (dr[index].ToString().Trim() == "") ? 0 : Utility.Util.GetValueOfInt(dr[index].ToString());
index++;
AD_Process_ID = (dr[index].ToString().Trim() == "") ? 0 : Utility.Util.GetValueOfInt(dr[index].ToString());
index++;
AD_Form_ID = (dr[index].ToString().Trim() == "") ? 0 : Utility.Util.GetValueOfInt(dr[index].ToString());
index++;
AD_Workflow_ID = (dr[index].ToString().Trim() == "") ? 0 : Utility.Util.GetValueOfInt(dr[index].ToString());
index++;
AD_Task_ID = (dr[index].ToString().Trim() == "") ? 0 : Utility.Util.GetValueOfInt(dr[index].ToString());
index++;
AD_Workbench_ID = (dr[index].ToString().Trim() == "") ? 0 : Utility.Util.GetValueOfInt(dr[index].ToString());
index++;
MRole role = MRole.GetDefault(ctx);
if (VTreeNode.ACTION_WINDOW.Equals(actionColor))
{
blnAccess = role.GetWindowAccess(AD_Window_ID);
}
else if (VTreeNode.ACTION_PROCESS.Equals(actionColor) ||
VTreeNode.ACTION_REPORT.Equals(actionColor))
{
blnAccess = role.GetProcessAccess(AD_Process_ID);
}
else if (VTreeNode.ACTION_FORM.Equals(actionColor))
{
blnAccess = role.GetFormAccess(AD_Form_ID);
}
else if (VTreeNode.ACTION_WORKFLOW.Equals(actionColor))
{
blnAccess = role.GetWorkflowAccess(AD_Workflow_ID);
}
else if (VTreeNode.ACTION_TASK.Equals(actionColor))
{
blnAccess = role.GetTaskAccess(AD_Task_ID);
}
}
if (blnAccess != null || _editable) // rw or ro for Role
{
retValue = new VTreeNode(Node_ID, seqNo,
name, description, Parent_ID, isSummary,
actionColor, onBar); // menu has no color
}
}
else
{
retValue = new VTreeNode(Node_ID, seqNo,
name, description, Parent_ID, isSummary,
actionColor, onBar);
}
break;
}
if (retValue != null && treeType == TreeType.MM)
{
// set VTreeNode ID's
retValue.AD_Window_ID = AD_Window_ID;
retValue.AD_Process_ID = AD_Process_ID;
retValue.AD_Form_ID = AD_Form_ID;
retValue.AD_Workflow_ID = AD_Workflow_ID;
retValue.AD_Task_ID = AD_Task_ID;
retValue.AD_Workbench_ID = AD_Workbench_ID;
}
return(retValue);
}
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (text with variables)</returns>
protected override String DoIt()
{
log.Info("C_BPartner_ID=" + _C_BPartner_ID
+ ", AD_Org_ID=" + _AD_Org_ID
+ ", AD_OrgType_ID=" + _AD_OrgType_ID
+ ", AD_Role_ID=" + _AD_Role_ID);
if (_C_BPartner_ID == 0)
{
throw new Exception("No Business Partner ID");
}
MBPartner bp = new MBPartner(GetCtx(), _C_BPartner_ID, Get_Trx());
if (bp.Get_ID() == 0)
{
throw new Exception("Business Partner not found - C_BPartner_ID=" + _C_BPartner_ID);
}
// BP Location
MBPartnerLocation[] locs = bp.GetLocations(false);
if (locs == null || locs.Length == 0)
{
throw new ArgumentException("Business Partner has no Location");
}
// Location
int C_Location_ID = locs[0].GetC_Location_ID();
if (C_Location_ID == 0)
{
throw new ArgumentException("Business Partner Location has no Address");
}
// Create Org
Boolean newOrg = _AD_Org_ID == 0;
MOrg org = new MOrg(GetCtx(), _AD_Org_ID, Get_Trx());
if (newOrg)
{
org.SetValue(bp.GetValue());
org.SetName(bp.GetName());
org.SetDescription(bp.GetDescription());
if (!org.Save())
{
return(GetRetrievedError(org, "Organization not saved"));
//throw new Exception("Organization not saved");
}
}
else // check if linked to already
{
int C_BPartner_ID = org.GetLinkedC_BPartner_ID();
if (C_BPartner_ID > 0)
{
throw new ArgumentException("Organization '" + org.GetName()
+ "' already linked (to C_BPartner_ID=" + C_BPartner_ID + ")");
}
}
_AD_Org_ID = org.GetAD_Org_ID();
// Update Org Info
MOrgInfo oInfo = org.GetInfo();
oInfo.SetAD_OrgType_ID(_AD_OrgType_ID);
if (newOrg)
{
oInfo.SetC_Location_ID(C_Location_ID);
}
// Create Warehouse
MWarehouse wh = null;
if (!newOrg)
{
MWarehouse[] whs = MWarehouse.GetForOrg(GetCtx(), _AD_Org_ID);
if (whs != null && whs.Length > 0)
{
wh = whs[0]; // pick first
}
}
// New Warehouse
if (wh == null)
{
wh = new MWarehouse(org);
if (!wh.Save())
{
return(GetRetrievedError(wh, "Warehouse not saved"));
//throw new Exception("Warehouse not saved");
}
}
// Create Locator
MLocator mLoc = wh.GetDefaultLocator();
if (mLoc == null)
{
mLoc = new MLocator(wh, "Standard");
mLoc.SetIsDefault(true);
mLoc.Save();
}
// Update/Save Org Info
oInfo.SetM_Warehouse_ID(wh.GetM_Warehouse_ID());
if (!oInfo.Save(Get_Trx()))
{
return(GetRetrievedError(oInfo, "Organization Info not saved"));
//throw new Exception("Organization Info not saved");
}
// Update BPartner
bp.SetAD_OrgBP_ID(_AD_Org_ID);
if (bp.GetAD_Org_ID() != 0)
{
bp.SetClientOrg(bp.GetAD_Client_ID(), 0); // Shared BPartner
}
// Save BP
if (!bp.Save())
{
return(GetRetrievedError(bp, "Business Partner not updated"));
//throw new Exception("Business Partner not updated");
}
// Limit to specific Role
if (_AD_Role_ID != 0)
{
Boolean found = false;
MRoleOrgAccess[] orgAccesses = MRoleOrgAccess.GetOfOrg(GetCtx(), _AD_Org_ID);
// delete all accesses except the specific
for (int i = 0; i < orgAccesses.Length; i++)
{
if (orgAccesses[i].GetAD_Role_ID() == _AD_Role_ID)
{
found = true;
}
else
{
orgAccesses[i].Delete(true);
}
}
// create access
if (!found)
{
MRoleOrgAccess orgAccess = new MRoleOrgAccess(org, _AD_Role_ID);
orgAccess.Save();
}
}
// Reset Client Role
MRole.GetDefault(GetCtx(), true);
return("Business Partner - Organization Link created");
} // doIt
/**
* Update/save Goals with Projects
* @return true if updated
*/
private Boolean UpdateProjects()
{
if (!MEASURETYPE_Project.Equals(GetMeasureType()) ||
GetC_ProjectType_ID() == 0)
{
return(false);
}
MGoal[] goals = MGoal.GetMeasureGoals(GetCtx(), GetPA_Measure_ID());
for (int i = 0; i < goals.Length; i++)
{
MGoal goal = goals[i];
// Find Role
MRole role = null;
if (goal.GetAD_Role_ID() != 0)
{
role = MRole.Get(GetCtx(), goal.GetAD_Role_ID());
}
else if (goal.GetAD_User_ID() != 0)
{
MUser user = MUser.Get(GetCtx(), goal.GetAD_User_ID());
MRole[] roles = user.GetRoles(goal.GetAD_Org_ID());
if (roles.Length > 0)
{
role = roles[0];
}
}
if (role == null)
{
role = MRole.GetDefault(GetCtx(), false); // could result in wrong data
}
//
Decimal? ManualActual = null;
MProjectType pt = MProjectType.Get(GetCtx(), GetC_ProjectType_ID());
String sql = pt.GetSqlPI(goal.GetRestrictions(false),
goal.GetMeasureScope(), GetMeasureDataType(), null, role);
IDataReader idr = null;
try // SQL statement could be wrong
{
idr = DataBase.DB.ExecuteReader(sql, null, null);
if (idr.Read())
{
ManualActual = Utility.Util.GetValueOfDecimal(idr[0]);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
// SQL may return no rows or null
if (ManualActual == null)
{
ManualActual = Env.ZERO;
log.Fine("No Value = " + sql);
}
goal.SetMeasureActual(ManualActual);
goal.Save();
}
return(true);
}
/**
* Add Restrictions to SQL
* @param sql orig sql
* @param queryOnly incomplete sql for query restriction
* @param restrictions restrictions
* @param role role
* @param tableName table name
* @param orgColumn org column
* @param bpColumn bpartner column
* @param pColumn product column
* @return updated sql
*/
public static String AddRestrictions(String sql, Boolean queryOnly,
MGoalRestriction[] restrictions, MRole role,
String tableName, String orgColumn, String bpColumn, String pColumn, Ctx ctx)
{
StringBuilder sb = new StringBuilder(sql);
// Org Restrictions
if (orgColumn != null)
{
List <int> list = new List <int>();
for (int i = 0; i < restrictions.Length; i++)
{
if (MGoalRestriction.GOALRESTRICTIONTYPE_Organization.Equals(restrictions[i].GetGoalRestrictionType()))
{
list.Add(restrictions[i].GetOrg_ID());
}
// Hierarchy comes here
}
if (list.Count == 1)
{
sb.Append(" AND ").Append(orgColumn)
.Append("=").Append(list[0]);
}
else if (list.Count > 1)
{
sb.Append(" AND ").Append(orgColumn).Append(" IN (");
for (int i = 0; i < list.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(list[i]);
}
sb.Append(")");
}
} // org
// BPartner Restrictions
if (bpColumn != null)
{
List <int> listBP = new List <int>();
List <int> listBPG = new List <int>();
for (int i = 0; i < restrictions.Length; i++)
{
if (MGoalRestriction.GOALRESTRICTIONTYPE_BusinessPartner.Equals(restrictions[i].GetGoalRestrictionType()))
{
listBP.Add(restrictions[i].GetC_BPartner_ID());
}
// Hierarchy comes here
if (MGoalRestriction.GOALRESTRICTIONTYPE_BusPartnerGroup.Equals(restrictions[i].GetGoalRestrictionType()))
{
listBPG.Add(restrictions[i].GetC_BP_Group_ID());
}
}
// BP
if (listBP.Count == 1)
{
sb.Append(" AND ").Append(bpColumn)
.Append("=").Append(listBP[0]);
}
else if (listBP.Count > 1)
{
sb.Append(" AND ").Append(bpColumn).Append(" IN (");
for (int i = 0; i < listBP.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listBP[i]);
}
sb.Append(")");
}
// BPG
if (bpColumn.IndexOf(".") == -1)
{
bpColumn = tableName + "." + bpColumn;
}
if (listBPG.Count == 1)
{
sb.Append(" AND EXISTS (SELECT * FROM C_BPartner bpx WHERE ")
.Append(bpColumn)
.Append("=bpx.C_BPartner_ID AND bpx.C_BP_GROUP_ID=")
.Append(listBPG[0]).Append(")");
}
else if (listBPG.Count > 1)
{
sb.Append(" AND EXISTS (SELECT * FROM C_BPartner bpx WHERE ")
.Append(bpColumn)
.Append("=bpx.C_BPartner_ID AND bpx.C_BP_GROUP_ID IN (");
for (int i = 0; i < listBPG.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listBPG[i]);
}
sb.Append("))");
}
} // bp
// Product Restrictions
if (pColumn != null)
{
List <int> listP = new List <int>();
List <int> listPC = new List <int>();
for (int i = 0; i < restrictions.Length; i++)
{
if (MGoalRestriction.GOALRESTRICTIONTYPE_Product.Equals(restrictions[i].GetGoalRestrictionType()))
{
listP.Add(restrictions[i].GetM_Product_ID());
}
// Hierarchy comes here
if (MGoalRestriction.GOALRESTRICTIONTYPE_ProductCategory.Equals(restrictions[i].GetGoalRestrictionType()))
{
listPC.Add(restrictions[i].GetM_Product_Category_ID());
}
}
// Product
if (listP.Count == 1)
{
sb.Append(" AND ").Append(pColumn)
.Append("=").Append(listP[0]);
}
else if (listP.Count > 1)
{
sb.Append(" AND ").Append(pColumn).Append(" IN (");
for (int i = 0; i < listP.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listP[i]);
}
sb.Append(")");
}
// Category
if (pColumn.IndexOf(".") == -1)
{
pColumn = tableName + "." + pColumn;
}
if (listPC.Count == 1)
{
sb.Append(" AND EXISTS (SELECT * FROM M_Product px WHERE ")
.Append(pColumn)
.Append("=px.M_Product_ID AND px.M_Product_Category_ID=")
.Append(listPC[0]).Append(")");
}
else if (listPC.Count > 1)
{
sb.Append(" AND EXISTS (SELECT * FROM M_Product px WHERE ")
.Append(pColumn)
.Append("=px.M_Product_ID AND px.M_Product_Category_ID IN (");
for (int i = 0; i < listPC.Count; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(listPC[i]);
}
sb.Append("))");
}
} // product
String finalSQL = sb.ToString();
if (queryOnly)
{
return(finalSQL);
}
if (role == null)
{
role = MRole.GetDefault(ctx);
}
String retValue = role.AddAccessSQL(finalSQL, tableName, true, false);
return(retValue);
}
//[MethodImpl(MethodImplOptions.Synchronized)]
//[OutputCache(NoStore = true, Duration = 0, VaryByParam = "*")]
/// <summary>
/// Entry Point of Framework
/// </summary>
/// <param name="form"></param>
/// <returns></returns>
public ActionResult Index(FormCollection form)
{
//if (!User.Identity.IsAuthenticated)
//{
// // Required to allow javascript redirection through to browser
// this.Response.TrySkipIisCustomErrors = true;
// this.Response.Status = "401 Unauthorized";
// this.Response.StatusCode = 401;
// // note that the following line is .NET 4.5 or later only
// // otherwise you have to suppress the return URL etc manually!
// this.Response.SuppressFormsAuthenticationRedirect = true;
// // If we got this far, something failed
//}
var url = CloudLogin.IsAllowedToLogin(Request.Url.ToString());
if (!string.IsNullOrEmpty(url))
{
return(RedirectPermanent(url));
}
VAdvantage.DataBase.DBConn.SetConnectionString();//Init database conection
LoginModel model = null;
if (User.Identity.IsAuthenticated)
{
try
{
//var conf = WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
// SessionStateSection section = (SessionStateSection) conf.GetSection("system.web/sessionState");
// int timeout = (int) section.Timeout.TotalMinutes;
Session.Timeout = 20; // ideal timout
}
catch
{
}
//AccountController a = new AccountController();
//a.LogOff();
FormsIdentity ident = User.Identity as FormsIdentity;
Ctx ctx = null;
if (ident != null)
{
FormsAuthenticationTicket ticket = ident.Ticket;
string loginContextString = ticket.UserData;// get login context string from Form Ticket
LoginContext lCtx = JsonHelper.Deserialize(loginContextString, typeof(LoginContext)) as LoginContext;
IDataReader dr = null;
//create class from string
string key = "";
if (Session["ctx"] != null)
{
ctx = Session["ctx"] as Ctx;
//Update Old Session
MSession session = MSession.Get(ctx, false);
if (session != null)
{
session.Logout();
}
key = ctx.GetSecureKey();
//if (Session.Timeout < 2)
//{
SessionEventHandler.SessionEnd(ctx);
Session.Timeout = 17;
//}
Session["ctx"] = null;
}
ctx = new Ctx(lCtx.ctxMap); //cretae new context
/* fix for User Value Null value */
if (string.IsNullOrEmpty(ctx.GetContext("##AD_User_Value")))
{
return(new AccountController().LogOff());
}
if (key != "")
{
ctx.SetSecureKey(key);
}
Session["ctx"] = ctx;
//get login Language object on server
var loginLang = ctx.GetAD_Language();
Language l = Language.GetLanguage(ctx.GetAD_Language()); //Language.GetLoginLanguage();
l = VAdvantage.Utility.Env.VerifyLanguage(ctx, l);
ctx.SetContext(VAdvantage.Utility.Env.LANGUAGE, l.GetAD_Language());
ctx.SetContext(VAdvantage.Utility.Env.ISRIGHTTOLEFT, VAdvantage.Utility.Env.IsRightToLeft(loginLang) ? "Y" : "N");
new VAdvantage.Login.LoginProcess(ctx).LoadSysConfig();
LoginHelper.SetSysConfigInContext(ctx);
ViewBag.culture = ctx.GetAD_Language();
ViewBag.direction = ctx.GetIsRightToLeft() ? "rtl" : "ltr";
//Change Authentication
model = new LoginModel();
model.Login1Model = new Login1Model();
model.Login2Model = new Login2Model();
model.Login1Model.UserValue = ctx.GetContext("##AD_User_Value");
model.Login1Model.DisplayName = ctx.GetContext("##AD_User_Name");
model.Login1Model.LoginLanguage = ctx.GetAD_Language();
model.Login2Model.Role = ctx.GetAD_Role_ID().ToString();
model.Login2Model.Client = ctx.GetAD_Client_ID().ToString();
model.Login2Model.Org = ctx.GetAD_Org_ID().ToString();
model.Login2Model.Warehouse = ctx.GetAD_Warehouse_ID().ToString();
var RoleList = new List <KeyNamePair>();
var ClientList = new List <KeyNamePair>();
var OrgList = new List <KeyNamePair>();
var WareHouseList = new List <KeyNamePair>();
string username = "";
IDataReader drRoles = LoginHelper.GetRoles(model.Login1Model.UserValue, false, false);
int AD_User_ID = 0;
if (drRoles.Read())
{
do // read all roles
{
AD_User_ID = Util.GetValueOfInt(drRoles[0].ToString());
int AD_Role_ID = Util.GetValueOfInt(drRoles[1].ToString());
String Name = drRoles[2].ToString();
KeyNamePair p = new KeyNamePair(AD_Role_ID, Name);
RoleList.Add(p);
username = Util.GetValueOfString(drRoles["username"].ToString());
}while (drRoles.Read());
}
drRoles.Close();
model.Login1Model.AD_User_ID = AD_User_ID;
model.Login1Model.DisplayName = username;
//string diableMenu = ctx.GetContext("#DisableMenu");
Helpers.MenuHelper mnuHelper = new Helpers.MenuHelper(ctx); // inilitilize menu class
bool disableMenu = MRole.GetDefault(ctx).IsDisableMenu();
ctx.SetIsBasicDB(mnuHelper.GetIsBasicDB());
// If Home page not linked OR home page Linked BUT Menu is not disabled , then show home page.
// If Home is linked as well as menu is disabled then don't load Default Home Page Settings
if (MRole.GetDefault(ctx).GetHomePage_ID() == 0 || (MRole.GetDefault(ctx).GetHomePage_ID() > 0 && !disableMenu))
{
HomeModels hm = new HomeModels();
objHomeHelp = new HomeHelper();
hm = objHomeHelp.getLoginUserInfo(ctx, 32, 32);
ViewBag.UserPic = hm.UsrImage;
}
ViewBag.DisplayName = model.Login1Model.DisplayName;
if (!disableMenu) // if menu is not disabled, only then load menu.
{
//get current user info
ViewBag.Menu = mnuHelper.GetMenuTree(); // create tree
Session["barNodes"] = ViewBag.Menu.GetBarNodes(); /* add is session to get it in favourite call */
ViewBag.TreeHtml = mnuHelper.GetMenuTreeUI(ViewBag.Menu.GetRootNode(), @Url.Content("~/"));
}
ViewBag.disableMenu = disableMenu;
mnuHelper.dispose();
// LoginHelper.GetClients(id)
ClientList = LoginHelper.GetClients(ctx.GetAD_Role_ID()); // .Add(new KeyNamePair(ctx.GetAD_Client_ID(), ctx.GetAD_Client_Name()));
OrgList = LoginHelper.GetOrgs(ctx.GetAD_Role_ID(), ctx.GetAD_User_ID(), ctx.GetAD_Client_ID()); // .Add(new KeyNamePair(ctx.GetAD_Org_ID(), ctx.GetAD_Org_Name()));
WareHouseList = LoginHelper.GetWarehouse(ctx.GetAD_Org_ID()); // .Add(new KeyNamePair(ctx.GetAD_Warehouse_ID(), ctx.GetContext("#M_Warehouse_Name")));
ViewBag.RoleList = RoleList;
ViewBag.ClientList = ClientList;
ViewBag.OrgList = OrgList;
ViewBag.WarehouseList = WareHouseList;
lock (_lock) // Locked bundle Object and session Creation to handle concurrent requests.
{
//Cretae new Sessin
MSession sessionNew = MSession.Get(ctx, true, GetVisitorIPAddress(true));
var lst = VAdvantage.ModuleBundles.GetStyleBundles(); //Get All Style Bundle
foreach (var b in lst)
{
if (!BundleTable.Bundles.Contains(b))
{
BundleTable.Bundles.Add(b); //Add in Mvc Bundle Table
}
}
var lstRTLStyle = VAdvantage.ModuleBundles.GetRTLStyleBundles(); //Get All Script Bundle
foreach (var b in lstRTLStyle)
{
if (!BundleTable.Bundles.Contains(b))
{
BundleTable.Bundles.Add(b); //Add in Mvc Bundlw Table
}
}
var lstScript = VAdvantage.ModuleBundles.GetScriptBundles(); //Get All Script Bundle
foreach (var b in lstScript)
{
if (!BundleTable.Bundles.Contains(b))
{
BundleTable.Bundles.Add(b); //Add in Mvc Bundlw Table
}
}
ViewBag.LibSuffix = "";
ViewBag.FrameSuffix = "_v1";
int libFound = 0;
foreach (Bundle b in BundleTable.Bundles)
{
if (b.Path.Contains("ViennaBase") && b.Path.Contains("_v") && ViewBag.LibSuffix == "")
{
ViewBag.LibSuffix = Util.GetValueOfInt(ctx.GetContext("#FRONTEND_LIB_VERSION")) > 2
? "_v3" : "_v2";
libFound++;
}
if (b.Path.Contains("VIS") && b.Path.Contains("_v"))
{
ViewBag.FrameSuffix = Util.GetValueOfInt(ctx.GetContext("#FRAMEWORK_VERSION")) > 1
? "_v2" : "_v1";
libFound++;
}
if (libFound >= 2)
{
break;
}
}
//check system setting// set to skipped lib
}
}
}
else
{
model = new LoginModel();
model.Login1Model = new Login1Model();
//model.Login1Model.UserName = "******";
//model.Login1Model.Password = "******";
model.Login1Model.LoginLanguage = "en_US";
model.Login2Model = new Login2Model();
ViewBag.RoleList = new List <KeyNamePair>();
ViewBag.OrgList = new List <KeyNamePair>();
ViewBag.WarehouseList = new List <KeyNamePair>();
ViewBag.ClientList = new List <KeyNamePair>();
ViewBag.Languages = Language.GetLanguages();
Session["ctx"] = null;
ViewBag.direction = "ltr";
ViewBag.LibSuffix = "";
foreach (Bundle b in BundleTable.Bundles)
{
if (b.Path.Contains("ViennaBase") && b.Path.Contains("_v"))
{
ViewBag.LibSuffix = "_v2";
break;
}
}
}
return(View(model));
}
} // prepare
/// <summary>
/// DoIt
/// </summary>
/// <returns>Message</returns>
protected override String DoIt()
{
log.Info("StatementDate=" + _StatementDate + ", IsSOTrx=" + _IsSOTrx
+ ", C_Currency_ID=" + _C_Currency_ID + ",AD_Org_ID=" + _AD_Org_ID
+ ", C_BP_Group_ID=" + _C_BP_Group_ID + ", C_BPartner_ID=" + _C_BPartner_ID
+ ", IsListInvoices=" + _IsListInvoices);
//
StringBuilder sql = new StringBuilder();
sql.Append("SELECT bp.C_BP_Group_ID, oi.C_BPartner_ID,oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID, "
+ "oi.C_Currency_ID, oi.IsSOTrx, " // 5..6
+ "oi.DateInvoiced, oi.NetDays,oi.DueDate,oi.DaysDue, "); // 7..10
if (_C_Currency_ID == 0 || _C_Currency_ID == -1)
{
sql.Append("oi.GrandTotal, oi.PaidAmt, oi.OpenAmt "); // 11..13
}
else
{
String s = ",oi.C_Currency_ID," + _C_Currency_ID + ",oi.DateAcct,oi.C_ConversionType_ID,oi.AD_Client_ID,oi.AD_Org_ID)";
sql.Append("currencyConvert(oi.GrandTotal").Append(s) // 11..
.Append(", currencyConvert(oi.PaidAmt").Append(s)
.Append(", currencyConvert(oi.OpenAmt").Append(s);
}
sql.Append(",oi.C_Activity_ID,oi.C_Campaign_ID,oi.C_Project_ID " // 14
+ "FROM RV_OpenItem oi"
+ " INNER JOIN C_BPartner bp ON (oi.C_BPartner_ID=bp.C_BPartner_ID) "
+ "WHERE oi.ISSoTrx=").Append(_IsSOTrx ? "'Y'" : "'N'");
if (_AD_Org_ID > 0)
{
sql.Append(" AND oi.AD_Org_ID=").Append(_AD_Org_ID);
}
if (_C_BPartner_ID > 0)
{
sql.Append(" AND oi.C_BPartner_ID=").Append(_C_BPartner_ID);
}
else if (_C_BP_Group_ID > 0)
{
sql.Append(" AND bp.C_BP_Group_ID=").Append(_C_BP_Group_ID);
}
sql.Append(" ORDER BY oi.C_BPartner_ID, oi.C_Currency_ID, oi.C_Invoice_ID");
log.Finest(sql.ToString());
String finalSql = MRole.GetDefault(GetCtx(), false).AddAccessSQL(
sql.ToString(), "oi", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
log.Finer(finalSql);
DataSet ds = null;
// IDataReader idr = null;
//
MAging aging = null;
int counter = 0;
int rows = 0;
int AD_PInstance_ID = GetAD_PInstance_ID();
//
try
{
//pstmt = DataBase.prepareStatement(finalSql, Get_Trx());
// idr = DataBase.DB.ExecuteReader(finalSql, null, Get_Trx());
ds = new DataSet();
ds = DataBase.DB.ExecuteDataset(finalSql, null, Get_Trx());
//while (idr.Read())
foreach (DataRow idr in ds.Tables[0].Rows)
{
int C_BP_Group_ID = Utility.Util.GetValueOfInt(idr[0]); // Utility. rs.getInt(1);
int C_BPartner_ID = Utility.Util.GetValueOfInt(idr[1]); // rs.getInt(2);
int C_Invoice_ID = _IsListInvoices ? Utility.Util.GetValueOfInt(idr[2]) : 0;
int C_InvoicePaySchedule_ID = _IsListInvoices ? Utility.Util.GetValueOfInt(idr[3]) : 0;
int C_Currency_ID = Utility.Util.GetValueOfInt(idr[4]);// rs.getInt(5);
Boolean IsSOTrx = "Y".Equals(Utility.Util.GetValueOfString(idr[5]));
//
// Timestamp DateInvoiced = rs.getTimestamp(7);
// int NetDays = rs.getInt(8);
DateTime?DueDate = Utility.Util.GetValueOfDateTime(idr[8]); // rs.getTimestamp(9);
// Days Due
int DaysDue = Utility.Util.GetValueOfInt(idr[9]) // rs.getInt(10) // based on today
+ _statementOffset;
//
Decimal?GrandTotal = Utility.Util.GetValueOfDecimal(idr[10]); // rs.getBigDecimal(11);
// BigDecimal PaidAmt = rs.getBigDecimal(12);
Decimal?OpenAmt = Utility.Util.GetValueOfDecimal(idr[12]); // rs.getBigDecimal(13);
//
int C_Activity_ID = _IsListInvoices ? Utility.Util.GetValueOfInt(idr[13]) : 0;
int C_Campaign_ID = _IsListInvoices ? Utility.Util.GetValueOfInt(idr[14]) : 0;
int C_Project_ID = _IsListInvoices ? Utility.Util.GetValueOfInt(idr[15]) : 0;
rows++;
// New Aging Row
if (aging == null || // Key
AD_PInstance_ID != aging.GetAD_PInstance_ID() ||
C_BPartner_ID != aging.GetC_BPartner_ID() ||
C_Currency_ID != aging.GetC_Currency_ID() ||
C_Invoice_ID != aging.GetC_Invoice_ID() ||
C_InvoicePaySchedule_ID != aging.GetC_InvoicePaySchedule_ID())
{
if (aging != null)
{
if (aging.Save())
{
log.Fine("#" + ++counter + " - " + aging);
}
else
{
log.Log(Level.SEVERE, "Not saved " + aging);
break;
}
}
aging = new MAging(GetCtx(), AD_PInstance_ID, _StatementDate,
C_BPartner_ID, C_Currency_ID,
C_Invoice_ID, C_InvoicePaySchedule_ID,
C_BP_Group_ID, DueDate, IsSOTrx, Get_Trx());
if (_AD_Org_ID > 0)
{
aging.SetAD_Org_ID(_AD_Org_ID);
}
aging.SetC_Activity_ID(C_Activity_ID);
aging.SetC_Campaign_ID(C_Campaign_ID);
aging.SetC_Project_ID(C_Project_ID);
}
// Fill Buckets
aging.Add(DueDate.Value, DaysDue, GrandTotal.Value, OpenAmt.Value);
}
ds = null;
// idr.Close();
if (aging != null)
{
if (aging.Save())
{
log.Fine("#" + ++counter + " - " + aging);
}
else
{
log.Log(Level.SEVERE, "Not saved " + aging);
}
}
}
catch (Exception e)
{
if (ds != null)
{
ds = null;
}
log.Log(Level.SEVERE, finalSql, e);
}
//
log.Info("#" + counter + " - rows=" + rows);
return("");
} // doIt
/// <summary>
/// Addres search
/// </summary>
/// <param name="name_startsWith"></param>
/// <returns></returns>
public List <LocationAddress> GetAddressesSearch(Ctx ctx, string name_startsWith)
{
List <LocationAddress> obj = new List <LocationAddress>();
string sqlquery = "";
#region Commentedny Mohit
//if (Env.IsBaseLanguage(ctx, ""))
//{
// sqlquery = " SELECT * FROM (SELECT (NVL(cn.Name,'')||' '|| NVL(l.ADDRESS1,'') ||' '|| NVL(l.ADDRESS2,'') ||' '|| NVL(l.ADDRESS3,'') ||' '|| NVL(l.ADDRESS4,'') ||' '|| NVL(l.CITY,'') ||' '|| NVL(l.REGIONNAME,'') ||' '|| NVL(l.POSTAL,'') ||' '|| NVL(l.POSTAL_ADD,'')) as address," +
// "cn.Name, l.ADDRESS1 , l.ADDRESS2 , l.ADDRESS3 , l.ADDRESS4 , l.CITY , l.REGIONNAME , l.POSTAL , l.POSTAL_ADD," +
// " l.AD_CLIENT_ID,l.AD_ORG_ID,l.C_CITY_ID,l.C_COUNTRY_ID,l.C_LOCATION_ID,l.C_REGION_ID FROM C_Location l" +
// " LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=l.C_COUNTRY_ID WHERE l.ISACTIVE='Y') qb1";
//}
//else
//{
// // Check applied by mohit - Picked data from translation tab - if base language
// sqlquery = " SELECT * FROM (SELECT (NVL(CNTRL.Name,'')||' '|| NVL(l.ADDRESS1,'') ||' '|| NVL(l.ADDRESS2,'') ||' '|| NVL(l.ADDRESS3,'') ||' '|| NVL(l.ADDRESS4,'') ||' '|| NVL(l.CITY,'') ||' '|| NVL(l.REGIONNAME,'') ||' '|| NVL(l.POSTAL,'') ||' '|| NVL(l.POSTAL_ADD,'')) as address," +
// " cnTRL.Name, l.ADDRESS1 , l.ADDRESS2 , l.ADDRESS3 , l.ADDRESS4 , l.CITY , l.REGIONNAME , l.POSTAL , l.POSTAL_ADD," +
// " l.AD_CLIENT_ID,l.AD_ORG_ID,l.C_CITY_ID,l.C_COUNTRY_ID,l.C_LOCATION_ID,l.C_REGION_ID FROM C_Location l" +
// " LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=l.C_COUNTRY_ID INNER JOIN C_Country_Trl CNTRL ON (cn.C_Country_ID=CNTRL.C_Country_ID) WHERE l.ISACTIVE='Y' AND cnTRL.AD_Language='" + ctx.GetAD_Language() + "') qb1";
//}
#endregion
// Check applied by mohit - asked by mukesh sir - to check if login langauge is base language - then pick non translated data.
if (Env.IsBaseLanguage(ctx, ""))
{
sqlquery = " SELECT (NVL(cn.Name,'')||' '|| NVL(C_Location.ADDRESS1,'') ||' '|| NVL(C_Location.ADDRESS2,'') ||' '|| NVL(C_Location.ADDRESS3,'') ||' '|| NVL(C_Location.ADDRESS4,'') ||' '|| NVL(C_Location.CITY,'') ||' '|| NVL(C_Location.REGIONNAME,'') ||' '|| NVL(C_Location.POSTAL,'') ||' '|| NVL(C_Location.POSTAL_ADD,'')) as address," +
"cn.Name, C_Location.ADDRESS1 , C_Location.ADDRESS2 , C_Location.ADDRESS3 , C_Location.ADDRESS4 , C_Location.CITY , C_Location.REGIONNAME , C_Location.POSTAL , C_Location.POSTAL_ADD," +
" C_Location.AD_CLIENT_ID,C_Location.AD_ORG_ID,C_Location.C_CITY_ID,C_Location.C_COUNTRY_ID,C_Location.C_LOCATION_ID,C_Location.C_REGION_ID FROM C_Location C_Location" +
" LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=C_Location.C_COUNTRY_ID WHERE C_Location.ISACTIVE='Y'" +
" AND Lower( (NVL(cn.Name,'') ||' ' || NVL(C_Location.ADDRESS1,'') ||' ' || NVL(C_Location.ADDRESS2,'') ||' ' || NVL(C_Location.ADDRESS3,'') ||' ' || NVL(C_Location.ADDRESS4,'') ||' ' || NVL(C_Location.CITY,'')" +
" ||' ' || NVL(C_Location.REGIONNAME,'') ||' ' || NVL(C_Location.POSTAL,'') ||' ' || NVL(C_Location.POSTAL_ADD,''))) like LOWER ('%" + name_startsWith + "%') AND rownum < 500";
}
else
{
// Check applied by mohit - Picked data from translation tab - if base language
sqlquery = " SELECT (NVL(CNTRL.Name,'')||' '|| NVL(C_Location.ADDRESS1,'') ||' '|| NVL(C_Location.ADDRESS2,'') ||' '|| NVL(C_Location.ADDRESS3,'') ||' '|| NVL(C_Location.ADDRESS4,'') ||' '|| NVL(C_Location.CITY,'') ||' '|| NVL(C_Location.REGIONNAME,'') ||' '|| NVL(C_Location.POSTAL,'') ||' '|| NVL(C_Location.POSTAL_ADD,'')) as address," +
" cnTRL.Name, C_Location.ADDRESS1 , C_Location.ADDRESS2 , C_Location.ADDRESS3 , C_Location.ADDRESS4 , C_Location.CITY , C_Location.REGIONNAME , C_Location.POSTAL , C_Location.POSTAL_ADD," +
" C_Location.AD_CLIENT_ID,C_Location.AD_ORG_ID,C_Location.C_CITY_ID,C_Location.C_COUNTRY_ID,C_Location.C_LOCATION_ID,C_Location.C_REGION_ID FROM C_Location C_Location" +
" LEFT JOIN C_Country cn on cn.C_COUNTRY_ID=C_Location.C_COUNTRY_ID INNER JOIN C_Country_Trl CNTRL ON (cn.C_Country_ID=CNTRL.C_Country_ID) WHERE C_Location.ISACTIVE='Y' AND cnTRL.AD_Language='" + ctx.GetAD_Language() + "'" +
" AND Lower((NVL(CNTRL.Name,'') ||' ' || NVL(C_Location.ADDRESS1,'') ||' ' || NVL(C_Location.ADDRESS2,'') ||' ' || NVL(C_Location.ADDRESS3,'') ||' ' || NVL(C_Location.ADDRESS4,'') ||' ' " +
" || NVL(C_Location.CITY,'') ||' ' || NVL(C_Location.REGIONNAME,'') ||' ' || NVL(C_Location.POSTAL,'') ||' ' || NVL(C_Location.POSTAL_ADD,''))) like Lower('%" + name_startsWith + "%') AND rownum <500";
}
sqlquery = MRole.GetDefault(ctx).AddAccessSQL(sqlquery, "C_Location", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
//sqlquery = "SELECT * FROM (" + sqlquery + " ) fltr WHERE LOWER(fltr.address) LIKE LOWER('" + name_startsWith + "%') or LOWER(fltr.address) LIKE LOWER('%" + name_startsWith + "%') or LOWER(fltr.address) LIKE LOWER('%" + name_startsWith + "')";
var ds = new DataSet();
ds = DB.ExecuteDataset(sqlquery);
if (ds != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
LocationAddress ladd = new LocationAddress
{
ADDRESS = Convert.ToString(ds.Tables[0].Rows[i]["address"]),
COUNTRYNAME = Convert.ToString(ds.Tables[0].Rows[i]["Name"]),
ADDRESS1 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS1"]),
ADDRESS2 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS2"]),
ADDRESS3 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS3"]),
ADDRESS4 = Convert.ToString(ds.Tables[0].Rows[i]["ADDRESS4"]),
CITYNAME = Convert.ToString(ds.Tables[0].Rows[i]["CITY"]),
STATENAME = Convert.ToString(ds.Tables[0].Rows[i]["REGIONNAME"]),
ZIPCODE = Convert.ToString(ds.Tables[0].Rows[i]["POSTAL"]),
AD_CLIENT_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_CLIENT_ID"]),
AD_ORG_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_ORG_ID"]),
// C_CITY_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_CITY_ID"]),
C_COUNTRY_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_COUNTRY_ID"] == DBNull.Value ? null : ds.Tables[0].Rows[i]["C_COUNTRY_ID"]),
C_LOCATION_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_LOCATION_ID"]),
C_REGION_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_REGION_ID"] == DBNull.Value ? null : ds.Tables[0].Rows[i]["C_REGION_ID"])
};
obj.Add(ladd);
}
}
ds = null;
return(obj);
}
/// <summary>
/// Get all the roles of current User. if a role is assigned then it will be shown as checked otherwise unchecked
/// </summary>
/// <param name="AD_User_ID"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <RolesInfo> GetRoleInfo(int AD_User_ID, string name)
{
List <RolesInfo> rInfo = new List <RolesInfo>();
int RoleWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='Role'", null, null));
if (!(bool)MRole.GetDefault(ctx).GetWindowAccess(RoleWindowID))
{
return(rInfo);
}
int UserTableID = MTable.Get_Table_ID("AD_User");
bool IsUpdate = true;
String sql = "SELECT AD_Client_ID, AD_ORg_ID from AD_User WHERE AD_User_ID=" + AD_User_ID;
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
IsUpdate = MRole.GetDefault(ctx).CanUpdate(Convert.ToInt32(ds.Tables[0].Rows[0]["AD_Client_ID"]), Convert.ToInt32(ds.Tables[0].Rows[0]["AD_Org_ID"]), UserTableID, AD_User_ID, false);
}
sql = @"Select AD_Role.AD_Role_ID, AD_Role.Name from AD_Role WHERE AD_Role.AD_Client_ID=" + ctx.GetAD_Client_ID() + " AND AD_Role.AD_Role_ID > 0 AND IsActive='Y'";
// string sql = @"SELECT AD_Role.AD_Role_ID,
// AD_Role.Name
// FROM AD_Role
// JOIN ad_user_roles
//
// on AD_Role.AD_role_ID=ad_user_roles.AD_Role_ID
// WHERE AD_User_Roles.IsActive='Y' AND AD_Role.IsActive='Y' AND AD_User_Roles.AD_User_ID=" + ctx.GetAD_User_ID();
if (name != null && name.Length > 0)
{
sql += " AND upper(AD_Role.Name) like ('%" + name.ToUpper() + "%')";
}
sql += " ORDER BY upper(AD_Role.Name)";
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_Role", true, false);
ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
RolesInfo roleInfo = new RolesInfo();
roleInfo.AD_Role_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Role_ID"]);
roleInfo.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
roleInfo.IsAssignedToUser = false;
roleInfo.roleWindowID = RoleWindowID;
roleInfo.IsUpdate = IsUpdate;
rInfo.Add(roleInfo);
}
sql = "Select AD_Role_ID, IsActive from ad_user_roles where AD_User_ID=" + AD_User_ID;
DataSet dsURoles = DB.ExecuteDataset(sql);
if (dsURoles != null && dsURoles.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsURoles.Tables[0].Rows.Count; i++)
{
RolesInfo rrinfo = rInfo.Where(a => a.AD_Role_ID == Convert.ToInt32(dsURoles.Tables[0].Rows[i]["AD_Role_ID"])).FirstOrDefault();
if (rrinfo != null)
{
if (dsURoles.Tables[0].Rows[i]["IsActive"].ToString().Equals("Y"))
{
rrinfo.IsAssignedToUser = true;
}
else
{
rrinfo.IsAssignedToUser = false;
}
}
}
}
}
rInfo = rInfo.OrderBy(a => !a.IsAssignedToUser).ToList();
return(rInfo);
}
} // getAD_Role_ID
/// <summary>
/// Load Preferences into Context for selected client.
/// <para>
/// Sets Org info in context and loads relevant field from
/// - AD_Client/Info,
/// - C_AcctSchema,
/// - C_AcctSchema_Elements
/// - AD_Preference
/// </para>
/// Assumes that the context is set for #AD_Client_ID, ##AD_User_ID, #AD_Role_ID
/// </summary>
/// <param name="org">org information</param>
/// <param name="warehouse">optional warehouse information</param>
/// <param name="timestamp">optional date</param>
/// <param name="printerName">optional printer info</param>
/// <returns>AD_Message of error (NoValidAcctInfo) or ""</returns>
public String LoadPreferences(KeyNamePair org,
KeyNamePair warehouse, DateTime timestamp, String printerName)
{
m_org = org;
if (m_ctx == null || org == null)
{
throw new ArgumentException("Required parameter missing");
}
if (m_ctx.GetContext("#AD_Client_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Client_ID");
}
if (m_ctx.GetContext("##AD_User_ID").Length == 0)
{
throw new Exception("Missing Context ##AD_User_ID");
}
if (m_ctx.GetContext("#AD_Role_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Role_ID");
}
// Org Info - assumes that it is valid
m_ctx.SetAD_Org_ID(org.GetKey());
m_ctx.SetContext("#AD_Org_Name", org.GetName());
Ini.SetProperty(Ini.P_ORG, org.GetName());
// Warehouse Info
if (warehouse != null)
{
m_ctx.SetContext("#M_Warehouse_ID", warehouse.GetKey());
Ini.SetProperty(Ini.P_WAREHOUSE, warehouse.GetName());
}
// Date (default today)
long today = CommonFunctions.CurrentTimeMillis();
if (timestamp != null)
{
today = CommonFunctions.CurrentTimeMillis(timestamp);
}
m_ctx.SetContext("#Date", today.ToString());
// Load User/Role Info
MUser user = MUser.Get(m_ctx, GetAD_User_ID());
MUserPreference preference = user.GetPreference();
MRole role = MRole.GetDefault(m_ctx, true);
// Optional Printer
if (printerName == null)
{
printerName = "";
}
if (printerName.Length == 0 && preference.GetPrinterName() != null)
{
printerName = preference.GetPrinterName();
}
m_ctx.SetPrinterName(printerName);
if (preference.GetPrinterName() == null && printerName.Length > 0)
{
preference.SetPrinterName(printerName);
}
// Other
m_ctx.SetAutoCommit(preference.IsAutoCommit());
m_ctx.SetAutoNew(Ini.IsPropertyBool(Ini.P_A_NEW));
if (role.IsShowAcct())
{
m_ctx.SetContext("#ShowAcct", preference.IsShowAcct());
}
else
{
m_ctx.SetContext("#ShowAcct", "N");
}
m_ctx.SetContext("#ShowTrl", preference.IsShowTrl());
m_ctx.SetContext("#ShowAdvanced", preference.IsShowAdvanced());
String retValue = "";
int AD_Client_ID = m_ctx.GetAD_Client_ID();
// int AD_Org_ID = org.getKey();
// int AD_User_ID = Env.getAD_User_ID (m_ctx);
int AD_Role_ID = m_ctx.GetAD_Role_ID();
// Other Settings
m_ctx.SetContext("#YYYY", "Y");
// AccountSchema Info (first)
String sql = "SELECT a.C_AcctSchema_ID, a.C_Currency_ID, a.HasAlias, c.ISO_Code, c.StdPrecision, t.AutoArchive " // Get AutoArchive from Tenant header
+ "FROM C_AcctSchema a"
+ " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)"
+ " INNER JOIN AD_Client t ON (ci.AD_Client_ID=t.AD_Client_ID)"
+ " INNER JOIN C_Currency c ON (a.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ci.AD_Client_ID='" + AD_Client_ID + "'";
IDataReader dr = null;
try
{
int C_AcctSchema_ID = 0;
dr = DataBase.DB.ExecuteReader(sql);
if (!dr.Read())
{
// No Warning for System
if (AD_Role_ID != 0)
{
retValue = "NoValidAcctInfo";
}
}
else
{
// Accounting Info
C_AcctSchema_ID = Utility.Util.GetValueOfInt(dr[0].ToString());
m_ctx.SetContext("$C_AcctSchema_ID", C_AcctSchema_ID);
m_ctx.SetContext("$C_Currency_ID", Utility.Util.GetValueOfInt(dr[1].ToString()));
m_ctx.SetContext("$HasAlias", dr[2].ToString());
m_ctx.SetContext("$CurrencyISO", dr[3].ToString());
m_ctx.SetStdPrecision(Utility.Util.GetValueOfInt(dr[4].ToString()));
m_ctx.SetContext("$AutoArchive", dr[5].ToString());
}
dr.Close();
// Accounting Elements
sql = "SELECT ElementType "
+ "FROM C_AcctSchema_Element "
+ "WHERE C_AcctSchema_ID='" + C_AcctSchema_ID + "'"
+ " AND IsActive='Y'";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
m_ctx.SetContext("$Element_" + dr["ElementType"].ToString(), "Y");
}
dr.Close();
// This reads all relevant window neutral defaults
// overwriting superseeded ones. Window specific is read in Maintain
sql = "SELECT Attribute, Value, AD_Window_ID "
+ "FROM AD_Preference "
+ "WHERE AD_Client_ID IN (0, @#AD_Client_ID@)"
+ " AND AD_Org_ID IN (0, @#AD_Org_ID@)"
+ " AND (AD_User_ID IS NULL OR AD_User_ID=0 OR AD_User_ID=@##AD_User_ID@)"
+ " AND IsActive='Y' "
+ "ORDER BY Attribute, AD_Client_ID, AD_User_ID DESC, AD_Org_ID";
// the last one overwrites - System - Client - User - Org - Window
sql = Utility.Env.ParseContext(m_ctx, 0, sql, false);
if (sql.Length == 0)
{
}
else
{
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
string AD_Window_ID = dr[2].ToString();
String at = "";
if (string.IsNullOrEmpty(AD_Window_ID))
{
at = "P|" + dr[0].ToString();
}
else
{
at = "P" + AD_Window_ID + "|" + dr[0].ToString();
}
String va = dr[1].ToString();
m_ctx.SetContext(at, va);
}
dr.Close();
}
// Default Values
sql = "SELECT t.TableName, c.ColumnName "
+ "FROM AD_Column c "
+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) "
+ "WHERE c.IsKey='Y' AND t.IsActive='Y'"
+ " AND EXISTS (SELECT * FROM AD_Column cc "
+ " WHERE ColumnName = 'IsDefault' AND t.AD_Table_ID=cc.AD_Table_ID AND cc.IsActive='Y')";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
LoadDefault(dr[0].ToString(), dr[1].ToString());
}
dr.Close();
}
catch
{
if (dr != null)
{
dr.Close();
}
}
Ini.SaveProperties(Ini.IsClient());
// Country
m_ctx.SetContext("#C_Country_ID", MCountry.GetDefault(m_ctx).GetC_Country_ID());
m_ctx.SetShowClientOrg(Ini.IsShowClientOrg() ? "Y" : "N");
m_ctx.SetShowMiniGrid(Ini.GetProperty(Ini.P_Show_Mini_Grid));
return(retValue);
} // loadPreferences
/// <summary>
/// Get All Users of current client and org
/// </summary>
/// <param name="searchText"></param>
/// <param name="sortBy"></param>
/// <param name="pageNo"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <UserInfo> GetUserInfo(string searchText, int sortBy, int pageNo, int pageSize)
{
List <UserInfo> uInfo = new List <UserInfo>();
int UserTableID = MTable.Get_Table_ID("AD_User");
int UserWindowID = Convert.ToInt32(DB.ExecuteScalar("SELECT AD_Window_ID from AD_Window WHERE Name='User'", null, null));
if (!(bool)MRole.GetDefault(ctx).GetWindowAccess(UserWindowID))
{
return(uInfo);
}
if (!MRole.GetDefault(ctx).IsTableAccess(UserTableID, false))
{
return(uInfo);
}
string sql = @"SELECT AD_User.Name,
AD_User.Email,
AD_User.AD_User_ID,
AD_User.IsActive,
AD_User.AD_Image_ID,
AD_User.AD_Client_ID,
AD_User.AD_Org_ID,
C_Country.Name as CName
FROM AD_User
LEFT OUTER JOIN C_LOcation
ON AD_User.C_Location_ID=C_Location.C_Location_ID
LEFT OUTER JOIN C_Country
ON C_Country.C_Country_ID=C_Location.C_Country_ID WHERE IsLoginUser='******' ";
if (!String.IsNullOrEmpty(searchText))
{
sql += " AND ( upper(AD_User.Value) like Upper('%" + searchText + "%') OR upper(AD_User.Name) like Upper('%" + searchText + "%') OR upper(AD_User.Email) like Upper('%" + searchText + "%'))";
}
sql += " ORDER BY AD_User.IsActive desc";
if (sortBy == -1 || sortBy == 1)
{
sql += " , upper(AD_User.Name) ASC";
}
else if (sortBy == 2)
{
sql += " , upper(AD_User.Value) ASC";
}
else if (sortBy == 3)
{
sql += " , upper(AD_User.Email) ASC";
}
sql = MRole.GetDefault(ctx).AddAccessSQL(sql, "AD_User", true, false);
DataSet ds = DB.ExecuteDatasetPaging(sql, pageNo, pageSize);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
UserInfo userInfo = new UserInfo();
userInfo.HasAccess = MRole.GetDefault(ctx).IsRecordAccess(UserTableID, Convert.ToInt32(ds.Tables[0].Rows[i]["AD_User_ID"]), true);
userInfo.Username = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
userInfo.Email = Convert.ToString(ds.Tables[0].Rows[i]["Email"]);
userInfo.AD_UserID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_User_ID"]);
userInfo.AD_OrgID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Org_ID"]);
userInfo.AD_ClientID = Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Client_ID"]);
userInfo.Country = Convert.ToString(ds.Tables[0].Rows[i]["CName"]);
userInfo.UserTableID = UserTableID;
userInfo.UserWindowID = UserWindowID;
userInfo.IsActive = ds.Tables[0].Rows[i]["IsActive"].ToString() == "Y" ? true : false;
userInfo.IsUpdate = MRole.GetDefault(ctx).CanUpdate(userInfo.AD_ClientID, userInfo.AD_OrgID, userInfo.UserTableID, userInfo.AD_UserID, false);
if (ds.Tables[0].Rows[i]["AD_Image_ID"] != DBNull.Value && ds.Tables[0].Rows[i]["AD_Image_ID"] != null && Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Image_ID"]) > 0)
{
MImage mimg = new MImage(ctx, Convert.ToInt32(ds.Tables[0].Rows[i]["AD_Image_ID"]), null);
var imgfll = mimg.GetThumbnailURL(46, 46);
userInfo.UserImage = imgfll;
if (userInfo.UserImage == "FileDoesn'tExist" || userInfo.UserImage == "NoRecordFound")
{
userInfo.UserImage = "";
}
}
else
{
userInfo.UserImage = "";
}
uInfo.Add(userInfo);
}
}
return(uInfo);
}
} // getWarehouses
/* HTML5 */
/// <summary>
/// Load Preferences into Context for selected client.
/// <para>
/// Sets Org info in context and loads relevant field from
/// - AD_Client/Info,
/// - C_AcctSchema,
/// - C_AcctSchema_Elements
/// - AD_Preference
/// </para>
/// Assumes that the context is set for #AD_Client_ID, ##AD_User_ID, #AD_Role_ID
/// </summary>
/// <param name="org">org information</param>
/// <param name="warehouse">optional warehouse information</param>
/// <param name="timestamp">optional date</param>
/// <param name="printerName">optional printer info</param>
/// <returns>AD_Message of error (NoValidAcctInfo) or ""</returns>
public String LoadPreferences(string date, String printerName)
{
if (m_ctx.GetContext("#AD_Client_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Client_ID");
}
if (m_ctx.GetContext("##AD_User_ID").Length == 0)
{
throw new Exception("Missing Context ##AD_User_ID");
}
if (m_ctx.GetContext("#AD_Role_ID").Length == 0)
{
throw new Exception("Missing Context #AD_Role_ID");
}
string dateS = m_ctx.GetContext("#Date");
DateTime dt = DateTime.Now;
long today = CommonFunctions.CurrentTimeMillis();
if (DateTime.TryParse(dateS, out dt))
{
today = CommonFunctions.CurrentTimeMillis(dt);
}
m_ctx.SetContext("#Date", today.ToString());
// Load User/Role Infos
MUser user = MUser.Get(m_ctx, m_ctx.GetAD_User_ID());
MUserPreference preference = user.GetPreference();
MRole role = MRole.GetDefault(m_ctx);
// Optional Printer
if (printerName == null)
{
printerName = "";
}
if (printerName.Length == 0 && preference.GetPrinterName() != null)
{
printerName = preference.GetPrinterName();
}
m_ctx.SetPrinterName(printerName);
if (preference.GetPrinterName() == null && printerName.Length > 0)
{
preference.SetPrinterName(printerName);
}
// Other
m_ctx.SetAutoCommit(preference.IsAutoCommit());
m_ctx.SetAutoNew(Ini.IsPropertyBool(Ini.P_A_NEW));
if (role.IsShowAcct())
{
m_ctx.SetContext("#ShowAcct", preference.IsShowAcct());
}
else
{
m_ctx.SetContext("#ShowAcct", "N");
}
m_ctx.SetContext("#ShowTrl", preference.IsShowTrl());
m_ctx.SetContext("#ShowAdvanced", preference.IsShowAdvanced());
String retValue = "";
int AD_Client_ID = m_ctx.GetAD_Client_ID();
// int AD_Org_ID = org.getKey();
// int AD_User_ID = Env.getAD_User_ID (m_ctx);
int AD_Role_ID = m_ctx.GetAD_Role_ID();
// Other Settings
m_ctx.SetContext("#YYYY", "Y");
//LoadSysConfig();
string sql = "";
IDataReader dr = null;
bool checkNonItem = true; // to identify that on Tenant there exista new column "IsAllowNonItem".
// AccountSchema Info (first)
try
{
sql = "SELECT a.C_AcctSchema_ID, a.C_Currency_ID, a.HasAlias, c.ISO_Code, c.StdPrecision, t.AutoArchive, t.IsAllowNonItem " // 6. Get "Alloe Non Item on Ship/Receipt" from Tenant header
+ "FROM C_AcctSchema a"
+ " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)"
+ " INNER JOIN AD_Client t ON (ci.AD_Client_ID=t.AD_Client_ID)"
+ " INNER JOIN C_Currency c ON (a.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ci.AD_Client_ID='" + AD_Client_ID + "'";
dr = DataBase.DB.ExecuteReader(sql);
}
catch
{
checkNonItem = false;
sql = "SELECT a.C_AcctSchema_ID, a.C_Currency_ID, a.HasAlias, c.ISO_Code, c.StdPrecision, t.AutoArchive " // 5. Get AutoArchive from Tenant header
+ "FROM C_AcctSchema a"
+ " INNER JOIN AD_ClientInfo ci ON (a.C_AcctSchema_ID=ci.C_AcctSchema1_ID)"
+ " INNER JOIN AD_Client t ON (ci.AD_Client_ID=t.AD_Client_ID)"
+ " INNER JOIN C_Currency c ON (a.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ci.AD_Client_ID='" + AD_Client_ID + "'";
}
try
{
int C_AcctSchema_ID = 0;
if (!checkNonItem)
{
dr = DataBase.DB.ExecuteReader(sql);
}
if (!dr.Read())
{
// No Warning for System
if (AD_Role_ID != 0)
{
retValue = "NoValidAcctInfo";
}
}
else
{
// Accounting Info
C_AcctSchema_ID = Utility.Util.GetValueOfInt(dr[0].ToString());
m_ctx.SetContext("$C_AcctSchema_ID", C_AcctSchema_ID);
m_ctx.SetContext("$C_Currency_ID", Utility.Util.GetValueOfInt(dr[1].ToString()));
m_ctx.SetContext("$HasAlias", dr[2].ToString());
m_ctx.SetContext("$CurrencyISO", dr[3].ToString());
m_ctx.SetStdPrecision(Utility.Util.GetValueOfInt(dr[4].ToString()));
m_ctx.SetContext("$AutoArchive", dr[5].ToString());
//
if (checkNonItem) // Get "Alloe Non Item on Ship/Receipt" from Tenant header if exist.
{
m_ctx.SetContext("$AllowNonItem", dr[6].ToString());
}
}
dr.Close();
// Accounting Elements
sql = "SELECT ElementType "
+ "FROM C_AcctSchema_Element "
+ "WHERE C_AcctSchema_ID='" + C_AcctSchema_ID + "'"
+ " AND IsActive='Y'";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
m_ctx.SetContext("$Element_" + dr["ElementType"].ToString(), "Y");
}
dr.Close();
// This reads all relevant window neutral defaults
// overwriting superseeded ones. Window specific is read in Maintain
sql = "SELECT Attribute, Value, AD_Window_ID "
+ "FROM AD_Preference "
+ "WHERE AD_Client_ID IN (0, @#AD_Client_ID@)"
+ " AND AD_Org_ID IN (0, @#AD_Org_ID@)"
+ " AND (AD_User_ID IS NULL OR AD_User_ID=0 OR AD_User_ID=@##AD_User_ID@)"
+ " AND IsActive='Y' "
+ "ORDER BY Attribute, AD_Client_ID, AD_User_ID DESC, AD_Org_ID";
// the last one overwrites - System - Client - User - Org - Window
sql = Utility.Env.ParseContext(m_ctx, 0, sql, false);
if (sql.Length == 0)
{
}
else
{
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
string AD_Window_ID = dr[2].ToString();
String at = "";
if (string.IsNullOrEmpty(AD_Window_ID))
{
at = "P|" + dr[0].ToString();
}
else
{
at = "P" + AD_Window_ID + "|" + dr[0].ToString();
}
String va = dr[1].ToString();
m_ctx.SetContext(at, va);
}
dr.Close();
}
// Default Values
sql = "SELECT t.TableName, c.ColumnName "
+ "FROM AD_Column c "
+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) "
+ "WHERE c.IsKey='Y' AND t.IsActive='Y'"
+ " AND EXISTS (SELECT * FROM AD_Column cc "
+ " WHERE ColumnName = 'IsDefault' AND t.AD_Table_ID=cc.AD_Table_ID AND cc.IsActive='Y')";
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
LoadDefault(dr[0].ToString(), dr[1].ToString());
}
dr.Close();
}
catch
{
if (dr != null)
{
dr.Close();
}
}
//Ini.SaveProperties(Ini.IsClient());
// Country
m_ctx.SetContext("#C_Country_ID", MCountry.GetDefault(m_ctx).GetC_Country_ID());
m_ctx.SetShowClientOrg(Ini.IsShowClientOrg() ? "Y" : "N");
m_ctx.SetShowMiniGrid(Ini.GetProperty(Ini.P_Show_Mini_Grid));
return(retValue);
} // loadPreferences
/// <summary>
/// Get the Print Formats for the table.
/// Fill the list and the popup menu
/// </summary>
/// <param name="AD_Table_ID">table</param>
private void GetPrintFormats(int AD_Table_ID, ToolStripDropDownButton sender)
{
ToolStripDropDownButton _popup = sender;
int AD_Client_ID = Env.GetContext().GetAD_Client_ID();
//
String sql = MRole.GetDefault(Env.GetContext()).AddAccessSQL(
"SELECT AD_PrintFormat_ID, Name, AD_Client_ID "
+ "FROM AD_PrintFormat "
+ "WHERE AD_Table_ID='" + AD_Table_ID + "' AND IsTableBased='Y' "
+ "ORDER BY AD_Client_ID DESC, IsDefault DESC, Name", // Own First
"AD_PrintFormat", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
KeyNamePair pp = null;
IDataReader dr = DataBase.DB.ExecuteReader(sql);
try
{
if (sender != null)
{
_popup.DropDownItems.Clear();
}
while (dr.Read())
{
if (Utility.Util.GetValueOfInt(dr[2].ToString()) == AD_Client_ID)
{
pp = new KeyNamePair(Utility.Util.GetValueOfInt(dr[0].ToString()), dr[1].ToString());
_list.Add(pp);
String actionCommand = pp.ToString();
ToolStripMenuItem mi = new System.Windows.Forms.ToolStripMenuItem();
mi.Text = actionCommand;
mi.Click += new EventHandler(ReportFormat_Click);
if (sender != null)
{
_popup.DropDownItems.Add(mi);
}
}
}
dr.Close();
dr = null;
}
catch
{
if (dr != null)
{
dr.Close();
}
}
if (_list.Count == 0)
{
if (pp == null)
{
CreateNewFormat(AD_Table_ID); // calls launch
}
else
{
CopyFormat(pp.GetKey(), AD_Client_ID);
}
}
// One Format exists or no invoker - show it
else if (_list.Count == 1 || sender == null)
{
if (sender != null)
{
_popup.DropDownItems.Clear();
}
LaunchReport((KeyNamePair)_list[0]);
}
// Multiple Formats exist - show selection
else
{
_popup.ShowDropDown();
}
}
/// <SUMmary>
/// Oppportunity Products
/// </SUMmary>
/// <returns>No of Lines created</returns>
private int OnlyOpportunityProducts()
{
if (!Env.IsModuleInstalled("VA073_"))
{
//sql = " SELECT distinct(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL"
// + " AND p.ref_order_id IS ANDNULL pl.m_product_id NOT IN (SELECT DISTINCT(M_Product_ID) FROM c_forecastline fl "
// + " INNER JOIN c_forecast f ON (fl.c_forecast_id = f.c_forecast_id) WHERE f.c_period_id = " + C_Period_ID
// + " AND f.ad_client_id = " + GetCtx().GetAD_Client_ID() + " AND fl.isactive = 'Y')";
sql = " SELECT DISTINCT(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL"
+ " AND p.ref_order_id IS NULL AND pl.m_product_id NOT IN (SELECT m_product_id FROM c_masterforecastline WHERE isactive = 'Y' AND c_masterforecast_id = " + GetRecord_ID() + ")";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, mf.Get_Trx());
while (idr.Read())
{
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "SELECT SUM(nvl(pl.plannedqty,0)) AS Quantity ,SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price, p.C_Currency_ID,pl.C_UOM_ID" +
" FROM c_projectline pl INNER JOIN c_project p ON (p.c_project_id = pl.c_project_id) "
+ " WHERE " +
"pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
+ " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " +
"AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND p.c_order_id IS NULL AND p.ref_order_id IS NULL AND pl.isactive = 'Y'"
+ " GROUP BY C_Currency_ID,pl.C_UOM_ID";
// totalQtyOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
//sql = " SELECT SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) FROM c_projectline pl INNER JOIN c_project p ON (p.c_project_id = pl.c_project_id) "
// + " WHERE " +
//"pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
//+ " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " +
//" AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND p.c_order_id IS NULL AND p.ref_order_id IS NULL AND pl.isactive = 'Y'";
//totalPriceOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
//Conversion from Project to MasterForecast Currency
totalPriceOpp = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Conversion from BaseUOM to UOM on Project Line
totalQtyOpp = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(idr[0]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Quantity"]));
}
if (totalQtyOpp.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPriceOpp.Value, totalQtyOpp.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
mfLine = GenerateMasterForecast(Util.GetValueOfInt(idr[0]), 0, Util.GetValueOfDecimal(Decimal.Zero), totalQtyOpp, avgPrice);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
}
else
{
//VA073_ Module is Installed
sql = "SELECT pl.m_product_id, p.c_project_id,p.C_Currency_ID,pl.c_projectline_id, pl.plannedqty,pl.C_UOM_ID," +
"(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price,pl.M_AttributeSetInstance_ID " +
" FROM C_Project p " +
"INNER JOIN C_ProjectLine pl ON p.C_Project_ID = pl.C_Project_ID" +
" WHERE p.c_order_id IS NULL AND p.ref_order_id IS NULL AND c_period_id = " + C_Period_ID + " AND p.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND C_ProjectLine_ID NOT IN (SELECT C_ProjectLine_ID FROM va073_masterforecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(pl.M_Product_ID,0)>0 ";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_Project", true, true); // fully qualified - RO
dsOpp = new DataSet();
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsOpp.Tables[0].Rows.Count; i++)
{
//Create MasterForecastline
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Conversion from BaseUOM to UOM on Project Line
OppQty = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["plannedqty"]));
if (OppQty == null)
{
OppQty = Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["plannedqty"]);
}
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, 0, 0, Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_Project_ID"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]), 0, 0,
C_Period_ID, Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]),
OppQty, ConvertedAmt,
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ProjectLine " + Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]) + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ProjectLine " + Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]));
}
}
else
{
Count++;
LineNo += 10;
//Update quantities AND price at Product line
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundOpportunity"));
}
}
return(Count);
}
} // create
/// <summary>
/// Load Tab Details from dr into vo
/// </summary>
/// <param name="vo">GridTabVO object</param>
/// <param name="dr">Datarow</param>
/// <returns>true if successful</returns>
private static bool LoadTabDetails(GridTabVO vo, IDataReader dr)
{
MRole role = MRole.GetDefault(vo.ctx, false);
bool showTrl = "Y".Equals(vo.ctx.GetContext("#ShowTrl")) || DataBase.GlobalVariable.IsVisualEditor;
bool showAcct = "Y".Equals(vo.ctx.GetContext("#ShowAcct")) || DataBase.GlobalVariable.IsVisualEditor;
bool showAdvanced = "Y".Equals(vo.ctx.GetContext("#ShowAdvanced")) || DataBase.GlobalVariable.IsVisualEditor;
// VLogger.get().warning("ShowTrl=" + showTrl + ", showAcct=" + showAcct);
try
{
vo.AD_Tab_ID = Utility.Util.GetValueOfInt(dr["AD_Tab_ID"]);
vo.Referenced_Tab_ID = Utility.Util.GetValueOfInt(dr["Referenced_Tab_ID"]);
vo.ctx.SetContext(vo.windowNo, vo.tabNo, "AD_Tab_ID", vo.AD_Tab_ID.ToString());
vo.Name = Utility.Util.GetValueOfString(dr["Name"]);
vo.ctx.SetContext(vo.windowNo, vo.tabNo, "Name", vo.Name);
// Translation Tab **
if (Utility.Util.GetValueOfString(dr["IsTranslationTab"]).Equals("Y"))
{
// Document Translation
vo.TableName = Utility.Util.GetValueOfString(dr["TableName"]);
if (!Env.IsBaseTranslation(vo.TableName) && // C_UOM, ...
!Env.IsMultiLingualDocument(vo.ctx))
{
showTrl = false;
}
if (!showTrl)
{
VLogger.Get().Config("TrlTab Not displayed - AD_Tab_ID="
+ vo.AD_Tab_ID + "=" + vo.Name + ", Table=" + vo.TableName
+ ", BaseTrl=" + Env.IsBaseTranslation(vo.TableName)
+ ", MultiLingual=" + Env.IsMultiLingualDocument(vo.ctx));
return(false);
}
}
// Advanced Tab **
if (!showAdvanced && Utility.Util.GetValueOfString(dr["IsAdvancedTab"]).Equals("Y"))
{
VLogger.Get().Config("AdvancedTab Not displayed - AD_Tab_ID="
+ vo.AD_Tab_ID + " " + vo.Name);
return(false);
}
// Accounting Info Tab **
if (!showAcct && Utility.Util.GetValueOfString(dr["IsInfoTab"]).Equals("Y"))
{
VLogger.Get().Fine("AcctTab Not displayed - AD_Tab_ID="
+ vo.AD_Tab_ID + " " + vo.Name);
return(false);
}
// DisplayLogic
vo.DisplayLogic = Utility.Util.GetValueOfString(dr["DisplayLogic"]);
// Access Level
vo.AccessLevel = Utility.Util.GetValueOfString(dr["AccessLevel"]);
if (!role.CanView(vo.ctx, vo.AccessLevel) && !DataBase.GlobalVariable.IsVisualEditor) // No Access
{
VLogger.Get().Fine("No Role Access - AD_Tab_ID=" + vo.AD_Tab_ID + " " + vo.Name);
return(false);
} // Used by MField.getDefault
vo.ctx.SetContext(vo.windowNo, vo.tabNo, "AccessLevel", vo.AccessLevel);
// Table Access
vo.AD_Table_ID = Utility.Util.GetValueOfInt(dr["AD_Table_ID"]);
vo.ctx.SetContext(vo.windowNo, vo.tabNo, "AD_Table_ID", vo.AD_Table_ID.ToString());
if (!role.IsTableAccess(vo.AD_Table_ID, true) && !DataBase.GlobalVariable.IsVisualEditor)
{
VLogger.Get().Config("No Table Access - AD_Tab_ID="
+ vo.AD_Tab_ID + " " + vo.Name);
return(false);
}
if (role.IsTableReadOnly(vo.AD_Table_ID) && !DataBase.GlobalVariable.IsVisualEditor)
{
vo.IsReadOnly = true;
}
if (Utility.Util.GetValueOfString(dr["IsReadOnly"]).Equals("Y"))
{
vo.IsReadOnly = true;
}
vo.ReadOnlyLogic = Utility.Util.GetValueOfString(dr["ReadOnlyLogic"]);
if (Utility.Util.GetValueOfString(dr["IsInsertRecord"]).Equals("N"))
{
vo.IsInsertRecord = false;
}
//
vo.Description = Utility.Util.GetValueOfString(dr["Description"]);
if (vo.Description == null)
{
vo.Description = "";
}
vo.Help = Utility.Util.GetValueOfString(dr["Help"]);
if (vo.Help == null)
{
vo.Help = "";
}
if (Utility.Util.GetValueOfString(dr["IsSingleRow"]).Equals("Y"))
{
vo.IsSingleRow = true;
}
if (Utility.Util.GetValueOfString(dr["HasTree"]).Equals("Y"))
{
vo.HasTree = true;
}
vo.AD_Table_ID = Utility.Util.GetValueOfInt(dr["AD_Table_ID"]);
vo.TableName = Utility.Util.GetValueOfString(dr["TableName"]);
if (Utility.Util.GetValueOfString(dr["IsView"]).Equals("Y"))
{
vo.IsView = true;
}
vo.AD_Column_ID = Utility.Util.GetValueOfInt(dr["AD_Column_ID"]); // Primary Parent Column
if (Utility.Util.GetValueOfString(dr["IsSecurityEnabled"]).Equals("Y"))
{
vo.IsSecurityEnabled = true;
}
if (Utility.Util.GetValueOfString(dr["IsDeleteable"]).Equals("Y"))
{
vo.IsDeleteable = true;
}
if (Utility.Util.GetValueOfString(dr["IsHighVolume"]).Equals("Y"))
{
vo.IsHighVolume = true;
}
vo.CommitWarning = Utility.Util.GetValueOfString(dr["CommitWarning"]);
if (vo.CommitWarning == null)
{
vo.CommitWarning = "";
}
vo.WhereClause = Utility.Util.GetValueOfString(dr["WhereClause"]);
if (vo.WhereClause == null)
{
vo.WhereClause = "";
}
vo.OrderByClause = Utility.Util.GetValueOfString(dr["OrderByClause"]);
if (vo.OrderByClause == null)
{
vo.OrderByClause = "";
}
vo.AD_Process_ID = Utility.Util.GetValueOfInt(dr["AD_Process_ID"]);
//if (dr.wasNull())
// vo.AD_Process_ID = 0;
vo.AD_Image_ID = Utility.Util.GetValueOfInt(dr["AD_Image_ID"]);
//if (dr.wasNull())
// vo.AD_Image_ID = 0;
vo.Included_Tab_ID = Utility.Util.GetValueOfInt(dr["Included_Tab_ID"]);
//if (dr.wasNull())
// vo.Included_Tab_ID = 0;
//
vo.TabLevel = Utility.Util.GetValueOfInt(dr["TabLevel"]);
//if (dr.wasNull())
// vo.TabLevel = 0;
//
vo.IsSortTab = Utility.Util.GetValueOfString(dr["IsSortTab"]).Equals("Y");
if (vo.IsSortTab)
{
vo.AD_ColumnSortOrder_ID = Utility.Util.GetValueOfInt(dr["AD_ColumnSortOrder_ID"]);
vo.AD_ColumnSortYesNo_ID = Utility.Util.GetValueOfInt(dr["AD_ColumnSortYesNo_ID"]);
}
//
// Replication Type - set R/O if Reference
try
{
//int index = dr.fin .findColumn("ReplicationType");
vo.ReplicationType = Utility.Util.GetValueOfString(dr["ReplicationType"]);
if ("R".Equals(vo.ReplicationType))
{
vo.IsReadOnly = true;
}
}
catch
{
}
}
catch (System.Exception ex)
{
VLogger.Get().Log(Level.SEVERE, "", ex);
return(false);
}
return(true);
}
/// <SUMmary>
/// Sales Order Products
/// </SUMmary>
/// <returns>No of lines created</returns>
private int SalesOrderProducts()
{
sql = "SELECT ol.m_product_id,ol.QtyOrdered,M_AttributeSetInstance_ID,ol.C_UOM_ID," +
" ol.C_OrderLine_ID,o.C_Order_ID,(NVL(PriceEntered,0) * NVL(QtyEntered,0)) AS Price,o.C_Currency_ID FROM C_Order o " +
" INNER JOIN C_OrderLine ol ON o.C_Order_ID = ol.C_Order_ID " +
" INNER JOIN C_Doctype d ON o.c_DocTypeTarget_ID = d.C_Doctype_ID " +
" WHERE d.DocBaseType='" + MDocBaseType.DOCBASETYPE_SALESORDER + "' " +
" AND d.DocSubTypeSo NOT IN ('" + MDocType.DOCSUBTYPESO_BlanketOrder + "','" + MDocType.DOCSUBTYPESO_Proposal + "')" +
" AND o.IsSOTrx='Y' AND o.IsReturnTrx='N' AND o.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND o.DateOrdered BETWEEN (SELECT startdate FROM C_Period WHERE C_Period_ID = " + C_Period_ID + ") " +
" AND (SELECT enddate FROM C_Period WHERE C_Period_ID = " + C_Period_ID + ") AND ol.QtyOrdered > ol.QtyDelivered " +
" AND ol.C_OrderLine_ID NOT IN (SELECT C_OrderLine_ID FROM va073_masterforecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(ol.M_Product_ID,0)>0 AND o.DocStatus IN('CO','CL') ";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_Order", true, true); // fully qualified - RO
dsOrder = new DataSet();
dsOrder = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOrder != null && dsOrder.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsOrder.Tables[0].Rows.Count; i++)
{
//create MasterForecastLine
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOrder.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_Order_ID"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"]), 0, 0, 0, 0,
C_Period_ID, Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfDecimal(dsOrder.Tables[0].Rows[i]["QtyOrdered"]), ConvertedAmt,
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + "for OrderLine" + Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"])
+ vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + "for OrderLine" + Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"]));
}
}
else
{
Count++;
LineNo += 10;
//Update quantities AND price at Product line
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundSalesOrder"));
}
return(Count);
}
private void InitBPartner(int WinNo, int bPartnerID, string bpType)
{
C_BPartner_ID = bPartnerID;
bool ro = false;
DataSet ds = null;
log.Config("C_BPartner_ID=" + bPartnerID);
// New bpartner
if (bPartnerID == 0)
{
_partner = null;
_pLocation = null;
_user = null;
_bprelation = null;
_bpLocation = null;
_bpGroup = null;
//return true;
}
_partner = new MBPartner(Env.GetCtx(), bPartnerID, null);
if (_partner.Get_ID() != 0)
{
// Contact - Load values
_pLocation = _partner.GetLocation(
Env.GetCtx().GetContextAsInt(WinNo, "C_BPartner_Location_ID"));
_user = _partner.GetContact(
Env.GetCtx().GetContextAsInt(WinNo, "AD_User_ID"));
}
isCustomer = _partner.IsCustomer();
isVendor = _partner.IsVendor();
isEmployee = _partner.IsEmployee();
_readOnly = !MRole.GetDefault(Env.GetCtx()).CanUpdate(
Env.GetCtx().GetAD_Client_ID(), Env.GetCtx().GetAD_Org_ID(),
MBPartner.Table_ID, 0, false);
log.Info("R/O=" + _readOnly);
// Get Data
_greeting = FillGreeting();
/************************************/
_bpGroup = FillBPGroup();
_bpRelation = FillBPRelation();
_bpLocation = FillBPLocation(0, ctx);
/************************************/
ro = _readOnly;
if (!ro)
{
ro = !MRole.GetDefault(Env.GetCtx()).CanUpdate(
Env.GetCtx().GetAD_Client_ID(), Env.GetCtx().GetAD_Org_ID(),
MBPartnerLocation.Table_ID, 0, false);
}
if (!ro)
{
ro = !MRole.GetDefault(Env.GetCtx()).CanUpdate(
Env.GetCtx().GetAD_Client_ID(), Env.GetCtx().GetAD_Org_ID(),
MLocation.Table_ID, 0, false);
}
ds = DB.ExecuteDataset("Select C_BPartnerRelation_ID, c_bpartnerrelation_location_id from C_BP_Relation where c_bpartner_id=" + _partner.GetC_BPartner_ID());
LoadBPartner(C_BPartner_ID, ds);
}
/// <summary>
///Load Lookup
/// </summary>
private void Load()
{
// log.config("MLocatorLookup Loader.run " + m_AD_Column_ID);
// Set Info - see VLocator.actionText
int only_Warehouse_ID = GetOnly_Warehouse_ID();
int only_Product_ID = GetOnly_Product_ID();
bool?only_IsSOTrx = IsOnly_Outgoing();
//int sqlParaCount = 0;
StringBuilder sql = new StringBuilder("SELECT * FROM M_Locator ")
.Append(" WHERE IsActive='Y'");
if (only_Warehouse_ID != 0)
{
sql.Append(" AND M_Warehouse_ID=@w");
}
if (only_Product_ID != 0)
{
sql.Append(" AND (IsDefault='Y' "); // Default Locator
// Something already stored
sql.Append("OR EXISTS (SELECT * FROM M_Storage s ") // Storage Locator
.Append("WHERE s.M_Locator_ID=M_Locator.M_Locator_ID AND s.M_Product_ID=@p)");
if (only_IsSOTrx == null || !only_IsSOTrx.Value)
{
// Default Product
sql.Append("OR EXISTS (SELECT * FROM M_Product p ") // Default Product Locator
.Append("WHERE p.M_Locator_ID=M_Locator.M_Locator_ID AND p.M_Product_ID=@p)");
// Product Locators
sql.Append("OR EXISTS (SELECT * FROM M_ProductLocator pl ") // Product Locator
.Append("WHERE pl.M_Locator_ID=M_Locator.M_Locator_ID AND pl.M_Product_ID=@p)");
// No locators defined for the warehouse
sql.Append("OR 0 = (SELECT COUNT(*) ");
sql.Append("FROM M_ProductLocator pl");
sql.Append(" INNER JOIN M_Locator l2 ON (pl.M_Locator_ID=l2.M_Locator_ID) ");
sql.Append("WHERE pl.M_Product_ID=@p AND l2.M_Warehouse_ID=M_Locator.M_Warehouse_ID )");
}
sql.Append(" ) ");
}
String finalSql = MRole.GetDefault((Context)GetCtx(), false).AddAccessSQL(
sql.ToString(), "M_Locator", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
//if (_loader.ThreadState == ThreadState.Suspended)
//{
// log.log(Level.SEVERE, "Interrupted");
// return;
//}
// Reset
_lookup.Clear();
int rows = 0;
try
{
List <System.Data.SqlClient.SqlParameter> para = new List <System.Data.SqlClient.SqlParameter>();
// int index = 1;
if (only_Warehouse_ID != 0)
{
// pstmt.setInt(index++, only_Warehouse_ID);
para.Add(new System.Data.SqlClient.SqlParameter("@w", only_Warehouse_ID));
}
if (only_Product_ID != 0)
{
para.Add(new System.Data.SqlClient.SqlParameter("@p", only_Product_ID));
}
DataSet ds = DataBase.DB.ExecuteDataset(finalSql, para.ToArray(), null);
//
foreach (DataRow dr in ds.Tables[0].Rows)
{
// Max out
if (rows++ > _maxRows)
{
log.Warning("Over Max Rows - " + rows);
break;
}
MLocator loc = new MLocator((Context)GetCtx(), dr, null);
int M_Locator_ID = loc.GetM_Locator_ID();
KeyNamePair pp = new KeyNamePair(M_Locator_ID, loc.ToString());
_lookup.Add(M_Locator_ID, pp);
}
ds.Dispose();
ds = null;
}
catch (Exception e)
{
log.Log(Level.SEVERE, finalSql, e);
}
log.Fine("Complete #" + _lookup.Count);
if (_lookup.Count == 0)
{
log.Finer(finalSql);
}
}
