/// <summary>
/// Create element for the new account
/// </summary>
/// <param name="ctx"></param>
/// <param name="m_AD_Org_ID"></param>
/// <param name="value"></param>
/// <param name="name"></param>
/// <param name="isExpenseType"></param>
/// <param name="m_C_Element_ID"></param>
/// <returns></returns>
public int CreateElementValue(Ctx ctx, int m_AD_Org_ID, String value, String name, Boolean isExpenseType, int m_C_Element_ID)
{
MElementValue ev = new MElementValue(ctx, value, name, null,
isExpenseType ? X_C_ElementValue.ACCOUNTTYPE_Expense : X_C_ElementValue.ACCOUNTTYPE_Revenue,
X_C_ElementValue.ACCOUNTSIGN_Natural,
false, false, null);
ev.SetC_Element_ID(m_C_Element_ID);
ev.SetAD_Org_ID(m_AD_Org_ID);
if (!ev.Save())
{
//log.Log(Level.WARNING, "C_ElementValue_ID not created");
Msg = "C_ElementValue_ID not created";
}
ID = ev.GetC_ElementValue_ID();
return(ev.GetC_ElementValue_ID());
}
/// <summary>
/// Check Accounts of Fact Lines
/// </summary>
/// <returns>true if success</returns>
public bool CheckAccounts()
{
// no lines -> nothing to distribute
if (_lines.Count == 0)
{
return(true);
}
// For all fact lines
for (int i = 0; i < _lines.Count; i++)
{
FactLine line = (FactLine)_lines[i];
MAccount account = line.GetAccount();
if (account == null)
{
log.Warning("No Account for " + line);
return(false);
}
MElementValue ev = account.GetAccount();
if (ev == null)
{
log.Warning("No Element Value for " + account
+ ": " + line);
return(false);
}
if (ev.IsSummary())
{
log.Warning("Cannot post to Summary Account " + ev
+ ": " + line);
return(false);
}
if (!ev.IsActive())
{
log.Warning("Cannot post to Inactive Account " + ev
+ ": " + line);
return(false);
}
} // for all lines
return(true);
}
} // doIt
/// <summary>
/// Create Beginning Balance Line
/// </summary>
private void CreateBalanceLine()
{
StringBuilder sb = new StringBuilder("INSERT INTO T_ReportStatement "
+ "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
+ "DateAcct, Name, Description,"
+ "AmtAcctDr, AmtAcctCr, Balance, Qty) ");
sb.Append("SELECT ").Append(GetAD_PInstance_ID()).Append(",0,0,")
.Append(DataBase.DB.TO_DATE(_DateAcct_From, true)).Append(",")
.Append(DataBase.DB.TO_STRING(Msg.GetMsg(GetCtx(), "BeginningBalance"))).Append(",NULL,"
+ "COALESCE(SUM(AmtAcctDr),0), COALESCE(SUM(AmtAcctCr),0), COALESCE(SUM(AmtAcctDr-AmtAcctCr),0), COALESCE(SUM(Qty),0) "
+ "FROM Fact_Acct_Balance "
+ "WHERE ").Append(_parameterWhere)
.Append(" AND DateAcct < ").Append(DataBase.DB.TO_DATE(_DateAcct_From));
// Start Beginning of Year
if (_Account_ID > 0)
{
_acct = new MElementValue(GetCtx(), _Account_ID, Get_TrxName());
if (!_acct.IsBalanceSheet())
{
MPeriod first = MPeriod.GetFirstInYear(GetCtx(), _DateAcct_From);
if (first != null)
{
sb.Append(" AND DateAcct >= ").Append(DataBase.DB.TO_DATE(first.GetStartDate()));
}
else
{
log.Log(Level.SEVERE, "First period not found");
}
}
}
//
int no = DataBase.DB.ExecuteQuery(sb.ToString(), null, Get_TrxName());
log.Fine("#" + no + " (Account_ID=" + _Account_ID + ")");
log.Finest(sb.ToString());
} // createBalanceLine
protected override string DoIt()
{
string msg = "";
string extension = filename;
int ind = filename.LastIndexOf(".");
extension = filename.Substring(ind, filename.Length - ind);
int client = Util.GetValueOfInt(GetAD_Client_ID());
int user = GetAD_User_ID();
if (extension.ToUpper() == ".XLS" || extension.ToUpper() == ".CSV")
{
try
{
/////////////////lakhwinder
ExcelReader reader = new ExcelReader(1, 1);
System.Data.DataTable dt = reader.ExtractDataTable("D:\\gh.xls", "Sheet1$"); //extract all the records form excel to DataTable
/////////////////
//excel = AutomationFactory.CreateObject("Excel.Application");
//excel.Visible = true;
////dynamic objExcel = AutomationFactory.CreateObject("Excel.Application");
//Microsoft.Office.Interop.Excel.Application xlsApp1;
//Microsoft.Office.Interop.Excel.Workbook xlsWorkbook1;
//Microsoft.Office.Interop.Excel.Worksheets xlsWorksheet1;
//Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbook xlsWrkBook = (Microsoft.Office.Interop.Excel.Workbook)xlsApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);//
//Microsoft.Office.Interop.Excel.Worksheet wrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsWrkBook.Worksheets.get_Item(1);
// Microsoft.Office.Interop.Excel.ApplicationClass xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// Microsoft.Office.Interop.Excel.WorkbookClass xlsWrkBook = (Microsoft.Office.Interop.Excel.WorkbookClass)xlsApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);//
//Microsoft.Office.Interop.Excel.Worksheet wrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsWrkBook.Worksheets.get_Item(1);
//string name = wrkSheet.Name.ToString();
//OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0");
//OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + name + "$]", con);
//da.Fill(dt);
if (dt.Rows.Count > 0)
{
sql = "select ad_tree_id from c_element where c_element_id = " + C_Elememt_ID + " and ad_client_id = " + client;
int ad_tree_id = Util.GetValueOfInt(DB.ExecuteScalar(sql));
for (int i = 0; i < dt.Rows.Count; i++)
{
string key = Util.GetValueOfString(dt.Rows[i]["(Account_Value)"]);
if (key != "")
{
sql = "select c_elementvalue_id from c_elementvalue where value = '" + key + "' and ad_client_id = " + client;
int C_ElementValue_ID1 = Util.GetValueOfInt(DB.ExecuteScalar(sql));
if (C_ElementValue_ID1 == 0)
{
int parent_ID = Util.GetValueOfInt(dt.Rows[i]["(Account_Parent)"]);
sql = "select c_elementvalue_id from c_elementvalue where value = '" + parent_ID + "' and ad_client_id = " + Util.GetValueOfInt(GetAD_Client_ID());
int C_ElementValue_ID_Parent = Util.GetValueOfInt(DB.ExecuteScalar(sql));
MElementValue eleValue = new MElementValue(GetCtx(), 0, null);
int C_ElementValue_ID = DB.GetNextID(GetAD_Client_ID(), "C_ElementValue", null);
string accSign = Util.GetValueOfString(dt.Rows[i]["(Account_Sign)"]);
if (accSign == "")
{
eleValue.SetAccountSign("N");
}
else
{
eleValue.SetAccountSign(accSign);
}
eleValue.SetC_Element_ID(C_Elememt_ID);
eleValue.SetC_ElementValue_ID(C_ElementValue_ID);
eleValue.SetValue(Util.GetValueOfString(dt.Rows[i]["(Account_Value)"]));
eleValue.SetName(Util.GetValueOfString(dt.Rows[i]["(Account_Name)"]));
eleValue.SetDescription(Util.GetValueOfString(dt.Rows[i]["(Account_Description)"]));
eleValue.SetIsActive(true);
// For Summary
if (dt.Rows[i]["(Account_Summary)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsSummary(true);
}
else
{
eleValue.SetIsSummary(false);
}
// For Account Type
if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "ASSET")
{
eleValue.SetAccountType("A");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "LIABILITY")
{
eleValue.SetAccountType("L");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "OWNER'S EQUITY")
{
eleValue.SetAccountType("O");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "REVENUE")
{
eleValue.SetAccountType("R");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "EXPENSE")
{
eleValue.SetAccountType("E");
}
else
{
eleValue.SetAccountType("M");
}
if (!eleValue.Save())
{
log.SaveError("NotSaved", "");
return(msg);
}
VAdvantage.Model.MTree obj = new VAdvantage.Model.MTree(GetCtx(), ad_tree_id, null);
C_ElementValue_ID = C_ElementValue_ID + 1;
VAdvantage.Model.MTreeNode mNode = new VAdvantage.Model.MTreeNode(obj, C_ElementValue_ID);
mNode.SetParent_ID(C_ElementValue_ID_Parent);
if (!mNode.Save())
{
log.SaveError("NodeNotSaved", "");
return(msg);
}
}
}
}
}
msg = Msg.GetMsg(GetCtx(), "ImportedSuccessfully");
return(msg);
}
catch
{
msg = Msg.GetMsg(GetCtx(), "ExcelSheetNotInProperFormat");
return(msg);
}
}
else
{
msg = Msg.GetMsg(GetCtx(), "UseDefaultExcelSheet");
return(msg);
}
}
protected override string DoIt()
{
string extension = filename;
string path = HostingEnvironment.ApplicationPhysicalPath;
if (filename.Contains("_FileCtrl"))
{
path = path + "TempDownload//" + filename;
if (Directory.Exists(path))
{
string[] files = Directory.GetFiles(path);
if (files != null && files.Length > 0)
{
filename = "//" + Path.GetFileName(files[0]);
}
}
else
{
_message = Msg.GetMsg(GetCtx(), "PathNotExist");
return(_message);
}
}
int ind = filename.LastIndexOf(".");
extension = filename.Substring(ind, filename.Length - ind);
int client = Util.GetValueOfInt(GetAD_Client_ID());
int user = GetAD_User_ID();
sql = "select ad_tree_id from c_element where c_element_id = " + C_Elememt_ID + " and ad_client_id = " + client;
int ad_tree_id = 0;
MTree tree = null;
try
{
ad_tree_id = Util.GetValueOfInt(DB.ExecuteScalar(sql));
tree = new MTree(GetCtx(), ad_tree_id, null);
}
catch
{
ad_tree_id = 0;
}
if (ad_tree_id == 0)
{
_message = Msg.GetMsg(GetCtx(), "TreeNotBind");
return(_message);
}
// if (extension.ToUpper() == ".XLSX" || extension.ToUpper() == ".XLS" || extension.ToUpper() == ".CSV")
if (extension.ToUpper() == ".XLSX" || extension.ToUpper() == ".CSV")
{
try
{
DataSet ds = ImportExcelXLS(path + filename, false);
System.Data.DataTable dt = null;
if (ds != null)
{
dt = ds.Tables[0];
}
if (dt != null && dt.Rows.Count > 0)
{
//if (ad_tree_id == 0)
//{
// int tableID = Convert.ToInt32(DB.ExecuteScalar("select ad_table_id from ad_table where lower(tablename)='vactwz_elementvalue'"));
// tree = new MTree(GetCtx(), 0, null);
// tree.SetName(CreateName("AcctWiz"));
// tree.SetAD_Table_ID(tableID);
// //tree.SetTreeType("EV");
// tree.Save();
// ad_tree_id = tree.Get_ID();
//}
MElementValue eleValue = null;
string key = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
key = Util.GetValueOfString(dt.Rows[i]["(Account_Value)"]);
if (key != "")
{
sql = " SELECT c_elementvalue_id FROM c_elementvalue WHERE IsActive='Y' AND C_ELEMENT_ID=" + C_Elememt_ID + " AND value = '" + key + "' AND ad_client_id = " + client;
int C_ElementValue_ID = 0;
try
{
C_ElementValue_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql));
}
catch
{
C_ElementValue_ID = 0;
}
eleValue = new MElementValue(GetCtx(), C_ElementValue_ID, null);
string parent_ID = Util.GetValueOfString(dt.Rows[i]["(Account_Parent)"]);
sql = "SELECT c_elementvalue_id FROM c_elementvalue WHERE IsActive='Y' AND C_Element_ID=" + C_Elememt_ID + " AND value = '" + parent_ID + "' AND ad_client_id = " + client;
int C_ElementValue_ID_Parent = Util.GetValueOfInt(DB.ExecuteScalar(sql));
try
{
C_ElementValue_ID_Parent = Util.GetValueOfInt(DB.ExecuteScalar(sql));
}
catch
{
C_ElementValue_ID_Parent = 0;
}
//eleValue = new MElementValue(GetCtx(), 0, null);
//int C_ElementValue_ID = DB.GetNextID(GetAD_Client_ID(), "VACTWZ_ELEMENTVALUE", null);
string accSign = Util.GetValueOfString(dt.Rows[i]["(Account_Sign)"]);
if (accSign == "")
{
eleValue.SetAccountSign("N");
}
else
{
eleValue.SetAccountSign(accSign);
}
eleValue.SetC_Element_ID(C_Elememt_ID);
// eleValue.SetC_ElementValue_ID(C_ElementValue_ID);
eleValue.SetValue(Util.GetValueOfString(dt.Rows[i]["(Account_Value)"]));
eleValue.SetName(Util.GetValueOfString(dt.Rows[i]["(Account_Name)"]));
eleValue.SetDescription(Util.GetValueOfString(dt.Rows[i]["(Account_Description)"]));
eleValue.SetIsActive(true);
// For Summary
if (dt.Rows[i]["(Account_Summary)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsSummary(true);
}
else
{
eleValue.SetIsSummary(false);
}
//For DefaultAccount
if (dt.Rows[i]["(Account_Document)"].ToString().ToUpper() == "YES")
{
///******************** Commented
eleValue.SetIsDefault(true);
}
else
{
///******************** Commented
eleValue.SetIsDefault(false);
}
//for MasterType
if (!string.IsNullOrEmpty(Util.GetValueOfString(dt.Rows[i]["(Master_Type)"])))
{
eleValue.SetMasterAccountType(dt.Rows[i]["(Master_Type)"].ToString());
}
//For Primary Group
string primaryGroup = dt.Rows[i]["(Primary_Group)"].ToString();
if (!string.IsNullOrEmpty(primaryGroup))
{
int primaryGroupID = Util.GetValueOfInt(DB.ExecuteScalar("select c_accountgroup_id from c_accountgroup where value='" + primaryGroup + "' AND AD_CLient_ID=" + GetCtx().GetAD_Client_ID()));
if (primaryGroupID > 0)
{
eleValue.SetC_AccountGroup_ID(primaryGroupID);
}
//try
//{
// eleValue.SetRef_C_AccountGroup_ID(Util.GetValueOfInt(primaryGroup));
//}
//catch { }
}
//For PrimarySub Group
string primarysubGroup = dt.Rows[i]["(Primary_Sub_Group)"].ToString();
if (!string.IsNullOrEmpty(primarysubGroup))
{
int primarysubGroupID = Util.GetValueOfInt(DB.ExecuteScalar("select c_accountsubgroup_id from c_accountsubgroup where value='" + primarysubGroup + "' AND AD_CLient_ID=" + GetCtx().GetAD_Client_ID()));
if (primarysubGroupID > 0)
{
eleValue.SetC_AccountSubGroup_ID(primarysubGroupID);
}
//eleValue.SetRef_C_AccountSubGroup_ID(Util.GetValueOfInt(primarysubGroup));
//try
//{
// eleValue.SetRef_C_AccountSubGroup_ID(Util.GetValueOfInt(primarysubGroup));
//}
//catch { }
}
// For Account Type
if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "ASSET")
{
eleValue.SetAccountType("A");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "LIABILITY")
{
eleValue.SetAccountType("L");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "OWNER'S EQUITY")
{
eleValue.SetAccountType("O");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "REVENUE")
{
eleValue.SetAccountType("R");
}
else if (Util.GetValueOfString(dt.Rows[i]["(Account_Type)"]).ToUpper() == "EXPENSE")
{
eleValue.SetAccountType("E");
}
else
{
eleValue.SetAccountType("M");
}
//string memo = dt.Rows[i]["(Memo_Ledger)"].ToString();
//if (dt.Rows[i]["(Memo_Ledger)"] != null && dt.Rows[i]["(Memo_Ledger)"] != DBNull.Value)
//{
// try
// {
// eleValue.SetRef_C_ElementValue_ID(Util.GetValueOfInt(memo));
// }
// catch { }
//}
//eleValue.SetParent_ID(C_ElementValue_ID_Parent);
if (!string.IsNullOrEmpty(parent_ID))
{
///******************** Commented
//eleValue.SetParentSerachKey(parent_ID.ToString());
}
if (!eleValue.Save())
{
log.SaveError("NotSaved", "");
//return msg;
}
VAdvantage.Model.MTree obj = new VAdvantage.Model.MTree(GetCtx(), ad_tree_id, null);
//C_ElementValue_ID = C_ElementValue_ID + 1;
VAdvantage.Model.MTreeNode mNode = VAdvantage.Model.MTreeNode.Get(obj, eleValue.Get_ID());
if (mNode == null)
{
mNode = new VAdvantage.Model.MTreeNode(tree, eleValue.Get_ID());
}
mNode.SetParent_ID(C_ElementValue_ID_Parent);
// ((PO)mNode).Set_Value("Parent_ID", C_ElementValue_ID_Parent);
if (!mNode.Save())
{
log.SaveError("NodeNotSaved", "");
return(msg);
}
}
else
{
}
}
/////////Set Memo Ledger
//int tempElementID = 0;
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// if (dt.Rows[i]["(Memo_Ledger)"] != null && dt.Rows[i]["(Memo_Ledger)"] != DBNull.Value)
// {
// if (!(string.IsNullOrEmpty(dt.Rows[i]["(Memo_Ledger)"].ToString())))
// {
// refElementValID = Util.GetValueOfInt(DB.ExecuteScalar("Select vactwz_elementvalue_ID from vactwz_elementvalue WHERE Value='" + dt.Rows[i]["(Memo_Ledger)"] + "'"));
// if (refElementValID > 0)
// {
// tempElementID = Util.GetValueOfInt(DB.ExecuteScalar("Select vactwz_elementvalue_ID from vactwz_elementvalue WHERE Value='" + dt.Rows[i]["(Account_Value)"] + "'"));
// eleValue = new MElementValue(GetCtx(), tempElementID, null);
// eleValue.SetRef_C_ElementValue_ID(refElementValID);
// eleValue.Save();
// }
// }
// }
//}
//******************** Commented
//if (tree != null)
//{
// sql = "Update C_Element SET TreeID=" + tree.Get_ID() + " WHERE C_Element_ID=" + C_Elememt_ID;
// DB.ExecuteQuery(sql);
//}
if (path.Contains("_FileCtrl"))
{
Directory.Delete(path, true);
}
}
msg = Msg.GetMsg(GetCtx(), "ImportedSuccessfully");
return(msg);
}
catch
{
if (_message != "")
{
msg = _message;
}
else
{
msg = Msg.GetMsg(GetCtx(), "ExcelSheetNotInProperFormat");
}
return(msg);
}
}
else
{
msg = Msg.GetMsg(GetCtx(), "UseDefaultExcelSheet");
return(msg);
}
}
} // prepare
/// <summary>
/// Perrform Process.
/// </summary>
/// <returns>Info</returns>
protected override String DoIt()
{
StringBuilder sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID;
// **** Prepare ****
// Delete Old Imported
if (_deleteOldImported)
{
sql = new StringBuilder("DELETE FROM I_ElementValue "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID, ").Append(_AD_Client_ID).Append("),"
+ " AD_Org_ID = COALESCE (AD_Org_ID, 0),"
+ " IsActive = COALESCE (IsActive, 'Y'),"
+ " Created = COALESCE (Created, SysDate),"
+ " CreatedBy = COALESCE (CreatedBy, 0),"
+ " Updated = COALESCE (Updated, SysDate),"
+ " UpdatedBy = COALESCE (UpdatedBy, 0),"
+ " I_ErrorMsg = NULL,"
+ " I_IsImported = 'N' "
+ "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Reset=" + no);
// **** Prepare ****
// Set Element
if (_C_Element_ID != 0)
{
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET ElementName=(SELECT Name FROM C_Element WHERE C_Element_ID=")
.Append(_C_Element_ID).Append(") "
+ "WHERE ElementName IS NULL AND C_Element_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Element Default=" + no);
}
//
sql = new StringBuilder("UPDATE I_ElementValue i "
+ "SET C_Element_ID = (SELECT C_Element_ID FROM C_Element e"
+ " WHERE i.ElementName=e.Name AND i.AD_Client_ID=e.AD_Client_ID)"
+ "WHERE C_Element_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Element=" + no);
//
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_IsImported='E', I_ErrorMsg='ERR=Invalid Element, ' "
+ "WHERE C_Element_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Config("Invalid Element=" + no);
// No Name, Value
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_IsImported='E', I_ErrorMsg='ERR=No Name, ' "
+ "WHERE (Value IS NULL OR Name IS NULL)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Config("Invalid Name=" + no);
// Set Column
sql = new StringBuilder("UPDATE I_ElementValue i "
+ "SET AD_Column_ID = (SELECT AD_Column_ID FROM AD_Column c"
+ " WHERE UPPER(i.Default_Account)=UPPER(c.ColumnName)"
+ " AND c.AD_Table_ID IN (315,266) AND AD_Reference_ID=25) "
+ "WHERE Default_Account IS NOT NULL AND AD_Column_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Column=" + no);
//
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')"
: "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Column, ' "
+ "WHERE AD_Column_ID IS NULL AND Default_Account IS NOT NULL"
+ " AND UPPER(Default_Account)<>'DEFAULT_ACCT'" // ignore default account
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Config("Invalid Column=" + no);
// Set Post* Defaults (ignore errors)
String[] yColumns = new String[] { "PostActual", "PostBudget", "PostStatistical", "PostEncumbrance" };
for (int i = 0; i < yColumns.Length; i++)
{
sql = new StringBuilder("UPDATE I_ElementValue SET ")
.Append(yColumns[i]).Append("='Y' WHERE ")
.Append(yColumns[i]).Append(" IS NULL OR ")
.Append(yColumns[i]).Append(" NOT IN ('Y','N')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set " + yColumns[i] + " Default=" + no);
}
// Summary
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET IsSummary='N' "
+ "WHERE IsSummary IS NULL OR IsSummary NOT IN ('Y','N')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set IsSummary Default=" + no);
// Doc Controlled
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET IsDocControlled = CASE WHEN AD_Column_ID IS NOT NULL THEN 'Y' ELSE 'N' END "
+ "WHERE IsDocControlled IS NULL OR IsDocControlled NOT IN ('Y','N')"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set IsDocumentControlled Default=" + no);
// Check Account Type A (E) L M O R
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET AccountType='E' "
+ "WHERE AccountType IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set AccountType Default=" + no);
//
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid AccountType, ' "
+ "WHERE AccountType NOT IN ('A','E','L','M','O','R')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Config("Invalid AccountType=" + no);
// Check Account Sign (N) C B
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET AccountSign='N' "
+ "WHERE AccountSign IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set AccountSign Default=" + no);
//
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid AccountSign, ' "
+ "WHERE AccountSign NOT IN ('N','C','D')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Config("Invalid AccountSign=" + no);
// No Value
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Key, ' "
+ "WHERE (Value IS NULL OR Value='')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Config("Invalid Key=" + no);
// **** Update ElementValue from existing
sql = new StringBuilder("UPDATE I_ElementValue i "
+ "SET C_ElementValue_ID=(SELECT C_ElementValue_ID FROM C_ElementValue ev"
+ " INNER JOIN C_Element e ON (ev.C_Element_ID=e.C_Element_ID)"
+ " WHERE i.C_Element_ID=e.C_Element_ID AND i.AD_Client_ID=e.AD_Client_ID"
+ " AND i.Value=ev.Value) "
+ "WHERE C_ElementValue_ID IS NULL"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Found ElementValue=" + no);
Commit();
// -------------------------------------------------------------------
int noInsert = 0;
int noUpdate = 0;
// Go through Records
sql = new StringBuilder("SELECT * "
+ "FROM I_ElementValue "
+ "WHERE I_IsImported='N'").Append(clientCheck)
.Append(" ORDER BY I_ElementValue_ID");
IDataReader idr = null;
try
{
//PreparedStatement pstmt = DataBase.prepareStatement(sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
X_I_ElementValue impEV = new X_I_ElementValue(GetCtx(), idr, Get_TrxName());
int C_ElementValue_ID = impEV.GetC_ElementValue_ID();
int I_ElementValue_ID = impEV.GetI_ElementValue_ID();
// **** Create/Update ElementValue
if (C_ElementValue_ID == 0) // New
{
MElementValue ev = new MElementValue(impEV);
if (ev.Save())
{
noInsert++;
impEV.SetC_ElementValue_ID(ev.GetC_ElementValue_ID());
impEV.SetI_IsImported(X_I_ElementValue.I_ISIMPORTED_Yes);
impEV.Save();
}
else
{
sql = new StringBuilder("UPDATE I_ElementValue i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||").Append(DataBase.DB.TO_STRING("Insert ElementValue "))
.Append("WHERE I_ElementValue_ID=").Append(I_ElementValue_ID);
DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
}
}
else // Update existing
{
MElementValue ev = new MElementValue(GetCtx(), C_ElementValue_ID, null);
if (ev.Get_ID() != C_ElementValue_ID)
{
}
ev.Set(impEV);
if (ev.Save())
{
noUpdate++;
impEV.SetI_IsImported(X_I_ElementValue.I_ISIMPORTED_Yes);
impEV.Save();
}
else
{
sql = new StringBuilder("UPDATE I_ElementValue i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||").Append(DataBase.DB.TO_STRING("Update ElementValue"))
.Append("WHERE I_ElementValue_ID=").Append(I_ElementValue_ID);
DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
}
}
} // for all I_Product
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
throw new Exception("create", e);
}
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_IsImported='N', Updated=SysDate "
+ "WHERE I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_ElementValue_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noUpdate), "@C_ElementValue_ID@: @Updated@");
Commit();
// ***** Set Parent
sql = new StringBuilder("UPDATE I_ElementValue i "
+ "SET ParentElementValue_ID=(SELECT C_ElementValue_ID"
+ " FROM C_ElementValue ev WHERE i.C_Element_ID=ev.C_Element_ID"
+ " AND i.ParentValue=ev.Value AND i.AD_Client_ID=ev.AD_Client_ID) "
+ "WHERE ParentElementValue_ID IS NULL"
+ " AND I_IsImported='Y' AND Processed='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Found Parent ElementValue=" + no);
//
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET I_ErrorMsg=" + ts + "||'Info=ParentNotFound, ' "
+ "WHERE ParentElementValue_ID IS NULL AND ParentValue IS NOT NULL"
+ " AND I_IsImported='Y' AND Processed='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Config("Not Found Patent ElementValue=" + no);
//
sql = new StringBuilder("SELECT i.ParentElementValue_ID, i.I_ElementValue_ID,"
+ " e.AD_Tree_ID, i.C_ElementValue_ID, i.Value||'-'||i.Name AS Info "
+ "FROM I_ElementValue i"
+ " INNER JOIN C_Element e ON (i.C_Element_ID=e.C_Element_ID) "
+ "WHERE i.C_ElementValue_ID IS NOT NULL AND e.AD_Tree_ID IS NOT NULL"
+ " AND i.ParentElementValue_ID IS NOT NULL"
+ " AND i.I_IsImported='Y' AND Processed='N' AND i.AD_Client_ID=").Append(_AD_Client_ID);
int noParentUpdate = 0;
try
{
//PreparedStatement pstmt = DataBase.prepareStatement(sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
//
String updateSQL = "UPDATE AD_TreeNode SET Parent_ID=@param1, SeqNo=@param2 "
+ "WHERE AD_Tree_ID=@param3 AND Node_ID=@param4";
//PreparedStatement updateStmt = DataBase.prepareStatement(updateSQL, Get_TrxName());
SqlParameter[] param = new SqlParameter[4];
//IDataReader idr=null;
while (idr.Read())
{
//updateStmt.setInt(1, rs.getInt(1)); // Parent
param[0] = new SqlParameter("@param1", Utility.Util.GetValueOfInt(idr[0]));
//updateStmt.setInt(2, rs.getInt(2)); // SeqNo (assume sequenec in import is the same)
param[1] = new SqlParameter("@param2", Utility.Util.GetValueOfInt(idr[1]));
//updateStmt.setInt(3, rs.getInt(3)); // Tree
param[2] = new SqlParameter("@param3", Utility.Util.GetValueOfInt(idr[2]));
//updateStmt.setInt(4, rs.getInt(4)); // Node
param[3] = new SqlParameter("@param4", Utility.Util.GetValueOfInt(idr[3]));
try
{
no = DataBase.DB.ExecuteQuery(updateSQL, param, Get_TrxName());
noParentUpdate += no;
}
catch (Exception ex)
{
log.Log(Level.SEVERE, "(ParentUpdate)", ex);
no = 0;
}
if (no == 0)
{
log.Info("Parent not found for " + Utility.Util.GetValueOfString(idr[4]));// rs.getString(5));
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
log.Log(Level.SEVERE, "(ParentUpdateLoop) " + sql.ToString(), e);
}
AddLog(0, null, Utility.Util.GetValueOfDecimal(noParentUpdate), "@ParentElementValue_ID@: @Updated@");
// Reset Processing Flag
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET Processing='-'"
+ "WHERE I_IsImported='Y' AND Processed='N' AND Processing='Y'"
+ " AND C_ElementValue_ID IS NOT NULL")
.Append(clientCheck);
if (_updateDefaultAccounts)
{
sql.Append(" AND AD_Column_ID IS NULL");
}
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Reset Processing Flag=" + no);
if (_updateDefaultAccounts)
{
UpdateDefaults(clientCheck);
}
// Update Description
sql = new StringBuilder("SELECT * FROM C_ValidCombination vc "
+ "WHERE EXISTS (SELECT * FROM I_ElementValue i "
+ "WHERE vc.Account_ID=i.C_ElementValue_ID)");
// Done
sql = new StringBuilder("UPDATE I_ElementValue "
+ "SET Processing='N', Processed='Y'"
+ "WHERE I_IsImported='Y'")
.Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Processed=" + no);
return("");
} // doIt
} // doIt
/// <summary>
/// Create Beginning Balance Line
/// </summary>
private void CreateBalanceLine()
{
StringBuilder sql = new StringBuilder(_insert);
// (AD_PInstance_ID, Fact_Acct_ID,
sql.Append("SELECT ").Append(GetAD_PInstance_ID()).Append(",0,");
// AD_Client_ID, AD_Org_ID, Created,CreatedBy, Updated,UpdatedBy,
sql.Append(GetAD_Client_ID()).Append(",");
if (_AD_Org_ID == 0 || _AD_Org_ID == -1)
{
sql.Append("0");
}
else
{
sql.Append(_AD_Org_ID);
}
sql.Append(", SysDate,").Append(GetAD_User_ID())
.Append(",SysDate,").Append(GetAD_User_ID()).Append(",");
// C_AcctSchema_ID, Account_ID, AccountValue, DateTrx, DateAcct, C_Period_ID,
sql.Append(_C_AcctSchema_ID).Append(",");
if (_Account_ID == 0 || _Account_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_Account_ID);
}
if (_AccountValue_From != null)
{
sql.Append(",").Append(DataBase.DB.TO_STRING(_AccountValue_From));
}
else if (_AccountValue_To != null)
{
sql.Append(",' '");
}
else
{
sql.Append(",null");
}
DateTime?balanceDay = _DateAcct_From; // TimeUtil.addDays(_DateAcct_From, -1);
sql.Append(",null,").Append(DataBase.DB.TO_DATE(balanceDay, true)).Append(",");
if (_C_Period_ID == 0 || _C_Period_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Period_ID);
}
sql.Append(",");
// AD_Table_ID, Record_ID, Line_ID,
sql.Append("null,null,null,");
// GL_Category_ID, GL_Budget_ID, C_Tax_ID, M_Locator_ID, PostingType,
sql.Append("null,null,null,null,'").Append(_PostingType).Append("',");
// C_Currency_ID, AmtSourceDr, AmtSourceCr, AmtSourceBalance,
sql.Append("null,null,null,null,");
// AmtAcctDr, AmtAcctCr, AmtAcctBalance, C_UOM_ID, Qty,
sql.Append(" COALESCE(SUM(AmtAcctDr),0),COALESCE(SUM(AmtAcctCr),0),"
+ "COALESCE(SUM(AmtAcctDr),0)-COALESCE(SUM(AmtAcctCr),0),"
+ " null,COALESCE(SUM(Qty),0),");
// M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID, C_LocFrom_ID,C_LocTo_ID,
if (_M_Product_ID == 0 || _M_Product_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_M_Product_ID);
}
sql.Append(",");
if (_C_BPartner_ID == 0 || _C_BPartner_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_BPartner_ID);
}
sql.Append(",");
if (_AD_OrgTrx_ID == 0 || _AD_OrgTrx_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_AD_OrgTrx_ID);
}
sql.Append(",");
if (_C_LocFrom_ID == 0 || _C_LocFrom_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_LocFrom_ID);
}
sql.Append(",");
if (_C_LocTo_ID == 0 || _C_LocTo_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_LocTo_ID);
}
sql.Append(",");
// C_SalesRegion_ID, C_Project_ID, C_Campaign_ID, C_Activity_ID,
if (_C_SalesRegion_ID == 0 || _C_SalesRegion_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_SalesRegion_ID);
}
sql.Append(",");
if (_C_Project_ID == 0 || _C_Project_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Project_ID);
}
sql.Append(",");
if (_C_Campaign_ID == 0 || _C_Campaign_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Campaign_ID);
}
sql.Append(",");
if (_C_Activity_ID == 0 || _C_Activity_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Activity_ID);
}
sql.Append(",");
// User1_ID, User2_ID, A_Asset_ID, Description)
if (_User1_ID == 0 || _User1_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_User1_ID);
}
sql.Append(",");
if (_User2_ID == 0 || _User2_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_User2_ID);
}
sql.Append(", null,null");
//
sql.Append(" FROM Fact_Acct WHERE AD_Client_ID=").Append(GetAD_Client_ID())
.Append(" AND ").Append(m_parameterWhere)
.Append(" AND DateAcct < ").Append(DataBase.DB.TO_DATE(_DateAcct_From, true));
// Start Beginning of Year
if (_Account_ID > 0)
{
m_acct = new MElementValue(GetCtx(), _Account_ID, Get_Trx());
if (!m_acct.IsBalanceSheet())
{
MPeriod first = MPeriod.GetFirstInYear(GetCtx(), _DateAcct_From);
if (first != null)
{
sql.Append(" AND DateAcct >= ").Append(DataBase.DB.TO_DATE(first.GetStartDate(), true));
}
else
{
log.Log(Level.SEVERE, "first period not found");
}
}
}
//
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
if (no == 0)
{
log.Fine(sql.ToString());
}
log.Fine("#" + no + " (Account_ID=" + _Account_ID + ")");
} // createBalanceLine
