public SigmaResultType AddEquipment(TypeEquipment objEquipment)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
SigmaResultType result = new SigmaResultType();
TransactionScope scope = null;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@EquipmentCodeMain", objEquipment.EquipmentCodeMain.Trim()));
paramList.Add(new SqlParameter("@EquipmentCodeSub", objEquipment.EquipmentCodeSub.Trim()));
paramList.Add(new SqlParameter("@Description", objEquipment.Description.Trim()));
paramList.Add(new SqlParameter("@ThirdLevel", objEquipment.ThirdLevel.Trim()));
paramList.Add(new SqlParameter("@Spec", objEquipment.Spec.Trim()));
paramList.Add(new SqlParameter("@EquipmentType", objEquipment.EquipmentType.Trim()));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
paramList.Add(new SqlParameter("@ModelNumber", objEquipment.ModelNumber.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddEquipment", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType AddCWA(TypeCWA objCWA)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ProjectId", Utilities.ToInt32(userinfo.CurrentProjectId.ToString().Trim())));
paramList.Add(new SqlParameter("@Name", objCWA.Name.Trim()));
paramList.Add(new SqlParameter("@Area", objCWA.Area));
paramList.Add(new SqlParameter("@Description", objCWA.Description.Trim()));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddCWA", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
//public SigmaResultType MultiMessageBox(List<TypeMessageBox> listObj)
//{
// TransactionScope scope = null;
// SigmaResultType result = new SigmaResultType();
// // Get connection string
// string connStr = ConnStrHelper.getDbConnString();
// using (scope = new TransactionScope(TransactionScopeOption.RequiresNew))
// {
// foreach (TypeMessageBox anObj in listObj)
// {
// switch (anObj.SigmaOperation)
// {
// case "C":
// AddMessageBox(anObj);
// break;
// case "U":
// UpdateMessageBox(anObj);
// break;
// case "D":
// RemoveMessageBox(anObj);
// break;
// }
// }
// scope.Complete();
// }
// result.IsSuccessful = true;
// return result;
//}
public SigmaResultType ListMessageBoxBySigmaUserId()
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ProjectId", userinfo.CurrentProjectId));
paramList.Add(new SqlParameter("@SigmaUserId", userinfo.SigmaUserId.Trim()));
// Get Data
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_ListMessageBoxBySigmaUserId", paramList.ToArray());
// Convert to REST/JSON String
result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
//result.AffectedRow = 1;
result.IsSuccessful = true;
// return
return result;
}
public SigmaResultType AddScheduledWorkItem(TypeScheduledWorkItem objScheduledWorkItem)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ExternalScheduleId", objScheduledWorkItem.ExternalScheduleId));
paramList.Add(new SqlParameter("@CwpId", objScheduledWorkItem.CwpId));
paramList.Add(new SqlParameter("@ScheduleName", objScheduledWorkItem.ScheduleName));
paramList.Add(new SqlParameter("@StartDate", objScheduledWorkItem.StartDate));
paramList.Add(new SqlParameter("@EndDate", objScheduledWorkItem.EndDate));
paramList.Add(new SqlParameter("@CrewMemebersAssigned", objScheduledWorkItem.CrewMemebersAssigned));
paramList.Add(new SqlParameter("@TotalWorkingHours", objScheduledWorkItem.TotalWorkingHours));
paramList.Add(new SqlParameter("@LeaderId", objScheduledWorkItem.LeaderId));
paramList.Add(new SqlParameter("@CreatedBy", objScheduledWorkItem.CreatedBy));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddScheduledWorkItem", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType AddFileStore(TypeFileStore objFileStore, string targetPath)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
//objFileStore.CompanyId = userinfo.CompanyId.ToString();
objFileStore.CreatedBy = userinfo.SigmaUserId;
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ProjectId", userinfo.CurrentProjectId));
paramList.Add(new SqlParameter("@FileTitle", objFileStore.FileTitle));
paramList.Add(new SqlParameter("@FileDescription", objFileStore.FileDescription));
paramList.Add(new SqlParameter("@FileCategory", objFileStore.FileCategory));
paramList.Add(new SqlParameter("@FileTypeCode", objFileStore.FileTypeCode));
paramList.Add(new SqlParameter("@CreatedBy", objFileStore.CreatedBy));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddFileStore", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
//scope.Complete();
if (objFileStore.UploadedFileInfo.UploadedFileInfoId == 0)
{
//FileInfo fileinfo = new FileInfo(targetPath + objFileStore.UploadedFileInfo.Path);
//objFileStore.UploadedFileInfo.Name = Path.GetFileNameWithoutExtension(targetPath + objFileStore.UploadedFileInfo.Path);
//objFileStore.UploadedFileInfo.Size = (int)fileinfo.Length;
//objFileStore.UploadedFileInfo.FileExtension = fileinfo.Extension;//Path.GetExtension(objFileStore.UploadedFileInfo.Path);
//objFileStore.UploadedFileInfo.FileStoreId = (int)outParam.Value;
//-----------------------------------------------------------------------------------------------------
//--------------- IE - The given path's format is not supported. error 해결 -------------------------
string filename = Path.GetFileNameWithoutExtension(targetPath + objFileStore.UploadedFileInfo.Path);
//string filename = System.IO.Path.GetFileName(targetPath + objFileStore.UploadedFileInfo.Path);
FileInfo fileinfo = new FileInfo(targetPath + objFileStore.UploadedFileInfo.Path);
string fileExtention = Path.GetExtension(targetPath + objFileStore.UploadedFileInfo.Path);
//-----------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------
objFileStore.UploadedFileInfo.Name = filename;
objFileStore.UploadedFileInfo.Size = (int)fileinfo.Length;
objFileStore.UploadedFileInfo.FileExtension = fileExtention;
objFileStore.UploadedFileInfo.FileStoreId = (int)outParam.Value;
UploadedFileInfoMgr uploadedFileInfoMgr = new UploadedFileInfoMgr();
uploadedFileInfoMgr.AddUploadedFileInfo(objFileStore.UploadedFileInfo);
}
scope.Complete();
}
return result;
}
public SigmaResultType AddComponentCustomField(TypeComponentCustomField objComponentCustomField)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ComponentId", objComponentCustomField.ComponentId));
paramList.Add(new SqlParameter("@CustomFieldId", objComponentCustomField.CustomFieldId));
paramList.Add(new SqlParameter("@Value", objComponentCustomField.Value));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId));
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddComponentCustomField", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = 1; //ComponentCustomField table에는 PK가 없기에 @NewId 필요 없음
scope.Complete();
}
return result;
}
public SigmaResultType AddCompany(TypeCompany objCompany)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@Name", objCompany.Name));
paramList.Add(new SqlParameter("@IsClient", objCompany.IsClient));
paramList.Add(new SqlParameter("@Address", objCompany.Address));
paramList.Add(new SqlParameter("@ContactName", objCompany.ContactName));
paramList.Add(new SqlParameter("@ContactPhone", objCompany.ContactPhone));
paramList.Add(new SqlParameter("@ContactFax", objCompany.ContactFax));
paramList.Add(new SqlParameter("@ContactEmail", objCompany.ContactEmail));
paramList.Add(new SqlParameter("@ContractTypeCode", objCompany.ContractTypeCode));
paramList.Add(new SqlParameter("@CompanyTypeCode", objCompany.CompanyTypeCode));
paramList.Add(new SqlParameter("@LogoFilePath", objCompany.LogoFilePath));
paramList.Add(new SqlParameter("@CreatedBy", objCompany.CreatedBy));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddCompany", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType ListConsumable(string offset, string max, string s_option, string s_key, string o_option, string o_desc)
{
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@MaxNumRows", (max == null ? 1000 : int.Parse(max))));
parameters.Add(new SqlParameter("@RetrieveOffset", (offset == null ? 0 : int.Parse(offset))));
parameters.Add(new SqlParameter("@SortColumn", o_option));
parameters.Add(new SqlParameter("@SortOrder", (o_desc != null ? o_desc.ToUpper() : null)));
// Get Data
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_ListConsumable", parameters.ToArray());
// 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;
// return
return result;
}
//public SigmaResultType GetTaskCategory(string taskCategoryId)
//{
// SigmaResultType result = new SigmaResultType();
// // Get connection string
// string connStr = ConnStrHelper.getDbConnString();
// // Compose parameters
// SqlParameter[] parameters = new SqlParameter[] {
// new SqlParameter("@TaskCategoryId", taskCategoryId)
// };
// // Get Data
// DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetTaskCategory", parameters);
// // Convert to REST/JSON String
// result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
// result.AffectedRow = 1;
// result.IsSuccessful = true;
// // return
// return result;
//}
//public SigmaResultType ListTaskCategory(string offset, string max, string s_option, string s_key, string o_option, string o_desc)
//{
// SigmaResultType result = new SigmaResultType();
// // Get connection string
// string connStr = ConnStrHelper.getDbConnString();
// // Compose parameters
// List<SqlParameter> parameters = new List<SqlParameter>();
// parameters.Add(new SqlParameter("@MaxNumRows", (max == null ? 1000 : int.Parse(max))));
// parameters.Add(new SqlParameter("@RetrieveOffset", (offset == null ? 0 : int.Parse(offset))));
// parameters.Add(new SqlParameter("@SortColumn", o_option));
// parameters.Add(new SqlParameter("@SortOrder", (o_desc != null ? o_desc.ToUpper() : null)));
// // Get Data
// DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_ListTaskCategory", parameters.ToArray());
// // 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;
// // return
// return result;
//}
public SigmaResultType AddTaskCategory(TypeTaskCategory objTaskCategory)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get TaskCode
objTaskCategory.TaskCategoryCode = GetTaskCode(objTaskCategory.DisciplineCode, objTaskCategory.TaskCategoryName);
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@DisciplineCode", objTaskCategory.DisciplineCode.Trim()));
paramList.Add(new SqlParameter("@TaskCategoryCode", objTaskCategory.TaskCategoryCode.Trim()));
paramList.Add(new SqlParameter("@TaskCategoryName", objTaskCategory.TaskCategoryName.Trim()));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddTaskCategory", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType SendMail(TypeSigmaUser objSigmaUser)
{
SigmaResultType result = new SigmaResultType();
try
{
MailMessage mail = new MailMessage();
mail.From = new MailAddress("*****@*****.**", "Administrator", System.Text.Encoding.UTF8);
mail.To.Add(objSigmaUser.Email);
mail.IsBodyHtml = true;
mail.Subject = "Element Sigma Login confirmation";
mail.Body = GetMailMessage(objSigmaUser);
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.SubjectEncoding = System.Text.Encoding.UTF8;
//SmtpClient scClient = new SmtpClient("127.0.0.1", 587);
SmtpClient scClient = new SmtpClient("127.0.0.1", 25);
//scClient.EnableSsl = true;
scClient.DeliveryMethod = SmtpDeliveryMethod.Network;
//scClient.Credentials = new System.Net.NetworkCredential("*****@*****.**", "P@ssw0rd!1");
scClient.Send(mail);
mail.Dispose();
}
catch
{
//throw new Exception("Invalid Email Address");
}
return result;
}
public SigmaResultType AddScheduledWorkItem(TypeScheduledWorkItem objScheduledWorkItem)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ExternalScheduleId", objScheduledWorkItem.ExternalScheduleId),
new SqlParameter("@CwpId", objScheduledWorkItem.CwpId),
new SqlParameter("@ScheduleName", objScheduledWorkItem.ScheduleName),
new SqlParameter("@StartDate", objScheduledWorkItem.StartDate),
new SqlParameter("@EndDate", objScheduledWorkItem.EndDate),
new SqlParameter("@CrewMemebersAssigned", objScheduledWorkItem.CrewMemebersAssigned),
new SqlParameter("@TotalWorkingHours", objScheduledWorkItem.TotalWorkingHours),
new SqlParameter("@LeaderId", objScheduledWorkItem.LeaderId),
new SqlParameter("@CreatedBy", objScheduledWorkItem.CreatedBy),
};
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, "usp_AddScheduledWorkItem", parameters);
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType BatchProcessScheduledWorkItem(List<TypeScheduledWorkItem> objList)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
using (scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
foreach (TypeScheduledWorkItem anObj in objList)
{
switch (anObj.SigmaOperation)
{
case "C":
AddScheduledWorkItem(anObj);
break;
case "U":
UpdateScheduledWorkItem(anObj);
break;
case "D":
RemoveScheduledWorkItem(anObj.ScheduledWorkItemId);
break;
}
}
scope.Complete();
}
return result;
}
public SigmaResultType AddProjectUserDiscipline(TypeProjectUserDiscipline objProjectUserDiscipline)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objProjectUserDiscipline.ProjectId = userinfo.CurrentProjectId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ProjectId", objProjectUserDiscipline.ProjectId));
paramList.Add(new SqlParameter("@SigmaUserId", objProjectUserDiscipline.SigmaUserId));
paramList.Add(new SqlParameter("@DisciplineCode", objProjectUserDiscipline.DisciplineCode));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddProjectUserDiscipline", paramList.ToArray());
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType AddSigmaLog(TypeSigmaLog objSigmaLog)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@Date", objSigmaLog.Date));
paramList.Add(new SqlParameter("@Thread", objSigmaLog.Thread));
paramList.Add(new SqlParameter("@Level", objSigmaLog.Level));
paramList.Add(new SqlParameter("@Logger", objSigmaLog.Logger));
paramList.Add(new SqlParameter("@Message", objSigmaLog.Message));
paramList.Add(new SqlParameter("@Exception", objSigmaLog.Exception));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddSigmaLog", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType AddSigmaJob(TypeSigmaJob objSigmaJob)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@SigmaJobName", objSigmaJob.SigmaJobName));
paramList.Add(new SqlParameter("@JobCategoryCode", objSigmaJob.JobCategoryCode));
paramList.Add(new SqlParameter("@CreatedBy", objSigmaJob.CreatedBy));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddSigmaJob", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType AddSigmaUserSigmaRole(TypeSigmaUserSigmaRole objSigmaUserSigmaRole)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
objSigmaUserSigmaRole.CreatedBy = userinfo.SigmaUserId;
//objSigmaUserSigmaRole.ProjectId = userinfo.CurrentProjectId;
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@SigmaRoleId", objSigmaUserSigmaRole.SigmaRoleId));
paramList.Add(new SqlParameter("@SigmaUserId", objSigmaUserSigmaRole.SigmaUserId));
paramList.Add(new SqlParameter("@ReportTo", objSigmaUserSigmaRole.ReportTo));
paramList.Add(new SqlParameter("@ReportToRole", objSigmaUserSigmaRole.ReportToRole));
paramList.Add(new SqlParameter("@IsDefault", objSigmaUserSigmaRole.IsDefault));
paramList.Add(new SqlParameter("@ProjectId", objSigmaUserSigmaRole.ProjectId));
paramList.Add(new SqlParameter("@CreatedBy", objSigmaUserSigmaRole.CreatedBy));
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddSigmaUserSigmaRole", paramList.ToArray());
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
/// <summary>
/// P6 관련
/// </summary>
/// <param name="objExternalSchedule"></param>
/// <returns></returns>
public SigmaResultType AddExternalSchedule(TypeExternalSchedule objExternalSchedule)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@Level", objExternalSchedule.Level));
paramList.Add(new SqlParameter("@StartDate", objExternalSchedule.StartDate));
paramList.Add(new SqlParameter("@EndDate", objExternalSchedule.EndDate));
paramList.Add(new SqlParameter("@OriginalDuration", objExternalSchedule.OriginalDuration));
paramList.Add(new SqlParameter("@ProjectObjectId", objExternalSchedule.ProjectObjectId));
paramList.Add(new SqlParameter("@ActivityObjectId", objExternalSchedule.ActivityObjectId));
paramList.Add(new SqlParameter("@ParentObjectId", objExternalSchedule.ParentObjectId));
paramList.Add(new SqlParameter("@RemainingDuration", objExternalSchedule.RemainingDuration));
paramList.Add(new SqlParameter("@ExternalProjectName", objExternalSchedule.ExternalProjectName));
paramList.Add(new SqlParameter("@CalendarId", objExternalSchedule.CalendarId));
paramList.Add(new SqlParameter("@CreatedBy", AuthMgr.GetUserInfo().SigmaUserId));
paramList.Add(new SqlParameter("@ProjectId", AuthMgr.GetUserInfo().CurrentProjectId));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddExternalSchedule", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
public SigmaResultType ListAssignmentComponentProgress(string offset, string max, List<string> s_option, List<string> s_key, string o_option, string o_desc)
{
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
// Compose parameters
List<SqlParameter> parameters = new List<SqlParameter>();
for (int i = 0; i < s_option.Count; i++)
{
parameters.Add(new SqlParameter(s_option[i], s_key[i]));
}
parameters.Add(new SqlParameter("@MaxNumRows", (max == null ? 1000 : int.Parse(max))));
parameters.Add(new SqlParameter("@RetrieveOffset", (offset == null ? 0 : int.Parse(offset))));
parameters.Add(new SqlParameter("@SortColumn", o_option));
parameters.Add(new SqlParameter("@SortOrder", (o_desc != null ? o_desc.ToUpper() : null)));
parameters.Add(new SqlParameter("@ProjectId", AuthMgr.GetUserInfo().CurrentProjectId));
// Get Data
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_ListComponentProgress", parameters.ToArray());
// 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;
// return
return result;
}
public SigmaResultType AddCustomFieldWithEquipmentCustomField(TypeCustomField objCustomField)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
SigmaResultType customField = new SigmaResultType();
SigmaResultType EquipmentCustomField = new SigmaResultType();
TypeEquipmentCustomField typeEquipmentCustomField = new TypeEquipmentCustomField();
typeEquipmentCustomField.EquipmentId = objCustomField.Parentid;
typeEquipmentCustomField.Value = objCustomField.Value;
typeEquipmentCustomField.CreatedBy = userinfo.SigmaUserId;
typeEquipmentCustomField.UpdatedBy = userinfo.SigmaUserId;
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
CustomFieldMgr custom = new CustomFieldMgr();
customField = custom.AddCustomField(objCustomField);
typeEquipmentCustomField.CustomFieldId = customField.ScalarValue;
EquipmentCustomField = AddEquipmentCustomField(typeEquipmentCustomField);
scope.Complete();
}
return result;
}
public SigmaResultType AddConsumable(TypeConsumable objConsumable)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@Description", objConsumable.Description));
paramList.Add(new SqlParameter("@PartNumber", objConsumable.PartNumber));
paramList.Add(new SqlParameter("@Vendor", objConsumable.Vendor));
paramList.Add(new SqlParameter("@UomCode", objConsumable.UomCode));
paramList.Add(new SqlParameter("@CreatedBy", objConsumable.CreatedBy));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddConsumable", paramList.ToArray());
result.IsSuccessful = true;
result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
return result;
}
/// <summary>
/// Import MTO 메뉴얼 등록시
/// </summary>
/// <param name="objComponent"></param>
/// <returns></returns>
public SigmaResultType AddComponetInfo(TypeComponent objComponent)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
SigmaResultType resultComponent = new SigmaResultType();
ComponentCustomFieldMgr ComCustomFieldMgr = new ComponentCustomFieldMgr();
using (scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
if (objComponent.ComponentId > 0)
{
resultComponent = UpdateComponent(objComponent);
}
else
{
resultComponent = AddComponent(objComponent);
objComponent.ComCustomField.ForEach(item => item.ComponentId = resultComponent.ScalarValue);
}
if (resultComponent.IsSuccessful && objComponent.ComCustomField.Count > 0)
{
ComponentCustomFieldMgr comcustomfieldMgr = new ComponentCustomFieldMgr();
TypeComponentCustomField cutomobj = new TypeComponentCustomField();
cutomobj.ComponentId = objComponent.ComponentId;
comcustomfieldMgr.RemoveComponentCustomField(cutomobj);
foreach (var item in objComponent.ComCustomField)
{
if (item.CustomFieldId == 0)
{
TypeCustomField obj = SetTypeCustomFieldObj(item);
CustomFieldMgr customFieldMtr = new CustomFieldMgr();
obj = customFieldMtr.GetCustomField(obj);
item.CustomFieldId = obj.CustomFieldId;
}
switch (item.SigmaOperation)
{
case SigmaOperation.INSERT:
ComCustomFieldMgr.AddComponentCustomField(item);
break;
case SigmaOperation.UPDATE:
ComCustomFieldMgr.UpdateComponentCustomField(item);
break;
case SigmaOperation.DELETE:
ComCustomFieldMgr.RemoveComponentCustomField(item);
break;
}
}
}
result.AffectedRow = resultComponent.AffectedRow;
result.ScalarValue = resultComponent.ScalarValue;
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
/// <summary>
/// Edit Material Informantion (popup)
/// </summary>
/// <param name="ComponentId">ComponentId</param>
/// <returns></returns>
public SigmaResultType GetComponentCustomField(string ComponentId)
{
SigmaResultType result = new SigmaResultType();
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@ComponentId", int.Parse(ComponentId)));
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetComponentCustomField", parameters.ToArray());
result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
result.IsSuccessful = true;
return result;
}
public SigmaResultType GetLicense()
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
SigmaResultType result = new SigmaResultType();
string connStr = ConnStrHelper.getDbConnString();
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@CompanyId", userinfo.CompanyId)
};
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetLicense", parameters);
result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
result.IsSuccessful = true;
return result;
}
public SigmaResultType GetScheduledWorkItem(string scheduledWorkItemId)
{
SigmaResultType result = new SigmaResultType();
string connStr = ConnStrHelper.getDbConnString();
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ScheduledWorkItemId", scheduledWorkItemId)
};
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetScheduledWorkItem", parameters);
result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
result.AffectedRow = 1;
result.IsSuccessful = true;
return result;
}
/// <summary>
/// 2014-03-09
/// Get GeneralForeman
/// Data > Schedule > Edit
/// </summary>
/// <returns></returns>
public SigmaResultType GetGeneralForeManCombo()
{
SigmaResultType result = new SigmaResultType();
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
string connStr = ConnStrHelper.getDbConnString();
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ProjectId", userinfo.CurrentProjectId)
};
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetGeneralForemanCombo", parameters);
result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
result.IsSuccessful = true;
return result;
}
public SigmaResultType AddImportHistory(TypeImportHistory objImportHistory)
{
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
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];
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@ImportCategory", objImportHistory.ImportCategory));
paramList.Add(new SqlParameter("@ImportedFileName", objImportHistory.ImportedFileName));
paramList.Add(new SqlParameter("@ImportedDate", objImportHistory.ImportedDate));
paramList.Add(new SqlParameter("@TotalCount", objImportHistory.TotalCount));
paramList.Add(new SqlParameter("@SuccessCount", objImportHistory.SuccessCount));
paramList.Add(new SqlParameter("@FailCount", objImportHistory.FailCount));
paramList.Add(new SqlParameter("@CreatedBy", objImportHistory.CreatedBy));
paramList.Add(new SqlParameter("@ProjectId", AuthMgr.GetUserInfo().CurrentProjectId));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddImportHistory", paramList.ToArray());
result.IsSuccessful = true;
str[0] = outParam.Value.ToString();
str[1] = objImportHistory.TotalCount.ToString();
str[2] = objImportHistory.SuccessCount.ToString();
str[3] = objImportHistory.FailCount.ToString();
ds.Tables[0].Rows.Add(str);
result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
result.ScalarValue = (int)outParam.Value;
result.IsSuccessful = true;
scope.Complete();
}
return result;
}
public SigmaResultType AddSigmaCode(TypeSigmaCode objSigmaCode)
{
TypeUserInfo userinfo = AuthMgr.GetUserInfo();
TransactionScope scope = null;
SigmaResultType result = new SigmaResultType();
// Get connection string
string connStr = ConnStrHelper.getDbConnString();
if (!(string.IsNullOrEmpty(objSigmaCode.CodeName))
&& !(string.IsNullOrEmpty(objSigmaCode.CodeCategory))
)
{
List<SqlParameter> paramList = new List<SqlParameter>();
paramList.Add(new SqlParameter("@Code", objSigmaCode.CodeCategory + objSigmaCode.CodeName.ToUpper().Replace(" ", "_")));
paramList.Add(new SqlParameter("@CodeCategory", objSigmaCode.CodeCategory.Trim()));
paramList.Add(new SqlParameter("@CodeName", objSigmaCode.CodeName.Trim()));
paramList.Add(new SqlParameter("@CodeShortName", objSigmaCode.CodeShortName.Trim()));
paramList.Add(new SqlParameter("@RefChar", objSigmaCode.RefChar));
paramList.Add(new SqlParameter("@RefNo", Utilities.ToInt32(objSigmaCode.RefNo)));
paramList.Add(new SqlParameter("@Description", objSigmaCode.Description.Trim()));
paramList.Add(new SqlParameter("@IsActive", objSigmaCode.IsActive));
paramList.Add(new SqlParameter("@SortOrder", objSigmaCode.SortOrder));
paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;
paramList.Add(outParam);
using (scope = new TransactionScope(TransactionScopeOption.Required))
{
result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddSigmaCode", paramList.ToArray());
result.IsSuccessful = true;
//result.ScalarValue = (int)outParam.Value;
scope.Complete();
}
}
else
{
result.AffectedRow = -1;
result.ErrorCode = "GlobalSetting0001";
result.ErrorMessage = "Validation";
result.IsSuccessful = false;
}
return result;
}
/// <summary>
/// Get Discipline Info[MTO]
/// </summary>
/// <param name="CwpName">CwpName</param>
/// <param name="CwpId">CwpId</param>
/// <returns></returns>
public SigmaResultType GetDiscipline(string CwpName, string CwpId)
{
SigmaResultType Result = new SigmaResultType();
string connStr = ConnStrHelper.getDbConnString();
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@CwpName", CwpName),
new SqlParameter("@CwpId", int.Parse(CwpId))
};
DataSet ds = SqlHelper.ExecuteDataset(connStr, "usp_GetDiscipline", parameters);
Result.JsonDataSet = JsonHelper.convertDataTableToJson(ds.Tables[0]);
Result.IsSuccessful = true;
return Result;
}
public SigmaResultType GetLicense()
{
SigmaResultType result = new SigmaResultType();
try
{
AuthMgr authMgr = new AuthMgr();
result = authMgr.GetLicense();
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}
public SigmaResultType GetUser(string userId)
{
SigmaResultType result = new SigmaResultType();
try
{
UserMgr userMgr = new UserMgr();
result = userMgr.GetUser(userId);
return result;
}
catch (Exception ex)
{
// Log Exception
ExceptionHelper.logException(ex);
result.IsSuccessful = false;
result.ErrorMessage = ex.Message;
return result;
}
}