Ejemplo n.º 1
0
 public SigmaResultType AddImportHistory(TypeImportHistory objImportHistory)
 {
     SigmaResultType result = new SigmaResultType();
     try
     {
         ImportHistoryMgr importHistoryMgr = new ImportHistoryMgr();
         result = importHistoryMgr.AddImportHistory(objImportHistory);
         return result;
     }
     catch (Exception ex)
     {
         // Log Exception
         ExceptionHelper.logException(ex);
         result.IsSuccessful = false;
         result.ErrorMessage = ex.Message;
         return result;
     }
 }
Ejemplo n.º 2
0
        public SigmaResultType ImportSigmaUserFromExcel(string filePath, string exportfilepath)
        {
            TypeUserInfo userinfo = AuthMgr.GetUserInfo();

            DataTable dt = new DataTable();
            DataTable tmpDt = new DataTable();
            SigmaResultType result = new SigmaResultType();

            dt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, true);

            DataTable tmpdt = new DataTable();
            tmpdt = dt.Copy();
            tmpdt.Rows.Clear();
            tmpdt.Columns.Add("Fail reason");

            int failCnt = 0;
            int SuccessCnt = 1;

            int availableCnt = GetAvailableLicenseCount();

            foreach (DataRow r in dt.Rows)
            {
                if (SuccessCnt > availableCnt)
                {
                    tmpdt.Rows.Add(r.ItemArray);
                    tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = "Exceeded the number of user licenses";
                    failCnt++;
                }
                else
                {
                    TypeSigmaUser obj = new TypeSigmaUser();
                    obj.SigmaUserId = r[0].ToString();
                    obj.EmployeeId = r[1].ToString();
                    obj.FirstName = r[2].ToString();
                    obj.LastName = r[3].ToString();
                    obj.PhoneNo = r[4].ToString();
                    obj.Email = r[5].ToString();
                    obj.Password = RandumPassword();
                    obj.CompanyName = r[6].ToString();
                    obj.CreatedBy = userinfo.SigmaUserId;

                    if (string.IsNullOrEmpty(GetFailreasonForRequiredUser(r)))
                    {
                        SigmaResultType rst = AddSigmaUserTemplate(obj);

                        if (rst.ErrorMessage != null)
                        {
                            tmpdt.Rows.Add(r.ItemArray);
                            tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = rst.ErrorMessage.ToString();
                            failCnt++;
                        }
                        else
                            SuccessCnt++;
                    }
                    else
                    {
                        tmpdt.Rows.Add(r.ItemArray);
                        tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = GetFailreasonForRequiredUser(r);
                        failCnt++;
                    }
                }
            }
            TypeImportHistory ImportHistory = new TypeImportHistory();
            ImportHistory.ImportCategory = "User";
            ImportHistory.ImportedFileName = Path.GetFileName(filePath).ToString();
            ImportHistory.ImportedDate = DateTime.Now.ToString();
            ImportHistory.TotalCount = dt.Rows.Count;
            ImportHistory.SuccessCount = dt.Rows.Count - failCnt;
            ImportHistory.FailCount = failCnt;
            ImportHistory.CreatedBy = userinfo.SigmaUserId;
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();
            result = HistoryMgr.AddImportHistory(ImportHistory);

            //if exists error list
            if (tmpdt.Rows.Count > 0)
            {
                if (!System.IO.Directory.Exists(exportfilepath))
                {
                    System.IO.Directory.CreateDirectory(exportfilepath);
                }

                //excel file generate for direct call 'export' link
                Export2Excel.ConvertExcelfromData(tmpdt, result.ScalarValue + Path.GetExtension(filePath), exportfilepath);

                //csv file generate for import error list view
                Export2Excel.ConvertCSVFile(tmpdt, result.ScalarValue + ".csv", exportfilepath);

            }
            return result;
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Set Add ImportHistory Info
        /// </summary>
        /// <param name="iTotalCnt"></param>
        /// <param name="FailCnt"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private SigmaResultType AddImportHistory(int iTotalCnt, int FailCnt, string FileName, string ImportCategory)
        {
            SigmaResultType HistoryResult = new SigmaResultType();
            TypeImportHistory ImportHistory = new TypeImportHistory();
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();

            //DB Date로 하기 말고.
            DateTime convertedDate = DateTime.Parse(DateTime.Now.ToString());
            DateTime localDate = convertedDate.ToLocalTime();

            int Failcnt = FailCnt++;
            int iSuccessCnt = iTotalCnt - Failcnt;
            ImportHistory.ImportCategory = ImportCategory;
            ImportHistory.ImportedFileName = FileName;
            ImportHistory.ImportedDate = localDate.ToString();
            ImportHistory.TotalCount = iTotalCnt;
            ImportHistory.SuccessCount = iSuccessCnt;
            ImportHistory.FailCount = Failcnt;
            ImportHistory.CreatedBy = userinfo.SigmaUserId;//임시
            return HistoryResult = HistoryMgr.AddImportHistory(ImportHistory);
        }
Ejemplo n.º 4
0
        public SigmaResultType ImportCivilMto(DataTable Exceldt, string filePath, string exportfilepath)
        {
            #region Set  Basic

            CommonCodeMgr common = new CommonCodeMgr();
            SigmaResultType SigmaResult = new SigmaResultType();
            DataLoaderCivil loader = new DataLoaderCivil();
            Export2Excel ExportExcel = new Export2Excel();
            TypeComponent cmpt = new TypeComponent();
            TypeMaterial mtr = new TypeMaterial();
            TypeComponentCustomField ccf = new TypeComponentCustomField();
            TypeMaterialCustomField mcf = new TypeMaterialCustomField();
            TypeImportHistory ImportHistory = new TypeImportHistory();
            TypeComponentReferenceDrawing crd = new TypeComponentReferenceDrawing();
            ComponentMgr comMgr = new ComponentMgr();
            MaterialMgr mtrMgr = new MaterialMgr();
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();
            ComponentCustomFieldMgr CCFMgr = new ComponentCustomFieldMgr();
            MaterialCustomFieldMgr MCFMgr = new MaterialCustomFieldMgr();
            TypeComponentProgress cmptPro = new TypeComponentProgress();
            ComponentProgressMgr cmpProMgr = new ComponentProgressMgr();
            CommonCodeMgr ComCodeMgr = new CommonCodeMgr();
            ComponentReferenceDrawingMgr crdMgr = new ComponentReferenceDrawingMgr();

            int Failcnt = 0;
            int ImportHistoryId = -1;
            int HisCnt = 0;
            int ComponentID = -1;

            if (Exceldt.Rows.Count > 0)
            {
                loader = MTOImportHelper.GetDataLoaderCivilOrdinal(Exceldt);
            }
            else
            {
                SigmaResult.IsSuccessful = false;
                SigmaResult.ErrorMessage = "no record from file.";
            }

            //Set TagNumber Name
            DataTable TmGroupDt = SetTagNumber(Exceldt);
            //Make Err Table
            DataTable ErrDataTable =  SetErrTable(Exceldt);
            #endregion

            #region Get Common Data From DB
            string FileName  = Path.GetFileName(filePath).ToString();

            //Get CWP Table
            //DataTable CwpdDt = common.GetCommonCode("*", "cwp", "");
            DataTable CwpdDt = common.GetCWP();
            SigmaResult = SetErrMassage(CwpdDt, "CWP"); //Err?

            //Get UOM Table
            string UOMWhere = "Where CodeCategory = 'UOM' ";
            DataTable UOMDt = common.GetCommonCode("*", "SigmaCode", UOMWhere);
            SigmaResult = SetErrMassage(UOMDt, "SigmaCode Table UOM");

            //Get Material Table
            string MtrWhere = "where DisciplineCode = 'DISCIPLINE_CIVIL'";
            DataTable MtrDt = common.GetCommonCode("*", "Material", MtrWhere);
            SigmaResult = SetErrMassage(MtrDt, "Material");

            //Get CostCode Table
            DataTable CostCodeDt = common.GetCommonCode("CostCodeId, CostCode", "CostCode", "");
            SigmaResult = SetErrMassage(CostCodeDt, "CostCode");

            //Get Discipline(SigmaCode Table)
            string DisciplineWhere = "where CodeCategory = 'DISCIPLINE' AND CodeName = 'Civil'";
            DataTable DisciplineDt = common.GetCommonCode("code", "SigmaCode", DisciplineWhere);
            SigmaResult = SetErrMassage(DisciplineDt, "SigmaCode Table DISCIPLINE");

            //Get TaskCatogory
            DataTable TaskCategoryDt = common.GetCommonCode("*", "TaskCategory", "where DisciplineCode = 'DISCIPLINE_CIVIL'");
            SigmaResult = SetErrMassage(TaskCategoryDt, "TaskCategory");
            int taskCategoryCnt = TaskCategoryDt.Rows.Count;
            int cnt1 = int.Parse(TaskCategoryDt.Rows[0][0].ToString());
            int cnt2 = int.Parse(TaskCategoryDt.Rows[taskCategoryCnt - 1][0].ToString());
            string where = "WHERE TaskCategoryId between " + cnt1 + " AND  " + cnt2;

            //Get TaskType
            DataTable TaskTypeDt = common.GetCommonCode("*", "TaskType", where);
            SigmaResult = SetErrMassage(TaskTypeDt, "TaskCategory");

            //StepRow의 Data 없는 경우 [progressStep Table 에서 ProgressStepId = 13|| Name = default progress의  Weight 값으로 처리]
            DataRow[] defaultStepRow = new DataTable().Select();// common.GetCommonCode("*", "ProgressStep", " Where Name = 'default progress'").Select();

            #endregion

            //Set Composite Data
            DataTable CompoDt = CheckCompositeData(Exceldt);

            foreach (DataRow r in Exceldt.Rows)
            {
                #region  Validation Data
                DataRow[] CwpRow = null; DataRow[] TaskCategoryRow = null; DataRow[] TaskTypeRow = null; DataRow[] CostCodeRow = null;
                DataRow[] StepRow = null; DataRow[] UOMRow = null; DataRow[] MtrRow = null; DataRow[] comMaterRow = null;
                string MtrName = string.Empty;   int TaskCategoryId = 0;  int TaskTyeId = 0;    int CostCodeid = 0;
                decimal ComQty = 0; string MultiQty = "N"; string TagNumber = string.Empty;
                string compositeMateral = string.Empty; decimal EstimatedManhour = 0; string EngTagNumber = string.Empty;

                List<TypeComponentProgress> listComponentProgress = new List<TypeComponentProgress>();

                //get Drawing Info
                DataTable DrawDt = common.GetCwpDrawing(r[loader.Ord_Drawing].ToString(), (r[loader.Ord_Revision].ToString()));
                SigmaResult = SetErrMassage(DrawDt, r[loader.Ord_Drawing].ToString());
                //get Choice UOM info
                string SelectUOMWhere = "CodeName = '" + r[loader.Ord_UOM] + "' OR CodeShortName = '" + r[loader.Ord_UOM] + "'";

                //Get CostCode Info
                if (!string.IsNullOrEmpty(r[loader.Ord_CostCode].ToString().Trim())) CostCodeRow = CostCodeDt.Select("CostCode = '" + r[loader.Ord_CostCode].ToString() + "'");
                if (CostCodeRow != null && CostCodeRow.Length > 0) CostCodeid = Convert.ToInt32(CostCodeRow[0][0].ToString());
                if (!string.IsNullOrEmpty(r[loader.Ord_CWP].ToString().Trim()))CwpRow = CwpdDt.Select("CwpName = '" + r[loader.Ord_CWP].ToString() + "'");
                if (!string.IsNullOrEmpty(r[loader.Ord_TaskCategory].ToString().Trim())) TaskCategoryRow = TaskCategoryDt.Select("TaskCategoryName = '" + r[loader.Ord_TaskCategory] + "'");
                if (!string.IsNullOrEmpty(r[loader.Ord_TaskType].ToString().Trim())) TaskTypeRow = TaskTypeDt.Select("TaskTypeName = '" + r[loader.Ord_TaskType] + "'");
                if (!string.IsNullOrEmpty(r[loader.Ord_MaterialDescription].ToString().Trim()))MtrName = r[loader.Ord_MaterialDescription].ToString();
                if (!string.IsNullOrEmpty(r[loader.Ord_EngTagNumber].ToString().Trim())) EngTagNumber = r[loader.Ord_EngTagNumber].ToString();
                if (!string.IsNullOrEmpty(r[loader.Ord_UOM].ToString().Trim())) UOMRow = UOMDt.Select(SelectUOMWhere);
                if(TaskCategoryRow != null && TaskCategoryRow.Length > 0 )TaskCategoryId = int.Parse(TaskCategoryRow[0][0].ToString());
                if(TaskTypeRow != null && TaskTypeRow.Length > 0 )TaskTyeId = int.Parse(TaskTypeRow[0][0].ToString());
                if (!string.IsNullOrEmpty(r[loader.Ord_AssoCompoMaterial].ToString().Trim())) compositeMateral = r[loader.Ord_AssoCompoMaterial].ToString();

                string MtrNameWhere = " AND Description = '" + MtrName + "'";

                //Check Exist material name
                if (TaskCategoryRow.Length > 0 && TaskTypeRow.Length > 0)
                {
                    MtrRow = MtrDt.Select("TaskCategoryId = '" + TaskCategoryRow[0][0] + "' " +
                                    "AND TaskTypeId = '" + TaskTypeRow[0][0] + "' " + MtrNameWhere);
                }

                if(UOMRow.Length > 0)
                {
                    UOMRow = MtrDt.Select("TaskCategoryId = '" + TaskCategoryRow[0][0] + "' " +
                                    "AND TaskTypeId = '" + TaskTypeRow[0][0] + "' AND UomCode = '" +UOMRow[0][0] + "'");
                }

                //MaterialDescription & AssociatedComposite Material Description
                if (compositeMateral != string.Empty)
                {
                    comMaterRow = CompoDt.Select("MaterialDescription ='" + compositeMateral + "'");
                }

                #endregion

                #region Set ProgressStep
                //Check ProgressStep Table
                StepRow = common.GetProgressStepByTaskCategoryTaskType(TaskCategoryId, TaskTyeId).Select();
                ComQty = Convert.ToDecimal(r[loader.Ord_Qty]);

                if (StepRow.Length != 0)
                {
                    foreach (DataRow Step in StepRow)
                    {
                        TypeComponentProgress obj = new TypeComponentProgress();
                        MultiQty = Step["IsMultipliable"].ToString();

                        if (MultiQty == "Y") // ProgressStep > IsMultipliable(MultipliedByQty) = "Y"
                        {
                            obj.EstimatedManhours = (Convert.ToDecimal(Step["Weight"]) / 100) * Convert.ToDecimal(MtrRow.Length > 0 ? MtrRow[0]["Manhours"] : 0) * ComQty;//Manhours*weight*Qty
                            obj.ProgressStepId = int.Parse(Step["ProgressStepId"].ToString());
                        }
                        else
                        {
                            obj.EstimatedManhours = (Convert.ToDecimal(Step["Weight"]) / 100) * Convert.ToDecimal(MtrRow.Length > 0 ? MtrRow[0]["Manhours"] : 0) * ComQty;//Manhours*weight*Qty 2014.3.31  임시 IsMultipliable 구분 없도록
                            obj.ProgressStepId = int.Parse(Step["ProgressStepId"].ToString());
                        }
                        EstimatedManhour = EstimatedManhour + obj.EstimatedManhours;
                        listComponentProgress.Add(obj);
                    }
                }
                else
                {
                    TypeComponentProgress obj = new TypeComponentProgress();
                    obj.EstimatedManhours = (defaultStepRow.Length > 0 ? Convert.ToDecimal(defaultStepRow[0]["weight"]) : 100) * ComQty;
                    obj.ProgressStepId = defaultStepRow.Length > 0 ? int.Parse(defaultStepRow[0]["ProgressStepId"].ToString()) : 0;
                    EstimatedManhour = EstimatedManhour + obj.EstimatedManhours;
                    listComponentProgress.Add(obj);
                }

                #endregion

                #region 1. Set ImportHistory(SuccessCount/FailCount)
                //ImportHistoryId 값 구하기 위해
                if (HisCnt == 0)
                {
                    SigmaResultType AddResult = AddImportHistory(0, 0, FileName, "MTO");
                    ImportHistoryId = Convert.ToInt32(AddResult.ScalarValue);
                    HisCnt = 1;// ImportHistory Table 한번만 입력
                }
                #endregion

                #region 2.  Set ErrDataTable

            if (CwpRow.Length == 0 || CwpRow == null)
            {
                ErrDataTable.Rows.Add(r.ItemArray);
                ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "The drawing type of [CWP] doesn’t exist. ";
                Failcnt = Failcnt + 1;
            }
            else if (DrawDt.Rows.Count == 0)
            {
                ErrDataTable.Rows.Add(r.ItemArray);
                ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "The drawing type of [Drawing Number] doesn’t exist. ";
                Failcnt = Failcnt + 1;
            }
            else if (TaskCategoryDt.Rows.Count == 0 || TaskCategoryRow.Length == 0)
            {
                ErrDataTable.Rows.Add(r.ItemArray);
                ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + r[loader.Ord_TaskCategory].ToString() + "]";
                Failcnt = Failcnt + 1;
            }
            else if (TaskTypeDt.Rows.Count == 0 || TaskTypeRow.Length == 0)
            {
                ErrDataTable.Rows.Add(r.ItemArray);
                ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + r[loader.Ord_TaskType].ToString() + "]";
                Failcnt = Failcnt + 1;
            }
            else if (MtrRow == null || MtrRow.Length == 0)
            {
                ErrDataTable.Rows.Add(r.ItemArray);
                ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + r[loader.Ord_MaterialDescription].ToString() + "]";
                Failcnt = Failcnt + 1;
            }
            else if (UOMRow == null || UOMRow.Length == 0)
            {
                ErrDataTable.Rows.Add(r.ItemArray);
                ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + r[loader.Ord_UOM].ToString() + "]";
                Failcnt = Failcnt + 1;
            }
            //else if (CostCodeRow == null || CostCodeRow.Length == 0)
            //{
            //    ErrDataTable.Rows.Add(r.ItemArray);
            //    ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + r[loader.Ord_CostCode].ToString() + "]";
            //    Failcnt = Failcnt + 1;
            //}
            //else if (comMaterRow != null && comMaterRow.Length == 0)
            //{
            //    ErrDataTable.Rows.Add(r.ItemArray);
            //    ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + compositeMateral + "]";
            //    Failcnt = Failcnt + 1;
            //}
            else
            {
                #endregion

                #region 3. Set & Save Component

                    #region 3.1 Set Tag Number

                if (!string.IsNullOrEmpty(r[loader.Ord_TagNumber].ToString().Trim()))
                    TagNumber = r[loader.Ord_TagNumber].ToString().Trim();
                else
                {
                    string name = string.Empty;

                    if (MtrName.Length > 3)
                    {
                        name = MtrName.Substring(0, MtrName.Length);
                    }

                    if ((comMaterRow != null && comMaterRow.Length > 0) || MtrName.Contains("Composite")) //Composite data가 있으면 TagNumber : CO-drawing number-sequence number
                    {
                        TagNumber = MTOImportHelper.GetCreateTagNumber(TmGroupDt, r[loader.Ord_Drawing].ToString().Trim(), "CO");
                    }
                    else
                    {
                        TagNumber = MTOImportHelper.GetCreateTagNumber(TmGroupDt, r[loader.Ord_Drawing].ToString().Trim(), string.Empty);//drawing number-sequence number
                    }
                }

                #endregion
                cmpt.CwpId = int.Parse(CwpRow[0][0].ToString());
                cmpt.DrawingId = Convert.ToInt32(DrawDt.Rows[DrawDt.Rows.Count - 1]["DrawingId"]);
                cmpt.MaterialId = Convert.ToInt32(MtrRow[0][0]);//metarial table data 있어야 함
                cmpt.TagNumber = TagNumber;
                cmpt.Qty = ComQty;
                cmpt.ScheduledWorkItemId = 0;
                cmpt.IsoLineNo = 0;
                cmpt.EstimatedManhour = EstimatedManhour;// Material의 manhours * Qty
                cmpt.ImportHistoryId = ImportHistoryId;
                cmpt.Description = MtrName;//Material Name
                cmpt.EngTagNumber = EngTagNumber;
                cmpt.CreatedBy = userinfo.SigmaUserId;
                SigmaResult = comMgr.AddComponent(cmpt);
                ComponentID = SigmaResult.ScalarValue;//for ComponentCustomField insert
                #endregion

                #region 4.Set & Save ComponentReferenceDrawing
                crd.ComponentId = ComponentID;
                crd.DrawingId = int.Parse(DrawDt.Rows[0]["DrawingId"].ToString());
                crd.CreatedBy = userinfo.SigmaUserId;
                SigmaResult = crdMgr.AddComponentReferenceDrawing(crd);
                #endregion

                #region 5. Set & Save ComponetCustomField[StructureNumber]

                if (r[loader.Ord_StructureNumber].ToString() != null)
                {
                    string FieldName = "Structure Number";
                    string ColumnValue = r[loader.Ord_StructureNumber].ToString();
                    string FieldWhere = "WHERE FieldName = '" + FieldName.Replace(" ","") + "'";
                    int customFieldId = 0;
                    DataRow[] customFieldRow = common.GetCommonCode("*", "CustomField", FieldWhere).Select();

                    if (customFieldRow.Count() > 0)
                    {
                        customFieldId = int.Parse(customFieldRow[0]["CustomFieldId"].ToString());
                    }
                    else
                    {
                        TypeCustomField rtn = new TypeCustomField();
                        rtn.FieldName = FieldName;
                        rtn.IsDisplayable = "Y";
                        CustomFieldMgr customFieldMtr = new CustomFieldMgr();
                        rtn = customFieldMtr.GetCustomField(rtn);
                        customFieldId = rtn.CustomFieldId;
                    }

                    ccf.CustomFieldId = customFieldId;
                    ccf.ComponentId = ComponentID;
                    ccf.Value = r[loader.Ord_StructureNumber].ToString();
                    ccf.CreatedBy = userinfo.SigmaUserId;
                    SigmaResult = CCFMgr.AddComponentCustomField(ccf);
                }

                #endregion

                #region 6. Set & Save ComponentProgress
                listComponentProgress.ForEach(x => x.ComponentId = ComponentID);
                listComponentProgress.ForEach(x => x.IsCompleted = "N");
                listComponentProgress.ForEach(x => x.AmountInstalled = 0);
                listComponentProgress.ForEach(x => x.SigmaOperation = "C");
                SigmaResult = cmpProMgr.MultiComponentProgress(listComponentProgress);
                #endregion

                #region 7. CustomField = "UD_" 인 경우[ComponentCustomField Table]
                //Excel 항목중에 "UD-"로 시작하는 것에 대해서 Validation 처리
                for (int i = 0; i < Exceldt.Columns.Count; i++)
                {
                    if (Exceldt.Columns[i].ToString().Substring(0, 3).ToUpper() == "UD_")
                    {
                        string RowValue = r.ItemArray.GetValue(i).ToString();
                        SigmaResult.IsSuccessful = CheckCustomField(Exceldt, Exceldt.Columns[i].ToString().Trim(), ComponentID, RowValue);
                    }
                }
                #endregion
            }
            }

            #region 8. Update Fail Count/Success Count(ImportHistory Table)
            Failcnt = Failcnt++;
            int iTotalCnt = Exceldt.Rows.Count;
            int iSuccessCnt = iTotalCnt - Failcnt;
            ImportHistory.ImportCategory = "MTO";
            ImportHistory.ImportedFileName = FileName;
            ImportHistory.ImportedDate = DateTime.Now.ToString();
            ImportHistory.TotalCount = iTotalCnt;
            ImportHistory.SuccessCount = iSuccessCnt;
            ImportHistory.FailCount = Failcnt;
            ImportHistory.UpdatedBy = userinfo.SigmaUserId;
            ImportHistory.ImportHistoryId = ImportHistoryId;
            SigmaResult = HistoryMgr.UpdateImportHistory(ImportHistory);

            #endregion

            //ConvertExcel file && CSV file
            Export2Excel.ConvertExcelfromData(ErrDataTable, ImportHistoryId.ToString()+".xlsx", exportfilepath);
            Export2Excel.ConvertCSVFile(ErrDataTable, ImportHistoryId.ToString()+".csv", exportfilepath);

            return SigmaResult;
        }
Ejemplo n.º 5
0
        public SigmaResultType ImportProjectCostCodeFromExcel(string filePath, string exportfilepath)
        {
            TypeUserInfo userinfo = AuthMgr.GetUserInfo();

            DataTable dt = new DataTable();
            DataTable tmpDt = new DataTable();
            SigmaResultType result = new SigmaResultType();

            dt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, true);

            DataTable tmpdt = new DataTable();
            tmpdt = dt.Copy();
            tmpdt.Rows.Clear();
            tmpdt.Columns.Add("Fail reason");

            int failCnt = 0;

            foreach (DataRow r in dt.Rows)
            {
                TypeProjectCostCode obj = new TypeProjectCostCode();
                obj.CostCode = r[0].ToString();
                obj.UomName = r[2].ToString();

                obj.ProjectId = userinfo.CurrentProjectId;
                obj.CreatedBy = userinfo.SigmaUserId;

                if (string.IsNullOrEmpty(GetFailreasonForRequiredProjectCostCode(r)))
                {
                    if (!string.IsNullOrEmpty(r[3].ToString()))
                        obj.EstimatedQty = Convert.ToDecimal(r[3].ToString());
                    if (!string.IsNullOrEmpty(r[4].ToString()))
                        obj.EstimatedManhour = Convert.ToDecimal(r[4].ToString());

                    SigmaResultType rst = AddProjectCostCodeByTemplate(obj);

                    if (!rst.IsSuccessful)
                    {
                        tmpdt.Rows.Add(r.ItemArray);
                        tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = rst.ErrorMessage.ToString();
                        failCnt++;
                    }
                }
                else
                {
                    tmpdt.Rows.Add(r.ItemArray);
                    tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = GetFailreasonForRequiredProjectCostCode(r);
                    failCnt++;
                }
            }
            TypeImportHistory ImportHistory = new TypeImportHistory();
            ImportHistory.ImportCategory = "ProjectCostCode";
            ImportHistory.ImportedFileName = Path.GetFileName(filePath).ToString();
            ImportHistory.ImportedDate = DateTime.Now.ToString();
            ImportHistory.TotalCount = dt.Rows.Count;
            ImportHistory.SuccessCount = dt.Rows.Count - failCnt;
            ImportHistory.FailCount = failCnt;
            ImportHistory.CreatedBy = userinfo.SigmaUserId;
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();
            result = HistoryMgr.AddImportHistory(ImportHistory);

            //if exists error list
            if (tmpdt.Rows.Count > 0)
            {
                if (!System.IO.Directory.Exists(exportfilepath))
                {
                    System.IO.Directory.CreateDirectory(exportfilepath);
                }

                //excel file generate for direct call 'export' link
                Export2Excel.ConvertExcelfromData(tmpdt, result.ScalarValue + Path.GetExtension(filePath), exportfilepath);

                //csv file generate for import error list view
                Export2Excel.ConvertCSVFile(tmpdt, result.ScalarValue + ".csv", exportfilepath);

            }
            return result;
        }
Ejemplo n.º 6
0
        private SigmaResultType AddImportHistory(int iTotalCnt, int FailCnt, string filePath, string ImportCategory)
        {
            SigmaResultType HistoryResult = new SigmaResultType();
            TypeImportHistory ImportHistory = new TypeImportHistory();
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();

            int Failcnt = FailCnt++;
            int iSuccessCnt = iTotalCnt - Failcnt;
            ImportHistory.ImportCategory = ImportCategory;
            ImportHistory.ImportedFileName = Path.GetFileName(filePath).ToString();
            ImportHistory.ImportedDate = DateTime.Now.ToString();
            ImportHistory.TotalCount = iTotalCnt;
            ImportHistory.SuccessCount = iSuccessCnt;
            ImportHistory.FailCount = Failcnt;
            ImportHistory.CreatedBy = "ADMIN";
            return HistoryResult = HistoryMgr.AddImportHistory(ImportHistory);
        }
Ejemplo n.º 7
0
        public SigmaResultType AddDrawing(string fileUrl, string exportfilepath)
        {
            SigmaResultType result = new SigmaResultType();
            TransactionScope scope = null;
            TypeUserInfo userinfo = AuthMgr.GetUserInfo();

            DataTable drawingdata = null;

            // Get connection string 123
            string connStr = ConnStrHelper.getDbConnString();

            // * [1] excel file upload - only one file
            string Importedfilename = Path.GetFileNameWithoutExtension(fileUrl);
            //FileInfo fileinfo = new FileInfo(Importedfilename);
            FileInfo fileinfo = new FileInfo(fileUrl);
            long fileSize = fileinfo.Length; // byte
            //long fileSize = 10;
            string fileExtention = Path.GetExtension(fileUrl);
            string fileType = "FILE_TYPE_DRAWING";
            string CreateBy = userinfo.SigmaUserId;

            int fileStroeId;
            int fileId;
            int AffectedRow;

            string fileCategory = "FILE_CATEGORY_DRAWING";

            TypeImportHistory ImportHistory = new TypeImportHistory();
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();

                // * [2] Save Drawing Info
                drawingdata = ImportHelper.ImportWorkSheet(fileUrl, true, false, "");

                #region Make ERR Data Table
                DataTable ErrDataTable = new DataTable("ErrDataTable");
                ErrDataTable = drawingdata.Copy();
                ErrDataTable.Rows.Clear();
                ErrDataTable.Columns.Add("Reason");//Desciption Of Err
                #endregion

                #region Get Common Data From DB
                //Get CWP Table
                CommonCodeMgr common = new CommonCodeMgr();
                DataTable CwpdDt = common.GetCommonCode("*", "cwp", "");

                //Get Drawing Type(SigmaCode Table)
                string DrawingTypeWhere = "where CodeCategory = 'DRAWING_TYPE'";
                DataTable DrawingTypeDt = common.GetCommonCode("CodeName", "SigmaCode", DrawingTypeWhere);
                #endregion

                int importid = 0;
                int Failcnt = 0;
                int HisCnt = 0;
                string Failreason = string.Empty;

                foreach (DataRow drow in drawingdata.Rows)
                {
                    int failrow = 0;

                    string xlsCwpName = drow["*CWP"].ToString();
                    string xlsDrawingno = drow["*Drawing Number"].ToString();
                    string xlsFileName = drow["*File Name"].ToString();
                    string xlsRevision = drow["*Revision"].ToString();
                    string xlsTitle = drow["*Drawing Title"].ToString();
                    string xlsDesc = drow["*Drawing Description"].ToString();
                    string xlsDrawingType = drow["*Drawing Type"].ToString();
                    string xlsRefDrawingno = drow["Reference Drawings"].ToString();
                    string xlsDetDrawingno = drow["Detailed Drawings"].ToString();

                    DataRow[] CwpRow = CwpdDt.Select("CwpName = '" + xlsCwpName + "'");
                    DataRow[] DrawingTypeRow = DrawingTypeDt.Select("CodeName = '" + xlsDrawingType + "'");

                    #region 1.  Set ErrDataTable

                    Failreason = GetFailreasonForRequired(drow);

                    if (string.IsNullOrEmpty(Failreason))
                    {
                        if (CwpRow.Length == 0)
                        {
                            ErrDataTable.Rows.Add(drow.ItemArray);
                            ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Reason"] = " InCorrect CWP!!";
                            Failcnt = Failcnt + 1;
                            failrow = 1;
                        }
                        //else if (string.IsNullOrEmpty(xlsDrawingno.Trim()))
                        //{
                        //    ErrDataTable.Rows.Add(drow.ItemArray);
                        //    ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Reason"] = " Drawing Number is required for Import";
                        //    Failcnt = Failcnt + 1;
                        //    failrow = 1;
                        //}
                        else if (DrawingTypeRow.Length == 0)
                        {
                            ErrDataTable.Rows.Add(drow.ItemArray);
                            ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Reason"] = " InCorrect DrawingType!!";
                            Failcnt = Failcnt + 1;
                            failrow = 1;
                        }
                    }
                    else
                    {
                        ErrDataTable.Rows.Add(drow.ItemArray);
                        ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Reason"] = Failreason;
                        Failcnt = Failcnt + 1;
                        failrow = 1;
                    }

                    #endregion

                    if (failrow == 1)
                        continue;

                    //ImportHistoryId 값 구하기 위해
                    if (HisCnt == 0)
                    {
                        SigmaResultType AddResult = AddImportHistory(0, 0, fileUrl, "DRAWING");
                        importid = Convert.ToInt32(AddResult.ScalarValue);
                        HisCnt = 1;// ImportHistory Table 한번만 입력
                    }

                    SqlParameter[] drawingParm = new SqlParameter[] {
                                //new SqlParameter("@ImportedSourceFileInfoID", fileId),
                                new SqlParameter("@ImportedSourceFileInfoID", importid),
                                new SqlParameter("@CwpName", xlsCwpName),
                                new SqlParameter("@Name", xlsDrawingno),
                                new SqlParameter("@FileName", xlsFileName),
                                new SqlParameter("@Title", xlsTitle),
                                new SqlParameter("@Description", xlsDesc),
                                new SqlParameter("@Revision", xlsRevision),
                                new SqlParameter("@DrawingType", xlsDrawingType),
                                new SqlParameter("@CreatedBy", CreateBy),
                                new SqlParameter("@ResultMsg", SqlDbType.VarChar, 100), // sp에서 output 설정했을 경우
                                new SqlParameter("RETURN_VALUE",SqlDbType.Int) // sp에서 return 값을 설정했을경우 사용
                    };

                    drawingParm[9].Direction = ParameterDirection.Output;
                    drawingParm[10].Direction = ParameterDirection.ReturnValue;

                    result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddDrawing", drawingParm);
                    string resultMsg = (string)drawingParm[9].Value;
                    AffectedRow = (int)drawingParm[10].Value;

                }

                //#region 7. Update Fail Count/Success Count(ImportHistory Table)
                //Failcnt = Failcnt++;
                //SigmaResultType UpdateImportHistory = new SigmaResultType();
                //int iTotalCnt = drawingdata.Rows.Count;
                //int iSuccessCnt = iTotalCnt - Failcnt;
                //ImportHistory.ImportCategory = "DRAWING";
                //ImportHistory.ImportedFileName = Path.GetFileName(fileUrl).ToString();
                //ImportHistory.ImportedDate = DateTime.Now.ToString();
                //ImportHistory.TotalCount = iTotalCnt;
                //ImportHistory.SuccessCount = iSuccessCnt;
                //ImportHistory.FailCount = Failcnt;
                //ImportHistory.UpdatedBy = "ADMIN";
                //ImportHistory.ImportHistoryId = importid;
                //UpdateImportHistory = HistoryMgr.UpdateImportHistory(ImportHistory);
                //#endregion

                ////ConvertExcel file && CSV file
                //Export2Excel.ConvertExcelfromData(ErrDataTable, importid.ToString());
                //Export2Excel.ConvertCSVFile(ErrDataTable, importid.ToString());

            // Import 후에 Reference Drawing 처리 한다. Transaction 독립 --> 변경 할 것.. Import 진행 중 모든 Reference Data 성립하는지 검증해 Miss Match 처리
            // [3] Save ReferenceDrawing Info
            drawingdata = ImportHelper.ImportWorkSheet(fileUrl, true, false, "");

            DrawingMgr drawingmgr = new DrawingMgr();
            SigmaResultType refresult = new SigmaResultType();

            foreach (DataRow drow in drawingdata.Rows)
            {
                //string xlsDrawingno = drow["Drawing Number"].ToString();
                //string xlsRevision = drow["Revision"].ToString();
                //string xlsRefDrawingno = drow["Reference Drawings"].ToString();
                //string xlsDetailDrawingno = drow["Detailed Drawings"].ToString();

                string xlsDrawingno = drow["*Drawing Number"].ToString();
                string xlsRevision = drow["*Revision"].ToString();
                string xlsRefDrawingno = drow["Reference Drawings"].ToString();
                string xlsDetailDrawingno = drow["Detailed Drawings"].ToString();

                // * RefDrawingNo는 하나의 컬럼에 ,로 구분지어 여러건 입력 될 수 있다. --> DetailDrawingNo 도 추가 할 것. leebw 02.17

                if (string.IsNullOrEmpty(xlsRefDrawingno))
                    continue;

                string[] arrRefDrawingno = xlsRefDrawingno.Split(',');

                foreach (string refdrawno in arrRefDrawingno)
                {
                    int failrow = 0;

                    refresult = drawingmgr.GetDrawingByNumber(refdrawno.Trim());

                    if (refresult.AffectedRow < 1)
                    {
                        //result.IsSuccessful = false;
                        //result.ErrorMessage = "Incorrect Reference Drawing";
                        //return result;
                        ErrDataTable.Rows.Add(drow.ItemArray);
                        ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Reason"] = " InCorrect Reference Drawing(not found)";
                        Failcnt = Failcnt + 1;
                        failrow = 1;
                    }

                    if (failrow == 1)
                        continue;

                    SqlParameter[] drawingParm = new SqlParameter[] {
                        new SqlParameter("@RefDrawingNo", refdrawno.Trim()),
                        new SqlParameter("@DrawingNo", xlsDrawingno),
                        new SqlParameter("@DetailDrawingNo", xlsDetailDrawingno),
                        new SqlParameter("@Revision", xlsRevision),
                        new SqlParameter("@CreatedBy", CreateBy),
                        new SqlParameter("RETURN_VALUE",SqlDbType.Int) // sp에서 return 값을 설정했을경우 사용
                    };

                    drawingParm[5].Direction = ParameterDirection.ReturnValue;

                    using (scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                    {
                        result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddReferenceDrawing", drawingParm);
                        //int AffectedRow = (int)drawingParm[5].Value;
                        AffectedRow = (int)drawingParm[5].Value;

                        scope.Complete();
                    }
                }

                // * DetailDrawingNo는 하나의 컬럼에 ,로 구분지어 여러건 입력 될 수 있다.
                if (string.IsNullOrEmpty(xlsDetailDrawingno))
                    continue;

                string[] arrDetailDrawingno = xlsDetailDrawingno.Split(',');

                foreach (string detaildrawno in arrDetailDrawingno)
                {
                    int failrow = 0;

                    refresult = drawingmgr.GetDrawingByNumber(detaildrawno.Trim());

                    if (refresult.AffectedRow < 1)
                    {
                        ErrDataTable.Rows.Add(drow.ItemArray);
                        ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Reason"] = " InCorrect Detail Drawing(not found)";
                        Failcnt = Failcnt + 1;
                        failrow = 1;
                    }

                    if (failrow == 1)
                        continue;

                    SqlParameter[] detaildrawingParm = new SqlParameter[] {
                        new SqlParameter("@DetailDrawingNo", detaildrawno.Trim()),
                        new SqlParameter("@DrawingNo", xlsDrawingno),
                        new SqlParameter("@Revision", xlsRevision),
                        new SqlParameter("@CreatedBy", CreateBy),
                        new SqlParameter("RETURN_VALUE",SqlDbType.Int) // sp에서 return 값을 설정했을경우 사용
                    };

                    detaildrawingParm[4].Direction = ParameterDirection.ReturnValue;

                    using (scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                    {
                        result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddDetailDrawing", detaildrawingParm);
                        AffectedRow = (int)detaildrawingParm[4].Value;

                        scope.Complete();
                    }
                }
            }

            #region 7. Update Fail Count/Success Count(ImportHistory Table)
            Failcnt = Failcnt++;
            SigmaResultType UpdateImportHistory = new SigmaResultType();
            int iTotalCnt = drawingdata.Rows.Count;
            int iSuccessCnt = iTotalCnt - Failcnt;
            ImportHistory.ImportCategory = "DRAWING";
            ImportHistory.ImportedFileName = Path.GetFileName(fileUrl).ToString();
            ImportHistory.ImportedDate = DateTime.Now.ToString();
            ImportHistory.TotalCount = iTotalCnt;
            ImportHistory.SuccessCount = iSuccessCnt;
            ImportHistory.FailCount = Failcnt;
            ImportHistory.UpdatedBy = "ADMIN";
            ImportHistory.ImportHistoryId = importid;
            UpdateImportHistory = HistoryMgr.UpdateImportHistory(ImportHistory);
            #endregion

            ////ConvertExcel file && CSV file
            //Export2Excel.ConvertExcelfromData(ErrDataTable, importid.ToString());
            //Export2Excel.ConvertCSVFile(ErrDataTable, importid.ToString());

            //excel file generate for direct call 'export' link
            Export2Excel.ConvertExcelfromData(ErrDataTable, importid.ToString() + fileExtention, exportfilepath);

            //csv file generate for import error list view
            Export2Excel.ConvertCSVFile(ErrDataTable, importid.ToString() + ".csv", exportfilepath);

            DataSet ds = new DataSet();

            ds.Tables.Add("ImportHistory");
            ds.Tables["ImportHistory"].Columns.Add("Id");
            ds.Tables["ImportHistory"].Columns.Add("Total");
            ds.Tables["ImportHistory"].Columns.Add("Success");
            ds.Tables["ImportHistory"].Columns.Add("Fail");

            string[] str = new string[4];

            str[0] = importid.ToString();
            str[1] = iTotalCnt.ToString();
            str[2] = iSuccessCnt.ToString();
            str[3] = Failcnt.ToString();

            ds.Tables["ImportHistory"].Rows.Add(str);

            // Convert to REST/JSON String
            result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
            //result.AffectedRow = (int)ds.Tables[1].Rows[0][0]; // returning count
            //result.ScalarValue = (int)ds.Tables[2].Rows[0][0]; // total count by search
            result.IsSuccessful = true;

            // 엑셀 파일 삭제
            System.IO.File.Delete(fileUrl);

            return result;
        }
Ejemplo n.º 8
0
        public SigmaResultType MultiImportHistory(List<TypeImportHistory> listObj)
        {
            SigmaResultType result = new SigmaResultType();

            try
            {
                ImportHistoryMgr importHistoryMgr = new ImportHistoryMgr();
                result = importHistoryMgr.MultiImportHistory(listObj);
                return result;
            }
            catch (Exception ex)
            {
                // Log Exception
                ExceptionHelper.logException(ex);
                result.IsSuccessful = false;
                result.ErrorMessage = ex.Message;
                return result;
            }
        }
Ejemplo n.º 9
0
        public SigmaResultType ListImportHistory(string ImportCategory)
        {
            SigmaResultType result = new SigmaResultType();
            try
            {
                var queryStr = WebOperationContext.Current.IncomingRequest.UriTemplateMatch.QueryParameters;
                string max = queryStr["max"];
                string offset = queryStr["offset"];
                string s_option = queryStr["s_option"];
                string s_key = queryStr["s_key"];
                string o_option = queryStr["o_option"];
                string o_desc = queryStr["o_desc"];

                ImportHistoryMgr importHistoryMgr = new ImportHistoryMgr();
                result = importHistoryMgr.ListImportHistory(ImportCategory, offset, max, s_option, s_key, o_option, o_desc);
                return result;
            }
            catch (Exception ex)
            {
                ExceptionHelper.logException(ex);
                result.IsSuccessful = false;
                result.ErrorMessage = ex.Message;
                return result;
            }
        }
Ejemplo n.º 10
0
        public SigmaResultType AddDrawing(string fileUrl, string exportfilepath)
        {
            TypeUserInfo userinfo = AuthMgr.GetUserInfo();

            DataTable dt = new DataTable();
            DataTable tmpDt = new DataTable();
            SigmaResultType result = new SigmaResultType();

            dt = Element.Shared.Common.ImportHelper.ImportWorkSheet(fileUrl, true);

            DataTable tmpdt = new DataTable();
            tmpdt = dt.Copy();
            tmpdt.Rows.Clear();
            tmpdt.Columns.Add("Fail reason");

            int failCnt = 0;

            foreach (DataRow r in dt.Rows)
            {
                TypeDrawing obj = new TypeDrawing();
                obj.CWP = r[0].ToString();
                obj.Name = r[1].ToString();
                obj.Revision = r[2].ToString();
                obj.FileName = r[3].ToString();
                obj.Title = r[4].ToString();
                obj.DrawingType = r[5].ToString();
                obj.Description = r[6].ToString();

                if (string.IsNullOrEmpty(GetFailreasonForRequired(r)))
                {
                    SigmaResultType rst = AddDrawing(obj);

                    if (!string.IsNullOrEmpty(rst.ErrorMessage) && rst.ErrorMessage != "Success")
                    {
                        tmpdt.Rows.Add(r.ItemArray);
                        tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = rst.ErrorMessage.ToString();
                        failCnt++;
                    }
                }
                else
                {
                    tmpdt.Rows.Add(r.ItemArray);
                    tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = GetFailreasonForRequired(r);
                    failCnt++;
                }
            }

            TypeImportHistory ImportHistory = new TypeImportHistory();
            ImportHistory.ImportCategory = "DRAWING";
            ImportHistory.ImportedFileName = Path.GetFileName(fileUrl).ToString();
            ImportHistory.ImportedDate = DateTime.Now.ToString();
            ImportHistory.TotalCount = dt.Rows.Count;
            ImportHistory.SuccessCount = dt.Rows.Count - failCnt;
            ImportHistory.FailCount = failCnt;
            ImportHistory.CreatedBy = userinfo.SigmaUserId;
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();
            result = HistoryMgr.AddImportHistory(ImportHistory);

            //if exists error list
            if (tmpdt.Rows.Count > 0)
            {
                if (!System.IO.Directory.Exists(exportfilepath))
                {
                    System.IO.Directory.CreateDirectory(exportfilepath);
                }

                //excel file generate for direct call 'export' link
                Export2Excel.ConvertExcelfromData(tmpdt, result.ScalarValue + Path.GetExtension(fileUrl), exportfilepath);

                //csv file generate for import error list view
                Export2Excel.ConvertCSVFile(tmpdt, result.ScalarValue + ".csv", exportfilepath);

            }

            return result;
        }
Ejemplo n.º 11
0
        public SigmaResultType ImportPersonnelFromExcel(string filePath, string exportfilepath)
        {
            TypeUserInfo userinfo = AuthMgr.GetUserInfo();

            DataTable dt = new DataTable();
            DataTable tmpDt = new DataTable();
            SigmaResultType result = new SigmaResultType();

            dt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, false);

            DataTable tmpdt = new DataTable();
            tmpdt = dt.Copy();
            tmpdt.Rows.Clear();
            tmpdt.Columns.Add("Fail reason");

            int failCnt = 0;

            foreach (DataRow r in dt.Rows)
            {
                TypePersonnel obj = new TypePersonnel();
                obj.EmployeeId = r[0].ToString();
                obj.FirstName = r[1].ToString();
                obj.LastName = r[2].ToString();
                obj.PhoneNumber = r[3].ToString();
                obj.EmailAddress = r[4].ToString();
                obj.RoleName = r[5].ToString();
                obj.NfcCardId = r[6].ToString();
                obj.PinCode = r[7].ToString();
                obj.CompanyName = r[8].ToString();
                obj.CreatedBy = userinfo.SigmaUserId;
                obj.ProjectId = userinfo.CurrentProjectId;

                if (string.IsNullOrEmpty(GetFailreasonForRequiredPersonnel(r)))
                {
                    SigmaResultType rst = AddPersonnelByTemplate(obj);

                    if (rst.ErrorMessage != null)
                    {
                        tmpdt.Rows.Add(r.ItemArray);
                        tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = rst.ErrorMessage.ToString();
                        failCnt++;
                    }
                }
                else
                {
                    tmpdt.Rows.Add(r.ItemArray);
                    tmpdt.Rows[tmpdt.Rows.Count - 1]["Fail reason"] = GetFailreasonForRequiredPersonnel(r);
                    failCnt++;
                }
            }
            TypeImportHistory ImportHistory = new TypeImportHistory();
            ImportHistory.ImportCategory = "HR";
            ImportHistory.ImportedFileName = Path.GetFileName(filePath).ToString();
            ImportHistory.ImportedDate = DateTime.Now.ToString();
            ImportHistory.TotalCount = dt.Rows.Count;
            ImportHistory.SuccessCount = dt.Rows.Count - failCnt;
            ImportHistory.FailCount = failCnt;
            ImportHistory.CreatedBy = userinfo.SigmaUserId;
            ImportHistoryMgr HistoryMgr = new ImportHistoryMgr();
            result = HistoryMgr.AddImportHistory(ImportHistory);

            //if exists error list
            if (tmpdt.Rows.Count > 0)
            {
                if (!System.IO.Directory.Exists(exportfilepath))
                {
                    System.IO.Directory.CreateDirectory(exportfilepath);
                }

                //excel file generate for direct call 'export' link
                Export2Excel.ConvertExcelfromData(tmpdt, result.ScalarValue + Path.GetExtension(filePath), exportfilepath);

                //csv file generate for import error list view
                Export2Excel.ConvertCSVFile(tmpdt, result.ScalarValue + ".csv", exportfilepath);

            }
            return result;
        }