public SigmaResultType AddDrawing(TypeDrawing objDrawingid)
{
SigmaResultType result = new SigmaResultType();
try
{
DrawingMgr drawingMgr = new DrawingMgr();
result = drawingMgr.AddDrawing(objDrawingid);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
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;
}
public SigmaResultType CountOrphanDrawing()
{
SigmaResultType result = new SigmaResultType();
try
{
DrawingMgr drawingMgr = new DrawingMgr();
result = drawingMgr.CountOrphanDrawing();
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
public SigmaResultType MultiDrawing(List<TypeDrawing> listObj)
{
SigmaResultType result = new SigmaResultType();
try
{
DrawingMgr drawingMgr = new DrawingMgr();
result = drawingMgr.MultiDrawing(listObj);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
public SigmaResultType ListRefDrawing(string CWPName)
{
SigmaResultType result = new SigmaResultType();
try
{
DrawingMgr drawingMgr = new DrawingMgr();
result = drawingMgr.ListRefDrawing(CWPName);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
public SigmaResultType ListDrawing()
{
SigmaResultType result = new SigmaResultType();
try
{
var queryStr = WebOperationContext.Current.IncomingRequest.UriTemplateMatch.QueryParameters;
string max = queryStr["max"];
string offset = queryStr["offset"];
//2014.2.14 modified querystring rule
List<string> s_option = new List<string>();
List<string> s_key = new List<string>();
s_option.Add("@DrawingName");
s_option.Add("@FileName");
s_option.Add("@DrawingType");
s_option.Add("@ViewOption");
s_key.Add(queryStr["DrawingName"]);
s_key.Add(queryStr["FileName"]);
s_key.Add(queryStr["DrawingType"]);
s_key.Add(queryStr["ViewOption"]);
string o_option = queryStr["o_option"];
string o_desc = queryStr["o_desc"];
DrawingMgr drawingMgr = new DrawingMgr();
result = drawingMgr.ListDrawing(offset, max, s_option, s_key, o_option, o_desc);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}