public SigmaResultType AddComponentReferenceDrawing(TypeComponentReferenceDrawing objComponentReferenceDrawing) { SigmaResultType result = new SigmaResultType(); try { ComponentReferenceDrawingMgr componentReferenceDrawingMgr = new ComponentReferenceDrawingMgr(); result = componentReferenceDrawingMgr.AddComponentReferenceDrawing(objComponentReferenceDrawing); return result; } catch (Exception ex) { // Log Exception ExceptionHelper.logException(ex); result.IsSuccessful = false; result.ErrorMessage = ex.Message; return result; } }
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; }