Пример #1
0
        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);
            }
        }