private System.Web.Mvc.FileStreamResult CreateExcel_2(MachineMtnReportDataSearch machineMtnReportDataSearch, IEnumerable <MachineMtnReportData> data, string fileName, params delegate_BuildExcelHandler[] BuildExcels)
{
var package = GetExcelData_2(machineMtnReportDataSearch, data, BuildExcels);
var fileDownloadName = fileName;
var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var fileStream = new MemoryStream();
package.SaveAs(fileStream);
fileStream.Position = 0;
var fsr = new System.Web.Mvc.FileStreamResult(fileStream, contentType);
fsr.FileDownloadName = fileDownloadName;
return(fsr);
}
private ExcelPackage GetExcelData_2(MachineMtnReportDataSearch machineMtnReportDataSearch, IEnumerable <MachineMtnReportData> data, delegate_BuildExcelHandler[] myDelegate_BuildExcels)
{
try
{
var excelPackage = new ExcelPackage();
List <BuildArgs> buildArgs = new List <BuildArgs>();
foreach (var BuildExcel in myDelegate_BuildExcels)
{
BuildExcel(machineMtnReportDataSearch, data, excelPackage, buildArgs);
}
return(excelPackage);
}
catch (Exception ex)
{
throw ex;
}
}
public void CreateReport(MachineMtnReportDataSearch machineMtnReportDataSearch)
{
#region get data.
ReturnMachineMtnReportData reportData = new ReturnMachineMtnReportData();
reportData = getData(machineMtnReportDataSearch);
#endregion
#region Create simple group report excel.
//create excel file.
//group report.
//-- Group MachinePartName.
//-- Group Machine.
//insert to excel.
#endregion
#region merge excel.
#endregion
}
//public void CreateAllExcels(MachineMtnReportDataSearch machineMtnReportDataSearch)
//{
// #region get data.
// MachineMtnReportDatasDB machineMtnReportDatasDB = new MachineMtnReportDatasDB();
// ReturnMachineMtnReportData reportData = machineMtnReportDatasDB.getData(machineMtnReportDataSearch);
// #endregion
// IEnumerable<MachineMtnReportData> data = reportData.lstMachineMtnReportData.AsEnumerable<MachineMtnReportData>();
// CreateExcel(machineMtnReportDataSearch, data, "MachineMtnReport_onlyGroup.xlsx",
// MachineMtnReportDatas_GetAllValues
// );
//}
//private void CreateExcel(MachineMtnReportDataSearch machineMtnReportDataSearch, IEnumerable<MachineMtnReportData> data, string fileName, params delegate_BuildExcelHandler[] BuildExcels)
//{
// byte[] excel = GetExcelData(machineMtnReportDataSearch, data, BuildExcels);
// SaveExcelToFile(fileName, excel);
//}
//private byte[] GetExcelData(MachineMtnReportDataSearch machineMtnReportDataSearch, IEnumerable<MachineMtnReportData> data, delegate_BuildExcelHandler[] myDelegate_BuildExcels)
//{
// using (var excelPackage = new ExcelPackage())
// {
// List<BuildArgs> buildArgs = new List<BuildArgs>();
// foreach (var BuildExcel in myDelegate_BuildExcels)
// BuildExcel(machineMtnReportDataSearch, data, excelPackage, buildArgs);
// return excelPackage.GetAsByteArray();
// }
//}
public System.Web.Mvc.FileStreamResult CreateAllExcels_2(MachineMtnReportDataSearch machineMtnReportDataSearch)
{
#region get data.
MachineMtnReportDatasDB machineMtnReportDatasDB = new MachineMtnReportDatasDB();
ReturnMachineMtnReportData reportData = machineMtnReportDatasDB.getData(machineMtnReportDataSearch);
#endregion
IEnumerable <MachineMtnReportData> data = reportData.lstMachineMtnReportData.AsEnumerable <MachineMtnReportData>();
List <delegate_BuildExcelHandler> buildExcels = new List <delegate_BuildExcelHandler>();
string fileName = "";
fileName += "BaoTriMay";
if (machineMtnReportDataSearch.FrequencyID == SMCommon.MachineMtnFrequency_Daily)
{
fileName += "_HangNgay";
buildExcels.Add(MachineMtnReportDatas_GetExcel_Daily);
}
fileName += "MachineID_" + machineMtnReportDataSearch.MachineID;
fileName += "MachineName_" + machineMtnReportDataSearch.MachineName;
fileName += "_" + DateTime.Now.ToString("MM.dd.yyyy.HH.mm.ss");
fileName += ".xlsx";
return(CreateExcel_2(machineMtnReportDataSearch, data, fileName, buildExcels.ToArray()));
}
private void MachineMtnReportDatas_GetExcel_Daily(MachineMtnReportDataSearch machineMtnReportDataSearch, IEnumerable <MachineMtnReportData> data, ExcelPackage ep, List <BuildArgs> buildArgs)
{
//init value.
//fix cứng số cột: số thự tự tên nước, saleperson, Year và danh sách 12 tháng (orderMonthFromIndex --> orderMonthToIndex)
string prefixTotalColumn = "_*_";
int startRowIdx = 8; // title table index.
int machinePartName_ColIdx = 2;
int contentMtn_ColIdx = 3;
int toolMtn_ColIdx = 4;
int methodMtn_ColIdx = 5;
int standard_ColIdx = 6;
int runningFrom_ColIdx = 7;
int DayAndShiftOrder_FromColIdx = runningFrom_ColIdx;
int DayAndShiftOrder_ToColIdx = 0;
int totalColumns_ColIdx = 0; // tổng số cột.
int rowIndex = startRowIdx;
//total Result index.
int totalResult_RowIdx = 0;
int OperatorRowIdx;
int CheckerRowIdx;
// workbook
var wb = ep.Workbook;
// new worksheet
var ws = wb.Worksheets.Add("Sheet01");
#region Title.
ws.Cells[2, 7].Value = ("Bảng kiểm tra bảo trì máy/Machine maintenance checksheet").ToUpper();
ws.Cells[2, 7].Style.Font.Bold = true;
ws.Cells[2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[2, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 2,
FromColumn = 7,
ToRow = 2,
ToColumn = 14
});
using (ExcelRange Rng = ws.Cells[3, 7]) //machineid.
{
Rng.Style.Font.Size = 11;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("MachineID: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.MachineID);
RichText.Bold = true;
RichText = RichTxtCollection.Add(" Machine Name: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.MachineName);
RichText.Bold = true;
}
ws.Cells[3, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[3, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 3,
FromColumn = 7,
ToRow = 3,
ToColumn = 14
});
using (ExcelRange Rng = ws.Cells[4, 7]) //FromDate ToDate
{
Rng.Style.Font.Size = 11;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("Từ ngày/FromDate: ");
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.FromDate.ToString("MM/dd/yyyy"));
RichText.Bold = true;
RichText = RichTxtCollection.Add(" Đến ngày/ToDate: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.ToDate.ToString("MM/dd/yyyy"));
RichText.Bold = true;
}
ws.Cells[4, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[4, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 4,
FromColumn = 7,
ToRow = 4,
ToColumn = 14
});
#endregion
#region Header.
// Header Value.
ws.Cells[rowIndex, machinePartName_ColIdx].Value = "Part Name";
ws.Cells[rowIndex, contentMtn_ColIdx].Value = "Content";
ws.Cells[rowIndex, toolMtn_ColIdx].Value = "Tool";
ws.Cells[rowIndex, methodMtn_ColIdx].Value = "Method";
ws.Cells[rowIndex, standard_ColIdx].Value = "Standard";
//-- MaintenanceDate headers
var dict_DayOrder = new Dictionary <string, int>();
int countDayAndShiftOrderColumn = 0;
var dict_DayAndShiftOrder = new Dictionary <string, int>();
for (var day = machineMtnReportDataSearch.FromDate; day <= machineMtnReportDataSearch.ToDate; day = day.AddDays(1))
{
int oneDay_FullShift_FromColIndex = DayAndShiftOrder_FromColIdx + countDayAndShiftOrderColumn;
for (int i = 0; i <= 2; i++) // i = order number column in excel, i++ = shift number.
{
//date header.
ws.Cells[rowIndex, (countDayAndShiftOrderColumn) + runningFrom_ColIdx].Value = day.ToString("MM/dd", enUS);
//shift header.
ws.Cells[rowIndex + 1, countDayAndShiftOrderColumn + runningFrom_ColIdx].Value = (i + 1).ToString();
dict_DayOrder.Add(day.ToString("MM/dd", enUS) + "_" + (i + 1).ToString(), countDayAndShiftOrderColumn); // sample "15/3_1" = date is 15/3 and shift = 1
dict_DayAndShiftOrder.Add(day.ToString("MM/dd", enUS) + (i + 1).ToString(), countDayAndShiftOrderColumn);
countDayAndShiftOrderColumn++;
}
int oneDay_FullShift_ToColIndex = oneDay_FullShift_FromColIndex + 2;
//merge cell MaintenanceDate.
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = rowIndex,
FromColumn = oneDay_FullShift_FromColIndex,
ToRow = rowIndex,
ToColumn = oneDay_FullShift_ToColIndex
});
//intDayOrder++;
}
DayAndShiftOrder_ToColIdx = DayAndShiftOrder_FromColIdx + countDayAndShiftOrderColumn - 1;
totalColumns_ColIdx = DayAndShiftOrder_ToColIdx;
// Headers style
using (var cell = ws.Cells[rowIndex, machinePartName_ColIdx, rowIndex + 1, totalColumns_ColIdx])
{
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 11;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
rowIndex++;
rowIndex++;
#endregion
//checking data.
if (data == null)
{
return;
}
#region Add TotalResult.
var maintenanceDateGroups = data.GroupBy(d => d.MaintenanceDate);
foreach (var maintenanceDateGroup in maintenanceDateGroups)
{
MachineMtnReportData tMachineMtn = maintenanceDateGroup.FirstOrDefault();
MachineMtnReportData TotalResult_Row = new MachineMtnReportData();
TotalResult_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
TotalResult_Row.Shift = tMachineMtn.Shift;
TotalResult_Row.FrequencyID = tMachineMtn.FrequencyID;
TotalResult_Row.MachinePartID = int.MaxValue;
TotalResult_Row.MachinePartName = "zzzzz01" + prefixTotalColumn + "Total Result";
TotalResult_Row.MtnDetailResult = tMachineMtn.TotalResult;
TotalResult_Row.MtnDetailResultContents = tMachineMtn.TotalResultContents;
MachineMtnReportData Operator_Row = new MachineMtnReportData();
Operator_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
Operator_Row.Shift = tMachineMtn.Shift;
Operator_Row.MachinePartID = int.MaxValue;
Operator_Row.MachinePartName = "zzzzz02" + prefixTotalColumn + "Operator";
Operator_Row.MtnDetailResult = tMachineMtn.OperatorName;
MachineMtnReportData Checker_Row = new MachineMtnReportData();
Checker_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
Checker_Row.Shift = tMachineMtn.Shift;
Checker_Row.MachinePartID = int.MaxValue;
Checker_Row.MachinePartName = "zzzzz03" + prefixTotalColumn + "Checker";
Checker_Row.MtnDetailResult = tMachineMtn.CheckerName;
IEnumerable <MachineMtnReportData> x = new MachineMtnReportData[] { TotalResult_Row, Operator_Row, Checker_Row };
data = data.Concat(x);
}
#endregion
//***************** Data *************
#region MachinePartName.
var machinePartNameGroups = data.GroupBy(d => new { d.MachinePartID, d.MachinePartName }).OrderBy(g => g.Key.MachinePartName);
#endregion
//var Start_RunningFromRowIndex = rowIndex;
foreach (var machinePartNameGroup in machinePartNameGroups)
{
int machinePartNameGroup_FromRowIndex = rowIndex;
#region ContentMtn.
int contentMtnFromRowIndex = rowIndex;
var contentMtnGroups = machinePartNameGroup.GroupBy(d => d.ContentMtn).OrderBy(g => g.Key);
#endregion
foreach (var contentMtnGroup in contentMtnGroups)
{
int contentMtnGroup_FromRowIndex = rowIndex;
#region ToolMtn.
int toolMtnFromRowIndex = rowIndex;
var toolMtnGroups = contentMtnGroup.GroupBy(d => d.ToolMtn).OrderBy(g => g.Key);
#endregion
foreach (var toolMtnGroup in toolMtnGroups)
{
int toolMtnGroup_FromRowIndex = rowIndex;
#region MethodMtn.
int methodMtnFromRowIndex = rowIndex;
var methodMtnGroups = toolMtnGroup.GroupBy(d => d.MethodMtn).OrderBy(g => g.Key);
#endregion
foreach (var methodMtnGroup in methodMtnGroups)
{
#region Standard.
int standard_FromRowIndex = rowIndex;
var standardGroups = methodMtnGroup.GroupBy(d => d.Standard).OrderBy(g => g.Key);
foreach (var standardGroup in standardGroups)
{
//insert MachinePartName, Tool, Content, ......
string _machinePartName = machinePartNameGroup.Key.MachinePartName;
if (_machinePartName.Contains(prefixTotalColumn)) //totalResult.
{
if (_machinePartName.Contains("zzzzz01")) //get totalResult Row index.
{
totalResult_RowIdx = rowIndex;
}
_machinePartName = _machinePartName.Remove(0, _machinePartName.IndexOf(prefixTotalColumn) + prefixTotalColumn.Length);
}
ws.Cells[rowIndex, machinePartName_ColIdx].Value = _machinePartName;
ws.Cells[rowIndex, contentMtn_ColIdx].Value = contentMtnGroup.Key;
ws.Cells[rowIndex, toolMtn_ColIdx].Value = toolMtnGroup.Key;
ws.Cells[rowIndex, methodMtn_ColIdx].Value = methodMtnGroup.Key;
ws.Cells[rowIndex, standard_ColIdx].Value = standardGroup.Key;
#region Maintenance Days.
int maintenanceDayFromRowIndex = rowIndex;
var maintenanceDayGroups = standardGroup.GroupBy(d => d.MaintenanceDate.ToString("MM/dd")).OrderBy(g => g.Key);
#endregion
int orderDay = 1; // *************** checking aaaa : orderday: phai lay theo thu tu cua methodMtnGroup
ws.Cells[rowIndex, DayAndShiftOrder_FromColIdx, rowIndex, DayAndShiftOrder_ToColIdx].Value = "";
foreach (var maintenanceDayGroup in maintenanceDayGroups)
{
var mtnDayShiftGroups = maintenanceDayGroup.GroupBy(d => d.Shift);
#region one day.
foreach (var mtnDayShiftGroup in mtnDayShiftGroups)
{
#region one day and one shift.
// ******************* Gán cụ thể vào Excel.
DateTime currentMtnDate = mtnDayShiftGroup.FirstOrDefault().MaintenanceDate; //.ToString("MM/dd", enUS);
MachineMtnReportData currentMtnReportData = mtnDayShiftGroup.FirstOrDefault();
int currentOrderDayShift = dict_DayOrder[currentMtnDate.ToString("MM/dd", enUS) + "_" + currentMtnReportData.Shift];
var currentCells = ws.Cells[rowIndex, currentOrderDayShift - 1 + runningFrom_ColIdx];
//set value for cell.
currentCells.Value = currentMtnReportData.MtnDetailResult;
//set color for OK, NG.
if (currentMtnReportData.MtnDetailResult == SMCommon.MachineMtnResult_OK)
{
currentCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
currentCells.Style.Fill.BackgroundColor.SetColor(SMCommon.ReportMachineMtn_Result_OK_Color);
}
else if (currentMtnReportData.MtnDetailResult == SMCommon.MachineMtnResult_NG)
{
currentCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
currentCells.Style.Fill.BackgroundColor.SetColor(SMCommon.ReportMachineMtn_Result_NG_Color);
}
orderDay++;
#endregion
}
#endregion
}
rowIndex++;
//Borders.
}
int standard_ToRowIndex = rowIndex - 1;
#endregion
}
int toolMtnGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = toolMtnGroup_FromRowIndex,
FromColumn = toolMtn_ColIdx,
ToRow = rowIndex - 1,
ToColumn = toolMtn_ColIdx
});
}
int contentMtnGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = contentMtnGroup_FromRowIndex,
FromColumn = contentMtn_ColIdx,
ToRow = rowIndex - 1,
ToColumn = contentMtn_ColIdx
});
}
int machinePartNameGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = machinePartNameGroup_FromRowIndex,
FromColumn = machinePartName_ColIdx,
ToRow = rowIndex - 1,
ToColumn = machinePartName_ColIdx
});
}
#region Footer
#endregion
#region Border Style.
int toRowIndex = rowIndex - 1;
// cells borders
ws.View.ShowGridLines = false;
foreach (var cell in ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx])
{
cell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Top.Color.SetColor(Color.Black);
cell.Style.Border.Bottom.Color.SetColor(Color.Black);
cell.Style.Border.Left.Color.SetColor(Color.Black);
cell.Style.Border.Right.Color.SetColor(Color.Black);
}
#endregion
#region Manipulate Cells (merge).
// merge cells
foreach (var args in buildArgs.Where(ba => ba.Action == BuildAction.Merge))
{
using (var cells = args.Worksheet.Cells[args.FromRow, args.FromColumn, args.ToRow, args.ToColumn])
{
cells.Merge = true;
}
}
// Vertical Alignment body table.
ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// new line Cell.
foreach (var cell in ws.Cells[startRowIdx, machinePartName_ColIdx, toRowIndex, standard_ColIdx])
{
if (cell.Value != null)
{
cell.Value = cell.Value.ToString().Replace("/", Environment.NewLine);
}
}
// wordwrap entire table.
using (var AllCellsTables = ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx])
{
AllCellsTables.Style.WrapText = true;
//AllCellsTables.AutoFitColumns();
}
//set height Row.
for (int intRow = startRowIdx; intRow < toRowIndex; intRow++)
{
if (ws.Cells[intRow, machinePartName_ColIdx] != null && ws.Cells[intRow, machinePartName_ColIdx].Value != null)
{
ws.Row(intRow).Height = SMCommon.MeasureTextHeight(ws.Cells[intRow, machinePartName_ColIdx].Value.ToString(), ws.Cells[intRow, machinePartName_ColIdx].Style.Font, 1);
}
}
//set width column.
ws.Column(machinePartName_ColIdx).Width = 25;
ws.Column(contentMtn_ColIdx).Width = 25;
ws.Column(toolMtn_ColIdx).Width = 25;
ws.Column(methodMtn_ColIdx).Width = 20;
ws.Column(standard_ColIdx).Width = 20;
#endregion
}
//MachinetMtnReport step
public ReturnMachineMtnReportData getData(MachineMtnReportDataSearch machineMtnReportDataSearch)
{
ReturnMachineMtnReportData returnMachineMtnReportData = new ReturnMachineMtnReportData();
if (String.IsNullOrEmpty(machineMtnReportDataSearch.MachineID))
{
returnMachineMtnReportData.Code = "99";
returnMachineMtnReportData.Message = "Không có MachineID/Input MachineID! ";
returnMachineMtnReportData.Total = 0;
returnMachineMtnReportData.lstMachineMtnReportData = new List <MachineMtnReportData>();
return(returnMachineMtnReportData);
}
List <MachineMtnReportData> lstMachineMtnReportData = null;
MachineMtnReportData MachineMtnReportData = null;
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tMachineMtnReportDatas";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MachineID", machineMtnReportDataSearch.MachineID);
cmd.Parameters.AddWithValue("@OperatorID", machineMtnReportDataSearch.OperatorID);
cmd.Parameters.AddWithValue("@Shift", machineMtnReportDataSearch.Shift);
cmd.Parameters.AddWithValue("@FrequencyID ", machineMtnReportDataSearch.FrequencyID);
if (machineMtnReportDataSearch.MaintenanceDate != DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@MaintenanceDate", SqlDbType.DateTime)).Value = machineMtnReportDataSearch.MaintenanceDate;
}
cmd.Parameters.Add(new SqlParameter("@FromDate", SqlDbType.DateTime)).Value = machineMtnReportDataSearch.FromDate;
cmd.Parameters.Add(new SqlParameter("@ToDate", SqlDbType.DateTime)).Value = machineMtnReportDataSearch.ToDate;
cmd.Parameters.AddWithValue("@FromInt", machineMtnReportDataSearch.FromInt);
cmd.Parameters.AddWithValue("@ToInt", machineMtnReportDataSearch.ToInt);
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
if (sqlDr.HasRows)
{
lstMachineMtnReportData = new List <MachineMtnReportData>();
while (sqlDr.Read())
{
MachineMtnReportData = new MachineMtnReportData();
//MachineMtnReportData.MachineID = sqlDr["MachineID"].ToString();
//MachineMtnReportData.MachineName = sqlDr["MachineName"].ToString();
MachineMtnReportData.OperatorID = sqlDr["OperatorID"].ToString();
//MachineMtnReportData.OperatorName = sqlDr["UserName"].ToString(); // UserName in Sql = operatorName in C#
if (!String.IsNullOrEmpty(sqlDr["Shift"].ToString()))
{
MachineMtnReportData.Shift = Convert.ToInt16(sqlDr["Shift"].ToString());
}
MachineMtnReportData.MaintenanceDate = Convert.ToDateTime(sqlDr["MaintenanceDate"].ToString());
//MachineMtnReportData.FrequencyID = Convert.ToInt16(sqlDr["FrequencyID"].ToString());
MachineMtnReportData.TotalResult = sqlDr["TotalResult"] != null ? sqlDr["TotalResult"].ToString() : "";
//if (!String.IsNullOrEmpty(sqlDr["TotalResultContents"].ToString()))
// MachineMtnReportData.TotalResultContents = sqlDr["TotalResultContents"].ToString();
if (!String.IsNullOrEmpty(sqlDr["Month"].ToString()))
{
MachineMtnReportData.Month = Convert.ToInt16(sqlDr["Month"].ToString());
}
if (!String.IsNullOrEmpty(sqlDr["Year"].ToString()))
{
MachineMtnReportData.Year = Convert.ToInt16(sqlDr["Year"].ToString());
}
if (!String.IsNullOrEmpty(sqlDr["Week"].ToString()))
{
MachineMtnReportData.Week = Convert.ToInt16(sqlDr["Week"].ToString());
}
MachineMtnReportData.CheckerID = Convert.ToInt16(sqlDr["CheckerID"].ToString());
if (!String.IsNullOrEmpty(sqlDr["MachinePartID"].ToString()))
{
MachineMtnReportData.MachinePartID = Convert.ToInt16(sqlDr["MachinePartID"].ToString());
MachineMtnReportData.MachinePartName = sqlDr["MachinePartName"].ToString();
}
MachineMtnReportData.ContentMtn = sqlDr["ContentMtn"].ToString();
MachineMtnReportData.ToolMtn = sqlDr["ToolMtn"].ToString();
MachineMtnReportData.MethodMtn = sqlDr["MethodMtn"].ToString();
MachineMtnReportData.Standard = sqlDr["Standard"].ToString();
MachineMtnReportData.MtnDetailResult = sqlDr["MtnDetailResult"].ToString();
MachineMtnReportData.MtnDetailResultContents = sqlDr["MtnDetailResultContents"].ToString();
//get Checker name.
int _checkerID = 0;
MachineMtnReportData.CheckerName = "";
if (Int32.TryParse(sqlDr["CheckerID"].ToString(), out _checkerID))
{
ReturnUser returnUser = (new UserDB()).GetbyID(_checkerID);
if (returnUser.Code == "00")
{
MachineMtnReportData.CheckerName = returnUser.lstUser[0].UserName;
}
}
MachineMtnReportData.CheckerResult = sqlDr["CheckerResult"].ToString();
lstMachineMtnReportData.Add(MachineMtnReportData);
}
returnMachineMtnReportData.Code = "00";
returnMachineMtnReportData.Message = "Lấy dữ liệu thành công.";
returnMachineMtnReportData.lstMachineMtnReportData = lstMachineMtnReportData;
}
else
{
returnMachineMtnReportData.Code = "01";
returnMachineMtnReportData.Message = "Không tồn tại bản ghi nào.";
returnMachineMtnReportData.Total = 0;
returnMachineMtnReportData.lstMachineMtnReportData = null;
}
}
//paging.
//if (returnMachineMtnReportData.Code == "00")
// returnMachineMtnReportData.Total = Convert.ToInt32(cmd.Parameters["@totalRow"].Value);
}
}
}
catch (Exception ex)
{
returnMachineMtnReportData.Code = "99";
returnMachineMtnReportData.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineMtnReportData.Total = 0;
returnMachineMtnReportData.lstMachineMtnReportData = null;
mylog4net.Error("public ReturnMachineMtnReportData SelectByCondition(string where, int pageSize) ", ex);
}
return(returnMachineMtnReportData);
}
