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()));
}
//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);
}