/// <summary>
/// Get Tree Node
/// </summary>
/// <param name="tree">tree</param>
/// <param name="Node_ID">node</param>
/// <returns>node or null</returns>
public static MTreeNode Get(MTree tree, int Node_ID)
{
MTreeNode retValue = null;
String sql = "SELECT * FROM AD_TreeNode WHERE AD_Tree_ID=@Param1 AND Node_ID=@Param2";
SqlParameter[] Param = new SqlParameter[2];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql, tree.get_TrxName());
//pstmt.setInt (1, tree.getAD_Tree_ID());
Param[0] = new SqlParameter("@Param1", tree.GetAD_Tree_ID());
Param[1] = new SqlParameter("@Param2", Node_ID);
//pstmt.setInt (2, Node_ID);
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql, Param, tree.Get_TrxName());
dt = new DataTable();
dt.Load(idr);
foreach (DataRow dr in dt.Rows)
{
retValue = new MTreeNode(tree.GetCtx(), dr, tree.Get_TrxName());
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
finally
{
dt = null;
idr.Close();
}
return(retValue);
} // get
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);
}
}
} // parse
public String ParseAndSaveLine(String line, int AD_Client_ID, int AD_Org_ID, int C_Element_ID, MTree tree)
{
log.Config(line);
// Fields with ',' are enclosed in "
StringBuilder newLine = new StringBuilder();
StringTokenizer st = new StringTokenizer(line, "\"", false);
newLine.Append(st.NextToken()); // first part
while (st.HasMoreElements())
{
String s = st.NextToken(); // enclosed part
newLine.Append(s.Replace(',', ' ')); // remove ',' with space
if (st.HasMoreTokens())
{
newLine.Append(st.NextToken()); // unenclosed
}
}
// add space at the end - tokenizer does not count empty fields
newLine.Append(" ");
// Parse Line - replace ",," with ", ," - tokenizer does not count empty fields
String pLine = Utility.Util.Replace(newLine.ToString(), ",,", ", ,");
pLine = Utility.Util.Replace(pLine, ",,", ", ,");
st = new StringTokenizer(pLine, ",", false);
// All fields there ?
if (st.CountTokens() == 1)
{
log.Log(Level.SEVERE, "Ignored: Require ',' as separator - " + pLine);
return("");
}
if (st.CountTokens() < 9)
{
log.Log(Level.SEVERE, "Ignored: FieldNumber wrong: " + st.CountTokens() + " - " + pLine);
return("");
}
// Fill variables
String Value = null, Name = null, Description = null,
AccountType = null, AccountSign = null, IsDocControlled = null,
IsSummary = null, Default_Account = null;
int accountParent = -1;
//
for (int i = 0; i < 9 && st.HasMoreTokens(); i++)
{
String s = st.NextToken().Trim();
// Ignore, if is it header line
if (s.StartsWith("[") && s.EndsWith("]"))
{
return("");
}
if (s == null)
{
s = "";
}
//
if (i == 0) // A - Value
{
Value = s;
}
else if (i == 1) // B - Name
{
Name = s;
}
else if (i == 2) // C - Description
{
Description = s;
}
else if (i == 3) // D - Type
{
AccountType = s.Length > 0 ? s[0].ToString() : "E";
}
else if (i == 4) // E - Sign
{
AccountSign = s.Length > 0 ? s[0].ToString() : "N";
}
else if (i == 5) // F - DocControlled
{
IsDocControlled = s.Length > 0 ? s[0].ToString() : "N";
}
else if (i == 6) // G - IsSummary
{
IsSummary = s.Length > 0 ? s[0].ToString() : "N";
}
else if (i == 7) // H - Default_Account
{
Default_Account = s;
}
else if (i == 8)
{
accountParent = Util.GetValueOfInt(s);
}
}
// Ignore if Value & Name are empty (no error message)
if ((Value == null || Value.Length == 0) && (Name == null || Name.Length == 0))
{
return("");
}
////////////////////
//Commented By Lakhwinder
//// Default Account may be blank
//if (Default_Account == null || Default_Account.Length == 0)
// // Default_Account = String.valueOf(s_keyNo++);
// return "";
//// No Summary Account
//if (IsSummary == null || IsSummary.Length == 0)
// IsSummary = "N";
//if (!IsSummary.Equals("N"))
// return "";
//// Validation
//if (AccountType == null || AccountType.Length == 0)
// AccountType = "E";
//if (AccountSign == null || AccountSign.Length == 0)
// AccountSign = "N";
//if (IsDocControlled == null || IsDocControlled.Length == 0)
// IsDocControlled = "N";
//////////////////////
// log.config( "Value=" + Value + ", AcctType=" + AccountType
// + ", Sign=" + AccountSign + ", Doc=" + docControlled
// + ", Summary=" + summary + " - " + Name + " - " + Description);
try
{
// Try to find - allows to use same natutal account for multiple default accounts
MElementValue na = null;
if (m_valueMap.ContainsKey(Value))
{
na = (MElementValue)m_valueMap[Value];
}
if (na == null)
{
// Create Account - save later
na = new MElementValue(m_ctx, Value, Name, Description, AccountType, AccountSign, IsDocControlled.ToUpper().StartsWith("Y"), IsSummary.ToUpper().StartsWith("Y"), m_trx);
int refElementID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ElementValue_ID FROM C_ElementValue
WHERE IsActive='Y' AND AD_Client_ID=" + na.GetAD_Client_ID() + " AND Value='" + accountParent + @"'
AND C_Element_ID=" + C_Element_ID, null, m_trx));
na.SetRef_C_ElementValue_ID(refElementID);
m_valueMap[Value] = na;
na.SetAD_Client_ID(AD_Client_ID);
na.SetAD_Org_ID(AD_Org_ID);
na.SetC_Element_ID(C_Element_ID);
na.SetVIS_DefaultAccount(Default_Account);
if (!na.Save(m_trx))
{
return("Acct Element Values NOT inserted");
//m_info.Append(Msg.Translate(m_lang, "C_ElementValue_ID")).Append(" # ").Append(m_nap.Count).Append("\n");
}
VAdvantage.Model.MTreeNode mNode = VAdvantage.Model.MTreeNode.Get(tree, na.Get_ID());
if (mNode == null)
{
mNode = new VAdvantage.Model.MTreeNode(tree, na.Get_ID());
}
((VAdvantage.Model.PO)mNode).Set_Value("Parent_ID", refElementID);
if (!mNode.Save(m_trx))
{
return("Acct Element Values NOT inserted");
}
}
if (!(Default_Account == null || Default_Account.Length == 0))
{
// Add to Cache
s_base.Add(Default_Account.ToUpper(), na);
}
}
catch (Exception e)
{
return(e.Message);
}
return("");
} //
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);
}
}
