/// <summary>
/// Get sales detail by personal
/// </summary>
/// <param name="model">data table model</param>
/// <param name="condition">search condition</param>
/// <param name="TAB_ID">Tab id</param>
/// <returns>JSON sales detail by personal</returns>
public ActionResult SalesDetail(DataTablesModel model, SalesDetailByPersonalCondition condition, string TAB_ID)
{
if (Request.IsAjaxRequest() && ModelState.IsValid)
{
condition.CompanyCode = GetLoginUser().CompanyCode;
var pageInfo = this.mainService.GetSalesDetailByPersonal(model, condition);
Session[Constant.SESSION_SEARCH_RESULT + TAB_ID] = pageInfo.Items;
decimal totalSales = 0;
decimal totalCost = 0;
decimal totalProfit = 0;
foreach (var data in pageInfo.Items)
{
totalSales += data.project_sales;
totalCost += data.actual_cost;
totalProfit += data.profit;
}
var result = Json(
new
{
sEcho = model.sEcho,
iTotalRecords = pageInfo.TotalItems,
iTotalDisplayRecords = pageInfo.TotalItems,
aaData = (from t in pageInfo.Items select new object[]
{
t.project_no,
HttpUtility.HtmlEncode(t.project_name),
t.project_sales.ToString("#,##0") + "円",
t.actual_cost.ToString("#,##0") + "円",
t.profit.ToString("#,##0") + "円",
}).ToList(),
totalSales = totalSales.ToString("#,##0") + "円",
totalCost = totalCost.ToString("#,##0") + "円",
totalProfit = totalProfit.ToString("#,##0") + "円"
},
JsonRequestBehavior.AllowGet);
return(result);
}
return(new EmptyResult());
}
/// <summary>
/// Get Sales detail list by personal
/// </summary>
/// <param name="startItem"></param>
/// <param name="itemsPerPage"></param>
/// <param name="columns"></param>
/// <param name="sortCol"></param>
/// <param name="sortDir"></param>
/// <param name="condition"></param>
/// <returns></returns>
public PageInfo <SalesDetailByPersonal> GetSalesDetailByPersonal(
int startItem,
int itemsPerPage,
string columns,
int?sortCol,
string sortDir,
SalesDetailByPersonalCondition condition)
{
var sb = new StringBuilder();
sb.Append(@"
SELECT * FROM (SELECT *, (tbData.project_sales - tbData.actual_cost) as profit FROM (SELECT tbProject.project_no, tbProject.project_name, (paymentCost + individual_sales) AS project_sales, ");
sb.AppendFormat(@"
(paymentCost + (ISNULL(dbo.RoundNumber('{0}', (tbSales.unit_cost * (tbWorkTime.total_work_day / dbo.GetNumberOfWorkDaysInMonth('{0}', {1}, {2})))), 0))) AS actual_cost ", condition.CompanyCode, condition.SelectedMonth, condition.SelectedYear);
sb.Append(@"
FROM ( ");
sb.Append(@"
SELECT ISNULL(tbIndividual.project_sys_id, ISNULL(tbSupplier.project_sys_id, tbOverheadCost.project_sys_id)) AS project_sys_id, ");
sb.Append(@"
ISNULL(tbIndividual.individual_sales, 0) AS individual_sales, ");
sb.Append(@"
(ISNULL(tbSupplier.amount, 0) + ISNULL(tbOverheadCost.amount, 0)) AS paymentCost, ");
sb.Append(@"
ISNULL(tbIndividual.unit_cost, 0) AS unit_cost ");
sb.Append(@"
FROM ( ");
sb.Append(@"
SELECT tbMaDetail.project_sys_id, SUM(tbMaDetail.individual_sales) AS individual_sales, ");
sb.Append(@"
(SELECT TOP 1 base_unit_cost ");
sb.Append(@"
FROM unit_price_history ");
sb.AppendFormat(@"
WHERE company_code = '{0}' ", condition.CompanyCode);
sb.AppendFormat(@"
AND user_sys_id ={0} ", condition.UserID);
sb.AppendFormat(@"
AND apply_start_date <= CONVERT(date, CAST({0} AS varchar(4)) + '/'+ RIGHT('0' + CAST({1} AS VARCHAR(2)), 2) + '/01') ", condition.SelectedYear, condition.SelectedMonth);
sb.AppendFormat(@"
AND del_flg = '0' ", Constant.DeleteFlag.NON_DELETE);
sb.Append(@"
ORDER BY apply_start_date DESC) AS unit_cost ");
sb.Append(@"
FROM member_assignment_detail AS tbMaDetail ");
sb.AppendFormat(@"
WHERE tbMaDetail.company_code = '{0}' ", condition.CompanyCode);
sb.AppendFormat(@"
AND tbMaDetail.user_sys_id = {0} ", condition.UserID);
sb.AppendFormat(@"
AND tbMaDetail.target_year = {0} ", condition.SelectedYear);
sb.AppendFormat(@"
AND tbMaDetail.target_month = {0} ", condition.SelectedMonth);
sb.Append(@"
GROUP BY tbMaDetail.project_sys_id ");
sb.Append(@"
) AS tbIndividual FULL JOIN ( ");
sb.Append(@"
SELECT tbSupDetail.project_sys_id, SUM(tbSupDetail.amount) AS amount ");
sb.Append(@"
FROM sales_payment_detail AS tbSupDetail INNER JOIN sales_payment AS tbSup ");
sb.Append(@"
ON tbSupDetail.company_code = tbSup.company_code ");
sb.Append(@"
AND tbSupDetail.project_sys_id = tbSup.project_sys_id ");
sb.Append(@"
AND tbSupDetail.customer_id = tbSup.customer_id ");
sb.Append(@"
AND tbSupDetail.ordering_flg = tbSup.ordering_flg ");
sb.AppendFormat(@"
WHERE tbSupDetail.company_code = '{0}' ", condition.CompanyCode);
sb.Append(@"
AND tbSupDetail.ordering_flg = '2' ");
sb.Append(@"
AND (ISNULL(tbSup.charge_person_id, ");
sb.Append(@"
(SELECT charge_person_id FROM project_info WHERE company_code = tbSup.company_code AND project_sys_id = tbSup.project_sys_id)) ");
sb.AppendFormat(@"
) = {0}", condition.UserID);
sb.AppendFormat(@"
AND tbSupDetail.target_year = {0} ", condition.SelectedYear);
sb.AppendFormat(@"
AND tbSupDetail.target_month = {0} ", condition.SelectedMonth);
sb.Append(@"
GROUP BY tbSupDetail.project_sys_id ");
sb.Append(@"
) AS tbSupplier ");
sb.Append(@"
ON tbIndividual.project_sys_id = tbSupplier.project_sys_id ");
sb.Append(@"
FULL JOIN ( ");
sb.Append(@"
SELECT tbOvcDetail.project_sys_id, SUM(tbOvcDetail.amount) AS amount ");
sb.Append(@"
FROM overhead_cost_detail AS tbOvcDetail INNER JOIN overhead_cost AS tbOvc ");
sb.Append(@"
ON tbOvcDetail.company_code = tbOvc.company_code ");
sb.Append(@"
AND tbOvcDetail.project_sys_id = tbOvc.project_sys_id ");
sb.Append(@"
AND tbOvcDetail.detail_no = tbOvc.detail_no ");
sb.AppendFormat(@"
WHERE tbOvcDetail.company_code = '{0}' ", condition.CompanyCode);
sb.AppendFormat(@"
AND tbOvc.charge_person_id = {0} ", condition.UserID);
sb.AppendFormat(@"
AND tbOvcDetail.target_year = {0} ", condition.SelectedYear);
sb.AppendFormat(@"
AND tbOvcDetail.target_month = {0} ", condition.SelectedMonth);
sb.Append(@"
GROUP BY tbOvcDetail.project_sys_id ");
sb.Append(@"
) AS tbOverheadCost ");
sb.Append(@"
ON tbIndividual.project_sys_id = tbOverheadCost.project_sys_id ");
sb.Append(@"
OR tbSupplier.project_sys_id = tbOverheadCost.project_sys_id ");
sb.Append(@"
) AS tbSales ");
sb.Append(@"
LEFT JOIN ( ");
sb.AppendFormat(@"
SELECT project_sys_id, dbo.Hour2Day('{0}', SUM(actual_work_time)) AS total_work_day ", condition.CompanyCode);
sb.Append(@"
FROM member_actual_work_detail ");
sb.AppendFormat(@"
WHERE company_code = '{0}' ", condition.CompanyCode);
sb.AppendFormat(@"
AND user_sys_id = {0} ", condition.UserID);
sb.AppendFormat(@"
AND actual_work_year = {0} ", condition.SelectedYear);
sb.AppendFormat(@"
AND actual_work_month = {0} ", condition.SelectedMonth);
sb.Append(@"
GROUP BY project_sys_id ");
sb.Append(@"
) AS tbWorkTime ");
sb.Append(@"
ON tbSales.project_sys_id = tbWorkTime.project_sys_id ");
sb.Append(@"
INNER JOIN project_info AS tbProject ");
sb.Append(@"
ON tbSales.project_sys_id = tbProject.project_sys_id ");
sb.AppendFormat(@"
AND tbProject.company_code = '{0}' ", condition.CompanyCode);
sb.Append(@"
AND tbProject.del_flg = '0' ");
if (!string.IsNullOrEmpty(condition.ContractTypeID))
{
sb.AppendFormat(@"
AND tbProject.contract_type_id IN ({0}) ", condition.ContractTypeID);
}
sb.Append(@"
AND (SELECT sales_type FROM m_status WHERE company_code = tbProject.company_code AND status_id = tbProject.status_id) = '0' ");
sb.Append(@"
) tbData ) tbFinal");
var query = new Sql(sb.ToString());
var pageInfo = Page <SalesDetailByPersonal>(startItem, int.MaxValue, columns, sortCol, sortDir, query);
return(pageInfo);
}
/// <summary>
/// Get Sales detail list by personal
/// </summary>
/// <param name="model"></param>
/// <param name="condition"></param>
/// <returns>List of sales detail by personal</returns>
public PageInfo <SalesDetailByPersonal> GetSalesDetailByPersonal(DataTablesModel model, SalesDetailByPersonalCondition condition)
{
var pageInfo = this._repository.GetSalesDetailByPersonal(
model.iDisplayStart,
model.iDisplayLength,
model.sColumns,
model.iSortCol_0,
model.sSortDir_0,
condition);
return(pageInfo);
}
public ActionResult ExportCsv(int UserID = 0, int TargetYear = 0, int TargetMonth = 0, int sortCol = 0, string sortDir = "asc", string ContractTypeID = "", string TAB_ID = "")
{
if (UserID > 0 && TargetYear > 0 && TargetMonth > 0)
{
List <string> titles = new List <string>()
{
"プロジェクト名",
"売上",
"原価",
"利益"
};
DataTablesModel model = new DataTablesModel
{
sEcho = "1",
iColumns = 5,
sColumns = "project_name,project_name,project_sales,actual_cost,profit",
iDisplayStart = 0,
iDisplayLength = int.MaxValue,
iSortCol_0 = sortCol,
sSortDir_0 = sortDir,
iSortingCols = 1
};
SalesDetailByPersonalCondition condition = new SalesDetailByPersonalCondition
{
CompanyCode = GetLoginUser().CompanyCode,
UserID = UserID,
SelectedYear = TargetYear,
SelectedMonth = TargetMonth,
ContractTypeID = ContractTypeID
};
var salesDetails = new List <SalesDetailByPersonal>();
if (Session[Constant.SESSION_SEARCH_RESULT + TAB_ID] != null)
{
salesDetails = Session[Constant.SESSION_SEARCH_RESULT + TAB_ID] as List <SalesDetailByPersonal>;
}
else
{
salesDetails = this.mainService.GetSalesDetailByPersonal(model, condition).Items;
}
IList <SalesDetailByPersonalExportCSV> datalist = new List <SalesDetailByPersonalExportCSV>();
foreach (var data in salesDetails)
{
datalist.Add(new SalesDetailByPersonalExportCSV()
{
project_name = data.project_name,
sales = data.project_sales.ToString("#,##0"),
cost = data.actual_cost.ToString("#,##0"),
profit = (data.project_sales - data.actual_cost).ToString("#,##0")
});
}
string fileName = "PersonalSalesDetail_" + Utility.GetCurrentDateTime().ToString("yyyyMMdd") + ".csv";
DataTable dt = Utility.ToDataTableT(datalist, titles.ToArray());
Utility.ExportToCsvData(this, dt, fileName);
}
return(new EmptyResult());
}
