public ReturnLine ListAll()
{
List <Line> lstLine = null;
Line line = null;
ReturnLine returnLine = new ReturnLine();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tLine_SelectAll";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
//if (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
if (sqlDr.HasRows)
{
lstLine = new List <Line>();
while (sqlDr.Read())
{
line = new Line();
line.LineID = sqlDr["LineID"].ToString();
line.LineName = sqlDr["LineName"].ToString();
line.FactoryID = sqlDr["FactoryID"].ToString();
line.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstLine.Add(line);
}
returnLine.Code = "00";
returnLine.Message = "Lấy dữ liệu thành công.";
returnLine.LstLine = lstLine;
returnLine.UserID = MyShareInfo.ID;
returnLine.UserName = MyShareInfo.UserName;
}
else
{
returnLine.Code = "01";
returnLine.Message = "Không tồn tại bản ghi nào.";
returnLine.Total = 0;
returnLine.LstLine = null;
}
}
}
}
}
catch (Exception ex)
{
returnLine.Code = "99";
returnLine.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnLine.Total = 0;
returnLine.LstLine = null;
mylog4net.Error("", ex);
}
return(returnLine);
}
public ReturnFPBCheckingItem GetbyMachineID(string MachineID, int FrequencyID)
{
List <FPBCheckingItem> lstFPBCheckingItem = null;
FPBCheckingItem FPBCheckingItem = null;
ReturnFPBCheckingItem returnFPBCheckingItem = new ReturnFPBCheckingItem();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
//cmd.Connection = _sqlConnection;
cmd.CommandText = "sp_FPBCheckingItem_SelectByMachineID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@MachineID", SqlDbType.VarChar)).Value = MachineID;
cmd.Parameters.Add(new SqlParameter("@FrequencyID", SqlDbType.Int)).Value = FrequencyID;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd, sqlConnection))
{
if (sqlDr.HasRows)
{
lstFPBCheckingItem = new List <FPBCheckingItem>();
while (sqlDr.Read())
{
FPBCheckingItem = new FPBCheckingItem();
FPBCheckingItem.IDFPBCheckingItem = int.Parse(sqlDr["IDFPBCheckingItem"].ToString());
FPBCheckingItem.MachineID = sqlDr["MachineID"].ToString();
FPBCheckingItem.CheckingItemName = sqlDr["CheckingItemName"].ToString();
FPBCheckingItem.FrequencyID = int.Parse(sqlDr["FrequencyID"].ToString());
FPBCheckingItem.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstFPBCheckingItem.Add(FPBCheckingItem);
}
returnFPBCheckingItem.Code = "00";
returnFPBCheckingItem.Message = "Lấy dữ liệu thành công.";
returnFPBCheckingItem.LstFPBCheckingItem = lstFPBCheckingItem;
}
else
{
returnFPBCheckingItem.Code = "01";
returnFPBCheckingItem.Message = "Không tồn tại bản ghi nào.";
returnFPBCheckingItem.Total = 0;
returnFPBCheckingItem.LstFPBCheckingItem = null;
}
}
}
}
}
catch (Exception ex)
{
returnFPBCheckingItem.Code = "99";
returnFPBCheckingItem.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnFPBCheckingItem.Total = 0;
returnFPBCheckingItem.LstFPBCheckingItem = null;
mylog4net.Error("", ex);
}
return(returnFPBCheckingItem);
}
public ReturnLine GetbyID(string LineID)
{
List <Line> lstLine = null;
Line line = null;
ReturnLine ReturnLine = new ReturnLine();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
//cmd.Connection = _sqlConnection;
cmd.CommandText = "sp_tLine_SelectByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@LineID", SqlDbType.VarChar)).Value = LineID.Trim();
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd, sqlConnection))
{
if (sqlDr.HasRows)
{
lstLine = new List <Line>();
while (sqlDr.Read())
{
line = new Line();
line.LineID = sqlDr["LineID"].ToString();
line.LineName = sqlDr["LineName"].ToString();
line.FactoryID = sqlDr["FactoryID"].ToString();
line.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstLine.Add(line);
}
ReturnLine.Code = "00";
ReturnLine.Message = "Lấy dữ liệu thành công.";
ReturnLine.LstLine = lstLine;
ReturnLine.Total = lstLine.Count;
//}
}
else
{
ReturnLine.Code = "01";
ReturnLine.Message = "Không tồn tại bản ghi nào.";
ReturnLine.Total = 0;
ReturnLine.LstLine = null;
}
}
}
}
}
catch (Exception ex)
{
ReturnLine.Code = "99";
ReturnLine.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
ReturnLine.Total = 0;
ReturnLine.LstLine = null;
mylog4net.Error("", ex);
}
return(ReturnLine);
}
public ReturnMachineType MachineTypeAll()
{
List <MachineType> lstMachineType = null;
MachineType machineType = null;
ReturnMachineType returnMachineType = new ReturnMachineType();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tMachineType_SelectAll";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
if (sqlDr.HasRows)
{
lstMachineType = new List <MachineType>();
while (sqlDr.Read())
{
machineType = new MachineType();
machineType.ID = int.Parse(sqlDr["ID"].ToString());
machineType.TypeName = sqlDr["TypeName"].ToString();
machineType.Description = sqlDr["Description"].ToString();
machineType.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstMachineType.Add(machineType);
}
returnMachineType.Total = lstMachineType.Count;
returnMachineType.Code = "00";
returnMachineType.Message = "Lấy dữ liệu thành công.";
returnMachineType.lstMachineType = lstMachineType;
}
else
{
returnMachineType.Code = "01";
returnMachineType.Message = "Không tồn tại bản ghi nào.";
returnMachineType.Total = 0;
returnMachineType.lstMachineType = null;
}
}
}
}
}
catch (Exception ex)
{
returnMachineType.Code = "99";
returnMachineType.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineType.Total = 0;
returnMachineType.lstMachineType = null;
mylog4net.Error("", ex);
}
return(returnMachineType);
}
public ReturnToolTypeList GetbyID(string ToolTypeID)
{
List <ToolTypeList> lstToolTypeList = null;
ToolTypeList toolTypeList = null;
ReturnToolTypeList returnToolTypeList = new ReturnToolTypeList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
//cmd.Connection = _sqlConnection;
cmd.CommandText = "sp_tToolTypeList_SelectByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ToolTypeID", SqlDbType.VarChar)).Value = ToolTypeID;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd, sqlConnection))
{
if (sqlDr.HasRows)
{
lstToolTypeList = new List <ToolTypeList>();
while (sqlDr.Read())
{
toolTypeList = new ToolTypeList();
toolTypeList.ToolTypeID = sqlDr["ToolTypeID"].ToString();
toolTypeList.ToolTypeName = sqlDr["ToolTypeName"].ToString();
toolTypeList.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstToolTypeList.Add(toolTypeList);
}
returnToolTypeList.Code = "00";
returnToolTypeList.Message = "Lấy dữ liệu thành công.";
returnToolTypeList.LstToolTypeList = lstToolTypeList;
}
else
{
returnToolTypeList.Code = "01";
returnToolTypeList.Message = "Không tồn tại bản ghi nào.";
returnToolTypeList.Total = 0;
returnToolTypeList.LstToolTypeList = null;
}
}
}
}
}
catch (Exception ex)
{
returnToolTypeList.Code = "99";
returnToolTypeList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolTypeList.Total = 0;
returnToolTypeList.LstToolTypeList = null;
mylog4net.Error("", ex);
}
return(returnToolTypeList);
}
public ReturnToolList SelectByCondition(string where)
{
List <ToolList> lstToolList = null;
ToolList toolList = null;
ReturnToolList returnToolList = new ReturnToolList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
//cmd.Connection = _sqlConnection;
cmd.CommandText = "sp_tToolList_SelectByCondition";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@where", SqlDbType.NVarChar)).Value = where;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd, sqlConnection))
{
if (sqlDr.HasRows)
{
lstToolList = new List <ToolList>();
while (sqlDr.Read())
{
toolList = new ToolList();
toolList.ToolID = sqlDr["ToolID"].ToString();
toolList.UserID = sqlDr["UserID"].ToString();
toolList.UserName = sqlDr["UserName"].ToString();
toolList.ToolType = sqlDr["ToolType"].ToString();
toolList.ItemCode = sqlDr["ItemCode"].ToString();
toolList.Maker = sqlDr["Maker"].ToString();
toolList.Specification = sqlDr["Specification"].ToString();
toolList.ReceiveDate = DateTime.Parse(sqlDr["ReceiveDate"].ToString());
toolList.StartUsing = DateTime.Parse(sqlDr["StartUsing"].ToString());
//toolList.ReceiveDate = sqlDr["ReceiveDate"].ToString();
//toolList.StartUsing = sqlDr["StartUsing"].ToString();
toolList.LifeTime = sqlDr["LifeTime"].ToString();
toolList.ExpireDate = DateTime.Parse(sqlDr["ExpireDate"].ToString());
//toolList.ExpireDate = sqlDr["ExpireDate"].ToString();
toolList.LineID = sqlDr["LineID"].ToString();
toolList.Status = sqlDr["Status"].ToString();
toolList.Remark = sqlDr["Remark"].ToString();
toolList.ImageUrl = sqlDr["ImageUrl"].ToString();
toolList.CreatedDate = DateTime.Parse(sqlDr["CreatedDate"].ToString());
toolList.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstToolList.Add(toolList);
}
returnToolList.Code = "00";
returnToolList.Message = "Lấy dữ liệu thành công.";
//_ReturnToolList.Total = Convert.ToInt32(cmd.Parameters["P_TOTAL"].Value.ToString());
returnToolList.lstToolList = lstToolList;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
//}
}
else
{
returnToolList.Code = "01";
returnToolList.Message = "Không tồn tại bản ghi nào.";
returnToolList.Total = 0;
returnToolList.lstToolList = null;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
}
}
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
public ReturnToolList SearchTools(ReturnToolList searchToolLists, int pageSize)
{
List <ToolList> lstToolList = null;
ToolList toolList = null;
ReturnToolList returnToolList = new ReturnToolList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tToolList_SearchTools";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ToolID", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.ToolID);
cmd.Parameters.Add(new SqlParameter("@ItemCode", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.ItemCode);
cmd.Parameters.Add(new SqlParameter("@Maker", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.Maker);
if (searchToolLists.aToolList.ReceiveDate == DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@ReceiveDate", SqlDbType.DateTime)).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add(new SqlParameter("@ReceiveDate", SqlDbType.DateTime)).Value = searchToolLists.aToolList.ReceiveDate;
}
if (searchToolLists.aToolList.StartUsing == DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@StartUsing", SqlDbType.DateTime)).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add(new SqlParameter("@StartUsing", SqlDbType.DateTime)).Value = searchToolLists.aToolList.StartUsing;
}
if (searchToolLists.aToolList.ExpireDate == DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@ExpireDate", SqlDbType.DateTime)).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add(new SqlParameter("@ExpireDate", SqlDbType.DateTime)).Value = searchToolLists.aToolList.ExpireDate;
}
cmd.Parameters.Add(new SqlParameter("@LineID", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.LineID);
cmd.Parameters.Add(new SqlParameter("@pageNumber", SqlDbType.Int)).Value = searchToolLists.PageNumber;
cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = pageSize;
cmd.Parameters.Add("@totalRow", SqlDbType.Int).Direction = ParameterDirection.Output;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
//if (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
if (sqlDr.HasRows)
{
lstToolList = new List <ToolList>();
while (sqlDr.Read())
{
toolList = new ToolList();
toolList.ToolID = sqlDr["ToolID"].ToString();
toolList.UserID = sqlDr["UserID"].ToString();
toolList.UserName = sqlDr["UserName"].ToString();
toolList.ToolType = sqlDr["ToolType"].ToString();
if (!String.IsNullOrEmpty(toolList.ToolType))
{
toolList.ToolTypeName = (new ToolTypeListDB()).GetToolTypeNamebyID(toolList.ToolType);
}
else
{
toolList.ToolTypeName = "";
}
toolList.ItemCode = sqlDr["ItemCode"].ToString();
toolList.Maker = sqlDr["Maker"].ToString();
toolList.Specification = sqlDr["Specification"].ToString();
toolList.ReceiveDate = DateTime.Parse(sqlDr["ReceiveDate"].ToString());
toolList.StartUsing = DateTime.Parse(sqlDr["StartUsing"].ToString());
toolList.LifeTime = sqlDr["LifeTime"].ToString();
toolList.ExpireDate = DateTime.Parse(sqlDr["ExpireDate"].ToString());
toolList.LineID = sqlDr["LineID"].ToString();
toolList.Status = sqlDr["Status"].ToString();
toolList.Remark = sqlDr["Remark"].ToString();
toolList.ImageUrl = sqlDr["ImageUrl"].ToString();
toolList.CreatedDate = DateTime.Parse(sqlDr["CreatedDate"].ToString());
toolList.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstToolList.Add(toolList);
}
returnToolList.Code = "00";
returnToolList.Message = "Lấy dữ liệu thành công.";
returnToolList.lstToolList = lstToolList;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
returnToolList.UserName = MyShareInfo.UserName;
}
else
{
returnToolList.Code = "01";
returnToolList.Message = "Không tồn tại bản ghi nào.";
returnToolList.Total = 0;
returnToolList.lstToolList = null;
returnToolList.UserName = MyShareInfo.UserName;
returnToolList.UserName = MyShareInfo.UserName;
}
}
//get return Totalpage value.
if (returnToolList.Code == "00")
{
returnToolList.Total = Convert.ToInt32(cmd.Parameters["@totalRow"].Value);
}
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
public ReturnToolList ListAll()
{
List <ToolList> lstToolList = null;
ToolList toolList = null;
ReturnToolList returnToolList = new ReturnToolList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tToolList_SelectAll";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
//if (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
if (sqlDr.HasRows)
{
lstToolList = new List <ToolList>();
while (sqlDr.Read())
{
toolList = new ToolList();
toolList.ToolID = sqlDr["ToolID"].ToString();
toolList.UserID = sqlDr["UserID"].ToString();
if (!String.IsNullOrEmpty(toolList.UserID))
{
toolList.UserName = (new UserDB()).getUserNameByID(Convert.ToInt16(toolList.UserID));
}
else
{
toolList.UserName = "";
}
toolList.ToolType = sqlDr["ToolType"].ToString();
if (!String.IsNullOrEmpty(toolList.ToolType))
{
toolList.ToolTypeName = (new ToolTypeListDB()).GetToolTypeNamebyID(toolList.ToolType);
}
else
{
toolList.ToolTypeName = "";
}
toolList.ItemCode = sqlDr["ItemCode"].ToString();
toolList.Maker = sqlDr["Maker"].ToString();
toolList.Specification = sqlDr["Specification"].ToString();
toolList.ReceiveDate = DateTime.Parse(sqlDr["ReceiveDate"].ToString());
toolList.StartUsing = DateTime.Parse(sqlDr["StartUsing"].ToString());
//toolList.ReceiveDate = sqlDr["ReceiveDate"].ToString();
//toolList.StartUsing = sqlDr["StartUsing"].ToString();
toolList.LifeTime = sqlDr["LifeTime"].ToString();
toolList.ExpireDate = DateTime.Parse(sqlDr["ExpireDate"].ToString());
//toolList.ExpireDate = sqlDr["ExpireDate"].ToString();
toolList.LineID = sqlDr["LineID"].ToString();
toolList.Status = sqlDr["Status"].ToString();
toolList.Remark = sqlDr["Remark"].ToString();
toolList.ImageUrl = sqlDr["ImageUrl"].ToString();
toolList.CreatedDate = DateTime.Parse(sqlDr["CreatedDate"].ToString());
toolList.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstToolList.Add(toolList);
}
returnToolList.Code = "00";
returnToolList.Message = "Lấy dữ liệu thành công.";
returnToolList.lstToolList = lstToolList;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
}
else
{
returnToolList.Code = "01";
returnToolList.Message = "Không tồn tại bản ghi nào.";
returnToolList.Total = 0;
returnToolList.lstToolList = null;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
}
}
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
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
}
public ReturnMachineMtnContentList GetbyID(int ID)
{
List <MachineMtnContentList> lstMachineTypeMtnContentList = null;
MachineMtnContentList machineMtnContentList = null;
ReturnMachineMtnContentList returnMachineTypeMtnContentList = new ReturnMachineMtnContentList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
//cmd.Connection = _sqlConnection;
cmd.CommandText = "sp_tMachineMtnContentList_SelectByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = ID;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd, sqlConnection))
{
if (sqlDr.HasRows)
{
lstMachineTypeMtnContentList = new List <MachineMtnContentList>();
while (sqlDr.Read())
{
machineMtnContentList = new MachineMtnContentList();
machineMtnContentList.ID = Convert.ToInt16(sqlDr["ID"].ToString());
machineMtnContentList.MachineTypeID = Convert.ToInt16(sqlDr["MachineTypeID"].ToString());
machineMtnContentList.MachinePartID = Convert.ToInt16(sqlDr["MachinePartID"].ToString());
machineMtnContentList.ContentMtn = sqlDr["ContentMtn"].ToString();
machineMtnContentList.ToolMtn = sqlDr["ToolMtn"].ToString();
machineMtnContentList.MethodMtn = sqlDr["MethodMtn"].ToString();
machineMtnContentList.Standard = sqlDr["Standard"].ToString();
machineMtnContentList.FrequencyID = Convert.ToInt16(sqlDr["FrequencyID"].ToString());
machineMtnContentList.MachinePartName = sqlDr["Name"].ToString();
machineMtnContentList.MachineTypeName = sqlDr["TypeName"].ToString();
machineMtnContentList.IsActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
machineMtnContentList.Min = sqlDr["Min"].ToString();
machineMtnContentList.Max = sqlDr["Max"].ToString();
lstMachineTypeMtnContentList.Add(machineMtnContentList);
}
returnMachineTypeMtnContentList.Code = "00";
returnMachineTypeMtnContentList.Message = "Lấy dữ liệu thành công.";
returnMachineTypeMtnContentList.lstMachineMtnContentList = lstMachineTypeMtnContentList;
}
else
{
returnMachineTypeMtnContentList.Code = "01";
returnMachineTypeMtnContentList.Message = "Không tồn tại bản ghi nào.";
returnMachineTypeMtnContentList.Total = 0;
returnMachineTypeMtnContentList.lstMachineMtnContentList = null;
}
}
}
}
}
catch (Exception ex)
{
returnMachineTypeMtnContentList.Code = "99";
returnMachineTypeMtnContentList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineTypeMtnContentList.Total = 0;
returnMachineTypeMtnContentList.lstMachineMtnContentList = null;
mylog4net.Error("", ex);
}
return(returnMachineTypeMtnContentList);
}
public ReturnMachineMtnContentList ListByPage(int page, int pageSize)
{
List <MachineMtnContentList> lstMachineTypeMtnContentList = null;
MachineMtnContentList machineMtnContentList = null;
ReturnMachineMtnContentList returnMachineTypeMtnContentList = new ReturnMachineMtnContentList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tMachineMtnContentList_SelectByPage";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@pageNumber", SqlDbType.Int)).Value = page;
cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = pageSize;
cmd.Parameters.Add("@totalRow", SqlDbType.Int).Direction = ParameterDirection.Output;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
//if (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
if (sqlDr.HasRows)
{
lstMachineTypeMtnContentList = new List <MachineMtnContentList>();
while (sqlDr.Read())
{
machineMtnContentList = new MachineMtnContentList();
machineMtnContentList.ID = Convert.ToInt16(sqlDr["ID"].ToString());
machineMtnContentList.MachineTypeID = Convert.ToInt16(sqlDr["MachineTypeID"].ToString());
machineMtnContentList.MachinePartID = Convert.ToInt16(sqlDr["MachinePartID"].ToString());
machineMtnContentList.ContentMtn = sqlDr["ContentMtn"].ToString();
machineMtnContentList.ToolMtn = sqlDr["ToolMtn"].ToString();
machineMtnContentList.MethodMtn = sqlDr["MethodMtn"].ToString();
machineMtnContentList.Standard = sqlDr["Standard"].ToString();
machineMtnContentList.FrequencyID = Convert.ToInt16(sqlDr["FrequencyID"].ToString());
machineMtnContentList.MachinePartName = sqlDr["Name"].ToString();
machineMtnContentList.MachineTypeName = sqlDr["TypeName"].ToString();
machineMtnContentList.IsActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
machineMtnContentList.Min = sqlDr["Min"].ToString();
machineMtnContentList.Max = sqlDr["Max"].ToString();
lstMachineTypeMtnContentList.Add(machineMtnContentList);
}
returnMachineTypeMtnContentList.Code = "00";
returnMachineTypeMtnContentList.Message = "Lấy dữ liệu thành công.";
returnMachineTypeMtnContentList.lstMachineMtnContentList = lstMachineTypeMtnContentList;
returnMachineTypeMtnContentList.UserID = MyShareInfo.ID;
returnMachineTypeMtnContentList.UserName = MyShareInfo.UserName;
returnMachineTypeMtnContentList.PageNumber = page;
}
else
{
returnMachineTypeMtnContentList.Code = "01";
returnMachineTypeMtnContentList.Message = "Không tồn tại bản ghi nào.";
returnMachineTypeMtnContentList.Total = 0;
returnMachineTypeMtnContentList.lstMachineMtnContentList = null;
}
//get return Totalpage value.
}
if (returnMachineTypeMtnContentList.Code == "00")
{
returnMachineTypeMtnContentList.Total = Convert.ToInt32(cmd.Parameters["@totalRow"].Value);
}
}
}
}
catch (Exception ex)
{
returnMachineTypeMtnContentList.Code = "99";
returnMachineTypeMtnContentList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineTypeMtnContentList.Total = 0;
returnMachineTypeMtnContentList.lstMachineMtnContentList = null;
mylog4net.Error("", ex);
}
return(returnMachineTypeMtnContentList);
}