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;
}
