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();
int result = 0;
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);
}
// Document controlled
if (dt.Columns.Contains("(Document_Controlled)") && dt.Rows[i]["(Document_Controlled)"] != DBNull.Value)
{
if (dt.Rows[i]["(Document_Controlled)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsDocControlled(true);
}
else
{
eleValue.SetIsDocControlled(false);
}
}
// Bank Account
if (dt.Columns.Contains("(Bank_Account)") && dt.Rows[i]["(Bank_Account)"] != DBNull.Value)
{
if (dt.Rows[i]["(Bank_Account)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsBankAccount(true);
}
else
{
eleValue.SetIsBankAccount(false);
}
}
// when bank account true, then only set Foreign Currency Account
if (eleValue.IsBankAccount() && dt.Columns.Contains("(Foreign_Currency_Account)") && dt.Rows[i]["(Foreign_Currency_Account)"] != DBNull.Value)
{
if (dt.Rows[i]["(Foreign_Currency_Account)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsForeignCurrency(true);
}
else
{
eleValue.SetIsForeignCurrency(false);
}
}
// when Foreign Currency true, then only set Currency
if (eleValue.IsForeignCurrency() && eleValue.Get_ColumnIndex("C_Currency_ID") >= 0)
{
sql = "SELECT C_Currency_ID FROM C_Currency WHERE IsActive='Y' AND ISO_CODE='"
+ Util.GetValueOfString(dt.Rows[i]["(Currency)"]) + "'";
result = Util.GetValueOfInt(DB.ExecuteScalar(sql));
if (result > 0)
{
eleValue.Set_Value("C_Currency_ID", result);
}
}
// Intermediate_Code
if (eleValue.Get_ColumnIndex("IsInterMediateCode") >= 0 &&
dt.Columns.Contains("(Intermediate_Code)") && dt.Rows[i]["(Intermediate_Code)"] != DBNull.Value)
{
if (dt.Rows[i]["(Intermediate_Code)"].ToString().ToUpper() == "YES")
{
eleValue.Set_Value("IsInterMediateCode", true);
}
else
{
eleValue.Set_Value("IsInterMediateCode", false);
}
}
// Allocation_Related
if (eleValue.Get_ColumnIndex("IsAllocationRelated") >= 0 &&
dt.Columns.Contains("(Allocation_Related)") && dt.Rows[i]["(Allocation_Related)"] != DBNull.Value)
{
if (dt.Rows[i]["(Allocation_Related)"].ToString().ToUpper() == "YES")
{
eleValue.Set_Value("IsAllocationRelated", true);
}
else
{
eleValue.Set_Value("IsAllocationRelated", false);
}
}
// Has Group
if (eleValue.Get_ColumnIndex("HasGroup") >= 0 &&
dt.Columns.Contains("(Has_Group)") && dt.Rows[i]["(Has_Group)"] != DBNull.Value)
{
if (dt.Rows[i]["(Has_Group)"].ToString().ToUpper() == "YES")
{
eleValue.Set_Value("HasGroup", true);
}
else
{
eleValue.Set_Value("HasGroup", false);
}
}
// Conversion Type
if (eleValue.Get_ColumnIndex("C_ConversionType_ID") >= 0)
{
sql = "SELECT C_ConversionType_ID FROM C_ConversionType WHERE IsActive='Y' AND Value='"
+ Util.GetValueOfString(dt.Rows[i]["(Currency_Type)"]) + "'";
result = Util.GetValueOfInt(DB.ExecuteScalar(sql));
if (result > 0)
{
eleValue.Set_Value("C_ConversionType_ID", result);
}
}
// 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())
{
return(GetRetrievedError(mNode, "NodeNotSaved"));
//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 = "";
int result = 0;
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");
}
// Document controlled
if (dt.Columns.Contains("(Document_Controlled)") && dt.Rows[i]["(Document_Controlled)"] != DBNull.Value)
{
if (dt.Rows[i]["(Document_Controlled)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsDocControlled(true);
}
else
{
eleValue.SetIsDocControlled(false);
}
}
// Bank Account
if (dt.Columns.Contains("(Bank_Account)") && dt.Rows[i]["(Bank_Account)"] != DBNull.Value)
{
if (dt.Rows[i]["(Bank_Account)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsBankAccount(true);
}
else
{
eleValue.SetIsBankAccount(false);
}
}
// when bank account true, then only set Foreign Currency Account
if (eleValue.IsBankAccount() && dt.Columns.Contains("(Foreign_Currency_Account)") && dt.Rows[i]["(Foreign_Currency_Account)"] != DBNull.Value)
{
if (dt.Rows[i]["(Foreign_Currency_Account)"].ToString().ToUpper() == "YES")
{
eleValue.SetIsForeignCurrency(true);
}
else
{
eleValue.SetIsForeignCurrency(false);
}
}
// when Foreign Currency true, then only set Currency
if (eleValue.IsForeignCurrency() && eleValue.Get_ColumnIndex("C_Currency_ID") >= 0)
{
sql = "SELECT C_Currency_ID FROM C_Currency WHERE IsActive='Y' AND ISO_CODE='"
+ Util.GetValueOfString(dt.Rows[i]["(Currency)"]) + "'";
result = Util.GetValueOfInt(DB.ExecuteScalar(sql));
if (result > 0)
{
eleValue.Set_Value("C_Currency_ID", result);
}
}
// Intermediate_Code
if (eleValue.Get_ColumnIndex("IsInterMediateCode") >= 0 &&
dt.Columns.Contains("(Intermediate_Code)") && dt.Rows[i]["(Intermediate_Code)"] != DBNull.Value)
{
if (dt.Rows[i]["(Intermediate_Code)"].ToString().ToUpper() == "YES")
{
eleValue.Set_Value("IsInterMediateCode", true);
}
else
{
eleValue.Set_Value("IsInterMediateCode", false);
}
}
// Allocation_Related
if (eleValue.Get_ColumnIndex("IsAllocationRelated") >= 0 &&
dt.Columns.Contains("(Allocation_Related)") && dt.Rows[i]["(Allocation_Related)"] != DBNull.Value)
{
if (dt.Rows[i]["(Allocation_Related)"].ToString().ToUpper() == "YES")
{
eleValue.Set_Value("IsAllocationRelated", true);
}
else
{
eleValue.Set_Value("IsAllocationRelated", false);
}
}
// Has Group
if (eleValue.Get_ColumnIndex("HasGroup") >= 0 &&
dt.Columns.Contains("(Has_Group)") && dt.Rows[i]["(Has_Group)"] != DBNull.Value)
{
if (dt.Rows[i]["(Has_Group)"].ToString().ToUpper() == "YES")
{
eleValue.Set_Value("HasGroup", true);
}
else
{
eleValue.Set_Value("HasGroup", false);
}
}
// Conversion Type
if (eleValue.Get_ColumnIndex("C_ConversionType_ID") >= 0)
{
sql = "SELECT C_ConversionType_ID FROM C_ConversionType WHERE IsActive='Y' AND Value='"
+ Util.GetValueOfString(dt.Rows[i]["(Currency_Type)"]) + "'";
result = Util.GetValueOfInt(DB.ExecuteScalar(sql));
if (result > 0)
{
eleValue.Set_Value("C_ConversionType_ID", result);
}
}
//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);
}
}