public SigmaResultType ImportConsumableLib(string filePath, string exportfilepath) { MaterialMgr materialMgr = new MaterialMgr(); SigmaCodeMgr sigmaCodeMgr = new SigmaCodeMgr(); CommonCodeMgr commonCodeMgr = new CommonCodeMgr(); SigmaResultType sigmaResult = new SigmaResultType(); TypeMaterial typeMaterial = new TypeMaterial(); DataLoaderConsumableLib loader = new DataLoaderConsumableLib(); DataTable Exceldt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, true); DataTable ErrDataTable = SetErrTable(Exceldt); int failCount = 0; int rowCount = Exceldt.Rows.Count; int columnCount = Exceldt.Columns.Count; string codeName = "ALL"; string codeCategory = "UOM"; if (rowCount > 0) { loader = MTOImportHelper.GetDataLoaderConsumableLib(Exceldt); DataSet SigmaCodeDS = sigmaCodeMgr.ListSigmaCodeByCodeCategory(codeName, codeCategory); foreach (DataRow row in Exceldt.Rows) { bool isValidation = true; #region Mandatory Check (*) for (int i = 0; i < columnCount; i++) { if (Exceldt.Columns[i].ToString().Substring(0, 1).ToUpper() == "*" && string.IsNullOrEmpty(row.ItemArray.GetValue(i).ToString())) { ErrDataTable.Rows.Add(row.ItemArray); ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "The value of [" + Exceldt.Columns[i].ToString() + "] is required."; failCount = failCount + 1; isValidation = false; break; } } #endregion Mandatory Check #region Reference Check (SigmaCode Table) DataRow UOM = null; if (isValidation) { UOM = SigmaCodeDS.Tables[0].Select("CodeName = '" + row[loader.Ord_UOM] + "'").FirstOrDefault(); if (UOM == null) { ErrDataTable.Rows.Add(row.ItemArray); ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + row[loader.Ord_UOM].ToString() + "]"; failCount = failCount + 1; isValidation = false; } } #endregion Reference Check #region Duplication Check if (isValidation) { string vendor = row[loader.Ord_Vendor].ToString().Trim(); string description = row[loader.Ord_Description].ToString().Trim(); string partNumber = row[loader.Ord_PartNumber].ToString().Trim(); string uomCode = UOM["Code"].ToString().Trim(); DataSet ConsumableDS = materialMgr.ListMaterialByVendorPartNumber(vendor, description, partNumber, uomCode); if (ConsumableDS.Tables[0].Rows.Count > 0) { ErrDataTable.Rows.Add(row.ItemArray); ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "This data[Consumable] is duplicated."; failCount = failCount + 1; isValidation = false; } } #endregion Duplication Check #region AddMaterial if (isValidation) { typeMaterial.DisciplineCode = ""; typeMaterial.TaskCategoryId = 0; typeMaterial.TaskTypeId = 0; typeMaterial.Manhours = 0; typeMaterial.UomCode = UOM["Code"].ToString().Trim(); typeMaterial.Vendor = row[loader.Ord_Vendor].ToString().Trim(); typeMaterial.Description = row[loader.Ord_Description].ToString().Trim(); typeMaterial.PartNumber = row[loader.Ord_PartNumber].ToString().Trim(); typeMaterial.CostCodeId = 0; typeMaterial.CreatedBy = userinfo.SigmaUserId; sigmaResult = materialMgr.AddMaterial(typeMaterial); if (sigmaResult.IsSuccessful) { // CustomField for (int i = 0; i < columnCount; i++) { if (Exceldt.Columns[i].ToString().Substring(0, 3).ToUpper() == "UD_") { string RowValue = row.ItemArray.GetValue(i).ToString(); sigmaResult.IsSuccessful = CheckMaterialCustomField(Exceldt, Exceldt.Columns[i].ToString().Trim(), sigmaResult.ScalarValue, RowValue); } } } } #endregion AddMaterial } // Set ImportHistory(SuccessCount/FailCount) sigmaResult = AddImportHistory(Exceldt.Rows.Count, failCount, Path.GetFileName(filePath).ToString(), "ConsumableLibrary"); // ConvertExcel file && CSV file Export2Excel.ConvertExcelfromData(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".xlsx", exportfilepath); Export2Excel.ConvertCSVFile(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".csv", exportfilepath); sigmaResult.IsSuccessful = true; } else { sigmaResult.IsSuccessful = false; sigmaResult.ErrorMessage = "no record from file."; } return sigmaResult; }
public SigmaResultType ImportDrawingTypeLib(string filePath, string exportfilepath) { SigmaCodeMgr sigmaCodeMgr = new SigmaCodeMgr(); CommonCodeMgr commonCodeMgr = new CommonCodeMgr(); SigmaResultType sigmaResult = new SigmaResultType(); TypeSigmaCode typeSigmaCode = new TypeSigmaCode(); DataLoaderDrawingType loader = new DataLoaderDrawingType(); DataTable Exceldt = Element.Shared.Common.ImportHelper.ImportWorkSheet(filePath, true); DataTable ErrDataTable = SetErrTable(Exceldt); int failCount = 0; int rowCount = Exceldt.Rows.Count; int columnCount = Exceldt.Columns.Count; if (rowCount > 0) { loader = MTOImportHelper.GetDataLoaderDrawingType(Exceldt); string codeCategory = "DRAWING_TYPE"; foreach (DataRow row in Exceldt.Rows) { bool isValidation = true; #region Mandatory Check (*) for (int i = 0; i < columnCount; i++) { if (Exceldt.Columns[i].ToString().Substring(0, 1).ToUpper() == "*" && string.IsNullOrEmpty(row.ItemArray.GetValue(i).ToString())) { ErrDataTable.Rows.Add(row.ItemArray); ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "The value of [" + Exceldt.Columns[i].ToString() + "] is required."; failCount = failCount + 1; isValidation = false; break; } } #endregion Mandatory Check #region Reference Check (SigmaCode Table) //DataRow DrawingType = null; //if (isValidation) //{ // DrawingType = SigmaCodeDT.Select("CodeName = '" + row[loader.Ord_DrawingType] + "'").FirstOrDefault(); // if (DrawingType == null) // { // ErrDataTable.Rows.Add(row.ItemArray); // ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "There is no item in the library to match up with the value of [" + row[loader.Ord_DrawingType].ToString() + "]"; // failCount = failCount + 1; // isValidation = false; // } //} #endregion Reference Check #region Duplication Check if (isValidation) { string codeName = row[loader.Ord_DrawingType].ToString(); DataSet SigmaCodeDS = sigmaCodeMgr.ListSigmaCodeByCodeCategory(codeName, codeCategory); if (SigmaCodeDS.Tables[0].Rows.Count > 0) { ErrDataTable.Rows.Add(row.ItemArray); ErrDataTable.Rows[ErrDataTable.Rows.Count - 1]["Fail Reason"] = "This data[Drawing Type] is duplicated."; failCount = failCount + 1; isValidation = false; } } #endregion Duplication Check #region AddSigmaCode if (isValidation) { typeSigmaCode.Code = ""; typeSigmaCode.CodeCategory = codeCategory; typeSigmaCode.CodeName = row[loader.Ord_DrawingType].ToString().Trim(); typeSigmaCode.CodeShortName = row[loader.Ord_DrawingType].ToString().Trim(); typeSigmaCode.RefChar = ""; typeSigmaCode.RefNo = ""; typeSigmaCode.Description = row[loader.Ord_Description].ToString(); typeSigmaCode.IsActive = "Y"; typeSigmaCode.SortOrder = ""; typeSigmaCode.SigmaOperation = "C"; typeSigmaCode.CreatedBy = userinfo.SigmaUserId; sigmaResult = sigmaCodeMgr.AddSigmaCode(typeSigmaCode); } #endregion AddSigmaCode } // Set ImportHistory(SuccessCount/FailCount) sigmaResult = AddImportHistory(Exceldt.Rows.Count, failCount, Path.GetFileName(filePath).ToString(), "DrawingType"); // ConvertExcel file && CSV file Export2Excel.ConvertExcelfromData(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".xlsx", exportfilepath); Export2Excel.ConvertCSVFile(ErrDataTable, sigmaResult.ScalarValue.ToString() + ".csv", exportfilepath); sigmaResult.IsSuccessful = true; } else { sigmaResult.IsSuccessful = false; sigmaResult.ErrorMessage = "no record from file."; } return sigmaResult; }
private bool CheckEquipmentCustomField(DataTable Exceldt, string ColName, int EquipmentId, string CustomFieldValue) { CommonCodeMgr common = new CommonCodeMgr(); CustomFieldMgr customFieldMgr = new CustomFieldMgr(); EquipmentMgr equipmentMgr = new EquipmentMgr(); MaterialCustomFieldMgr materialCustomFieldMgr = new MaterialCustomFieldMgr(); TypeCustomField customField = new TypeCustomField(); TypeEquipmentCustomField equipmentCustomField = new TypeEquipmentCustomField(); SigmaResultType mResult = new SigmaResultType(); SigmaResultType cResult = new SigmaResultType(); DataSet CustomFiledSD = materialCustomFieldMgr.ListMaterialCustomFieldByFieldName(ColName); DataRow CustomFiledSR = CustomFiledSD.Tables[0].Rows[0]; if (CustomFiledSD.Tables[0].Rows.Count > 0) { equipmentCustomField.EquipmentId = EquipmentId; equipmentCustomField.CustomFieldId = Convert.ToInt32(CustomFiledSR["CustomFieldId"].ToString()); equipmentCustomField.Value = CustomFieldValue; equipmentCustomField.CreatedBy = userinfo.SigmaUserId; mResult = equipmentMgr.AddEquipmentCustomField(equipmentCustomField); } else { customField.FieldName = ColName; customField.IsDisplayable = "Y"; customField.CreatedBy = userinfo.SigmaUserId; cResult = customFieldMgr.AddCustomField(customField); if (cResult.IsSuccessful) { equipmentCustomField.EquipmentId = EquipmentId; equipmentCustomField.CustomFieldId = cResult.ScalarValue; equipmentCustomField.Value = CustomFieldValue; equipmentCustomField.CreatedBy = userinfo.SigmaUserId; mResult = equipmentMgr.AddEquipmentCustomField(equipmentCustomField); } } return mResult.IsSuccessful; }
private bool CheckMaterialCustomField(DataTable Exceldt, string ColName, int MaterialId, string CustomFieldValue) { CommonCodeMgr common = new CommonCodeMgr(); CustomFieldMgr customFieldMgr = new CustomFieldMgr(); MaterialMgr materialMgr = new MaterialMgr(); MaterialCustomFieldMgr materialCustomFieldMgr = new MaterialCustomFieldMgr(); TypeCustomField customField = new TypeCustomField(); TypeMaterialCustomField materialCustomField = new TypeMaterialCustomField(); SigmaResultType mResult = new SigmaResultType(); SigmaResultType cResult = new SigmaResultType(); DataSet CustomFiledSD = materialCustomFieldMgr.ListMaterialCustomFieldByFieldName(ColName); if (CustomFiledSD.Tables[0].Rows.Count > 0) // CustomField Table 동일 FieldName이 있으면 MaterialCustomField Table 입력 { DataRow CustomFiledSR = CustomFiledSD.Tables[0].Rows[0]; materialCustomField.MaterialId = MaterialId; materialCustomField.CustomFieldId = Convert.ToInt32(CustomFiledSR["CustomFieldId"].ToString()); materialCustomField.Value = CustomFieldValue; materialCustomField.CreatedBy = userinfo.SigmaUserId; mResult = materialMgr.AddMaterialCustomField(materialCustomField); } else // CustomField Table & MaterialCustomField Table 에 입력 { customField.FieldName = ColName; customField.IsDisplayable = "Y"; customField.CreatedBy = userinfo.SigmaUserId; cResult = customFieldMgr.AddCustomField(customField); if (cResult.IsSuccessful) { materialCustomField.MaterialId = MaterialId; materialCustomField.CustomFieldId = cResult.ScalarValue; materialCustomField.Value = CustomFieldValue; materialCustomField.CreatedBy = userinfo.SigmaUserId; mResult = materialMgr.AddMaterialCustomField(materialCustomField); } } return mResult.IsSuccessful; }
/// <summary> /// Excel File 항목중에서 "UD-" 시작하는 이름은 CustomField Table 에서 유무확인후 ComponentCustomField Table에 입력 /// </summary> /// <returns></returns> private bool CheckCustomField(DataTable Exceldt, string ColName, int ComponentId, string CustomFieldValue) { bool bResult = false; CommonCodeMgr common = new CommonCodeMgr(); TypeCustomField Typecf = new TypeCustomField(); CustomFieldMgr CustFieldMgr = new CustomFieldMgr(); TypeComponentCustomField Typeccf = new TypeComponentCustomField(); ComponentCustomFieldMgr ccfMgr = new ComponentCustomFieldMgr(); string CustomFieldWhere = string.Empty; DataRow[] cfRow = null; CustomFieldWhere = "WHERE FieldName like '" + ColName + "'"; cfRow = common.GetCommonCode("CustomFieldId, FieldName", "CustomField", CustomFieldWhere).Select(); if (cfRow.Length > 0)//CustomField Table에 있으면 ComponentCustomField Table에만 입력 { Typeccf.ComponentId = ComponentId; Typeccf.CustomFieldId = Convert.ToInt32(cfRow[0][0]); Typeccf.Value = CustomFieldValue; Typeccf.CreatedBy = userinfo.SigmaUserId; bResult = ccfMgr.AddComponentCustomField(Typeccf).IsSuccessful; } else //CustomField Table & ComponentCustomField Table 에 입력 { Typecf.FieldType = "STRING"; Typecf.FieldName = ColName; Typecf.IsDisplayable = "Y"; Typecf.CreatedBy = userinfo.SigmaUserId; bResult = CustFieldMgr.AddCustomField(Typecf).IsSuccessful; if (bResult == true) { int CustomFieldId = Convert.ToInt32(CustFieldMgr.AddCustomField(Typecf).ScalarValue.ToString()); Typeccf.ComponentId = ComponentId; Typeccf.CustomFieldId = CustomFieldId; Typeccf.Value = CustomFieldValue; Typeccf.CreatedBy = userinfo.SigmaUserId; bResult = ccfMgr.AddComponentCustomField(Typeccf).IsSuccessful; } } return bResult; }
public SigmaResultType UpdateMTO(TypeComponent objComponent, TypeMaterial objMaterial) { SigmaResultType sResult = new SigmaResultType(); CommonCodeMgr common = new CommonCodeMgr(); ComponentMgr comMgr = new ComponentMgr(); ComponentProgressMgr comproMgr = new ComponentProgressMgr(); CommonCodeMgr commonMgr = new CommonCodeMgr(); DataRow[] StepRow = null; decimal ComQty = objComponent.Qty; string MultiQty = "N"; decimal EstimatedManhour = 0; List<TypeComponentProgress> listComponentProgress = new List<TypeComponentProgress>(); //Get Material Table string MtrWhere = "where DisciplineCode = 'DISCIPLINE_CIVIL'"; DataTable MtrDt = common.GetCommonCode("*", "Material", MtrWhere); //Check Exist material name DataRow[] MtrRow = MtrDt.Select("TaskCategoryId = '" + objMaterial.TaskCategoryId + "' " + "AND TaskTypeId = '" + objMaterial.TaskTypeId + "' " + " AND Description = '" + objMaterial.Description + "'"); //StepRow의 Data 없는 경우 [progressStep Table 에서 ProgressStepId = 13|| Name = default progress의 Weight 값으로 처리] DataRow[] defaultStepRow = new DataTable().Select();//common.GetCommonCode("*", "ProgressStep", " Where Name = 'Default work'").Select(); //Check Material if (MtrRow.Length > 0) { objComponent.MaterialId = int.Parse(MtrRow[0][0].ToString()); StepRow = commonMgr.GetProgressStepByTaskCategoryTaskType(objMaterial.TaskCategoryId, objMaterial.TaskTypeId).Select(); 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); } objComponent.EstimatedManhour = EstimatedManhour; sResult = comMgr.AddComponetInfo(objComponent); } if (sResult.IsSuccessful) { if (MtrRow.Length > 0) { listComponentProgress.ForEach(x => x.ComponentId = objComponent.ComponentId); listComponentProgress.ForEach(x => x.IsCompleted = "N"); listComponentProgress.ForEach(x => x.AmountInstalled = 0); listComponentProgress.ForEach(x => x.SigmaOperation = "C"); sResult = comproMgr.MultiComponentProgress(listComponentProgress); } else { sResult.IsSuccessful = false; sResult.StringValue = "The Data of Material do not Exist!!"; } } else { sResult.IsSuccessful = false; sResult.StringValue = "Add ComponetInfo Failed!!"; } return sResult; }
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; }
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; }
/// <summary> /// 2014-02-28 /// ReadWBS & Set /// </summary> /// <param name="projectObjectId">projectId</param> /// /// <param name="Url">Url</param> /// <param name="userName">userName</param> /// <param name="password">password</param> public SigmaResultType ReadP6WBSManager(int p6projectId) { SigmaResultType Result = new SigmaResultType(); CommonCodeMgr commonMgr = new CommonCodeMgr(); string p6username, p6password, p6login; p6login = System.Configuration.ConfigurationManager.AppSettings["P6Login"].ToString(); p6username = p6login.Split(';')[0].Split('=')[1].Trim(); p6password = p6login.Split(';')[1].Split('=')[1].Trim(); P6WS.WBSService.WBSPortBinding wbsp = new P6WS.WBSService.WBSPortBinding(); //wbsp.Url = ConfigurationManager.AppSettings[P6WS_SERVICES_WBS_SERVICE].ToString();//임시루 wbsp.Url = System.Configuration.ConfigurationManager.AppSettings["P6WS.WBSService"]; P6WS.WBSService.ReadWBS rwbs = new P6WS.WBSService.ReadWBS(); wbsp.CookieContainer = P6Login(p6username, p6password); P6WS.WBSService.WBSFieldType[] wbsFields = new P6WS.WBSService.WBSFieldType[17]; P6ProjectCombo(p6username, p6password); // 이 구문이 실행되면 P6DT에 값이 채워짐. projectRow = P6DT.Select("ProjectObjectId ='" + p6projectId.ToString() + "'"); #region set p6 wbsFields[0] = P6WS.WBSService.WBSFieldType.ObjectId; wbsFields[2] = P6WS.WBSService.WBSFieldType.Name; wbsFields[3] = P6WS.WBSService.WBSFieldType.ProjectId; wbsFields[4] = P6WS.WBSService.WBSFieldType.ProjectObjectId; wbsFields[5] = P6WS.WBSService.WBSFieldType.SummaryRemainingDuration; wbsFields[6] = P6WS.WBSService.WBSFieldType.StartDate; wbsFields[7] = P6WS.WBSService.WBSFieldType.FinishDate; wbsFields[8] = P6WS.WBSService.WBSFieldType.ObjectId; wbsFields[9] = P6WS.WBSService.WBSFieldType.Code; wbsFields[10] = P6WS.WBSService.WBSFieldType.Name; wbsFields[11] = P6WS.WBSService.WBSFieldType.SequenceNumber; wbsFields[12] = P6WS.WBSService.WBSFieldType.Code; wbsFields[13] = P6WS.WBSService.WBSFieldType.ParentObjectId; rwbs.Field = wbsFields; rwbs.Filter = "ProjectObjectId='" + p6projectId + "'"; P6WS.WBSService.WBS[] wbs = wbsp.ReadWBS(rwbs); #endregion if (wbs != null && wbs.Length > 0) { for (int i = 0; i < wbs.Length; i++) { TypeExternalSchedule TypeExSchedule = new TypeExternalSchedule(); TypeScheduledWorkItem TypeSWI = new TypeScheduledWorkItem(); ExternalScheduleMgr ExSchMgr = new ExternalScheduleMgr(); ScheduledWorkItemMgr SchMgr = new ScheduledWorkItemMgr(); if (wbs[i].Name != "WBSFIWP" && wbs[i].Code != "FIWP") // 뭐냐 이건... { #region set Level = 3 // Set TypeExternalSchedule TypeExSchedule.Level = "3"; TypeExSchedule.StartDate = wbs[i].StartDate.ToString(); TypeExSchedule.EndDate = wbs[i].FinishDate.ToString(); TypeExSchedule.ProjectObjectId = p6projectId; TypeExSchedule.ParentObjectId = 0; TypeExSchedule.OriginalDuration = Convert.ToInt32(wbs[i].SummaryRemainingDuration); TypeExSchedule.RemainingDuration = Convert.ToInt32(wbs[i].SummaryRemainingDuration); TypeExSchedule.ActivityObjectId = wbs[i].ObjectId; TypeExSchedule.ExternalProjectName = projectRow[0]["P6ProjectName"].ToString(); TypeExSchedule.CalendarId = 0; TypeExSchedule.CreatedBy = userinfo.SigmaUserId; TypeExSchedule.ProjectId = userinfo.CurrentProjectId; Result = ExSchMgr.AddExternalSchedule(TypeExSchedule); // Set TypeScheduledWorkItem TypeSWI.ExternalScheduleId = Result.ScalarValue; TypeSWI.CwpId = null; TypeSWI.ScheduleName = wbs[i].Name; TypeSWI.StartDate = wbs[i].StartDate.ToString(); TypeSWI.EndDate = wbs[i].FinishDate.ToString(); TypeSWI.CrewMemebersAssigned = 0; TypeSWI.TotalWorkingHours = 0; TypeSWI.LeaderId = "0"; TypeSWI.CreatedBy = userinfo.SigmaUserId; Result = SchMgr.AddScheduledWorkItem(TypeSWI); #endregion //Level 4 로 Input ReadActivities(TypeExSchedule.ActivityObjectId, p6projectId, p6username, p6password); } } } return Result; }