public List <DefectRateData> GetListDefectRateReportFromTo(string Dept, string codeProcess, DateTime from, DateTime to)
{
List <DefectRateData> defectRates = new List <DefectRateData>();
try
{
LoadDataSummary loadData = new LoadDataSummary();
List <MQCItemSummary> ListmQCItems = loadData.GetMQCItemSummariesFromTo(from, to, Dept, "MQC");
foreach (var mQCItems in ListmQCItems)
{
DefectRateData defectRate = new DefectRateData();
defectRate.Lot = mQCItems.Lot;
defectRate.Line = mQCItems.Line;
defectRate.Product = mQCItems.product;
defectRate.DateTime_from = mQCItems.Time_from;
defectRate.DateTime_to = mQCItems.Time_To;
defectRate.TotalQuantity = mQCItems.QuantityTotal;
defectRate.ReworkQuantity = mQCItems.ReworkQty;
defectRate.ReworkRate = mQCItems.ReworkRate;
defectRate.DefectQuantity = mQCItems.NGQty;
defectRate.OutputQuantity = mQCItems.OutputQty;
defectRate.DefectRate = (defectRate.TotalQuantity != 0) ? (defectRate.DefectQuantity / defectRate.TotalQuantity) : 0;
LoadDefectMapping loadDefectTop13 = new LoadDefectMapping();
List <NGItemsMapping> listTop13 = loadDefectTop13.listNGMappingGetReportTop13(Dept, "MQC");
List <DefectItem> listDefectTop13 = new List <DefectItem>();
for (int i = 0; i < listTop13.Count; i++)
{
var getlist = mQCItems.defectItems.Where(d => d.DefectSFT == listTop13[i].NGCode_SFT).ToList();
DefectItem defect = new DefectItem();
if (getlist != null && getlist.Count > 0)
{
defect = getlist[0];
defect.Quantity = getlist.Select(s => s.Quantity).Sum();
}
defect.Note = listTop13[i].Note;
listDefectTop13.Add(defect);
}
var listDefectTop13Groupby = listDefectTop13.OrderBy(d => d.Note).ToList();
defectRate.defectItems = listDefectTop13Groupby;
defectRate.ReworkItems = mQCItems.ReworkItems;
defectRate.TargetMQC = new TargetMQC();
LoadTargetProduction loadTarget = new LoadTargetProduction();
TimeSpan timeSpan = to - from;
defectRate.TargetMQC = loadTarget.GetArraystarget(defectRate.Product, from, from.AddDays(Math.Round(timeSpan.TotalDays - 1, 0)));
defectRates.Add(defectRate);
}
}
catch (Exception ex)
{
Logfile.Output(StatusLog.Error, "GetDefectRateReport(DateTime from, DateTime to, string Dept, string codeProcess)", ex.Message);
}
return(defectRates);
}
public List <DefectRateData> GetListDefectRateReportAmountOfTimeDaily(string Dept, string codeProcess, PeriodProduction period)
{
List <DefectRateData> defectRates = new List <DefectRateData>();
try
{
LoadDataSummary loadData = new LoadDataSummary();
List <MQCItemSummary> ListmQCItems = loadData.GetMQCItemSummaries(period, Dept, "MQC");
foreach (var mQCItems in ListmQCItems)
{
DefectRateData defectRate = new DefectRateData();
defectRate.Lot = mQCItems.Lot;
defectRate.Line = mQCItems.Line;
defectRate.Product = mQCItems.product;
defectRate.DateTime_from = mQCItems.Time_from;
defectRate.DateTime_to = mQCItems.Time_To;
defectRate.TotalQuantity = mQCItems.QuantityTotal;
defectRate.ReworkQuantity = mQCItems.ReworkQty;
defectRate.ReworkRate = mQCItems.ReworkRate;
defectRate.DefectQuantity = mQCItems.NGQty;
defectRate.OutputQuantity = mQCItems.OutputQty;
defectRate.DefectRate = (defectRate.TotalQuantity != 0) ? (defectRate.DefectQuantity / defectRate.TotalQuantity) : 0;
LoadDefectMapping loadDefectTop13 = new LoadDefectMapping();
List <NGItemsMapping> listTop13 = loadDefectTop13.listNGMappingGetReportTop13(Dept, "MQC");
List <DefectItem> listDefectTop13 = new List <DefectItem>();
for (int i = 0; i < listTop13.Count; i++)
{
var getlist = mQCItems.defectItems.Where(d => d.DefectSFT == listTop13[i].NGCode_SFT).ToList();
DefectItem defect = new DefectItem();
if (getlist != null && getlist.Count > 0)
{
defect = getlist[0];
defect.Quantity = getlist.Select(s => s.Quantity).Sum();
}
defect.Note = listTop13[i].Note;
listDefectTop13.Add(defect);
}
var listDefectTop13Groupby = listDefectTop13.OrderBy(d => d.Note).ToList();
defectRate.defectItems = listDefectTop13Groupby;
DateTime dateTarget = DateTime.Now.Date;
if (period == PeriodProduction.AllDay)
{
dateTarget = DateTime.Now.Date.AddDays(-1);
}
else if (period == PeriodProduction.dayshift)
{
dateTarget = DateTime.Now.Date;
}
else if (period == PeriodProduction.nightshift)
{
dateTarget = DateTime.Now.Date.AddDays(-1);
}
defectRate.TargetMQC = new TargetMQC();
LoadTargetProduction loadTarget = new LoadTargetProduction();
defectRate.TargetMQC = loadTarget.GetTargetMQC(defectRate.Product, dateTarget.ToString("yyyyMMdd"));
defectRates.Add(defectRate);
}
}
catch (Exception ex)
{
Logfile.Output(StatusLog.Error, "GetDefectRateReport(DateTime from, DateTime to, string Dept, string codeProcess)", ex.Message);
}
return(defectRates);
}
public void ExportToTemplateMQCDefect(string PathTemplate, string pathSaveExcel, DefectRateData defectRate)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet; //sheet 2
//Excel.Worksheet xlWorkSheet1; //sheet 1
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(PathTemplate, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
#region Sheet 1
//Add data in Sheet 1
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2); //add data sheet1
//xlWorkSheet.Cells[6, 1] = "BackLog Report on " + DateTime.Now.ToString("MMM/dd/yyyy"); //Line
string strWorksheetName = xlWorkSheet.Name; //Get the name of worksheet. //xlWorkSheet.Cells[1, 11] = dateupdate; //Line
if (strWorksheetName == "优減")
{ //Fill ngay thang nam //xlWorkSheet.Cells[2, 11] = usersend; //Model
string date = DateTime.Now.ToString("yyyy.MM.dd");
xlWorkSheet.Cells[3, 10] = date; //xlWorkSheet.Cells[3, 11] = version; //User
xlWorkSheet.Cells[4, 2] = defectRate.TotalQuantity;
xlWorkSheet.Cells[4, 6] = defectRate.DefectQuantity;
xlWorkSheet.Cells[17, 2] = date;
double countDefect = 0;
// xlWorkSheet.Cells[2, 31] = DateTime.Now.ToString("MM");
for (int i = 0; i < defectRate.defectItems.Count; i++)
{
if (defectRate.defectItems[i].Note == (i + 1))
{
xlWorkSheet.Cells[7, 2 + i] = defectRate.defectItems[i].Quantity;
countDefect += defectRate.defectItems[i].Quantity;
}
}
xlWorkSheet.Cells[7, 7] = defectRate.DefectQuantity - countDefect;
}
#endregion
xlWorkBook.SaveAs(pathSaveExcel, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue,
misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close();
// MessageBox.Show("Excel file created, you can find in the folder " + pathSaveExcel, "Notice", MessageBoxButtons.OK, MessageBoxIcon.Information);
//xlWorkBook.Close(true, misValue, misValue);
//xlApp.Workbooks.Open(pathSaveExcel);
//xlApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("An error happened in the process.");
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public void ExportToTemplateMQCDefectTop16(string PathTemplate, string pathSaveExcel, DefectRateData defectRate)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet; //sheet 2
//Excel.Worksheet xlWorkSheet1; //sheet 1
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(PathTemplate, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
#region Sheet 1
//Add data in Sheet 1
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //add data sheet1
//xlWorkSheet.Cells[6, 1] = "BackLog Report on " + DateTime.Now.ToString("MMM/dd/yyyy"); //Line
string strWorksheetName = xlWorkSheet.Name; //Get the name of worksheet. //xlWorkSheet.Cells[1, 11] = dateupdate; //Line
if (strWorksheetName == "报表")
{ //Fill ngay thang nam //xlWorkSheet.Cells[2, 11] = usersend; //Model
string date = DateTime.Now.ToString("yyyy.MM.dd");
xlWorkSheet.Cells[2, 11] = DateTime.Now.ToString("dd");
xlWorkSheet.Cells[2, 13] = DateTime.Now.ToString("MM");
xlWorkSheet.Cells[2, 15] = DateTime.Now.ToString("yyyy");
xlWorkSheet.Cells[6, 1] = defectRate.Product;//xlWorkSheet.Cells[3, 11] = version; //User
xlWorkSheet.Cells[6, 2] = defectRate.Lot;
xlWorkSheet.Cells[6, 3] = defectRate.DateTime_from + "-" + defectRate.DateTime_to;
xlWorkSheet.Cells[6, 4] = defectRate.TotalQuantity;
xlWorkSheet.Cells[6, 23] = defectRate.OutputQuantity;
xlWorkSheet.Cells[6, 24] = defectRate.DefectQuantity;
xlWorkSheet.Cells[6, 25] = defectRate.DefectRate;
double countDefect = 0;
// xlWorkSheet.Cells[2, 31] = DateTime.Now.ToString("MM");
for (int i = 0; i < defectRate.defectItems.Count; i++)
{
if (defectRate.defectItems[i].Note == (i + 1))
{
xlWorkSheet.Cells[6, 5 + i] = defectRate.defectItems[i].Quantity;
countDefect += defectRate.defectItems[i].Quantity;
}
}
xlWorkSheet.Cells[6, 21] = defectRate.DefectQuantity - countDefect;
}
#endregion
xlWorkBook.SaveAs(pathSaveExcel, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue,
misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close();
}
catch (Exception ex)
{
Logfile.Output(StatusLog.Error, "ExportToTemplateMQCDefectTop16 : An error happened in the process.", ex.Message);
}
}
public DefectRateData GetDefectRateReportByLot(DateTime from, DateTime to, string Dept, string codeProcess, string lot)
{
DefectRateData defectRate = new DefectRateData();
try
{
//code lay tren ERP va SFT
// StringBuilder sql = new StringBuilder();
// sql.Append("select sum(TA011) as outputQty, sum(TA012) as DefectQTy, sum(TA011)+ sum(TA012) as TotalQty ");
// sql.Append("from SFCTA ");
// sql.Append("where 1=1 ");
// sql.Append("and TA004 = '" + Dept + "'");
// sql.Append("and TA003 = '" + codeProcess + "'");
// sql.Append("and CREATE_DATE >= '" + from.ToString("yyyyMMdd") + "'");
// sql.Append("and CREATE_DATE <= '" + to.ToString("yyyyMMdd") + "'");
// sqlERPCON sqlERPCON = new sqlERPCON();
// DataTable dt = new DataTable();
// sqlERPCON.sqlDataAdapterFillDatatable(sql.ToString(), ref dt);
//var defectItems = (from DataRow dr in dt.Rows
// select new DefectRateData()
// {
// TotalQuantity =double.Parse( dr["TotalQty"].ToString()),
// DefectQuantity = double.Parse( dr["DefectQTy"].ToString()),
// OutputQuantity = double.Parse(dr["outputQty"].ToString())
// }).ToList();
// defectRate = defectItems[0];
// defectRate.DateTime_from = from;
// defectRate.DateTime_to = to;
// defectRate.DefectRate = (defectRate.TotalQuantity != 0) ? (defectRate.DefectQuantity / defectRate.TotalQuantity) : 0;
LoadDataSummary loadData = new LoadDataSummary();
MQCItemSummary mQCItems = loadData.GetMQCItemSummarybyLot(from, to, Dept, "MQC", lot);
defectRate.Product = mQCItems.product;
defectRate.Lot = lot;
defectRate.TotalQuantity = mQCItems.QuantityTotal;
defectRate.DefectQuantity = mQCItems.NGQty;
defectRate.OutputQuantity = mQCItems.OutputQty;
defectRate.DateTime_from = mQCItems.Time_from;
defectRate.DateTime_to = mQCItems.Time_To;
defectRate.DefectRate = (defectRate.TotalQuantity != 0) ? (defectRate.DefectQuantity / defectRate.TotalQuantity) : 0;
LoadDefectMapping loadDefectTop16 = new LoadDefectMapping();
List <NGItemsMapping> listTop16 = loadDefectTop16.listNGMappingGetReportTop16(Dept, "MQC");
List <DefectItem> listDefectTop16 = new List <DefectItem>();
for (int i = 0; i < listTop16.Count; i++)
{
var getlist = mQCItems.defectItems.Where(d => d.DefectSFT == listTop16[i].NGCode_SFT).ToList();
DefectItem defect = new DefectItem();
if (getlist != null && getlist.Count > 0)
{
defect = getlist[0];
defect.Quantity = getlist.Select(s => s.Quantity).Sum();
}
defect.Note = listTop16[i].Note;
listDefectTop16.Add(defect);
}
var listDefectTop16Groupby = listDefectTop16.OrderBy(d => d.Note).ToList();
defectRate.defectItems = listDefectTop16Groupby;
}
catch (Exception ex)
{
Log.Logfile.Output(Log.StatusLog.Error, "GetDefectRateReport(DateTime from, DateTime to, string Dept, string codeProcess)", ex.Message);
}
return(defectRate);
}
