public ProjectUpdateHandler(GlobalSolusindoDb db, tblM_User user, ProjectValidator projectValidator, ProjectFactory projectFactory, ProjectQuery projectQuery, AccessControl accessControl) : base(db, user)
{
this.projectValidator = projectValidator;
this.projectFactory = projectFactory;
this.projectQuery = projectQuery;
this.projectEntryDataProvider = new ProjectEntryDataProvider(db, user, accessControl, projectQuery);
}
/// <inheritdoc/>
public async Task <ICollection <ProjectResponse> > GetAllAsync(ProjectQuery queryObj, CancellationToken cansellationToken)
{
var results = await _projectRepository.GetProjectsAsync(cansellationToken);
var query = results.AsQueryable();
var columnsMap = new Dictionary <string, Expression <Func <Context.Models.Project, object> > >
{
["Name"] = p => p.Name,
["Priority"] = p => p.Priority,
["Start"] = p => p.Start,
["Completion"] = p => p.Completion,
["Status"] = p => p.Status
};
query = query.ApplyFilteringByExactValue(queryObj);
query = query.ApplyOrdering(queryObj, columnsMap);
query = query.ApplyPaging(queryObj?.Page, queryObj?.PageSize);
//results = await query.ToListAsync();
results = query.ToList();
var response = _mapper.Map <ICollection <ProjectResponse> >(results);
return(response);
}
public PageGridData <ProjectDto> GetProjectPage(ProjectQuery query)
{
using (var dbScope = _dbScopeFactory.CreateReadOnly())
{
var db = dbScope.DbContexts.Get <FireProjDbContext>();
var data = db.Project.Select(r => new ProjectDto
{
Id = r.Id,
ProjectName = r.ProjectName,
ProjectRepo = r.ProjectRepo,
ProjectDesc = r.ProjectDesc,
ProjectSlnName = r.ProjectSlnName,
ProjectCsprojName = r.ProjectCsprojName,
CreatorId = r.CreatorId,
CreatorName = r.CreatorName,
OnlineVersion = r.OnlineVersion,
RepoId = r.RepoId,
});
if (!string.IsNullOrEmpty(query.Search))
{
data = data.Where(r => r.ProjectName.Contains(query.Search));
}
var result = data.OrderBy(r => r.Id).Skip(query.Offset).Take(query.Limit).ToList();
var total = data.Count();
return(new PageGridData <ProjectDto> {
rows = result, total = total
});
}
}
public IHttpActionResult Get(int id)
{
ThrowIfUserHasNoRole(readRole);
using (ProjectQuery projectQuery = new ProjectQuery(Db))
{
var data = projectQuery.GetByPrimaryKey(id);
SaveLog("Project", "Get", JsonConvert.SerializeObject(new { primaryKey = id }));
return(Ok(new SuccessResponse(data)));
}
}
public ActionResult GetDraftList()
{
try
{
var queryParams = new NameValueCollection();
int userType = CurrenUserInfo.UserType;
string loginName = CurrenUserInfo.LoginName;
var query = new ProjectQuery(queryParams);
var sqlCondition = new StringBuilder();
sqlCondition.Append("ISNULL(IsDelete,0)!=1 and CurrentStatus=-1"); //CurrentStatus审批状态为1（同意）
sqlCondition.Append($" and createby='{loginName}' ");
PageRequest preq = new PageRequest
{
TableName = " [View_Project] ",
Where = sqlCondition.ToString(),
Order = " Id DESC ",
IsSelect = true,
IsReturnRecord = true,
PageSize = query.PageSize,
PageIndex = query.PageIndex,
FieldStr = "*"
};
var result = new ProjectBLL().GetView_ProjectByPage(preq);
result.ForEach(project => {
project.StartTimeStr = project.StartTime.HasValue ? project.StartTime.Value.ToString("yyyy-MM-dd") : string.Empty;
project.EndTimeStr = project.EndTime.HasValue ? project.EndTime.Value.ToString("yyyy-MM-dd") : string.Empty;
project.CreateTimeStr = project.CreateTime.HasValue ? project.CreateTime.Value.ToString("yyyy-MM-dd") : string.Empty;
});
result = result.OrderByDescending(r => r.CreateTime).ToList();
var response = new ResponseEntity <object>(true, string.Empty,
new DataGridResultEntity <View_Project>
{
TotalRecords = preq.Out_AllRecordCount,
DisplayRecords = preq.Out_PageCount,
ResultData = result
});
return(Json(response, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new ResponseEntity <object>(-999, string.Empty, ""), JsonRequestBehavior.AllowGet));
}
}
public async Task Projects_GetAllByQueryId_ShouldNotNull()
{
var queryObj = new ProjectQuery {
ProjectId = _projectId
};
var result = await _projectService.GetAllAsync(queryObj, CancellationToken.None);
Assert.NotNull(result);
Assert.IsNotEmpty(result);
Assert.AreEqual(1, result.Count);
}
public async Task <List <ProjectListItem> > Handle(ProjectQuery request, CancellationToken cancellationToken)
{
var dbProjects = await _solutionProject.GetProjectBySolutionId(request.SolutionId);
return(dbProjects.Select(x => new ProjectListItem()
{
Id = x.Id.ToString(),
Name = x.Name,
CName = x.CName
}).ToList());
}
public async Task <QueryResult <Project> > GetAllAsync(ProjectQuery queryObj)
{
var result = new QueryResult <Project>();
var query = _context.Projects
.Include(p => p.Status)
.Include(p => p.Product)
.Include(p => p.AssignedUser)
.Include(p => p.Client)
.AsQueryable();
if (!string.IsNullOrWhiteSpace(queryObj.UserId))
{
query = query.Where(p => p.AssignedUserId == queryObj.UserId);
}
if (queryObj.StatusId.HasValue)
{
query = query.Where(p => p.StatusId == queryObj.StatusId.Value);
}
if (!string.IsNullOrWhiteSpace(queryObj.SearchTerm))
{
query = queryObj.SearchTerm == "!Completed"
? query.Where(p => !p.Status.Name.Contains("Completed"))
: query.Where(p =>
p.Client.Name.Contains(queryObj.SearchTerm) ||
p.Client.Email.Contains(queryObj.SearchTerm) ||
p.Product.Name.Contains(queryObj.SearchTerm) ||
p.Status.Name.Contains(queryObj.SearchTerm) ||
p.AssignedUser.FullName.Contains(queryObj.SearchTerm));
}
var columnsMap = new Dictionary <string, Expression <Func <Project, object> > >
{
["dateReceived"] = p => p.DateReceived,
["dueDate"] = p => p.DueDate,
["client"] = p => p.Client.Name,
["product"] = p => p.Product.Name,
["status"] = p => p.Status.Name,
["assigned"] = p => p.AssignedUser.FullName,
["priority"] = p => p.GetDaysRemaining
};
query = query.ApplyOrdering(queryObj, columnsMap);
result.TotalItems = await query.CountAsync();
query = query.ApplyPaging(queryObj);
result.Items = await query.ToListAsync();
return(result);
}
public async Task Projects_GetAllByFilterAndSortingDesc_ShouldNotNull(string columnName, bool isSortAcsending)
{
var queryObj = new ProjectQuery {
IsSortAcsending = isSortAcsending, SortBy = columnName
};
var result = await _projectService.GetAllAsync(queryObj, CancellationToken.None);
var project = result.LastOrDefault();
Assert.NotNull(result);
Assert.IsNotEmpty(result);
Assert.AreEqual(_priority, project.Priority);
}
public IHttpActionResult Search([FromUri] ProjectSearchFilter filter)
{
ThrowIfUserHasNoRole(readRole);
if (filter == null)
{
throw new KairosException("Missing search filter parameter");
}
using (var projectQuery = new ProjectQuery(Db))
{
var data = projectQuery.Search(filter);
return(Ok(new SuccessResponse(data)));
}
}
public async Task Projects_GetAllByFilter_ShouldNotNull(string columnName)
{
var queryObj = new ProjectQuery {
Name = columnName
};
var result = await _projectService.GetAllAsync(queryObj, CancellationToken.None);
var project = result.LastOrDefault();
Assert.NotNull(result);
Assert.IsNotEmpty(result);
Assert.AreEqual(1, result.Count);
Assert.AreEqual(_name, project.Name);
}
public List <SPGetProject_Result> Filter(ProjectQuery query, out int count)
{
count = 0;
int pageNumber = query.PageNumber != 0 ? query.PageNumber : 1;
var keyword = string.IsNullOrEmpty(query.Keyword) != true ? query.Keyword : "";
var start = 0;
var limit = query.PageSize;
start = (pageNumber - 1) * limit;
int totalRow = 0;
List <SPGetProject_Result> result = new List <SPGetProject_Result>();
ObjectParameter prTotalRow = new ObjectParameter("total", totalRow);
result = _entities.SPGetProject(query.DepartmentId, Util.DetecVowel(keyword), start, limit, prTotalRow).ToList();
count = (prTotalRow.Value == null) ? 0 : Convert.ToInt32(prTotalRow.Value);
return(result);
}
private SOWEntryFormData CreateFormData(SOWDTO sowDTO)
{
if (sowDTO == null)
{
return(new SOWEntryFormData());
}
SOWEntryFormData formData = new SOWEntryFormData();
var project = new ProjectQuery(this.Db).GetByPrimaryKey(sowDTO.Project_FK);
if (project != null)
{
formData.Projects.Add(project);
}
var bts = new BTSQuery(this.Db).GetByPrimaryKey(sowDTO.BTS_FK);
if (bts != null)
{
formData.BTSes.Add(bts);
}
foreach (var assign in sowDTO.SOWAssigns)
{
var user = new UserQuery(Db).GetByPrimaryKey(assign.User_FK);
if (user != null)
{
formData.Users.Add(user);
}
}
foreach (var track in sowDTO.SOWTracks)
{
sowDTO.Technology_FK = track.TipePekerjaan_FK;
var technology = new TechnologyQuery(this.Db).GetByPrimaryKey((int)track.TipePekerjaan_FK);
if (technology != null)
{
formData.Technologies.Add(technology);
}
}
return(formData);
}
public ProjectQuery Open(string projectName)
{
var projectPath = Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
projectPath = Path.Combine(projectPath, "..\\..\\tests\\", projectName);
_files = new List <IFileDescriptor>();
FileDescriptor _rootFolder;
_rootFolder = new FileDescriptor(projectPath);
_rootFolder.OpenFolder("", "");
_rootFolder.label = projectName;
MapFiles(_rootFolder);
var project = new ProjectQuery
{
root = _rootFolder,
};
return(project);
}
public BaseListResponse <SPGetProject_Result> Search([FromUri] ProjectQuery query)
{
return(taskService.Filter(query));
}
public async Task <IActionResult> Query(ProjectQuery projectQuery)
{
int page = _pageSettings.Value.page;
int pageSize = _pageSettings.Value.pageSize;
int ReportProgressUpperBound; //上界
int ReportProgressLowerBound; //下界
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
string nameQuery;
nameQuery = projectQuery.Name ?? "";
if ((int)projectQuery.ReportProgressQuery == int.MaxValue) //判断是否为"任意"
{
ReportProgressUpperBound = int.MaxValue;
ReportProgressLowerBound = 0;
}
else
{
ReportProgressUpperBound = (int)projectQuery.ReportProgressQuery;
ReportProgressLowerBound = (int)projectQuery.ReportProgressQuery;
}
//var linq1 =await _mainRepository.Projects;
var linqVar = _mainRepository.EntityItems.Where(p => p.Name.Contains(nameQuery))
.Where(p => (p.EnterDate > projectQuery.EnterDateAfter && p.EnterDate < projectQuery.EnterDateBefore))
.Where(p => (p.SiteFinishedDate > projectQuery.SiteFinishedDateAfter && p.SiteFinishedDate < projectQuery.SiteFinishedDateBefore))
.Where(p => (p.ReportProgress >= ReportProgressLowerBound && p.ReportProgress <= ReportProgressUpperBound));
var model = new ItemListViewModel <ProjectViewModel>
{
ItemViewModels = await linqVar.Select(p => new ProjectViewModel
{
Id = p.Id,
Name = p.Name,
CreateTime = p.CreateTime,
StandardValue = p.StandardValue,
CalcValue = p.CalcValue,
EnterProgress = (EnterProgress)p.EnterProgress,
EnterDate = p.EnterDate,
SiteProgress = (SiteProgress)p.SiteProgress,
SiteFinishedDate = p.SiteFinishedDate,
ExitDate = p.ExitDate,
ReportProgress = (ReportProgress)p.ReportProgress,
ReportNo = p.ReportNo,
ReportPublishedDate = p.ReportPublishedDate,
DelayDays = p.DelayDays,
DelayRate = p.DelayRate,
ProjectProgressExplanation = p.ProjectProgressExplanation,
ContractId = p.ContractId,
BridgeId = p.BridgeId
}).OrderBy(p => p.Id).Skip((page - 1) * pageSize).Take(pageSize).ToAsyncEnumerable().ToList(),
PagingInfo = new PagingInfo
{
CurrentPage = page,
ItemsPerPage = pageSize,
TotalItems = linqVar.Count()
}
};
return(PartialView("ProjectListPartial", model));
}
public void SetUp()
{
this.codeQuery = new ProjectQuery(@"..\..\..\RoslinqTestTarget\RoslinqTestTarget.csproj");
}
/// <summary>
/// Index met zoek opties
/// </summary>
public void Index(string zoektekst)
{
ProjectQuery projecten = new ProjectQuery();
projecten.SearchTerm = zoektekst;
IList<Project> projects = projecten.GetQuery(ActiveRecordMediator.GetSessionFactoryHolder().CreateSession(typeof(ModelBase))).List<Project>();
//IList<Project> projects = projecten.GetQuery(UnitOfWork.CurrentSession).List<Project>();
PropertyBag.Add("projecten", projects);
if (projects.Count == 0)
AddErrorMessageToPropertyBag("Er zijn nog geen projecten gedefiniëerd.");
}
public ActionResult GetApprovalList()
{
try
{
var queryParams = new NameValueCollection();
if (!ParamHelper.CheckParaQ(ref queryParams))
{
return(Json(new ResponseEntity <int>(RegularFunction.RegularSqlRegexText), JsonRequestBehavior.AllowGet));
}
int userType = CurrenUserInfo.UserType;
string loginName = CurrenUserInfo.LoginName;
var query = new ProjectQuery(queryParams);
var sqlCondition = new StringBuilder();
sqlCondition.Append("ISNULL(IsDelete,0)!=1 and CurrentStatus=0"); //CurrentStatus审批状态为0（草稿）
if (userType != GDS.Entity.Constant.ConstantDefine.Admin) //验证审批权限
{
var departmentList = new DepartmentBLL().GetDepartmentByAuditor(loginName);
if (departmentList != null && departmentList.Count > 0)
{
sqlCondition.Append($" and DepartId in ({string.Join(",", departmentList.Select(x => x.Id))}) ");
}
else
{
return(Json(new ResponseEntity <dynamic>(10, "权限不足", null), JsonRequestBehavior.AllowGet));
}
}
//if (userType == GDS.Entity.Constant.ConstantDefine.ProjectManager) //
//{
// sqlCondition.Append($" and (createby = '{loginName}' or projectmanager = '{loginName}' or charindex(';{loginName};', ';' + TeamMembers + ';') > 0) ");
//}
//else if (userType == GDS.Entity.Constant.ConstantDefine.User) //
//{
// sqlCondition.Append($"and charindex(';{loginName};', ';' + TeamMembers + ';') > 0");
//}
if (userType == GDS.Entity.Constant.ConstantDefine.ProjectManager) //
{
sqlCondition.Append($" and (createby = '{loginName}' or charindex('{loginName}', projectmanager) > 0 or charindex('{loginName}', TeamMembers) > 0) ");
}
//else if (userType == GDS.Entity.Constant.ConstantDefine.User) //
//{
// sqlCondition.Append($"and charindex(';{loginName};', TeamMembers) > 0");
//}
if (!string.IsNullOrEmpty(query.DepartId) && query.DepartId != "0")
{
sqlCondition.Append($" and BusinessDept = {query.DepartId}");
}
if (!string.IsNullOrEmpty(query.ProjectType))
{
sqlCondition.Append($" and ProjectType = {query.ProjectType}");
}
if (!string.IsNullOrEmpty(query.Name))
{
sqlCondition.Append($" and Name like '%{query.Name}%'");
}
if (!string.IsNullOrEmpty(query.CreateBy))
{
sqlCondition.Append($" and CreateBy like '%{query.CreateBy}%'");
}
PageRequest preq = new PageRequest
{
TableName = " [View_Project] ",
Where = sqlCondition.ToString(),
Order = " Id DESC ",
IsSelect = true,
IsReturnRecord = true,
PageSize = query.PageSize,
PageIndex = query.PageIndex,
FieldStr = "*"
};
var result = new ProjectBLL().GetView_ProjectByPage(preq);
result.ForEach(project => {
project.CreateTimeStr = project.CreateTime.HasValue ? project.CreateTime.Value.ToString("yyyy-MM-dd") : string.Empty;
});
var response = new ResponseEntity <object>(true, string.Empty,
new DataGridResultEntity <View_Project>
{
TotalRecords = preq.Out_AllRecordCount,
DisplayRecords = preq.Out_PageCount,
ResultData = result
});
return(Json(response, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new ResponseEntity <object>(-999, string.Empty, ""), JsonRequestBehavior.AllowGet));
}
}
public HttpResponseMessage Export(GlobalSolusindoDb Db, string fileName, UserSearchFilter filter)
{
_fileName = fileName;
_workbook = new XSSFWorkbook(); //Creating New Excel object
var workbook = new XLWorkbook();
using (var userSearch = new UserSearch(Db))
{
var data = userSearch.GetDataByFilter(filter);
var dataExport = data.Records.Select(c => new UserExportDTO
{
Nomor = c.User_PK,
JoinDate = c.JoinDate,
UserName = c.Username,
KategoriJabatanTitle = c.KategoriJabatanTitle,
Name = c.Name,
TglLahir = c.TglLahir,
NoKTP = c.NoKTP,
ReligionName = c.Religion == null || c.Religion == 0 ? "" : religionQuery.GetByPrimaryKey((int)c.Religion).Name, //?
CategoryContract = c.CategoryContract == null || c.CategoryContract == 0 ? "" : categoryContractQuery.GetByPrimaryKey((int)c.CategoryContract).Name,
Project = c.Project == null || c.Project == 0 ? "" : projectQuery.GetByPrimaryKey((int)c.Project).Project_PK + "-" + projectQuery.GetByPrimaryKey((int)c.Project).OperatorTitle + "-" + projectQuery.GetByPrimaryKey((int)c.Project).VendorTitle + "-" + projectQuery.GetByPrimaryKey((int)c.Project).DeliveryAreaTitle, //?
Gender = c.Gender == null || c.Gender == 0 ? "" : genderQuery.GetByPrimaryKey((int)c.Gender).Name,
MartialStatus = c.MaritalStatus == null || c.MaritalStatus == 0 ? "" : maritalStatusQuery.GetByPrimaryKey((int)c.MaritalStatus).Name,
NPWP = c.NPWP, //?
BPJS = c.BPJS, //?
ContactNumber = c.NoHP,
G1EmailID = c.Email,
PersonalEmail = c.PersonalEmail,
Address = c.Address,
NamaBank = c.BankName, //?
NoRekening = c.AccountNumber, //?
Salary = c.Salary, //?
Remark = c.Description, //?
Status = null //?
}).ToList();
projectQuery.Dispose();
DataTable user = new DataTable("UserUpload");
UserExportDTO obj = new UserExportDTO();
foreach (var item in obj.GetType().GetProperties())
{
user.Columns.Add(item.Name);
}
DataRow dr;
foreach (var item in dataExport)
{
dr = user.NewRow();
dr["Nomor"] = item.Nomor;
dr["UserName"] = item.UserName;
dr["KategoriJabatanTitle"] = item.KategoriJabatanTitle;
dr["Name"] = item.Name;
dr["TglLahir"] = item.TglLahir;
dr["NoKTP"] = item.NoKTP;
dr["ReligionName"] = item.ReligionName;
dr["CategoryContract"] = item.CategoryContract;
dr["Project"] = item.Project;
dr["Gender"] = item.Gender;
dr["MartialStatus"] = item.MartialStatus;
dr["NPWP"] = item.NPWP;
dr["BPJS"] = item.BPJS;
dr["JoinDate"] = item.JoinDate;
dr["ContactNumber"] = item.ContactNumber;
dr["G1EmailID"] = item.G1EmailID;
dr["PersonalEmail"] = item.PersonalEmail;
dr["Address"] = item.Address;
dr["NamaBank"] = item.NamaBank;
dr["NoRekening"] = item.NoRekening;
dr["Salary"] = item.Salary;
dr["Remark"] = item.Remark;
dr["Status"] = item.Status;
user.Rows.Add(dr);
}
workbook.AddWorksheet(user);
var worksheet = workbook.Worksheet(1);
worksheet.Columns().Width = 15;
using (var jabatanQry = new KategoriJabatanQuery())
{
//SETUP TABLE
DataTable validationTable = new DataTable();
validationTable.TableName = "Kategori Jabatan";
//SETUP COLUMN
LOVDTO objkatja = new LOVDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTable.Columns.Add(item.Name);
}
//DATA ROW
var dataval = jabatanQry.GetQuery().Select(c => new LOVDTO {
Id = c.KategoriJabatan_PK, Name = c.Title
}).ToList();
DataRow dr2;
int startcell = 2, endcell = 2;
foreach (var item in dataval)
{
dr2 = validationTable.NewRow();
dr2["Id"] = item.Id;
dr2["Name"] = item.Name;
validationTable.Rows.Add(dr2);
endcell++;
}
var worksheet2 = workbook.AddWorksheet(validationTable);
worksheet.Column(3).SetDataValidation().List(worksheet2.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//GENDER
using (var genderQuery = new GenderQuery())
{
//SETUP TABLE
DataTable validationTablegender = new DataTable();
validationTablegender.TableName = "Gender";
//SETUP COLUMN
GenderDTO objgender = new GenderDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTablegender.Columns.Add(item.Name);
}
var datagender = genderQuery.GetQuery().Select(c => new GenderDTO {
Gender_PK = c.Gender_PK, Name = c.Name
}).ToList();
DataRow drgender;
startcell = 2; endcell = 2;
foreach (var item in datagender)
{
drgender = validationTablegender.NewRow();
drgender["Id"] = item.Gender_PK;
drgender["Name"] = item.Name;
validationTablegender.Rows.Add(drgender);
endcell++;
}
var worksheetgender = workbook.AddWorksheet(validationTablegender);
worksheet.Column(10).SetDataValidation().List(worksheetgender.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//RELIGION
using (var ReligionQuery = new ReligionQuery())
{
//SETUP TABLE
DataTable validationTableReligion = new DataTable();
validationTableReligion.TableName = "Religion";
//SETUP COLUMN
ReligionDTO objReligion = new ReligionDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableReligion.Columns.Add(item.Name);
}
var dataReligion = ReligionQuery.GetQuery().Select(c => new ReligionDTO {
Religion_PK = c.Religion_PK, Name = c.Name
}).ToList();
DataRow drReligion;
startcell = 2; endcell = 2;
foreach (var item in dataReligion)
{
drReligion = validationTableReligion.NewRow();
drReligion["Id"] = item.Religion_PK;
drReligion["Name"] = item.Name;
validationTableReligion.Rows.Add(drReligion);
endcell++;
}
var worksheetReligion = workbook.AddWorksheet(validationTableReligion);
worksheet.Column(7).SetDataValidation().List(worksheetReligion.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//MaritalStatus
using (var MaritalStatusQuery = new MaritalStatusQuery())
{
//SETUP TABLE
DataTable validationTableMaritalStatus = new DataTable();
validationTableMaritalStatus.TableName = "MaritalStatus";
//SETUP COLUMN
MaritalStatusDTO objMaritalStatus = new MaritalStatusDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableMaritalStatus.Columns.Add(item.Name);
}
var dataMaritalStatus = MaritalStatusQuery.GetQuery().Select(c => new MaritalStatusDTO {
MaritalStatus_PK = c.MaritalStatus_PK, Name = c.Name
}).ToList();
DataRow drMaritalStatus;
startcell = 2; endcell = 2;
foreach (var item in dataMaritalStatus)
{
drMaritalStatus = validationTableMaritalStatus.NewRow();
drMaritalStatus["Id"] = item.MaritalStatus_PK;
drMaritalStatus["Name"] = item.Name;
validationTableMaritalStatus.Rows.Add(drMaritalStatus);
endcell++;
}
var worksheetMaritalStatus = workbook.AddWorksheet(validationTableMaritalStatus);
worksheet.Column(11).SetDataValidation().List(worksheetMaritalStatus.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//CategoryContract
using (var CategoryContractQuery = new CategoryContractQuery())
{
//SETUP TABLE
DataTable validationTableCategoryContract = new DataTable();
validationTableCategoryContract.TableName = "CategoryContract";
//SETUP COLUMN
CategoryContractDTO objCategoryContract = new CategoryContractDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableCategoryContract.Columns.Add(item.Name);
}
var dataCategoryContract = CategoryContractQuery.GetQuery().Select(c => new CategoryContractDTO {
CategoryContract_PK = c.CategoryContract_PK, Name = c.Name
}).ToList();
DataRow drCategoryContract;
startcell = 2; endcell = 2;
foreach (var item in dataCategoryContract)
{
drCategoryContract = validationTableCategoryContract.NewRow();
drCategoryContract["Id"] = item.CategoryContract_PK;
drCategoryContract["Name"] = item.Name;
validationTableCategoryContract.Rows.Add(drCategoryContract);
endcell++;
}
var worksheetCategoryContract = workbook.AddWorksheet(validationTableCategoryContract);
worksheet.Column(8).SetDataValidation().List(worksheetCategoryContract.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Project
using (var ProjectQuery2 = new ProjectQuery())
{
//SETUP TABLE
DataTable validationTableProject = new DataTable();
validationTableProject.TableName = "Project";
//SETUP COLUMN
ProjectDTO objProject = new ProjectDTO();
foreach (var item in objkatja.GetType().GetProperties())
{
validationTableProject.Columns.Add(item.Name);
}
var dataProject = ProjectQuery2.GetQuery();
DataRow drProject;
startcell = 2; endcell = 2;
foreach (var item in dataProject)
{
drProject = validationTableProject.NewRow();
drProject["Id"] = item.Project_PK;
drProject["Name"] = item.Project_PK + "-" + item.OperatorTitle + "-" + item.VendorTitle + "-" + item.DeliveryAreaTitle;
validationTableProject.Rows.Add(drProject);
endcell++;
}
var worksheetProject = workbook.AddWorksheet(validationTableProject);
worksheet.Column(9).SetDataValidation().List(worksheetProject.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
}
}
}
}
}
worksheet.Protect("kairosg1")
.SetFormatCells() // Cell Formatting
.SetInsertColumns() // Inserting Columns
.SetDeleteColumns() // Deleting Columns
.SetDeleteRows(); // Deleting Rows;
worksheet.Columns(1, 2).Style.Fill.BackgroundColor = XLColor.DarkGray;
worksheet.Columns(1, 2).Style.Font.FontColor = XLColor.Black;
worksheet.Columns(3, 20).Style.Protection.SetLocked(false);
}
MemoryStream memoryStream = GetStream(workbook);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
return(response);
}
// TODO: You need to use SOLID, because this method depends / tightly coupled to implementation of Project and ProjectQuery
public static IQueryable <Context.Models.Project> ApplyFilteringByExactValue(this IQueryable <Context.Models.Project> query, ProjectQuery queryObj)
{
if (!(queryObj is null) && queryObj.ProjectId.HasValue)
{
query = query.Where(p => p.Id == queryObj.ProjectId.Value);
}
if (!(queryObj is null) && !String.IsNullOrWhiteSpace(queryObj.Name))
{
query = query.Where(p => p.Name.ToLower() == queryObj.Name.ToLower());
}
if (!(queryObj is null) && queryObj.Priority >= 1)
{
query = query.Where(p => p.Priority == queryObj.Priority);
}
if (!(queryObj is null) && !(queryObj.Status is null))
{
query = query.Where(p => p.Status == queryObj.Status);
}
return(query);
}
/// <summary>
/// 获取项目分页
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IHttpActionResult Get([FromUri] ProjectQuery query)
{
var result = _projectSvc.GetProjectPage(query);
return(Ok(result));
}
public ActionResult GetProjectList()
{
try
{
var queryParams = new NameValueCollection();
if (!ParamHelper.CheckParaQ(ref queryParams))
{
return(Json(new ResponseEntity <int>(RegularFunction.RegularSqlRegexText), JsonRequestBehavior.AllowGet));
}
int userType = CurrenUserInfo.UserType;
string loginName = CurrenUserInfo.LoginName;
var query = new ProjectQuery(queryParams);
var sqlCondition = new StringBuilder();
sqlCondition.Append("ISNULL(IsDelete,0)!=1 and CurrentStatus=1"); //CurrentStatus审批状态为1（同意）
if (userType == GDS.Entity.Constant.ConstantDefine.ProjectManager) //
{
sqlCondition.Append($" and (createby = '{loginName}' or charindex('{loginName}', projectmanager) > 0 or charindex('{loginName}', TeamMembers) > 0) ");
}
else if (userType == GDS.Entity.Constant.ConstantDefine.User) //
{
sqlCondition.Append($"and charindex('{loginName}', TeamMembers) > 0");
}
if (!string.IsNullOrEmpty(query.DepartId) && query.DepartId != "0")
{
sqlCondition.Append($" and BusinessDept = {query.DepartId}");
}
if (!string.IsNullOrEmpty(query.Status))
{
sqlCondition.Append($" and Status = {query.Status}");
}
if (!string.IsNullOrEmpty(query.ProjectType))
{
sqlCondition.Append($" and ProjectType = {query.ProjectType}");
}
if (!string.IsNullOrEmpty(query.Name))
{
sqlCondition.Append($" and Name like '%{query.Name}%'");
}
if (!string.IsNullOrEmpty(query.ProjectManager))
{
sqlCondition.Append($" and ProjectManager like '%{query.ProjectManager}%'");
}
if (!string.IsNullOrEmpty(query.No))
{
sqlCondition.Append($" and No like '%{query.No}%'");
}
PageRequest preq = new PageRequest
{
TableName = " [View_Project] ",
Where = sqlCondition.ToString(),
Order = " Id DESC ",
IsSelect = true,
IsReturnRecord = true,
PageSize = query.PageSize,
PageIndex = query.PageIndex,
FieldStr = "*"
};
var result = new ProjectBLL().GetView_ProjectByPage(preq);
result.ForEach(project => {
project.CreateTimeStr = project.CreateTime.HasValue? project.CreateTime.Value.ToString("yyyy-MM-dd") : string.Empty;
});
result = result.OrderByDescending(r => r.CreateTime).ToList();
var response = new ResponseEntity <object>(true, string.Empty,
new DataGridResultEntity <View_Project>
{
TotalRecords = preq.Out_AllRecordCount,
DisplayRecords = preq.Out_PageCount,
ResultData = result
});
return(Json(response, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new ResponseEntity <object>(-999, string.Empty, ""), JsonRequestBehavior.AllowGet));
}
}
public ProjectEntryDataProvider(GlobalSolusindoDb db, tblM_User user, AccessControl accessControl, ProjectQuery projectQuery) : base(db, user)
{
this.accessControl = accessControl;
this.projectQuery = projectQuery;
}
public HttpResponseMessage Export(GlobalSolusindoDb Db, tblM_User user, string fileName, TaskListSearchFilter filter)
{
_fileName = fileName;
//CREATE WORKBOOK
var workbook = new XLWorkbook();
DataTable SOW = new DataTable("SOWUpload"); //DataTable Name = Worksheet Name
SOWExportDTO obj = new SOWExportDTO();
//Setup Column Names
foreach (var item in obj.GetType().GetProperties())
{
SOW.Columns.Add(item.Name);
}
workbook.AddWorksheet(SOW); // NO DATA = ADD Worksheet to WorkBook
//Worksheet Properties
var worksheet = workbook.Worksheet(1);
worksheet.Columns().Width = 15;
//Validation Table
using (var ProjectQuery2 = new ProjectQuery())
{
//SETUP TABLE PROJECT
DataTable validationTableProject = new DataTable();
validationTableProject.TableName = "Project";
//SETUP COLUMN
LOVDTO objProject = new LOVDTO();
foreach (var item in objProject.GetType().GetProperties())
{
validationTableProject.Columns.Add(item.Name);
}
var dataProject = ProjectQuery2.GetProjectByPM(user.User_PK);
DataRow drProject;
int startcell = 2, endcell = 2;
foreach (var item in dataProject)
{
drProject = validationTableProject.NewRow();
drProject["Id"] = item.Project_PK;
drProject["Name"] = item.Project_PK + "-" + item.OperatorTitle + "-" + item.VendorTitle + "-" + item.DeliveryAreaTitle;
validationTableProject.Rows.Add(drProject);
endcell++;
}
var worksheetProject = workbook.AddWorksheet(validationTableProject);
worksheet.Column(2).SetDataValidation().List(worksheetProject.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE VALIDATION BTS
using (var BTSQuery = new BTSQuery())
{
//SETUP TABLE BTS
DataTable validationTableBTS = new DataTable();
validationTableBTS.TableName = "BTS";
//SETUP COLUMN
LOVDTO objBTS = new LOVDTO();
foreach (var item in objBTS.GetType().GetProperties())
{
validationTableBTS.Columns.Add(item.Name);
}
var dataBTS = BTSQuery.GetQuery();
DataRow drBTS;
startcell = 2; endcell = 2;
foreach (var item in dataBTS)
{
drBTS = validationTableBTS.NewRow();
drBTS["Id"] = item.BTS_PK;
drBTS["Name"] = item.TowerID + "-" + item.Name;
validationTableBTS.Rows.Add(drBTS);
endcell++;
}
var worksheetBTS = workbook.AddWorksheet(validationTableBTS);
worksheet.Column(4).SetDataValidation().List(worksheetBTS.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE TECHNOLOGY
using (var TechnologyQuery = new TechnologyQuery())
{
//SETUP TABLE Technology
DataTable validationTableTechnology = new DataTable();
validationTableTechnology.TableName = "Technology";
//SETUP COLUMN
LOVDTO objTechnology = new LOVDTO();
foreach (var item in objTechnology.GetType().GetProperties())
{
validationTableTechnology.Columns.Add(item.Name);
}
var dataTechnology = TechnologyQuery.GetQuery();
DataRow drTechnology;
startcell = 2; endcell = 2;
foreach (var item in dataTechnology)
{
drTechnology = validationTableTechnology.NewRow();
drTechnology["Id"] = item.Technology_PK;
drTechnology["Name"] = item.Title;
validationTableTechnology.Rows.Add(drTechnology);
endcell++;
}
var worksheetTechnology = workbook.AddWorksheet(validationTableTechnology);
worksheet.Column(5).SetDataValidation().List(worksheetTechnology.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
using (var UserQuery = new UserQuery())
{
//SETUP TABLE Teamlead
DataTable validationTableUser = new DataTable();
validationTableUser.TableName = "Teamlead";
//SETUP COLUMN
LOVDTO objUser = new LOVDTO();
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUser.Columns.Add(item.Name);
}
var dataUser = UserQuery.GetByJabatanAndProject(1, user.User_PK);
DataRow drUser;
startcell = 2; endcell = 2;
foreach (var item in dataUser)
{
drUser = validationTableUser.NewRow();
drUser["Id"] = item.User_PK;
drUser["Name"] = item.Name;
validationTableUser.Rows.Add(drUser);
endcell++;
}
var worksheetUser = workbook.AddWorksheet(validationTableUser);
worksheet.Column(12).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE PLOQC
DataTable validationTableUserPLOQC = new DataTable();
validationTableUserPLOQC.TableName = "RNO";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserPLOQC.Columns.Add(item.Name);
}
var dataUserPLOQC = UserQuery.GetByJabatanAndProject(6, user.User_PK); // PLOQC JABATAN???
DataRow drUserPLOQC;
startcell = 2; endcell = 2;
foreach (var item in dataUserPLOQC)
{
drUserPLOQC = validationTableUserPLOQC.NewRow();
drUserPLOQC["Id"] = item.User_PK;
drUserPLOQC["Name"] = item.Name;
validationTableUserPLOQC.Rows.Add(drUserPLOQC);
endcell++;
}
var worksheetUserPLOQC = workbook.AddWorksheet(validationTableUserPLOQC);
worksheet.Column(13).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE RF
DataTable validationTableUserRF = new DataTable();
validationTableUserRF.TableName = "RF";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserRF.Columns.Add(item.Name);
}
var dataUserRF = UserQuery.GetByJabatanAndProject(5, user.User_PK);
DataRow drUserRF;
startcell = 2; endcell = 2;
foreach (var item in dataUserRF)
{
drUserRF = validationTableUserRF.NewRow();
drUserRF["Id"] = item.User_PK;
drUserRF["Name"] = item.Name;
validationTableUserRF.Rows.Add(drUserRF);
endcell++;
}
var worksheetUserRF = workbook.AddWorksheet(validationTableUserRF);
worksheet.Column(14).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE Rigger
DataTable validationTableUserRigger = new DataTable();
validationTableUserRigger.TableName = "Rigger";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserRigger.Columns.Add(item.Name);
}
var dataUserRigger = UserQuery.GetByJabatanAndProject(3, user.User_PK);
DataRow drUserRigger;
startcell = 2; endcell = 2;
foreach (var item in dataUserRigger)
{
drUserRigger = validationTableUserRigger.NewRow();
drUserRigger["Id"] = item.User_PK;
drUserRigger["Name"] = item.Name;
validationTableUserRigger.Rows.Add(drUserRigger);
endcell++;
}
var worksheetUserRigger = workbook.AddWorksheet(validationTableUserRigger);
worksheet.Column(15).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE DT
DataTable validationTableUserDT = new DataTable();
validationTableUserDT.TableName = "DriveTester";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserDT.Columns.Add(item.Name);
}
var dataUserDT = UserQuery.GetByJabatanAndProject(2, user.User_PK);
DataRow drUserDT;
startcell = 2; endcell = 2;
foreach (var item in dataUserDT)
{
drUserDT = validationTableUserDT.NewRow();
drUserDT["Id"] = item.User_PK;
drUserDT["Name"] = item.Name;
validationTableUserDT.Rows.Add(drUserDT);
endcell++;
}
var worksheetUserDT = workbook.AddWorksheet(validationTableUserDT);
worksheet.Column(16).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
}
//SETUP TABLE TEAMLEAD, RNO, RIGGER, TESTER
}
MemoryStream memoryStream = GetStream(workbook);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
return(response);
}
public ProjectListDataProvider(GlobalSolusindoDb db, tblM_User user, ProjectQuery projectSearch) : base(db, user)
{
this.projectQuery = projectSearch;
}
public async Task <ICollection <ProjectResponse> > GetProjects(ProjectQuery queryObj, CancellationToken cancellationToken)
{
return(await _projectService.GetAllAsync(queryObj, cancellationToken));
}