public void ExportPCR(DateTime?date_from, DateTime?date_to) { TNCUtility util = new TNCUtility(); var query = from a in dbPCR.v_pcr_main select a; if (date_from != null && date_to != null) { var dateto = date_to.Value.AddDays(1); query = query.Where(a => a.issued_date >= date_from.Value && a.issued_date <= dateto); } var output = query.ToList() .Select(s => new { s.pcr_id, s.emp_fname, s.emp_lname, s.group_name, s.state_name, s.status_name, s.email, s.type_name, s.item_no, s.cust_no, s.issue_tel, IssueDate = s.issued_date != null ? s.issued_date.Value.ToString("dd-MM-yyyy") : "", ExpectDate = s.expected_date != null ? s.expected_date.Value.ToString("dd-MM-yyyy") : "", CompDate = s.completed_date != null ? s.completed_date.Value.ToString("dd-MM-yyyy") : "", s.change_txt }); util.CreateExcel(output.ToList(), "PCRReport"); }
public void ExportMoldStatus(byte sel_plant, string sel_product = "", string sel_wc = "") { TNCUtility util = new TNCUtility(); var sql = dbTEMS.v_mold_status.Where(w => w.plant_id == sel_plant); if (sel_product != "") { sql = sql.Where(w => w.prod_id == sel_product); } if (sel_wc != "") { sql = sql.Where(w => w.wc_id == sel_wc); } var output = sql.ToList() .Select(s => new { ItemCode = s.item_code, Need = s.need, Circulate = s.circulate, Spare = s.spare, Total = s.Total, s.OK_Mold, s.OK_Prod, s.NG_Mold, s.NG_Prod, s.NG_TnD, s.NG_Nok }); util.CreateExcel(output.ToList(), "Item_export"); }
public void ExportOverdueByDate(DateTime date_from, DateTime date_to) { TNCUtility util = new TNCUtility(); var query = from a in dbDC.V_Max_Transaction.Where(w => w.status_id == 100 && (w.check_date >= date_from && w.check_date <= date_to)).ToList() join b in dbTNC.tnc_group_master.ToList() on a.org_id equals b.id select new { a.doc_type_short, a.group_code, a.run_no, a.rev_no, a.doc_no, a.doc_name, a.eff_date, a.check_date, a.org_id, b.group_name }; var output = query .Select(s => new { DocNo = s.doc_no, DocName = s.doc_name, EffDate = s.eff_date.ToString("dd-MM-yyyy"), CheckDate = s.check_date.Value.ToString("dd-MM-yyyy"), //s.check_date != null ? s.check_date.Value.ToString("dd-MM-yyyy") : "", Group = s.group_name, }); util.CreateExcel(output.ToList(), "OverDue_" + date_from.ToString("dd-MM-yyyy") + "_" + date_to.ToString("dd-MM-yyyy")); }
public void ExportPCO(DateTime date_from, DateTime date_to, int selGroup = 0) { TNCUtility util = new TNCUtility(); var dateto = date_to.AddDays(1); var query = from a in dbPC.V_Report where a.request_date >= date_from && a.request_date <= dateto select a; if (selGroup != 0) { query = query.Where(w => w.issue_group == selGroup); } var output = query.ToList() .Select(s => new { DocNo = s.id, TypeOfChange = s.chage_type, ChangeDetail = s.change_detail, IssueGroup = s.group_name, IssueDate = s.request_date.ToString("dd/MM/yyyy"), Status = s.status_name, Days = s.DiffDate }); util.CreateExcel(output.ToList(), "PackingChange"); }
public void ExportMonthly(DateTime date_from, DateTime date_to) { TNCUtility util = new TNCUtility(); var dateto = date_to.AddDays(1); var query = (from a in dbDC.V_Transaction where a.status_id >= 100 && (a.act_dt >= date_from && a.act_dt <= dateto) orderby a.doc_lv descending select new { a.doc_lv, a.doc_type_full, a.operation_name, a.status_name, a.doc_no, a.rev_no, a.doc_name, a.group_name, a.eff_date, a.act_dt }).AsEnumerable() // <<== This forces the following Select to operate in memory .Select(t => new { Level = t.doc_lv, DocType = t.doc_type_full, Operation = t.operation_name, Status = t.status_name, DocNo = t.doc_no.Substring(0, t.doc_no.Length - 3), Rev = t.rev_no, DocName = t.doc_name, Group = t.group_name, EffDate = t.eff_date.ToString("dd-MM-yyyy"), CompleteDate = t.act_dt.Value.ToString("dd-MM-yyyy") }).ToList(); //var query = (from a in dbDC.V_Transaction.Where(w => w.status_id >= 100 // && (w.act_dt >= date_from && w.act_dt <= dateto)).ToList() // join b in dbTNC.tnc_group_master.ToList() on a.org_id equals b.id // orderby a.doc_lv descending // select new // { // Level = a.doc_lv, // DocType = a.doc_type_full, // Operation = a.operation_name, // Status = a.status_name, // DocNo = a.doc_no.Substring(0, a.doc_no.Length - 3), // Rev = a.rev_no, // DocName = a.doc_name, // Group = b.group_name, // EffDate = a.eff_date.ToString("dd-MM-yyyy"), // CompleteDate = a.act_dt.Value.ToString("dd-MM-yyyy") // }).ToList(); util.CreateExcel(query, "Monthly_" + date_from.ToString("dd-MM-yyyy") + "_" + date_to.ToString("dd-MM-yyyy")); }
public void SendEmailControlled(string doc_type_short, string group_code, int run_no, byte rev_no, string receiver) { if (!string.IsNullOrEmpty(receiver)) { string mailto = ""; char[] delimiter = new char[] { ',' }; string[] email = receiver.Split(delimiter, StringSplitOptions.RemoveEmptyEntries); int max_email = 47; if (email.Length > max_email)//Max send email = 50 { for (int i = 0; i < max_email; i++) { mailto += "," + email[i]; } mailto = mailto.Substring(1); } else { mailto = receiver; } var get_doc = (from a in dbDC.V_Transaction where a.doc_type_short == doc_type_short && a.group_code == group_code && a.run_no == run_no && a.rev_no == rev_no select a).FirstOrDefault(); if (get_doc != null) { TNCUtility tnc_util = new TNCUtility(); string subject = ""; string body = "";//For Real //string body = "Mail :" + mailto + "<br />";//For Test string int_link = "http://webExternal/DocControl"; string ext_link = "http://webexternal.nok.co.th/DocControl"; string Param = "/Home/ShowDocDetail?dt=" + get_doc.doc_type_short + "&gc=" + get_doc.group_code + "&rn=" + get_doc.run_no + "&rv=" + get_doc.rev_no; subject = "Controlled (" + get_doc.doc_no + ")"; body = "Dear. All Concern,<br /><br />" + "<b>Document No. : </b>" + get_doc.doc_no + "<br />" + "<b>Document Name : </b>" + get_doc.doc_name + "<br />" + "<b>Effective Date : </b>" + get_doc.eff_date.Date.ToString("dd-MM-yyyy") + "<br />" + "<b>You can see document at link ---> </b><a href='" + int_link + get_doc.attach_file.Substring(1) + "'>View</a><br />" + "<b>Link : </b><a href='" + int_link + "/Home/Controlled'>Internal</a> | <a href='" + ext_link + "/Home/Controlled'>External</a><br />" + "<br /><b>*This message is automatic sending from Document Control, please do not reply*</b>"; //tnc_util.SendMail(16, "*****@*****.**", mailto, subject, body);//For Real tnc_util.SendMail(16, "*****@*****.**", "*****@*****.**", subject, body);//For Test } } }
static void Main(string[] args) { PackingChangeEntities db = new PackingChangeEntities(); try { var query = from a in db.td_transaction where a.act_id == null && a.status_id < 100 //orderby a.lv_id, a.org_id ascending select a; //int i = 1; foreach (var q in query) { GetEmail gm = new GetEmail(); string mailto = gm.ByOrg(q.org_id, q.lv_id); if (!string.IsNullOrEmpty(mailto)) { TNCUtility tnc_util = new TNCUtility(); string pkc = "PKC-" + q.gpcode + "-" + q.year + "-" + q.runno.ToString("000"); string int_link = "http://webExternal/PKC/Home"; string subject = "[Remind] Packing Change waiting for process (" + pkc + ")"; string body = //"Mail To : " + mailto + "<br />Dear. All Concern,<br /><br />" + "You have Packing Change No. <b>" + pkc + "</b> waiting for Process at State : " + q.tm_status.status_name + "<br />" + "<br /><a href='" + int_link + "/MainPCO?id=" + pkc + "'>Packing Change Online</a><br />" + "<br /><b>Best Regard.<br />" + "From Packing Change-Admin</b>"; tnc_util.SendMail(31, "*****@*****.**", mailto, subject, body);//For Real //tnc_util.SendMail(31, "*****@*****.**", "*****@*****.**", subject, body);//For Test //Console.WriteLine(i + ". " + pkc); //i++; } //break; } } catch (Exception) { Console.WriteLine("Error PKC E-mail Remind"); Console.ReadLine(); } }
public void ExportCancel(DateTime date_from, DateTime date_to) { TNCUtility util = new TNCUtility(); var query = (from a in dbDC.V_Transaction.ToList() where a.status_id == 103 && (a.act_dt >= date_from && a.act_dt <= date_to) select new { Level = a.doc_lv, DocType = a.doc_type_full, //Operation = a.operation_name, DocNo = a.doc_no.Substring(0, a.doc_no.Length - 3), Rev = a.rev_no, DocName = a.doc_name, CancelDate = a.act_dt.Value.ToString("dd-MM-yyyy") }).ToList(); util.CreateExcel(query, "Cancel_" + date_from.ToString("dd-MM-yyyy") + "_" + date_to.ToString("dd-MM-yyyy")); }
public void ExportCopy(DateTime date_from, DateTime date_to) { TNCUtility util = new TNCUtility(); var dateto = date_to.AddDays(1); var query = (from a in dbDC.V_Report_Copy.Where(w => (w.act_dt >= date_from && w.act_dt <= dateto)).ToList() orderby a.doc_no ascending select new { DocNo = a.doc_no, DocName = a.doc_name, Qty = a.qty, DocType = a.type, Paper = a.paper_name, Group = a.group_name, CompleteDate = a.act_dt.ToString("dd-MM-yyyy"), Reason = a.reason }); util.CreateExcel(query.ToList(), "Copy_" + date_from.ToString("dd-MM-yyyy") + "_" + date_to.ToString("dd-MM-yyyy")); }
public void ExportEmailList(int selGroup) { TNCUtility util = new TNCUtility(); var query = (from a in dbDC.TD_DistributionList.Where(w => w.group_id == selGroup || w.group_id == 0).ToList() join b in dbDC.V_Max_Transaction.Where(w => w.status_id == 100).ToList() on new { a.doc_type_short, a.group_code, a.run_no, a.rev_no } equals new { b.doc_type_short, b.group_code, b.run_no, b.rev_no } join c in dbTNC.tnc_group_master.ToList() on b.org_id equals c.id select new { DocNo = b.doc_no.Substring(0, b.doc_no.Length - 3), Rev = a.rev_no, DocName = b.doc_name, EffDate = b.eff_date.ToString("dd-MM-yyyy"), Originator = c.group_name }).ToList(); util.CreateExcel(query, "EmailList"); }
public void ExportOverdue(int selGroup) { TNCUtility util = new TNCUtility(); var dt = DateTime.Now; var query = from a in dbDC.V_Max_Transaction.Where(w => w.status_id == 100 && w.eff_date <= dt && w.check_date < dt).ToList() join b in dbTNC.tnc_group_master.ToList() on a.org_id equals b.id select new { a.doc_type_short, a.group_code, a.run_no, a.rev_no, a.doc_no, a.doc_name, a.eff_date, a.check_date, a.org_id, b.group_name }; if (selGroup != 0)//Page Load { query = query.Where(w => w.org_id == selGroup); } var output = query .Select(s => new { DocNo = s.doc_no, DocName = s.doc_name, EffDate = s.eff_date.ToString("dd-MM-yyyy"), CheckDate = s.check_date != null ? s.check_date.Value.ToString("dd-MM-yyyy") : "", Group = s.group_name, InProcess = CheckActiveTran(s.doc_type_short, s.group_code, s.run_no, s.rev_no) }); util.CreateExcel(output.ToList(), "OverDue"); }
static void Main(string[] args) { PackingChangeEntities db = new PackingChangeEntities(); try { string email_test = "[email protected],[email protected]";//,[email protected]"; string int_link = "http://webExternal/PKC/Home/MainPCO?id="; var sale = from a in db.v_pack_std_qa_sale where a.status == "Pending" group a by a.sale into g select new { sales = g.Key, list = g }; foreach (var g in sale) { GetEmail gm = new GetEmail(); string mailto = gm.ByOrg(g.sales.Value); TNCUtility tnc_util = new TNCUtility(); string subject = "[Remind] Please Follow up Packing Standard"; string body = "<style> table { border-collapse: collapse; } table, td, th { border: 1px solid black; } td, th { padding: 4px; }</style>"; //body += "Mail To : " + mailto + "<br />"; //For Test body += "Dear. All Concern,<br /><br />"; body += "<table><tr><td>Control No.</td><td>Change Detail</td></tr>"; foreach (var l in g.list) { var pkc = "PKC-" + l.gpcode + "-" + l.year + "-" + l.runno.ToString("000"); body += "<tr><td><a href='" + int_link + pkc + "'>" + pkc + "</a></td><td>" + l.change_detail + "</td></tr>"; } body += "</table><br />Packing Change Online"; //Console.WriteLine(body); tnc_util.SendMail(31, "*****@*****.**", mailto, subject, body, bcc: email_test);//For Real //tnc_util.SendMail(31, "*****@*****.**", "*****@*****.**", subject, body);//For Test } //--------------------------------------------// var qa = from a in db.v_pack_std_qa_sale where a.status == null group a by a.qa into g select new { qa = g.Key, list = g }; foreach (var g in qa) { GetEmail gm = new GetEmail(); string mailto = gm.ByOrg(g.qa); TNCUtility tnc_util = new TNCUtility(); string subject = "[Remind] Please Add Packing Standard"; string body = "<style> table { border-collapse: collapse; } table, td, th { border: 1px solid black; } td, th { padding: 4px; }</style>";//"Dear. All Concern,<br /><br />"; //body += "Mail To : " + mailto; body += "Dear. All Concern,<br /><br />"; body += "<table><tr><td>Control No.</td><td>Change Detail</td></tr>";//For Test foreach (var l in g.list) { var pkc = "PKC-" + l.gpcode + "-" + l.year + "-" + l.runno.ToString("000"); body += "<tr><td><a href='" + int_link + pkc + "'>" + pkc + "</a></td><td>" + l.change_detail + "</td></tr>"; } body += "</table><br />Packing Change Online"; //Console.WriteLine(body); tnc_util.SendMail(31, "*****@*****.**", mailto, subject, body, bcc: "*****@*****.**");//For Real //tnc_util.SendMail(31, "*****@*****.**", "*****@*****.**", subject, body, flag: 0);//For Test } } catch (Exception) { Console.WriteLine("Error PKC E-mail Remind QA, Sales"); Console.ReadLine(); } }
//Program Email Reminder for Review Document static void Main(string[] args) { //Program pgm = new Program(); try { MailCenterEntities dbMail = new MailCenterEntities(); DocumentControlEntities dbDoc = new DocumentControlEntities(); TNC_ADMINEntities dbTNC = new TNC_ADMINEntities(); DateTime dtNow = DateTime.Now.Date; DateTime dtCheck = DateTime.Now.AddMonths(1).Date;//Before one month //DateTime dtTest = new DateTime(2015, 6, 6);//for Test //Review every 2 years and send email before due date 1 month. var query = from a in dbDoc.V_Max_Transaction where a.check_date <= dtCheck && a.status_id == 100//100 = Controlled select a; //var count_query = 0; //var count_send = 0; foreach (var q in query) { GetEmail gm = new GetEmail(); string email = gm.GetEmailbyOrg(q.org_id, 0);//Get email Mgr. down var get_mail = (from a in dbTNC.View_Organization where a.group_id == q.org_id select a).FirstOrDefault(); string cc = null; if (get_mail != null) { cc += !string.IsNullOrEmpty(get_mail.DeptMgr_email) ? "," + get_mail.DeptMgr_email : ""; cc += !string.IsNullOrEmpty(get_mail.PlantMgr_email) ? "," + get_mail.PlantMgr_email : ""; } cc = !string.IsNullOrEmpty(cc) ? cc.Substring(1) : null; //Console.WriteLine("doc no:" + q.doc_no);//Comment for Real if (!string.IsNullOrEmpty(email)) { TNCUtility tnc_util = new TNCUtility(); string int_link = "http://webExternal/DocControl"; string ext_link = "http://webexternal.nok.co.th/DocControl"; string subject = "Pls. review document no. " + q.doc_no; string body = "Dear. Document Owner,<br /><br />" + "<b>This document / format need to review period 2 years</b><br />" + "<b>Document No. : </b>" + q.doc_no + "<br />" + "<b>Document Name : </b>" + q.doc_name + "<br />" + "<b>You can see document at link ---> </b><a href='" + int_link + q.attach_file.Substring(1) + "'>View</a><br />" + "<b>Link : </b><a href='" + int_link + "/Home/OverDueReview'>Internal</a> | <a href='" + ext_link + "/Home/OverDueReview'>External</a><br />" + "<br /><b>*This message is automatic sending from Document Control, please do not reply*</b>"; tnc_util.SendMail(16, "*****@*****.**", email, subject, body, cc);//For Real //tnc_util.SendMail(16, "*****@*****.**", "*****@*****.**", subject, body);//For Test //count_send++; } //else //{ // Console.WriteLine("NO EMAIL:" + q.doc_no); //} //count_query++; } //Console.WriteLine("total : " + count_query + ", count send : " + count_send); } catch (Exception) { Console.WriteLine("Error E-mail Remind"); Console.ReadLine(); } }