using MailQuery.Models;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Net;
|
using System.Net.Http;
|
using System.Web.Http;
|
using MailQuery.Filter;
|
using System.Xml.Linq;
|
using System.Web.Helpers;
|
|
namespace MailQuery.Controllers
|
{
|
|
|
[AllowedIP]
|
public class queryController : ApiController
|
{
|
|
private static string datePattern = "=([0-9]{4})[-/]([1-9]|[0][1-9]|[1][0-2])[-/]([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])=";
|
private System.Text.RegularExpressions.Regex regexdate = new System.Text.RegularExpressions.Regex(datePattern);//,System.Text.RegularExpressions.RegexOptions.Compiled);
|
private static string inputPattern = "={0}="; //19999/12/32 testing
|
private static string replaceEsc = "=";
|
private static string[] nothingString = new string[] { };
|
private static CsmsResult[] nothingCSMSResult = new CsmsResult[] { };
|
|
private static CsmsResult[] toofewCSMSResult = new CsmsResult[] { new CsmsResult { codedesc = "條件過少" } };
|
private static string msgnoQueryPattern = "{0}%"; //len must>6
|
|
private static string powernoQueryPattern = "{0}%"; //len must>5
|
public class HelpPageApiModel
|
{
|
public string ResponseDocumentation { get; set; }
|
|
// existing code
|
}
|
|
private string[] getmatchDateString(string sDate)
|
{
|
if (regexdate.IsMatch(string.Format(inputPattern,sDate)) ) {
|
|
return regexdate.Match(string.Format(inputPattern, sDate)).Value
|
.Replace(replaceEsc , "")
|
.Split("/-".ToCharArray()) ;
|
}
|
return nothingString;
|
}
|
private bool checkAcceptAPI(queryCond cond)
|
{
|
if(cond is null) return false;
|
string szClient = cond.client;
|
if(szClient == null) return false;
|
|
bool bPass = false;
|
|
using (developEntities entities = new developEntities())
|
{
|
var allowSource =( from lst601 in entities.SysEnvVar
|
where lst601.CodeID == 601
|
select lst601.CodeValue).ToList();
|
if (allowSource.Count==0) { bPass = false; }
|
else
|
{
|
List<string> lstCode = new List<string>();
|
foreach (string code in allowSource)
|
{
|
lstCode.AddRange(code.Split(new char[] { ',' }));
|
}
|
lstCode.RemoveAll(x => x == "");
|
if(!String.IsNullOrEmpty( szClient))
|
{
|
|
if( lstCode.Contains(szClient))
|
bPass = true; //accept
|
}
|
}
|
}
|
|
return bPass;
|
}
|
|
|
[AllowedIP]
|
public IEnumerable<CsmsResult> query(queryCond cond)
|
{
|
if (!checkAcceptAPI(cond)) return nothingCSMSResult;
|
|
int check_condsize = 0;
|
|
List<CsmsResult> results = new List<CsmsResult>();
|
|
using (developEntities entities = new developEntities())
|
{
|
//string entitySQL = "select MsgNo, Email,MailorName ,MailorTel,UnitName,CateName FROM FeaturesResult ";
|
string entitySQL = "select * FROM FeaturesResult ";
|
List < System.Data.SqlClient.SqlParameter> queryParam = new List<System.Data.SqlClient.SqlParameter>();
|
|
List<string> condarray = new List<string>();
|
if (cond.email != null) { check_condsize++; condarray.Add(" Email = @email"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@email", cond.email )); }
|
if (cond.mailorname != null) { check_condsize++; condarray.Add(" MailorName = @mailorname "); queryParam.Add(new System.Data.SqlClient.SqlParameter("@mailorname", cond.mailorname)); }
|
if (cond.mailortel != null) { string tel = cond.mailortel.Replace(" ", "").Replace("%", ""); if (tel.Length >= 6) { tel = "%" + tel + "%"; check_condsize++; condarray.Add(" MailorTel like @mailortel"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@mailortel","%"+ tel+ "%" )); } else { } }
|
if (cond.msgno != null && cond.msgno.Length>=6) { check_condsize++; condarray.Add(" MsgNo like @msgno"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@msgno", string.Format(msgnoQueryPattern, cond.msgno.Replace("%","").Trim() +"%" )) ) ; }
|
if (cond.powerno != null && cond.powerno.Length >= 6) { check_condsize++; condarray.Add(" PowerNo = @powerno"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@powerno",string.Format( powernoQueryPattern, cond.powerno.Replace("%", "").Trim() + "%" ))); }
|
if (cond.createdate_from != null) {
|
try {
|
string[] from_dt = getmatchDateString(cond.createdate_from);
|
if (from_dt.Length==3)
|
{
|
//
|
DateTime dtFrom = new DateTime(
|
Convert.ToInt16(from_dt[0]), Convert.ToInt16(from_dt[1]), Convert.ToInt16(from_dt[2])
|
);
|
//[SelfReceivedDate]
|
condarray.Add(" CreatedDate >= @dtFrom"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@dtFrom", dtFrom));
|
}
|
}catch(Exception ex)
|
{
|
//faildt
|
}
|
}
|
if (cond.createdate_to != null)
|
{
|
try
|
{
|
string[] to_dt = getmatchDateString(cond.createdate_to);
|
if (to_dt.Length == 3)
|
{
|
DateTime dtFrom = new DateTime(
|
Convert.ToInt16(to_dt[0]), Convert.ToInt16(to_dt[1]), Convert.ToInt16(to_dt[2])
|
);
|
|
condarray.Add(" CreatedDate <= @dateto"); queryParam.Add(new System.Data.SqlClient.SqlParameter("@dateto", dtFrom));
|
}
|
}
|
catch (Exception ex)
|
{
|
//faildt
|
}
|
}
|
//must have one or more cond
|
if (check_condsize ==0) {
|
return toofewCSMSResult;
|
}
|
|
for (int i=0; i< condarray.Count; i++)
|
{
|
if (i == 0)
|
{
|
entitySQL += " WHERE ";
|
}else
|
{
|
entitySQL += " AND ";
|
}
|
entitySQL+= condarray[i];
|
}
|
|
var res = entities.FeaturesResult.SqlQuery(entitySQL, queryParam.ToArray()).
|
ToList().OrderByDescending(x => x.currentProsID);
|
|
var allunit = entities.SYS_UNIT_M.ToList();
|
CsmsResult csmsResult = null;
|
string tempMsgno = "";
|
foreach (var oneRec in res)
|
{
|
|
var thisunit=allunit.Find(f => f.UnitID == oneRec.ToUnit);
|
var qunit = ((thisunit.MasterUnit != null && thisunit.MasterUnit != "-1") ? thisunit.MasterUnit : thisunit.UnitID);
|
var masterunit = allunit.Find(f => f.UnitID == qunit);
|
string dtAllow_d = (oneRec.AllowedDate != null ? oneRec.AllowedDate.Value.ToString("yyyy/MM/dd HH:mm:ss") : "NULL");
|
//string dtAllow_t = (oneRec.AllowedDate != null ? oneRec.AllowedDate.Value.ToLongTimeString() : "");
|
string dtSelf = (oneRec.SelfDonedDate != null ? oneRec.SelfDonedDate.Value.ToString("yyyy/MM/dd") : "NULL");
|
|
csmsResult = new CsmsResult { msgno = oneRec.MsgNo, mailorname = oneRec.MailorName, mailortel = oneRec.MailorTel,
|
email = oneRec.Email, catename = oneRec.CateName ,powerno= oneRec.PowerNo,
|
allowedddate= dtAllow_d ,selfdoneddate= dtSelf,codedesc= (oneRec.MsgState==9 ? "已回覆" :"辦理中"),
|
unitname =thisunit.UnitName ,masterunit=masterunit.UnitName,ismaster= (oneRec.IsMaster==1 ? "主案件" :"") };
|
|
/*
|
results.Add(new CsmsResult { msgno = oneRec.MsgNo, mailorname = oneRec.MailorName, mailortel = oneRec.MailorTel,
|
email = oneRec.Email, catename = oneRec.CateName ,powerno= oneRec.PowerNo,
|
allowedddate= dtAllow_d ,selfdoneddate= dtSelf,codedesc= (oneRec.MsgState==9 ? "已回覆" :"辦理中"),
|
unitname =thisunit.UnitName ,masterunit=masterunit.UnitName,ismaster= (oneRec.IsMaster==1 ? "主案件" :"") });
|
*/
|
|
if (oneRec == null)
|
{
|
continue;
|
}
|
|
|
|
//如果相等，已有寫入過此編號
|
// 111 -- toxxx
|
// 111 -- toooo
|
// 111 -- toroot
|
|
// 112 <-
|
|
if (oneRec.MsgNo.Trim().Equals(tempMsgno))
|
{
|
|
// 111 -- toooo [1+]
|
// 111 -- toroot [1+] <---
|
// 112 <--[1+]
|
//not 1, 只要有就不加入前面的
|
if (!oneRec.ToUnit.Trim().Equals("root"))
|
{
|
//toroot 不要 其他都加
|
results.Add(csmsResult);
|
|
}
|
}
|
else
|
{
|
|
|
// 111 -- toxxx [1]
|
// 111 -- toooo [1+]
|
// 111 -- toroot [1+]
|
// 112 <--[1+]
|
|
// if (!tempMsgno.Equals(""))
|
// {
|
// 111 -- toxxx [1]
|
// 112 <--[1+]
|
//save 111
|
// results.Add(csmsResult);
|
// }
|
// 111 -- toroot [1]
|
// 111 -- toxxx [1+]
|
|
tempMsgno = oneRec.MsgNo.Trim();
|
|
string toMsgNo = oneRec.MsgNo.Trim().Equals(oneRec.MsgID.Trim()) ? "未受理" : oneRec.MsgNo.Trim();
|
|
|
csmsResult.msgno = toMsgNo;
|
|
if (!csmsResult.msgno.Equals("未受理"))
|
results.Add(csmsResult); //latest
|
|
|
|
|
//檢查是否為併案信件
|
//if (item.IsCombin == 0 && item.FileCombinNo.Length > 0)
|
|
}
|
|
}
|
|
|
|
}
|
return results.ToArray();
|
}
|
}
|
}
|
