private DataTable FindPttBInPis(string PTTB_str)
{
OracleQuery2 cc2 = new OracleQuery2();
DataTable dt = cc2.LoadMobileInPTTB(PTTB_str);
SQLServerQuery ss = new SQLServerQuery();
string result = "";
foreach (DataRow r in dt.Rows)
{
result = result + r["CODE"].ToString() + ",";
}
result = result.TrimEnd(',');
result = result.TrimStart(',');
DataTable dt_pis = ss.LoadPosecodeINPIS(result);
DataTable dtppl = new DataTable();
dtppl.Columns.Add("MOBILE");
dtppl.Columns.Add("DEPARTMENT_ID");
dtppl.Columns.Add("EMPLOYEE_ID");
dtppl.Columns.Add("NAME");
dtppl.Columns.Add("COMPANY");
dtppl.Columns.Add("GROUP_ID");
if (dt != null && dt_pis != null && dt.Rows.Count > 0 && dt_pis.Rows.Count > 0)
{
var results = from table1 in dt.AsEnumerable()
join table2 in dt_pis.AsEnumerable() on table1["CODE"].ToString() equals table2["P_ID"].ToString()
select new
{
code = table1["CODE"].ToString(),
DEPART = table1["DEPARTMENT_ID"].ToString(),
mobile = !string.IsNullOrEmpty(table1["mobile"].ToString()) ? table1["mobile"].ToString() : table2["mobile"].ToString(),
name = table2["FULLNAMETH"].ToString(),
unit = table2["unitname"].ToString(),
GROUP_ID = table1["GROUP_ID"].ToString(),
};
foreach (var item in results)
{
dtppl.Rows.Add(item.mobile, item.DEPART, item.code, item.name, item.unit, item.GROUP_ID);
}
}
return(dtppl);
}
public DataTable LoadAllTempAIS()
{
DataTable dt = null;
OracleQuery2 ora = new OracleQuery2();
dt = ora.ExecuteProcedureToDataTable("smstemplateall",
new object[] { });
DataTable dtt = new DataTable();
DataColumn colString = new DataColumn("StringCol");
colString.DataType = System.Type.GetType("System.String");
dtt.Columns.Add(colString);
DataColumn colString2 = new DataColumn("StringCol2");
colString2.DataType = System.Type.GetType("System.String");
dtt.Columns.Add(colString2);
dtt.Rows.Add("081".ToString(), "081".ToString());
return(dt);
}
private void BindGrid()
{
OracleQuery2 cc2 = new OracleQuery2();
DataTable dt = cc2.LoadAdmin();
SQLServerQuery ss = new SQLServerQuery();
string result = "";
foreach (DataRow r in dt.Rows)
{
result = result + r["EMPLOYEE_ID"].ToString() + ",";
}
result = result.TrimEnd(',');
DataTable dt_pis = ss.LoadINPIS("", result);
DataTable Final_result = new DataTable();
Final_result.Columns.Add("EMPLOYEE_ID");
Final_result.Columns.Add("FULLNAMETH");
Final_result.Columns.Add("POSNAME");
Final_result.Columns.Add("UNITNAME");
Final_result.Columns.Add("IS_ADMIN");
if (dt != null && dt_pis != null && dt.Rows.Count > 0 && dt_pis.Rows.Count > 0)
{
var results = from table1 in dt.AsEnumerable()
join table2 in dt_pis.AsEnumerable() on table1["EMPLOYEE_ID"].ToString() equals table2["P_ID"].ToString()
select new
{
EMPLOYEE_ID = table1["EMPLOYEE_ID"].ToString(),
FULLNAMETH = table2["FULLNAMETH"].ToString(),
POSNAME = table2["POSNAME"].ToString(),
unitname = table2["unitname"].ToString(),
is_admin = table1["IS_ADMIN"].ToString() == "1"?"ADMIN":"USER"
};
foreach (var item in results)
{
Final_result.Rows.Add(item.EMPLOYEE_ID, item.FULLNAMETH, item.POSNAME, item.unitname, item.is_admin);
}
}
AdminGridView.DataSource = Final_result;
AdminGridView.DataBind();
}
protected void SavePPL_Click(object sender, EventArgs e)
{
OracleQuery2 cc2 = new OracleQuery2();
string EmployeeID = EmployeeIDLabel.Text;
string isadmin = IsAdminChk.Checked ? "1" : "0";
string err = cc2.insertAuthor(EmployeeID, isadmin);
if (err != "-1")
{
grpPanel.Visible = false;
AddPPLPanel.Visible = false;
BindGrid();
}
else
{
errorlabel.Text = "มีชื่ออยู่ในระบบแล้ว";
SavePPL.Enabled = false;
NoResult.Visible = true;
}
}
public void RetrySend(string mobile, string msg, string logid)
{
string sender = ConfigurationSettings.AppSettings["ServiceMode"].ToString();
string SMSGatewaySever = ConfigurationSettings.AppSettings["SMSGatewaySever"].ToString();
int SMSGatewaySeverPort = Int32.Parse(ConfigurationSettings.AppSettings["SMSGatewaySeverPort"]);
string url = ConfigurationSettings.AppSettings["SMSGatewaySever_url"].ToString();
//string SMSGatewaySever = "203.170.230.170";
//int SMSGatewaySeverPort = 2257;
byte[] data = new byte[1024];
string str = string.Empty;
string TRANSID = "BULK";
string FROM = "GasControl";
string TO = "66896213113";
string CONTENT = "Test from automation"; // MSG;
string error = "";
OracleQuery2 cc2 = new OracleQuery2();
TO = CleanNumber(mobile);
CONTENT = ConvertToHex(msg);
if (!string.IsNullOrEmpty(TO))
{
try
{
string _data = String.Format("TRANSID={0}&CMD=SENDMSG&FROM={1}&TO={2}&REPORT=Y&CHARGE=N&CODE=PTTNGV_BulkSMS&CTYPE=UNICODE&CONTENT={3}"
, TRANSID, FROM, TO, CONTENT
);
string response = SendPost(url, _data);
XmlDocument xDoc = new XmlDocument();
xDoc.LoadXml(response);
string xpath = "XML";
var nodes = xDoc.SelectNodes(xpath);
foreach (XmlNode childrenNode in nodes)
{
string status = childrenNode.SelectSingleNode("//STATUS").InnerText;
string DETAIL = childrenNode.SelectSingleNode("//DETAIL").InnerText;
string SMID = childrenNode.SelectSingleNode("//SMID").InnerText;
LogToFile("LOGDATE:" + DateTime.Now + ": " + status + " " + DETAIL + " " + SMID + "," + TO);
if (status == "OK")
{
cc2.UpdateLog(1, DETAIL, SMID, logid);
}
else
{
error += "fail:" + TO + ";";
cc2.UpdateLog(0, DETAIL, SMID, logid);
}
}
//LogToFile( response+"," + TO);
}
catch (Exception e)
{
Console.WriteLine("Unable to connect to server." + e.ToString());
str = String.Format("{0}|ERR" + "|Connect server error||", e.Message.ToString());
LogToFile("LOGDATE:" + DateTime.Now + ": " + " Error connect " + str + " " + DateTime.Now);
//เขียน event log เพิ่มเติมเ
// this.LOG(stationid, str, "");
// turn off event log
if (!System.Diagnostics.EventLog.SourceExists("SMS_Auto"))
{
System.Diagnostics.EventLog.CreateEventSource("SMS_Auto", "MyLog");
}
// configure the event log instance to use this source name
error += "fail:" + TO + ";";
//LogToFile(e.ToString() + " " + DateTime.Now);
}
}
}
public string sendSMS_webPost(DataTable ds, string content, string sendername, string loginName)
{
DateTime dtNow = System.DateTime.Now;
string sender = ConfigurationSettings.AppSettings["ServiceMode"].ToString();
string strHeader = dtNow.ToString("yyyy-MM-dd HH:mm:ss") + " " + sender;
string SMSGatewaySever = ConfigurationSettings.AppSettings["SMSGatewaySever"].ToString();
int SMSGatewaySeverPort = Int32.Parse(ConfigurationSettings.AppSettings["SMSGatewaySeverPort"]);
string url = ConfigurationSettings.AppSettings["SMSGatewaySever_url"].ToString();
//string SMSGatewaySever = "203.170.230.170";
//int SMSGatewaySeverPort = 2257;
byte[] data = new byte[1024];
string str = string.Empty;
string TRANSID = "BULK";
string FROM = "GasControl";
string TO = "66896213113";
string CONTENT = "Test from automation" + strHeader; // MSG;
string error = "";
OracleQuery2 cc2 = new OracleQuery2();
int counttotal = 0, countsuc = 0, counterr = 0;
#region sendSMS
if (ds != null && ds.Rows.Count > 0)
{
content = content.Trim() + " " + sendername;
CONTENT = ConvertToHex(content);
for (int i = 0; i < ds.Rows.Count; i++)
{
string Employee_ID = ds.Rows[i]["EMPLOYEE_ID"].ToString();
string TO_Original = ds.Rows[i]["MOBILE"].ToString();
string Department_ID = ds.Rows[i]["Department_ID"].ToString();
string GROUP_ID = ds.Rows[i]["GROUP_ID"].ToString();
string name = ds.Rows[i]["NAME"].ToString();
string unit = ds.Rows[i]["COMPANY"].ToString();
TO = CleanNumber(TO_Original);
if (!string.IsNullOrEmpty(TO))
{
counttotal = counttotal + 1;
try
{
string _data = String.Format("TRANSID={0}&CMD=SENDMSG&FROM={1}&TO={2}&REPORT=Y&CHARGE=N&CODE=PTTNGV_BulkSMS&CTYPE=UNICODE&CONTENT={3}"
, TRANSID, FROM, TO, CONTENT
);
string response = SendPost(url, _data);
XmlDocument xDoc = new XmlDocument();
// xDoc.LoadXml(@"<XML>
// <STATUS>OK</STATUS>
// <DETAIL></DETAIL>
// <SMID>1501019168651</SMID>
// </XML>");
xDoc.LoadXml(response);
string xpath = "XML";
var nodes = xDoc.SelectNodes(xpath);
foreach (XmlNode childrenNode in nodes)
{
string status = childrenNode.SelectSingleNode("//STATUS").InnerText;
string DETAIL = childrenNode.SelectSingleNode("//DETAIL").InnerText;
string SMID = childrenNode.SelectSingleNode("//SMID").InnerText;
LogToFile("LOGDATE:" + DateTime.Now + ": " + status + " " + DETAIL + " " + SMID + "," + TO);
if (status == "OK")
{
countsuc = countsuc + 1;
cc2.insertLog(Employee_ID, Department_ID, TO_Original, content, loginName, 1, DETAIL, SMID, GROUP_ID, name, unit);
}
else if (SMID != "")
{
countsuc = countsuc + 1;
cc2.insertLog(Employee_ID, Department_ID, TO_Original, content, loginName, 1, DETAIL, SMID, GROUP_ID, name, unit);
}
else
{
counterr = counterr + 1;
cc2.insertLog(Employee_ID, Department_ID, TO_Original, content, loginName, 0, DETAIL, SMID, GROUP_ID, name, unit);
}
}
//LogToFile( response+"," + TO);
}
catch (Exception e)
{
Console.WriteLine("Unable to connect to server." + e.ToString());
str = String.Format("{0}|ERR" + "|Connect server error||", e.Message.ToString());
LogToFile("LOGDATE:" + DateTime.Now + ": " + " Error connect " + str + " " + DateTime.Now);
//เขียน event log เพิ่มเติมเ
// this.LOG(stationid, str, "");
// turn off event log
if (!System.Diagnostics.EventLog.SourceExists("SMS_Auto"))
{
System.Diagnostics.EventLog.CreateEventSource("SMS_Auto", "MyLog");
}
// configure the event log instance to use this source name
error += "fail:" + TO + ";";
//LogToFile(e.ToString() + " " + DateTime.Now);
}
}
}
}
if (counterr == 0)
{
return("1");
}
else
{
return(error + " TOTAL : " + counttotal + " SUCCESS : " + countsuc + " FAIL: " + counterr);
}
#endregion
}
protected void SndFinal_Click(object sender, EventArgs e)
{
updateProgress.Visible = true;
grpPanel.Visible = false;
DetailPanel.Visible = false;
PreviewPanel.Visible = false;
PreviewSendPanel.Visible = false;
DataTable dataTable = ViewState["GroupList"] as DataTable;
if (dataTable != null && dataTable.Rows.Count > 0)
{
DataTable PTTB = dataTable.Select("Department_ID =100").Count() > 0 ? dataTable.Select("Department_ID =100").CopyToDataTable() : null;
DataTable PTT = dataTable.Select("Department_ID =200").Count() > 0 ? dataTable.Select("Department_ID =200").CopyToDataTable() : null;
DataTable GC = dataTable.Select("Department_ID =300").Count() > 0 ? dataTable.Select("Department_ID =300").CopyToDataTable() : null;
DataTable CS = dataTable.Select("Department_ID =400").Count() > 0 ? dataTable.Select("Department_ID =400").CopyToDataTable() : null;
DataTable TCS = dataTable.Select("Department_ID =500").Count() > 0 ? dataTable.Select("Department_ID =500").CopyToDataTable() : null;
DataTable TEST = dataTable.Select("Department_ID =600").Count() > 0 ? dataTable.Select("Department_ID =600").CopyToDataTable() : null;
string PTTB_str, PTT_str, GC_str, CS_str, TCS_str, TEST_str = "";
PTTB_str = PTTB == null ? "" : getListStringGroup(PTTB);
PTT_str = PTT == null ? "" : getListStringGroup(PTT);
GC_str = GC == null ? "" : getListStringGroup(GC);
CS_str = CS == null ? "" : getListStringGroup(CS);
TCS_str = TCS == null ? "" : getListStringGroup(TCS);
TEST_str = TEST == null ? "" : getListStringGroup(TEST);
DataTable Result_Dt = new DataTable();
OracleQuery cc = new OracleQuery();
OracleQuery2 cc2 = new OracleQuery2();
Result_Dt.Columns.Add("MOBILE", typeof(string));
Result_Dt.Columns.Add("DEPARTMENT_ID", typeof(string));
Result_Dt.Columns.Add("EMPLOYEE_ID", typeof(string));
Result_Dt.Columns.Add("NAME", typeof(string));
Result_Dt.Columns.Add("COMPANY", typeof(string));
Result_Dt.Columns.Add("GROUP_ID", typeof(string));
//Result_Dt.Rows.Add();
if (PTTB_str != "")
{
Result_Dt.Merge(FindPttBInPis(PTTB_str));
}
if (PTT_str != "")
{
Result_Dt.Merge(FindPttInPis(PTT_str));
}
if (GC_str != "")
{
Result_Dt.Merge(cc2.LoadMobileInGC(GC_str));
}
if (CS_str != "")
{
Result_Dt.Merge(cc2.LoadMobileInCS(CS_str));
}
if (TCS_str != "")
{
Result_Dt.Merge(cc2.LoadMobileInTCS(TCS_str));
}
if (TEST_str != "")
{
Result_Dt.Merge(cc2.LoadMobileInTest(TEST_str));
}
DataTable output = Result_Dt.Rows.Count > 0 ? Result_Dt.Copy() : null;
DataView dv = new DataView(output);
dv.Sort = "MOBILE";
DataTable output2 = dv.ToTable();
DataTable newDataTable = new DataTable();
newDataTable.Columns.Add("MOBILE", typeof(string));
newDataTable.Columns.Add("DEPARTMENT_ID", typeof(string));
newDataTable.Columns.Add("EMPLOYEE_ID", typeof(string));
newDataTable.Columns.Add("NAME", typeof(string));
newDataTable.Columns.Add("COMPANY", typeof(string));
newDataTable.Columns.Add("GROUP_ID", typeof(string));
var dis = output.AsEnumerable().Select(row => new
{
MOBILE = row.Field <string>("MOBILE"),
DEPARTMENT_ID = row.Field <string>("DEPARTMENT_ID"),
EMPLOYEE_ID = row.Field <string>("EMPLOYEE_ID"),
NAME = row.Field <string>("NAME"),
COMPANY = row.Field <string>("COMPANY"),
GROUP_ID = row.Field <string>("GROUP_ID")
}).GroupBy(x => x.MOBILE).Select(x => x.FirstOrDefault());
foreach (var array in dis)
{
newDataTable.Rows.Add(array.MOBILE, array.DEPARTMENT_ID, array.EMPLOYEE_ID, array.NAME, array.COMPANY, array.GROUP_ID);
}
//ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "notext", "alert('"+ output.Rows.Count +","+ dis.Count() + "')", true);
SendSMS ss = new SendSMS();
string resultsent = "";
string smsText = MSGTEXT.Text;//SetSMSText();
if (smsText == null)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "notext", "alert('No Text Message')", true);
}
else if (dis == null)
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "notext", "alert('No Recipient')", true);
}
else
{
//DataView view = new DataView(dis);
//dis = dis.ToTable(true);
string loginName = Session["ID"].ToString();
string sendername = "";
if (SenderCheck.Checked)
{
sendername = "From GasControl";
}
else
{
sendername = "";
}
resultsent = ss.sendSMS_webPost(newDataTable, smsText, sendername, loginName);
if (resultsent == "1")
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "notext", "alert('Success')", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "notext", "alert('" + resultsent + "')", true);
}
}
return;
}
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "notext", "alert('No Recipient')", true);
}
private void group2sendSch()
{
updateProgress.Visible = false;
try
{
string shtext = GroupTosend.Text.TrimEnd().ToUpper();
string group_id = GroupList.SelectedValue;
if (GroupList.Items.FindByText(shtext) != null)
{
group_id = GroupList.Items.FindByText(shtext).Value;
}
else
{
group_id = GroupList.Items[0].Value;
}
DataTable dt = new DataTable();
int ismain = 0;
if (shtext == "" && group_id == "-1")
{
return;
}
OracleQuery2 cc2 = new OracleQuery2();
DataTable dttemp = cc2.LoadAllGroups();
if (shtext == "" && group_id != "-1")
{
dt = dttemp.Select("GROUP_NAME = '" + group_id + "'").CopyToDataTable();
}
else if (shtext != "" && group_id != "-1")
{
dt = dttemp.Select("GROUP_NAME = '" + group_id + "' and " + "Keyword like '%" + shtext + "%'").CopyToDataTable();
}
else if (shtext != "" && group_id == "-1")
{
dt = dttemp.Select("Keyword like '%" + shtext + "%'").CopyToDataTable();
}
if (dt != null && dt.Rows.Count > 0)
{
dt.Columns.Add("ISselect");
foreach (DataRow r in dt.Rows)
{
r["ISselect"] = 0;
}
#region
//if (ismain == 0)
//{
// string group_name = "";
// if (GroupList.Items.FindByText(GroupTosend.Text) != null)
// group_name = GroupTosend.Text;
// else
// group_name = "";
// DataRow newRow = dt.NewRow();
// newRow["GROUP_ID"] = GroupList.SelectedValue == "-1" ? dt.Rows[dt.Rows.Count - 1]["GROUP_ID"] : GroupList.SelectedValue;
// newRow["GROUP_NAME"] = GroupList.SelectedValue == "-1" ? group_name : GroupList.SelectedItem.Text;
// newRow["DEPARTMENT"] = dt.Rows[dt.Rows.Count - 1]["DEPARTMENT"];
// //newRow["DESCR"] = "";
// //newRow["NAME"] = "";
// newRow["ISselect"] = 0;
// dt.Rows.InsertAt(newRow, 0);
//}
#endregion
}
else
{
return;
}
DataView dv = dt.DefaultView;
dv.Sort = "GROUP_NAME,GROUP_ID asc";
dt = dv.ToTable();
ViewState["SCHTable"] = dt;
GroupResultGrid.DataSource = dt;
GroupResultGrid.DataBind();
grpPanel.Visible = true;
DetailPanel.Visible = true;
}
catch
{
return;
}
}
