public ActionResult Report19_ByUser(DateTime? startDate, DateTime? endDate, DateTime? lastUpdateDate, ref ExpenseReportModel model)
{
bool hasRecord = false;
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString);
string subContractor_userGroup_name = UserGroup.SubCon;
int subContractor_id = int.Parse((from o in this._db.recsys_option
where o.type_code == "UG" && o.active == true && o.name == subContractor_userGroup_name
select o.value).FirstOrDefault());
using (connection)
{
SqlCommand command = new SqlCommand(
string.Format(
@"
SELECT
cs.user_id, cs.id, u.name, u.employee_number, cc.code,
cs.price1
FROM [recsys_costs] cs
join [recsys_users] u on cs.user_id = u.id
join [recsys_type] t on u.type = t.id
left outer join [recsys_cost] cc on cs.cost_id = cc.id
where {0}
{1}
{2}
{3}
{4}
order by u.employee_number, cs.account_date
",
"u.user_group != " + subContractor_id,
startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1,'{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
"and cs.status = 0",
lastUpdateDate.HasValue ? string.Format("and cs.last_update >= '{0}' and cs.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
")") : string.Empty
),
connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
hasRecord = reader.HasRows;
if (!hasRecord)
{
//# log down
}
else
{
model.ByUserRows = new List<ExpenseReportByUserRow>();
while (reader.Read())
{
ExpenseReportByUserRow newRow = model.ByUserRows.FirstOrDefault(theRow => theRow.EmployeeID == reader.GetInt32(0));
if (newRow == null) //# if the row does not exists, add the new row and map details to it
{
newRow = new ExpenseReportByUserRow();
if (!reader.IsDBNull(0))
{
newRow.EmployeeID = reader.GetInt32(0);
}
if (!reader.IsDBNull(2))
{
newRow.EmployeeName = reader.GetString(2);
}
if (!reader.IsDBNull(3))
newRow.EmployeeNumber = reader.GetString(3);
model.ByUserRows.Add(newRow);
}
//# add sub rows
newRow.SubRows.Add(new ExpenseReportByUserSubRow()
{
CategoryName = reader.IsDBNull(4) ? string.Empty : reader.GetString(4),
Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5)
});
}
reader.Close();
}
}
if (hasRecord)
return View("~/Views/ReportsGen/Report19_ByUser.cshtml", model);
else
return Content("找不到任何紀錄。");
}
public ActionResult Report19_BySupplier(DateTime? startDate, DateTime? endDate, IEnumerable<int> supplierIDs, DateTime? lastUpdateDate, ref ExpenseReportModel model)
{
//# validation
if (supplierIDs.Count() <= 0)
throw new SystemException("invalid Suppliers parameter values");
bool hasRecord = false;
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString);
string idsSqlClause = string.Empty;
foreach (int id in supplierIDs)
idsSqlClause += (string.IsNullOrEmpty(idsSqlClause) ? string.Empty : ",") + id;
using (connection)
{
SqlCommand command = new SqlCommand(
string.Format(
@"
select id, name
from recsys_supplier
where id in ({0})
order by name
",
idsSqlClause
),
connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
hasRecord = reader.HasRows;
if (!hasRecord)
{
//# log down
}
else
{
model.BySupplierRows = new List<ExpenseReportBySupplierRow>();
while (reader.Read())
{
ExpenseReportBySupplierRow newRow = new ExpenseReportBySupplierRow();
if (!reader.IsDBNull(0))
newRow.SupplierID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
newRow.SupplierName = reader.GetString(1);
model.BySupplierRows.Add(newRow);
}
reader.Close();
//# get sub-rows
command = new SqlCommand(
string.Format(
@"
select
s.id,
s.name,
c.code,
cc.price1
from recsys_costs cc
join recsys_supplier s on cc.supplier_id = s.id
left outer join recsys_cost c on cc.cost_id = c.id
where s.id in ({0})
{1}
{2}
{3}
order by cc.account_date
",
idsSqlClause,
startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1, '{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
lastUpdateDate.HasValue ? string.Format("and cc.last_update >= '{0}' and cc.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
")") : string.Empty
),
connection);
reader = command.ExecuteReader();
while (reader.Read())
{
int supplierID = reader.GetInt32(0);
ExpenseReportBySupplierRow newRow = model.BySupplierRows.FirstOrDefault(theRow => theRow.SupplierID == supplierID);
newRow.SubRows.Add(new ExpenseReportBySupplierSubRow()
{
CategoryName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
Price = reader.IsDBNull(3) ? 0 : reader.GetDouble(3)
});
}
reader.Close();
}
}
if (hasRecord)
return View("~/Views/ReportsGen/Report19_BySupplier.cshtml", model);
else
return Content("找不到任何紀錄。");
}
public ActionResult Report19_ByCategory(DateTime? startDate, DateTime? endDate, IEnumerable<int> costCategoryIDs, DateTime? lastUpdateDate, ref ExpenseReportModel model)
{
//# validation
if (costCategoryIDs.Count() <= 0)
throw new SystemException("invalid Cost Categories parameter values");
bool hasRecord = false;
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString);
string idsSqlClause = string.Empty;
foreach (int id in costCategoryIDs)
idsSqlClause += (string.IsNullOrEmpty(idsSqlClause) ? string.Empty : ",") + id;
//string dateClause = string.Empty;
//if (startDate.HasValue && endDate.HasValue)
//{
// dateClause = string.Format(" and account_date between {0} and {1} ", startDate.Value.ToString(DB_DATE_FORMAT), endDate.Value.ToString(DB_DATE_FORMAT));
//}
//else
//{
// if (startDate.HasValue)
// {
// dateClause += string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT));
// }
// if (endDate.HasValue)
// {
// dateClause += string.Format("and account_date <= '{0}'", endDate.Value.ToString(DB_DATE_FORMAT));
// }
//}
using (connection)
{
SqlCommand command = new SqlCommand(
string.Format(
@"
select id, code, name
from recsys_cost
where id in ({0})
order by code
",
idsSqlClause
),
connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
hasRecord = reader.HasRows;
if (!hasRecord)
{
//# log down
}
else
{
model.ByCategoriesRows = new List<ExpenseReportByCategoryRow>();
while (reader.Read())
{
ExpenseReportByCategoryRow newRow = new ExpenseReportByCategoryRow();
if (!reader.IsDBNull(0))
newRow.CategoryID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
newRow.CategoryCode = reader.GetString(1);
if (!reader.IsDBNull(2))
newRow.CategoryName = reader.GetString(2);
model.ByCategoriesRows.Add(newRow);
}
reader.Close();
//# get sub-rows
command = new SqlCommand(
string.Format(
@"
SELECT
co.cost_id as costCategoryID,
co.center,
c.customer_code,
co.price1,
q.number,
m.start_date,
m.end_date,
r.table2
FROM [recsys_costs] co
left join [recsys_relate] r on co.id = r.id1
left join [recsys_maintenance] m on m.id = r.id2 and r.table2 = 'maintenance'
left join [recsys_quotation] q on q.id = r.id2 and r.table2 = 'quotation'
left join [recsys_relate_customers] c on c.id = isnull(m.customer_id, q.customer_id)
where r.table1 = 'costs'
and co.cost_id is not null
and co.cost_id in ({0})
{1}
{2}
{3}
order by account_date
",
idsSqlClause,
startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1, '{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
lastUpdateDate.HasValue ? string.Format("and co.last_update >= '{0}' and co.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
")") : string.Empty
),
connection);
reader = command.ExecuteReader();
while (reader.Read())
{
int costCategoryID = reader.GetInt32(0);
ExpenseReportByCategoryRow newRow = model.ByCategoriesRows.FirstOrDefault(theRow => theRow.CategoryID == costCategoryID);
newRow.SubRows.Add(new ExpenseReportByCategorySubRow()
{
Center = reader.IsDBNull(1)
? null
: new Center() { name = reader.GetString(1) },
CustomerCode = reader.GetString(7)=="maintenance"
? reader.IsDBNull(2)
? string.Empty
: reader.IsDBNull(5)
? reader.GetString(2)
: reader.IsDBNull(6)
? reader.GetString(2) + " ( " + reader.GetDateTime(5).ToString("dd/MM/yyyy") + " - )"
: reader.GetString(2) + " ( " + reader.GetDateTime(5).ToString("dd/MM/yyyy") + " - " + reader.GetDateTime(6).ToString("dd/MM/yyyy") + " )"
: reader.GetString(7)=="quotation"
? reader.IsDBNull(4)
? "ACQ No.: "
: "ACQ No.: " + reader.GetString(4)
: string.Empty,
Price = reader.IsDBNull(3) ? 0 : reader.GetDouble(3)
});
}
reader.Close();
}
}
if (hasRecord)
return View("~/Views/ReportsGen/Report19_ByCategory.cshtml", model);
else
return Content("找不到任何紀錄。");
}
public ActionResult Report19_BySubCon(DateTime? startDate, DateTime? endDate, DateTime? lastUpdateDate, ref ExpenseReportModel model)
{
string sql = string.Format(
@"
SELECT
s.name as subConName, u.name as userName, cc.code + ' ' + cc.name as category, cs.invoice, cs.price1 as price, cs.account_date, cs.remark,
c.customer_code as customerCode, q.number as number, m.start_date as startDate, m.end_date as endDate, r.table2 as tableName
FROM [recsys_costs] cs
left join [recsys_users] u on cs.user_id = u.id
left join [recsys_supplier] s on cs.supplier_id = s.id
left outer join [recsys_cost] cc on cs.cost_id = cc.id
left join [recsys_relate] r on cs.id = r.id1
left join [recsys_maintenance] m on m.id = r.id2 and r.table2 = 'maintenance'
left join [recsys_quotation] q on q.id = r.id2 and r.table2 = 'quotation'
left join [recsys_relate_customers] c on c.id = isnull(m.customer_id, q.customer_id)
where cc.status = 1 and cc.IsSubCon = 1 and r.table1 = 'costs'
{0}
{1}
{2}
order by cc.code, cs.account_date, u.name, s.name",
startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1,'{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
lastUpdateDate.HasValue ? string.Format("and cs.last_update >= '{0}' and cs.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
")") : string.Empty
);
IEnumerable<report19_bySubCon> expenses = Common.queryToObject<report19_bySubCon>(this._db.ExecuteStoreQuery<report19_bySubCon>(sql));
//Maintenance/ACQ No. Column
foreach (report19_bySubCon expense in expenses)
{
expense.maintenanceACQ = expense.tableName == "maintenance"
? string.IsNullOrEmpty(expense.customerCode)
? string.Empty
: expense.startDate.HasValue
? expense.endDate.HasValue
? expense.customerCode + " ( " + expense.startDate.Value.ToString("dd/MM/yyyy") + " - " + expense.endDate.Value.ToString("dd/MM/yyyy") + " )"
: expense.customerCode + " ( " + expense.startDate.Value.ToString("dd/MM/yyyy") + " - )"
: expense.customerCode
: expense.tableName == "quotation"
? string.IsNullOrEmpty(expense.number)
? "ACQ No.: "
: "ACQ No.: " + expense.number
: string.Empty;
}
if (expenses.Count()>0)
{
/*-----------Excel-----------*/
string title = "Expenses by Sub-Contractor";
string[] header = new string[]
{
"Category",
"User名稱",
"供應商",
"Maintenance/ACQ No.",
"Invoice",
"價錢",
"入帳日期",
"備註"
};
ExcelPackage excel = new ExcelPackage();
ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title);
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
int row = 1, col = 1;
#region header
sheet.Cells[1, 1].Value = title;
sheet.Cells[2, 8].Value = "Date: " + DateTime.Now.Date.ToString("dd/MM/yyyy");
row += 3;
foreach (string colName in header)
{
sheet.Cells[row, col++].Value = colName;
}
sheet.Cells[4, 1, row, col].Style.Font.Bold = true;
#endregion
++row;
string lastCategory = "";
int repeatedCategoryNum = 0;
foreach (report19_bySubCon expense in expenses)
{
col = 1;
#region merge record for each subcontractor
if (expense.category == lastCategory) repeatedCategoryNum++;
else
{
if(repeatedCategoryNum>0)
sheet.Cells[row - 1 - repeatedCategoryNum, 1, row - 1, 1].Merge = true;
repeatedCategoryNum = 0;
}
#endregion
#region data
sheet.Cells[row, col++].Value = expense.category;
sheet.Cells[row, col++].Value = expense.userName;
sheet.Cells[row, col++].Value = expense.subConName;
sheet.Cells[row, col++].Value = expense.maintenanceACQ;
sheet.Cells[row, col++].Value = expense.invoice;
sheet.Cells[row, col++].Value = expense.price;
sheet.Cells[row, col++].Value = string.Format("{0:dd/MM/yyyy}", expense.account_date);
sheet.Cells[row, col++].Value = expense.remark;
#endregion
lastCategory = expense.category;
row++;
}
#region merge expenses for the lastest sub-contractor
if (repeatedCategoryNum > 0)
{
sheet.Cells[row - 1 - repeatedCategoryNum, 1, row - 1, 1].Merge = true;
}
#endregion
#region set date and number column format
sheet.Column(7).Style.Numberformat.Format = @"dd/MM/yyyy";
sheet.Column(6).Style.Numberformat.Format = @"0.00";
#endregion
#region set autofit, alignment and wraptext
for (int i = 1; i < col; i++)
{
sheet.Column(i).AutoFit(0, 30);
sheet.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
sheet.Column(4).AutoFit(0, 30);
sheet.Cells[5, 1, row - 1, 5].Style.WrapText = true;
sheet.Cells[5, 8, row - 1, 8].Style.WrapText = true;
#endregion
sheet.Cells[1, 1, 1, 8].Merge = true;
sheet.Cells[1, 1].Style.Font.Bold = true;
sheet.Cells[1, 1].Style.Font.Size = 20;
for (int i = 1; i < col ; i++)
for (int j = 4; j < row; j++)
sheet.Cells[j, i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
sheet.PrinterSettings.LeftMargin = sheet.PrinterSettings.RightMargin = sheet.PrinterSettings.TopMargin = sheet.PrinterSettings.BottomMargin = 0.3M / 2.54M;
sheet.PrinterSettings.RepeatRows = sheet.Cells["5:5"];
sheet.PrinterSettings.Orientation = eOrientation.Landscape;
sheet.HeaderFooter.OddHeader.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
sheet.HeaderFooter.EvenHeader.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
sheet.PrinterSettings.HeaderMargin = 0.3M / 2.54M;
sheet.PrinterSettings.FitToPage = true;
sheet.PrinterSettings.FitToWidth = 1;
sheet.PrinterSettings.FitToHeight = 0;
return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", title + ".xlsx");
}
else
return Content("找不到任何紀錄。");
}
public ActionResult Report19(Report19Model model)
{
ExpenseReportModel reportModel = new ExpenseReportModel()
{
CompanyName1 = "REC ENGINEERING COMPANY LIMITED",
CompanyName2 = "REC ENGINEERING CONTRACTING COMPANY LIMITED",
Mode = model.Mode,
PrintingDate = DateTime.Now
};
DateTime? start;
DateTime? end;
if (!string.IsNullOrEmpty(model.start_date))
{
start = DateTime.ParseExact(model.start_date, DATEPICKER_DATE_FORMAT, null);
}
else
{
start = null;
}
if (!string.IsNullOrEmpty(model.end_date))
{
end = DateTime.ParseExact(model.end_date, DATEPICKER_DATE_FORMAT, null);
}
else
{
end = null;
}
DateTime? lastUpdate;
if (!string.IsNullOrEmpty(model.lastUpdateDate))
{
lastUpdate = DateTime.ParseExact(model.lastUpdateDate, DATEPICKER_DATE_FORMAT, null);
}
else
{
lastUpdate = null;
}
switch (model.Mode)
{
case Report19ModelMode.By_Category:
if (model.SelectedCostCategories == null || model.SelectedCostCategories.Count() <= 0)
return Content("請選擇Cost Category");
else
return this.Report19_ByCategory(start, end, model.SelectedCostCategories, lastUpdate, ref reportModel);
case Report19ModelMode.By_Employee:
return this.Report19_ByUser(start, end, lastUpdate, ref reportModel);
case Report19ModelMode.By_Sub__Contractor:
return this.Report19_BySubCon(start, end, lastUpdate, ref reportModel);
case Report19ModelMode.By_Supplier:
if (model.SelectedSuppliers == null || model.SelectedSuppliers.Count() <= 0)
return Content("請選擇Supplier");
else
return this.Report19_BySupplier(start, end, model.SelectedSuppliers, lastUpdate, ref reportModel);
case Report19ModelMode.By_Minor_Work:
var minor_work_cats = (from c in this._db.recsys_cost
where c.status == 1 && c.IsMinorWork == 1
select c.id);
int[] minor_work_cats_array = minor_work_cats.ToArray(); //minor-work-category-id
if (minor_work_cats_array.Length == 0) return Content("沒有設定Minor Work Category");
return this.Report19_ByCategory(start, end, minor_work_cats_array, lastUpdate, ref reportModel);
default:
return Content("請選擇報告模式");
}
}