/// <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);
}
public void TestFindProjectsBy(int expected, ProjectCondition condition)
{
var data = Project.FindProjectsBy(condition);
Assert.AreEqual(expected, data.Count);
}
