/// <summary> /// Search Assignment by project /// </summary> /// <param name="model">DataTablesModel</param> /// <param name="condition">ProjectCondition</param> /// <param name="SORT_COLUMN">Sort column</param> /// <param name="SORT_TYPE">Sort type</param> /// <param name="TAB_ID">Tab id</param> /// <returns>Json Assignment By Project</returns> public ActionResult SearchAssignmentByProject(DataTablesModel model, ProjectCondition condition, int SORT_COLUMN, string SORT_TYPE, string TAB_ID) { if (Request.IsAjaxRequest()) { if (ModelState.IsValid) { Session["PMS06003_AssignmentByProject"] = model.iDisplayLength; var pagePlan = this._service.SearchPlanByProject(model, condition, GetLoginUser().CompanyCode); var listPlan = this._service.GetListPlanByProject(condition, GetLoginUser().CompanyCode, SORT_COLUMN, SORT_TYPE); Session[Constant.SESSION_SEARCH_RESULT + TAB_ID] = Utility.SerializeDynamicToJson(listPlan); var results = buildTableResults(condition, pagePlan.Items); var result = Json( new { sEcho = model.sEcho, iTotalRecords = pagePlan.TotalItems, iTotalDisplayRecords = pagePlan.TotalItems, aaData = results }); // save search condition SaveRestoreData(condition); return(result); } } return(new EmptyResult()); }
/// <summary> /// Search list plan by Project /// </summary> /// <param name="condition"></param> /// <param name="companycode"></param> /// <returns></returns> public IList <dynamic> GetListPlanByProject(ProjectCondition condition, string companycode, int sort_colum, string sort_type) { Sql sql = buildSelectPlanQueryByProject(condition, companycode, true, sort_colum, sort_type); var pageInfo = this._database.Fetch <dynamic>(sql); return(pageInfo); }
/// <summary> /// Search list plan by Project /// </summary> /// <param name="condition">condition</param> /// <param name="companycode">companycode</param> /// <returns>List Plan By Project</returns> public IList <dynamic> GetListPlanByProject(ProjectCondition condition, string companycode, int sort_colum, string sort_type) { var listPlan = this._repository.GetListPlanByProject( condition, companycode, sort_colum, sort_type); return(listPlan); }
/// <summary> /// Search page plan by Project /// </summary> /// <param name="model">model</param> /// <param name="condition">condition</param> /// <param name="companycode">companycode</param> /// <returns>List plan by project</returns> public PageInfo <dynamic> SearchPlanByProject(DataTablesModel model, ProjectCondition condition, string companycode) { var pagePlan = this._repository.SearchPlanByProject( model.iDisplayStart, model.iDisplayLength, model.sColumns, model.iSortCol_0, model.sSortDir_0, condition, companycode); return(pagePlan); }
public SearchProject() { this._cond = new ProjectCondition(); this._projects = new List <Project>(); InitializeComponent(); //基底クラスで実装したコールバック関数でイベントフック this.Load += base.Form_Load; this.btnNew.Click += base.NewEntryButton_Click; this.btnSearch.Click += base.SearchButton_Click; this.grdList.CellDoubleClick += base.DataGridView_CellDoubleClick; }
/// <summary> /// Export to csv by project /// </summary> /// <param name="start_date"></param> /// <param name="end_date"></param> /// <param name="project_name"></param> /// <param name="customer_id"></param> /// <param name="group_id"></param> /// <param name="tag_id"></param> /// <param name="eff_type"></param> /// <param name="sort_colum"></param> /// <param name="sort_type"></param> /// <param name="DelFlag"></param> /// <param name="status"></param> /// <param name="TAB_ID"></param> /// <returns>File CSV export</returns> public ActionResult ExportCsvByProject(string start_date, string end_date, string project_name, int?customer_id, int?group_id, int?tag_id, int eff_type, int sort_colum, string sort_type, bool DelFlag, string status, string TAB_ID) { ProjectCondition condition = new ProjectCondition(); condition.START_DATE = start_date; condition.PROJECT_NAME = project_name; condition.END_DATE = end_date; condition.CUSTOMER_ID = customer_id; condition.GROUP_ID = group_id; condition.TAG_ID = tag_id; condition.EFF_TYPE = eff_type; condition.DELETE_FLG = DelFlag; condition.STATUS_ID = status; IList <string> jsonResults = new List <string>(); IList <dynamic> listPlan = new List <dynamic>(); if (Session[Constant.SESSION_SEARCH_RESULT + TAB_ID] != null) { jsonResults = Session[Constant.SESSION_SEARCH_RESULT + TAB_ID] as IList <string>; listPlan = Utility.DeserializeJsonToDynamic(jsonResults); } else { listPlan = this._service.GetListPlanByProject(condition, GetLoginUser().CompanyCode, sort_colum, sort_type); } var results = buildTableResults(condition, listPlan, true); List <string> fixColumns = new List <string>() { "担当者", "プロジェクト名", "ランク", "受注金額", "見積工数" }; List <string> listMonth = Utility.getListMonth(condition.START_DATE, condition.END_DATE); string[] columns = fixColumns.Concat(listMonth).ToArray(); DataTable dataTable = Utility.ToDateTable(results, columns); Utility.ExportToCsvData(this, dataTable, "AssignmentByProject_" + Utility.GetCurrentDateTime().ToString("yyyyMMdd") + ".csv"); return(new EmptyResult()); }
/// <summary> /// 获取评估列表 /// </summary> /// <returns></returns> public JsonResult GetProjectList(ProjectCondition condition, int index, int rows) { int total; var result = ProjectService.GetProjectList(condition, index, rows, out total, UserHelper.GetCurrentUser().Id); return(Json(new { rows = result.Select(x => new { x.Id, x.ProjectNo, x.PledgeAddress, x.EvalType, x.PropertyType, ProjectStatus = x.ProjectStatus.ToString(), CreateTime = string.Format("{0:yyyy-MM-dd HH:mm:ss}", x.CreateTime), IsFirstCredit = x.IsFirstCredit ? "是" : "否", CanDownLoadSummaryData = x.SummaryData.CreateTime.HasValue, x.RevokeReason }), total }, JsonRequestBehavior.AllowGet)); }
public void TestFindProjectsBy(int expected, ProjectCondition condition) { var data = Project.FindProjectsBy(condition); Assert.AreEqual(expected, data.Count); }
/// <summary> /// Search page plan by Project /// </summary> /// <param name="condition"></param> /// <param name="companycode"></param> /// <returns></returns> public PageInfo <dynamic> SearchPlanByProject(int startItem, int itemsPerPage, string columns, int?sortCol, string sortDir, ProjectCondition condition, string companycode) { Sql sql = buildSelectPlanQueryByProject(condition, companycode, false, 0, string.Empty); var pageInfo = Page <dynamic>(startItem, itemsPerPage, columns, sortCol, sortDir, sql); return(pageInfo); }
/// <summary> /// Build search query by Project /// </summary> /// <param name="condition"></param> /// <param name="companycode"></param> /// <returns></returns> private Sql buildSelectQueryByProject(ProjectCondition condition, string companycode, bool isExport = false) { string sStartYearMonth = condition.START_DATE.Length == 7 ? condition.START_DATE.Replace("/", string.Empty) : condition.START_DATE.Replace("/", "0"); string sEndYearMonth = condition.END_DATE.Length == 7 ? condition.END_DATE.Replace("/", string.Empty) : condition.END_DATE.Replace("/", "0"); string cols = getStringColumnNames(sStartYearMonth, sEndYearMonth); StringBuilder query = new StringBuilder(); query.Append(@" SELECT * FROM ( SELECT piv.* FROM ( SELECT pi.project_sys_id, "); if (!isExport) { query.Append(@" pi.company_code, "); } query.Append(@" pi.project_no, pi.project_name, mr.rank, pi.total_sales, cast( mad.target_year as VARCHAR(4)) + '/' + RIGHT('0' + CAST(mad.target_month AS VARCHAR(2)),2) AS [Month], "); switch (condition.EFF_TYPE) { case 1: query.AppendFormat(" dbo.Day2Hour(@companycode, plan_man_days) as plan_man_days "); break; case 2: query.Append(" plan_man_days "); break; case 3: query.AppendFormat(" dbo.Day2Month(@companycode, mad.target_month, mad.target_year,plan_man_days) as plan_man_days "); break; } query.Append(" ,mr.display_order AS rank_order "); query.Append(@" FROM project_info pi LEFT JOIN m_rank mr ON pi.rank_id = mr.rank_id LEFT JOIN member_assignment_detail mad ON pi.project_sys_id = mad.project_sys_id " ); if (condition.CUSTOMER_ID != null) { query.Append(@" LEFT JOIN sales_payment sp ON mad.project_sys_id = sp.project_sys_id " ); } query.Append(@" WHERE (CAST( mad.target_year AS VARCHAR(4)) + RIGHT('0' + CAST(mad.target_month AS VARCHAR(2)),2)) BETWEEN @startDate AND @endDate AND pi.company_code = @companycode "); if (condition.PROJECT_NAME != null) { query.AppendFormat(@" AND pi.project_name LIKE '%{0}%' ESCAPE '\' ", replaceWildcardCharacters(condition.PROJECT_NAME)); } if (condition.CUSTOMER_ID != null) { query.Append(@" AND sp.ordering_flg = 1 AND sp.customer_id = @customer_id "); } query.Append(@" )x PIVOT ( SUM(plan_man_days) for Month in (" + cols + @") ) piv ) tblProject "); Sql sql = new Sql(query.ToString(), new { startDate = sStartYearMonth }, new { endDate = sEndYearMonth }, new { companycode = companycode }, new { project_name = condition.PROJECT_NAME }, new { customer_id = condition.CUSTOMER_ID } ); return(sql); }
/// <summary> /// build query for select plan /// </summary> /// <param name="condition"></param> /// <param name="companycode"></param> /// <param name="isExport"></param> /// <returns></returns> private Sql buildSelectPlanQueryByProject(ProjectCondition condition, string companycode, bool isExport, int sort_colum, string sort_type) { string sStartYearMonth = condition.START_DATE.Length == 7 ? condition.START_DATE.Replace("/", string.Empty) : condition.START_DATE.Replace("/", "0"); string sEndYearMonth = condition.END_DATE.Length == 7 ? condition.END_DATE.Replace("/", string.Empty) : condition.END_DATE.Replace("/", "0"); string cols = getStringColumnNames(sStartYearMonth, sEndYearMonth); StringBuilder query = new StringBuilder(); query.Append(@" SELECT piv.* FROM ( SELECT pi.project_sys_id, pi.del_flg, pi.company_code, mr.display_order AS rank_order, us.display_name as person_in_charge, pi.project_name, mr.rank, pi.total_sales, cast( mad.target_year as VARCHAR(4)) + '/' + RIGHT('0' + CAST(mad.target_month AS VARCHAR(2)),2) AS [Month], "); switch (condition.EFF_TYPE) { case 1: query.AppendFormat(" dbo.Day2Hour(@company_code, plan_man_days) as plan_man_days "); break; case 2: query.Append(" plan_man_days "); break; case 3: query.AppendFormat(" dbo.Day2Month(@company_code, mad.target_month, mad.target_year,plan_man_days) as plan_man_days "); break; } query.Append(@" FROM project_info pi INNER JOIN m_status ON pi.company_code = m_status.company_code AND pi.status_id = m_status.status_id INNER JOIN m_user us ON us.company_code = pi.company_code AND us.user_sys_id = pi.charge_person_id LEFT JOIN m_rank mr ON pi.company_code = mr.company_code AND pi.rank_id = mr.rank_id LEFT JOIN member_assignment_detail mad ON pi.company_code = mad.company_code AND pi.project_sys_id = mad.project_sys_id "); if (condition.CUSTOMER_ID != null) { query.Append(@" LEFT JOIN sales_payment sp ON mad.project_sys_id = sp.project_sys_id "); } query.Append(@" WHERE (CAST( mad.target_year AS VARCHAR(4)) + RIGHT('0' + CAST(mad.target_month AS VARCHAR(2)),2)) BETWEEN @startDate AND @endDate AND pi.company_code = @company_code "); if (!string.IsNullOrEmpty(condition.STATUS_ID)) { query.Append(" AND pi.status_id in (" + condition.STATUS_ID + ") "); } if (condition.PROJECT_NAME != null) { query.AppendFormat(@" AND pi.project_name LIKE '%{0}%' ESCAPE '\' ", replaceWildcardCharacters(condition.PROJECT_NAME)); } if (condition.CUSTOMER_ID != null) { query.Append(@" AND sp.ordering_flg = 1 AND sp.customer_id = @customer_id "); if (condition.TAG_ID != null) { query.Append(@" AND sp.tag_id = @tag_id "); } } if (condition.GROUP_ID != null) { query.Append(@" AND us.group_id = @group_id "); } if (!condition.DELETE_FLG) { query.Append(" AND pi.del_flg = @del_flg "); } if (!condition.INACTIVE_FLG) { query.Append(" AND m_status.sales_type <> '2' "); } query.Append(@" )x PIVOT ( SUM(plan_man_days) for Month in (" + cols + @") ) piv "); string sort_column_name = "project_sys_id"; if (isExport) { switch (sort_colum) { case 1: sort_column_name = "person_in_charge"; break; case 2: sort_column_name = "project_name"; break; case 3: sort_column_name = "rank"; break; case 4: sort_column_name = "total_sales"; break; default: sort_column_name = "project_sys_id"; break; } query.Append(@" ORDER BY " + sort_column_name + " " + sort_type); } Sql sql = new Sql(query.ToString(), new { startDate = sStartYearMonth }, new { endDate = sEndYearMonth }, new { company_code = companycode }, new { project_name = condition.PROJECT_NAME }, new { customer_id = condition.CUSTOMER_ID }, new { tag_id = condition.TAG_ID }, new { group_id = condition.GROUP_ID }, new { del_flg = Constant.DeleteFlag.NON_DELETE } ); return(sql); }
/// <summary> /// 项目列表 /// </summary> /// <param name="condition"></param> /// <param name="index"></param> /// <param name="size"></param> /// <param name="total"></param> /// <param name="userId"></param> /// <returns></returns> public IList <ProjectModel> GetProjectList(ProjectCondition condition, int index, int size, out int total, long userId) { return (ProjectService.Instance.GetProjectList(condition, index, size, out total, userId) .ToListModel <ProjectModel, Project>()); }
/// <summary> /// Format datatable /// </summary> /// <param name="condition">ProjectCondition</param> /// <param name="pagePlan">IList</param> /// <param name="isExportCsv">isExportCsv</param> /// <returns>List</returns> public IList <object[]> buildTableResults(ProjectCondition condition, IList <dynamic> pagePlan, bool isExportCsv = false) { List <IDictionary <string, object> > planDetailTemp = new List <IDictionary <string, object> >(); if (pagePlan != null) { // Add dynamic data to list foreach (var item in pagePlan) { planDetailTemp.Add(item); } } var timeUnit = string.Empty; if (condition.EFF_TYPE == Convert.ToInt32(Constant.TimeUnit.HOUR)) { timeUnit = Constant.TimeUnit.Items[0].ToString(); } else if (condition.EFF_TYPE == Convert.ToInt32(Constant.TimeUnit.DAY)) { timeUnit = Constant.TimeUnit.Items[1].ToString(); } else { timeUnit = Constant.TimeUnit.Items[2].ToString(); } List <string> listMonth = Utility.getListMonth(condition.START_DATE, condition.END_DATE); List <object[]> resultList = new List <object[]>(); for (int i = 0; i < planDetailTemp.Count; i++) // plan { var plan = planDetailTemp[i].Values.ToList(); decimal totalPlan = 0; object[] planActual = new object[plan.Count - 3]; if (isExportCsv) { planActual[0] = plan[4]; // person_in_charge planActual[1] = plan[5]; // project_name planActual[2] = plan[6]; // rank planActual[3] = Convert.ToDecimal(plan[7].ToString()).ToString("#,##0.##") + "円"; // total_sales; for (var k = 8; k < listMonth.Count + 8; k++) // calculate total plan { decimal p = plan[k] != null?Math.Round(Convert.ToDecimal(plan[k]), 2) : 0; totalPlan += p; planActual[k - 3] = p.ToString("#,##0.00"); // plan in month } planActual[4] = totalPlan.ToString("#,##0.00") + timeUnit; // total plan } else { string hiddenTmp = string.Empty; if (plan[2].ToString().Equals("1")) { hiddenTmp = "<input type='hidden' name='del_flg' value='{0}' />"; } planActual[0] = plan[1]; // project_sys_id for initiate sort var project_id_plan = plan[1]; // project_id_temp planActual[1] = HttpUtility.HtmlEncode(plan[5]) + hiddenTmp; // person_in_charge if (IsInFunctionList(Constant.FunctionID.ProjectDetail)) { planActual[2] = "<a href='#' title='" + HttpUtility.HtmlEncode(plan[6]) + "' class='project-detail-link' project-id='" + project_id_plan + @"'><label class='longtext'>" + HttpUtility.HtmlEncode(plan[6]) + "</label></a>"; } else { planActual[2] = "<div class='short-text text-overflow' title=" + HttpUtility.HtmlEncode(plan[6]) + ">" + HttpUtility.HtmlEncode(plan[6]) + "</div>"; } planActual[3] = (plan[7] ?? string.Empty).ToString(); // rank planActual[4] = Convert.ToDecimal(plan[8].ToString()).ToString("#,##0.##") + "円"; // total_sales; for (var k = 9; k < listMonth.Count + 9; k++) // calculate total plan { decimal p = plan[k] != null?Math.Round(Convert.ToDecimal(plan[k]), 2) : 0; totalPlan += p; planActual[k - 3] = p.ToString("#,##0.00") + hiddenTmp; // plan in month } planActual[5] = totalPlan.ToString("#,##0.00") + timeUnit; // total plan } resultList.Add(planActual.ToArray()); } return(resultList); }