public bool ExportReportProductionDaiLy(PeriodProduction period, string Pathsave)
{
try
{
DefectRateReport defectRateReport = new DefectRateReport();
List <DefectRateData> defectRateDatas = new List <DefectRateData>();
defectRateDatas = defectRateReport.GetListDefectRateReportAmountOfTimeDaily("B01", "0010", period);
if (defectRateDatas.Count == 0)
{
return(false);
}
Class.ToolSupport exportExcel = new Class.ToolSupport();
return(true);
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "ExportReportProductionDaiLy()", ex.Message);
return(false);
}
}
public static void ReturnDateTimePeriodProduction(PeriodProduction period, ref DateTime dateFrom, ref DateTime dateTo)
{
DateTime from = new DateTime();
DateTime to = new DateTime();
if (period == PeriodProduction.dayshift)
{
from = DateTime.Now.Date + new TimeSpan(8, 0, 0);
to = DateTime.Now.Date + new TimeSpan(20, 0, 0);
}
else if (period == PeriodProduction.nightshift)
{
from = DateTime.Now.Date.AddDays(-1) + new TimeSpan(20, 0, 0);
to = DateTime.Now.Date + new TimeSpan(8, 0, 0);
}
else if (period == PeriodProduction.AllDay)
{
from = DateTime.Now.Date.AddDays(-1) + new TimeSpan(8, 0, 0);
to = DateTime.Now.Date + new TimeSpan(8, 0, 0);
}
dateFrom = from; dateTo = to;
}
public bool ExportReportProductionDaiLy(PeriodProduction period)
{
try
{
DefectRateReport defectRateReport = new DefectRateReport();
List <DefectRateData> defectRateDatas = new List <DefectRateData>();
defectRateDatas = defectRateReport.GetListDefectRateReportAmountOfTimeDaily("B01", "0010", period);
if (defectRateDatas.Count == 0)
{
return(false);
}
Log.ExportExcelTool exportExcel = new Log.ExportExcelTool();
exportExcel.ExportToTemplateMQCDefectDaily(pathDaily, @"C:\ERP_Temp\MQC_Daily_Report" + "-" + DateTime.Now.ToString("yyyyMMdd hhmmss") + ".xlsx", defectRateDatas);
return(true);
}
catch (Exception ex)
{
Logfile.Output(StatusLog.Error, "ExportReportProductionDaiLy()", ex.Message);
return(false);
}
}
private bool SendEmailStatus(List <ScheduleReportItems> listReport)
{
foreach (var item in listReport)
{
if (item.ReportType == "Daily")
{
if (DateTime.Now.Hour == int.Parse(item.Hours) && DateTime.Now.Minute == int.Parse(item.Minutes))
{
List <EmailNeedSend> emailNeeds = new List <EmailNeedSend>();
emailNeeds = EmailNeedSends(item.ReportName);
if (CheckIsSentMailComplete(item) == false)
{
if (emailNeeds != null && emailNeeds.Count > 0)
{
if (item.ReportName == "BackLogReport")
{
SendMailFunction sendmail = new SendMailFunction();
var isOK = sendmail.SendMailwithExportExcelbyCompanyMail(item, emailNeeds, ref dgv_export, item.AttachedFolder, version);
if (isOK)
{
InsertSendMailtoRecord(item, item.AttachedFolder);
isExportExcel = false;
}
}
else if (item.ReportName == "MQC_Daily")
{
SendMailFunction sendmail = new SendMailFunction();
PeriodProduction period = new PeriodProduction();
if (int.Parse(item.Hours) == 8)
{
period = PeriodProduction.AllDay;
}
else if (int.Parse(item.Hours) == 20)
{
period = PeriodProduction.dayshift;
}
else if (int.Parse(item.Hours) < 8)
{
period = PeriodProduction.nightshift;
}
else if (int.Parse(item.Hours) > 8 && int.Parse(item.Hours) < 20)
{
period = PeriodProduction.dayshift;
}
if (sendmail.SendMailwithExportExceMQCbyCompanyMail(period, item, emailNeeds))
{
InsertSendMailtoRecord(item, item.AttachedFolder);
isExportExcel = false;
}
}
}
}
}
}
else if (item.ReportType == "Weekly")
{
if (DateTime.Now.DayOfWeek.ToString() == item.Day && DateTime.Now.Hour == int.Parse(item.Hours) && DateTime.Now.Minute == int.Parse(item.Minutes))
{
List <EmailNeedSend> emailNeeds = new List <EmailNeedSend>();
emailNeeds = EmailNeedSends(item.ReportName);
if (CheckIsSentMailComplete(item) == false)
{
if (emailNeeds != null && emailNeeds.Count > 0)
{
SendMailFunction sendmail = new SendMailFunction();
if (sendmail.SendMailtoReportByCompanyMail(item, emailNeeds))
{
InsertSendMailtoRecord(item, item.AttachedFolder);
}
}
}
}
}
else if (item.ReportType == "Monthly")
{
if (DateTime.Now.Date.ToString() == item.Date && DateTime.Now.Hour == int.Parse(item.Hours) && DateTime.Now.Minute == int.Parse(item.Minutes))
{
List <EmailNeedSend> emailNeeds = new List <EmailNeedSend>();
emailNeeds = EmailNeedSends(item.ReportName);
if (CheckIsSentMailComplete(item) == false)
{
if (emailNeeds != null && emailNeeds.Count > 0)
{
SendMailFunction sendmail = new SendMailFunction();
if (sendmail.SendMailtoReportByCompanyMail(item, emailNeeds))
{
InsertSendMailtoRecord(item, item.AttachedFolder);
}
}
}
}
}
else if (item.ReportType == "Yearly")
{
if (DateTime.Now.ToString("MMMM") == item.Month && DateTime.Now.Date.ToString() == item.Date && DateTime.Now.Hour == int.Parse(item.Hours) && DateTime.Now.Minute == int.Parse(item.Minutes))
{
List <EmailNeedSend> emailNeeds = new List <EmailNeedSend>();
emailNeeds = EmailNeedSends(item.ReportName);
if (CheckIsSentMailComplete(item) == false)
{
if (emailNeeds != null && emailNeeds.Count > 0)
{
SendMailFunction sendmail = new SendMailFunction();
if (sendmail.SendMailtoReportByCompanyMail(item, emailNeeds))
{
InsertSendMailtoRecord(item, item.AttachedFolder);
}
}
}
}
}
}
return(false);
}
public List <MQCItemSummary> GetMQCItemSummaries(PeriodProduction period, string site, string process)
{
DateTime from = new DateTime(); DateTime to = new DateTime();
DateTimeControl.ReturnDateTimePeriodProduction(period, ref from, ref to);
string date = from.ToString("yyyy-MM-dd");
string time = from.ToString("HH:mm:ss");
List <MQCItemSummary> qCItemSummaries = new List <MQCItemSummary>();
try
{
LoadDataMQC dataMQC = new LoadDataMQC();
List <MQCDataItems> mQCDataItems = dataMQC.listMQCDataItemsbySite(from, to, site, process);
//Nhom theo san pham
var ListMQCbyProduct = mQCDataItems
.GroupBy(u => u.lot)
.Select(grp => grp.ToList())
.ToList();
foreach (var qCDataItems in ListMQCbyProduct)
{
MQCItemSummary itemSummary = new MQCItemSummary();
itemSummary.product = qCDataItems[0].model;
itemSummary.defectItems = new List <DefectItem>();
var ListItemsData = qCDataItems
.GroupBy(u => u.item)
.Select(grp => grp.ToList())
.ToList();
var ListItemsDate = qCDataItems
.GroupBy(u => u.inspectdate)
.ToList();
foreach (var itemDate in ListItemsDate)
{
var ListItemsTime = qCDataItems
.GroupBy(u => u.inspecttime)
.ToList();
if (ListItemsDate.Count() == 1)
{
itemSummary.Time_from = "[" + ListItemsDate[0].Key.ToString("dd-MM-yy") + "] " + ListItemsTime.Min(d => d.Key).ToString();
itemSummary.Time_To = "[" + ListItemsDate[0].Key.ToString("dd-MM-yy") + "] " + ListItemsTime.Max(d => d.Key).ToString();
}
else if (ListItemsDate.Count() == 2)
{
itemSummary.Time_from = "[" + ListItemsDate[0].Key.ToString("dd-MM-yy") + "] " + ListItemsTime.Min(d => d.Key).ToString();
itemSummary.Time_To = "[" + ListItemsDate[1].Key.ToString("dd-MM-yy") + "] " + ListItemsTime.Max(d => d.Key).ToString();
}
}
//Khi thay doi ngay can phai chinh lai thoi gian
foreach (var itemData in ListItemsData)
{
DefectItem item = new DefectItem();
item.DefectCode = itemData[0].item;
itemSummary.Lot = itemData[0].lot;
item.Quantity = itemData.Select(d => d.data).Sum();
if (itemData[0].remark == "OP")
{
itemSummary.OutputQty += item.Quantity;
}
else if (itemData[0].remark == "NG")
{
LoadDefectMapping defectMapping = new LoadDefectMapping();
NGItemsMapping nGItemsMapping = defectMapping.GetNGMapping(site, process, item.DefectCode);
item.DefectSFT = nGItemsMapping.NGCode_SFT;
item.DefectSFTName = nGItemsMapping.NGCodeName_SFT;
itemSummary.defectItems.Add(item);
itemSummary.NGQty += item.Quantity;
}
}
itemSummary.QuantityTotal = itemSummary.OutputQty + itemSummary.NGQty;
itemSummary.DefectRate = (itemSummary.QuantityTotal != 0) ? (itemSummary.NGQty / itemSummary.QuantityTotal) : 0;
qCItemSummaries.Add(itemSummary);
}
}
catch (Exception ex)
{
Log.Logfile.Output(Log.StatusLog.Error, "GetMQCItemSummaries(DateTime from, DateTime to, string site, string process)", ex.Message);
}
return(qCItemSummaries);
}
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)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "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
{
//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();
List <MQCItemSummary> ListmQCItems = loadData.GetMQCItemSummaries(period, Dept, "MQC");
foreach (var mQCItems in ListmQCItems)
{
DefectRateData defectRate = new DefectRateData();
defectRate.Lot = mQCItems.Lot;
defectRate.Product = mQCItems.product;
defectRate.DateTime_from = mQCItems.Time_from;
defectRate.DateTime_to = mQCItems.Time_To;
defectRate.TotalQuantity = mQCItems.QuantityTotal;
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)
{
Log.Logfile.Output(Log.StatusLog.Error, "GetDefectRateReport(DateTime from, DateTime to, string Dept, string codeProcess)", ex.Message);
}
return(defectRates);
}
public List <MQCItemSummary> GetMQCItemSummaries(PeriodProduction period, string site, string process)
{
DateTime from = new DateTime(); DateTime to = new DateTime();
DateTimeControl.ReturnDateTimePeriodProduction(period, ref from, ref to);
List <MQCItemSummary> qCItemSummaries = new List <MQCItemSummary>();
try
{
LoadDataMQC dataMQC = new LoadDataMQC();
List <MQCDataItems> mQCDataItems = dataMQC.listMQCDataItemsbySite(from, to, site, process);
//Nhom theo san pham
var ListMQCbyProduct = mQCDataItems
.OrderBy(d => d.line)
.GroupBy(u => u.lot)
.Select(grp => grp.ToList())
.ToList();
foreach (var qCDataItems in ListMQCbyProduct)
{
MQCItemSummary itemSummary = new MQCItemSummary();
itemSummary.product = qCDataItems[0].model;
itemSummary.defectItems = new List <DefectItem>();
var ListItemsData = qCDataItems
.GroupBy(u => u.item)
.Select(grp => grp.ToList())
.ToList();
itemSummary.Time_from = dataMQC.GetMinTimeProductionOfProduct(itemSummary.product, from, to).Substring(0, 16);
itemSummary.Time_To = dataMQC.GetMaxTimeProductionOfProduct(itemSummary.product, from, to).Substring(0, 16);
//Khi thay doi ngay can phai chinh lai thoi gian
foreach (var itemData in ListItemsData)
{
itemSummary.product = itemData[0].model;
itemSummary.Line = itemData[0].line;
itemSummary.Lot = itemData[0].lot;
if (itemData[0].remark == "OP")
{
itemSummary.OutputQty = itemData.Select(d => d.data).Sum();
}
else if (itemData[0].remark == "NG")
{
DefectItem item = new DefectItem();
item.DefectCode = itemData[0].item;
item.Quantity = itemData.Select(d => d.data).Sum();
LoadDefectMapping defectMapping = new LoadDefectMapping();
NGItemsMapping nGItemsMapping = defectMapping.GetNGMapping(site, process, item.DefectCode);
item.DefectSFT = nGItemsMapping.NGCode_SFT;
item.DefectSFTName = nGItemsMapping.NGCodeName_SFT;
itemSummary.defectItems.Add(item);
itemSummary.NGQty += item.Quantity;
}
else if (itemData[0].remark == "RW")
{
DefectItem item = new DefectItem();
item.DefectCode = itemData[0].item;
item.Quantity = itemData.Select(d => d.data).Sum();
itemSummary.ReworkQty += item.Quantity;
}
}
itemSummary.QuantityTotal = itemSummary.OutputQty + itemSummary.NGQty /*+ itemSummary.ReworkQty*/;
itemSummary.DefectRate = (itemSummary.QuantityTotal != 0) ? (itemSummary.NGQty / itemSummary.QuantityTotal) : 0;
itemSummary.ReworkRate = (itemSummary.QuantityTotal != 0) ? (itemSummary.ReworkQty / itemSummary.QuantityTotal) : 0;
qCItemSummaries.Add(itemSummary);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "GetMQCItemSummaries(DateTime from, DateTime to, string site, string process)", ex.Message);
}
return(qCItemSummaries);
}
public bool SendMailwithExportExceMQCbyCompanyMail(PeriodProduction period, ScheduleReportItems items, List <EmailNeedSend> emailNeedSends)
{
try
{
MQC.MQCReport mQCReport = new MQC.MQCReport();
if (mQCReport.ExportReportProductionDaiLy(period))
{
Log.Logfile.Output(Log.StatusLog.Normal, "Export MQC report to excel sucessfull");
}
else // return false de khoi gui mail
{
Log.Logfile.Output(Log.StatusLog.Normal, "Export MQC report to excel fail");
return(false);
}
}
catch (Exception ex)
{
Log.Logfile.Output(Log.StatusLog.Error, "Export MQC report to excel fail!", ex.Message);
}
try
{
if (emailNeedSends.Count > 0)
{
MailMessage mail = new MailMessage();
if (items.IsBodyHTML)
{
mail.IsBodyHtml = true;
}
else
{
mail.IsBodyHtml = false;
}
SmtpClient SmtpServer = new SmtpClient("103.18.179.112", 25);
mail.From = new MailAddress("*****@*****.**");
// mail.To.Add("*****@*****.**");
foreach (var email in emailNeedSends)
{
mail.To.Add(email.EmailReceive);
}
mail.Subject = items.Subject + " On " + DateTime.Now.ToString("MMM-dd-yyyy");
if (items.IsBodyHTML)
{
string pathTemplate = Environment.CurrentDirectory + @"\Resources\EmailTemplate.html";
if (File.Exists(pathTemplate))
{
string html = File.ReadAllText(pathTemplate);
string htmlReplaced = "";
htmlReplaced = html.Replace("@Replace1", items.Subject);
htmlReplaced = htmlReplaced.Replace("@Replace2", DateTime.Now.ToString("MMM-dd-yyyy"));
mail.Body = htmlReplaced;
}
}
else
{
mail.Body = items.Contents;
}
List <string> listfileattached = new List <string>();
if (items.AttachedFolder != "" && items.AttachedFolder != null)
{
DirectoryInfo d = new DirectoryInfo(items.AttachedFolder); //Assuming Test is your Folder
FileInfo[] Files = d.GetFiles(); //Getting excel files
foreach (FileInfo file in Files)
{
System.Net.Mail.Attachment attachment;
if (file.Name.Contains(items.ReportName))
{
attachment = new System.Net.Mail.Attachment(file.FullName);
mail.Attachments.Add(attachment);
listfileattached.Add(file.FullName);
SmtpServer.Port = 25;
SmtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "techlink@123");
// SmtpServer.EnableSsl = true; // dung email cong ty thi bo dong nay
SmtpServer.Send(mail);
}
else
{
return(false);
}
}
}
mail.Dispose();
SmtpServer.Dispose();
try
{
foreach (var item in listfileattached)
{
if (File.Exists(item))
{
File.Delete(item);//Xoa file after send file
}
}
}
catch (Exception ex)
{
Log.Logfile.Output(Log.StatusLog.Error, "Delete file attached fail :", ex.Message);
}
Log.Logfile.Output(Log.StatusLog.Normal, "Send mail suscess :", items.ReportName + "|" + items.ReportType + "|" + items.Subject);
return(true);
}
}
catch (Exception ex)
{
Log.Logfile.Output(Log.StatusLog.Error, "Send mail fail :", ex.Message);
}
return(true);
}
