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