public static CReturnData Delete_Sqllite(string versionList)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
/*產生刪除 sqllite檔案上傳記錄表 的SQL*/
string StrSQL = "/*sqllite檔案上傳記錄表*/ " +
"DELETE FROM userUploadLog WHERE CONVERT(varchar, [version]) + '##' + fileUploadNewName + '##' + fileUploadOldName IN ( " +
"SELECT [Value] FROM dbo.UTILfn_Split('" + versionList + "', '^^') AS tbl_version " +
") ";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(StrSQL);
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
}
db.DBDisconnect();
return(myData);
}
public static CReturnData DelFileProcess(string proid, string fileid)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
StringBuilder sql = new StringBuilder("UPDATE MIP_HAPPY ");
sql.Append("SET F_IDX = null ,F_NAME=null");
sql.Append(" where HAPPY_ID =").Append(proid);
///*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(sql.ToString());
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
new FileManager().deleteFileByKey(fileid, db.getOcnn());
db.DBDisconnect();
}
return(myData);
}
public static CReturnData EdtRelationProcess(string StrRoleRelation)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
string strSQL = "TRUNCATE TABLE DMSRoleManage " +
"INSERT INTO DMSRoleManage(DMSRoleID, DMSRoleIDManaged) " +
"SELECT LEFT([Value], CHARINDEX('^^', [Value], 0) - 1) AS DMSRoleID, SUBSTRING([Value], CHARINDEX('^^', [Value], 0) + 2, LEN([Value])) AS DMSRoleIDManaged " +
"FROM dbo.UTILfn_Split('" + StrRoleRelation + "', '||') ";
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(strSQL);
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
db.DBDisconnect();
//string filepath = @"c:\temp\posttestLog\";
//System.IO.File.WriteAllText(filepath + "EdtRelationProcess.txt", strSQL);
}
return(myData);
}
public int insert(string fileName, byte[] imageData, string ver_no, string sqlType)
{
int ret = 0;
string StrSQL = "INSERT INTO userUploadLog(sqltype, fileUploadOldName, fileUploadNewName, datetime, version_no,imageData)"
+ "values( @sqltype, @fileUploadOldName, @fileUploadNewName,GETDATE() , @version_no,@imageData) ";
Database db = new Database();
DataTable dt = new DataTable();
/*連線DB*/
/* 20111004-add */
//Initialize SqlCommand object for insert.
SqlCommand SqlCom = new SqlCommand(StrSQL, db.getOcnn());
CReturnData myData = new CReturnData();
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
SqlCom.Parameters.Add(new SqlParameter("@sqltype", sqlType));
SqlCom.Parameters.Add(new SqlParameter("@fileUploadOldName", fileName));
SqlCom.Parameters.Add(new SqlParameter("@fileUploadNewName", fileName));
SqlCom.Parameters.Add(new SqlParameter("@version_no", MDS.Utility.NUtility.checkString(ver_no)));
/*20160506-add 透過檔案管理平台歸檔取得索引值*/
//SqlCom.Parameters.Add(new SqlParameter("@file_index", saveFile(fileName, MIPLibrary.MIPUtil.GetBytes(fileName))));
SqlCom.Parameters.Add(new SqlParameter("@imageData", imageData));
SqlCom.ExecuteNonQuery();
db.getOcnn().Close();
return(myData.nRet);
}
public static CReturnData getProKind2(string strProKind2)//B1010
{
CReturnData myData = new CReturnData();
//MipSystemModule model = new MipSystemModule();
//model.SQL = "SELECT * FROM MIP_CODES WHERE CLEVEL=@CLEVEL AND CSTATUS='0' and CKEY!='B2020' ORDER BY CORDER ";
//model.KEY = "CKEY";
//model.NAME = "CNAME";
//model.rowParameters= new SqlParameter("@CLEVEL", strProKind2);
//List<Dictionary<string, string>> list = model.getProductList();
List <Dictionary <string, string> > list = MIPCode.getProductList(strProKind2);
list.Insert(0, (new Dictionary <string, string>()
{
{ "CKEY", "" }, { "CNAME", "請選擇" }
}));
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
//string jsonResult = fastJSON.JSON.ToJSON(list);
string jsonResult = serializer.Serialize(list);
myData.returnData = jsonResult;
myData.nRet = 0;
return(myData);
}
public static CReturnData Add_Sqllite(string FileName, string OldFileName, string sqltype)
{
/* 20111005-modify 寫入動作改由 singleFileUpload.aspx.cs handle */
CReturnData myData = new CReturnData();
myData.nRet = 0;
return(myData);
}
public static CReturnData DenyData(string dataList)
{
CReturnData myData = new CReturnData();
Database db = new Database();
int nRet = -1;
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
SqlTransaction sqlTrans = null;
try
{
sqlTrans = db.getOcnn().BeginTransaction();
string[] strCompanyArr = dataList.Split(new String[] { "^^" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string HAPPY_ID in strCompanyArr)
{
string strSQL = "UPDATE MIP_HAPPY SET CSTATUS=1 WHERE HAPPY_ID=@HAPPY_ID; ";
SqlCommand SqlCom = new SqlCommand(strSQL, db.getOcnn(), sqlTrans);
SqlCom.Parameters.Add(new SqlParameter("@HAPPY_ID", HAPPY_ID));
nRet = SqlCom.ExecuteNonQuery();
string outMsg = db.outMsg;
Debug.Write("nRet:" + nRet);
Debug.Write("outMsg:" + outMsg);
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
}//for
sqlTrans.Commit();
}
catch (Exception ex)
{
Debug.Write("PCA_0050Q->DenyData Exception :" + ex.Message);
if (sqlTrans != null)
{
sqlTrans.Rollback();
}
throw ex;
}
finally
{
db.getOcnn().Close();
db.DBDisconnect();
}
return(myData);
}
protected void postSession()
{
string url = Request.Url.Scheme + "://" + Request.Url.Host + Request.ApplicationPath + "/SysFun/" + "AppStore.ashx";
myData = new CReturnData();
//string uri = string.Format("{0}?who={1}", url, "joey");
WebRequest request = WebRequest.Create(url);
request.Method = "POST";
//加上"data="，讓server端可以透過Request.Form["data"]讀取
string postData = Session["UserID"].ToString();
byte[] byteArray = Encoding.UTF8.GetBytes(postData);
request.ContentType = "application/x-www-form-urlencoded";
// Set the ContentLength property of the WebRequest.
request.ContentLength = byteArray.Length;
try
{
logger.Debug("請求網站：" + url);
myData.nRet = 0;
using (Stream dataStream = request.GetRequestStream())
{
// Write the data to the request stream.
dataStream.Write(byteArray, 0, byteArray.Length);
}
// Get the response.
logger.Debug("等待回應：" + request.GetResponse());
using (WebResponse response = request.GetResponse())
{
using (Stream dataStream = response.GetResponseStream())
{
using (StreamReader reader = new StreamReader(dataStream))
{
string responseFromServer = reader.ReadToEnd();
s1 = Request.Url.Scheme + "://" + Request.Url.Authority + "/" + System.Configuration.ConfigurationManager.AppSettings["APPLICATION"] + "/SysFun/" + "AppManage/" + "App_List.aspx?app=" + Request.ApplicationPath + "&t=" + responseFromServer;
logger.Debug("回應的網址(appstore)：" + s1);
}
}
}
}
catch (Exception ex)
{
myData.nRet = 1;
myData.outMsg = ex.Message;
MessageBox("錯誤訊息:" + ex.Message + "請稍候在試");
//throw ex;
}
// Get the request stream.
}
public static CReturnData Delete_AccountID(string AccountIDList, string myAccountName, string myAccountID)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
string ModuleDesc = ParseWording("B0136");
string FunctionDesc = ParseWording("B0137");
string ActionDesc = ParseWording("A0003");
/*產生刪除帳號的SQL*/
string StrSQL = "DECLARE @tmpAccountID TABLE( " +
"myGUID varchar(50), " +
"AccountID varchar(20), " +
"Name nvarchar(50) " +
") " +
"INSERT INTO @tmpAccountID(myGUID, AccountID, Name) " +
"SELECT newid(), tblA.[Value], SecurityUserAccount.Name " +
"FROM dbo.UTILfn_Split('" + AccountIDList + "', '^^') AS tblA " +
"INNER JOIN SecurityUserAccount ON tblA.[Value] = SecurityUserAccount.AccountID " +
"/*使用者資料表*/ " +
"DELETE FROM SecurityUserAccount WHERE AccountID IN (SELECT AccountID FROM @tmpAccountID) " +
"/*連離線狀態表*/ " +
"DELETE FROM LinetStatus WHERE AccountID IN (SELECT AccountID FROM @tmpAccountID) " +
"/*個人密碼紀錄表*/ " +
"DELETE FROM SecurityUserPwd WHERE AccountID IN (SELECT AccountID FROM @tmpAccountID) " +
"/*使用者權限*/ " +
"DELETE FROM SecurityUserAccount_FunctionRole WHERE AccountID IN (SELECT AccountID FROM @tmpAccountID) " +
"DELETE FROM SecurityUserAccount_ActionRole WHERE AccountID IN (SELECT AccountID FROM @tmpAccountID) " +
"/*操作紀錄-刪除*/" +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT myGUID, N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0006") + "', AccountID, '', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM @tmpAccountID " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT myGUID, N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0007") + "', Name, '', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM @tmpAccountID ";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(StrSQL);
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
db.DBDisconnect();
}
return(myData);
}
public static CReturnData AddProcess(string oParentGroupID_Sel, string oParentGroupID, string oGroupID, string oGroupName, string oTEL, string oAddress, string oMemo, string myAccountName, string myAccountID)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
string myGUID = System.Guid.NewGuid().ToString();
string ModuleDesc = ParseWording("B0136");
string FunctionDesc = ParseWording("B0139");
string ActionDesc = ParseWording("B0045");
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
string StrSQL = "/*群組資料表*/ " +
"INSERT INTO SecurityGroup(GroupID, GroupName, Address, TEL, Memo) " +
"SELECT '" + oGroupID + "', N'" + oGroupName.Replace("'", "''") + "', N'" + oAddress.Replace("'", "''") + "', '" + oTEL + "', N'" + oMemo.Replace("'", "''") + "' " +
"/*從屬關係資料表*/ " +
"INSERT INTO SecurityRelation(ParentGroupID, AccountType, AccountID) " +
"SELECT '" + oParentGroupID + "', '0', '" + oGroupID + "' " +
"/*操作紀錄-新增畫面*/" +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0012") + "', '', N'" + oParentGroupID_Sel.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0047") + "', '', N'" + oGroupID + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0101") + "', '', N'" + oGroupName.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("A0019") + "', '', N'" + oAddress.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("A0020") + "', '', N'" + oTEL + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("A0021") + "', '', N'" + oMemo.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() ";
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(StrSQL);
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
db.DBDisconnect();
//string filepath = @"c:\temp\posttestLog\";
//System.IO.File.WriteAllText(filepath + "AddProcess.txt", StrSQL);
}
return(myData);
}
public static CReturnData PwUnLock_AccountID(string AccountIDList, string myAccountName, string myAccountID)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
string ModuleDesc = ParseWording("B0136");
string FunctionDesc = ParseWording("B0137");
string ActionDesc = "解除鎖定";
/*產生帳號解除鎖定的SQL*/
string StrSQL = "DECLARE @tmpAccountID TABLE( " +
"myGUID varchar(50), " +
"AccountID varchar(20), " +
"Name nvarchar(50) " +
") " +
"INSERT INTO @tmpAccountID(myGUID, AccountID, Name) " +
"SELECT newid(), tblA.[Value], SecurityUserAccount.Name " +
"FROM dbo.UTILfn_Split('" + AccountIDList + "', '^^') AS tblA " +
"INNER JOIN SecurityUserAccount ON tblA.[Value] = SecurityUserAccount.AccountID " +
"/*更新使用者資料表*/ " +
"UPDATE SecurityUserAccount " +
"set ifailTimes = 0 " +
", dLockTime= NULL " +
", PWLastUpdateTime = NULL " +
" WHERE AccountID IN (SELECT AccountID FROM @tmpAccountID) " +
"/*操作紀錄-密碼重置*/" +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT myGUID, N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0006") + "', AccountID, '', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM @tmpAccountID " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT myGUID, N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0006") + "', AccountID, '', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM @tmpAccountID ";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(StrSQL);
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
db.DBDisconnect();
}
return(myData);
}
public static CReturnData Deploy()
{
CReturnData myData = new CReturnData();
Database db = new Database();
int nRet = -1;
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
try
{
string appPath = HttpContext.Current.Request.PhysicalApplicationPath;
string saveDir = @"UserUpLoad\tempFile\";
string sqlliteFilePath = appPath + saveDir + MIPLibrary.SQLLiteHelper.SYSINFO_SQLITE_NAME;
MIPLibrary.SQLLiteHelper _SQLLiteHelper = new MIPLibrary.SQLLiteHelper();
//發版日
string product_LastUpdateTime = "";
product_LastUpdateTime = _SQLLiteHelper.execute(MIPLibrary.SQLLiteHelper.SYSINFO_SQLITE, sqlliteFilePath, db.getOcnn());
string strInfo = "發佈最新公告資料完成:" + sqlliteFilePath + "\r\n 發版日：" + product_LastUpdateTime;
Debug.Write(strInfo);
myData.nRet = 0;
myData.outMsg = strInfo;
}
catch (Exception ex)
{
Debug.Write("YL0140Q Exception :" + ex.Message);
myData.outMsg = ex.Message;
throw ex;
}
finally
{
db.getOcnn().Close();
db.DBDisconnect();
}
return(myData);
}
public static CReturnData getProKind2(string strProKind2)//B1010
{
CReturnData myData = new CReturnData();
List <Dictionary <string, string> > list = MIPCode.getProductList(strProKind2);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
//string jsonResult = fastJSON.JSON.ToJSON(list);
string jsonResult = serializer.Serialize(list);
myData.returnData = jsonResult;
myData.nRet = 0;
return(myData);
}
/// <summary>
///
/// </summary>
/// <param name="deviceid"></param>
/// <param name="phonetype"></param>
/// <param name="sqltype"></param>
/// <param name="filename"></param>
/// <param name="version"></param>
public void DownLoadLog(string deviceid, string phonetype, string sqltype, string filename, string version)
{
Database db = new Database();
// DataTable dt = new DataTable();
CReturnData myData = new CReturnData();
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
//Log下載記錄
//string StrSQL = "INSERT INTO userDownloadLog(deviceid, phonetype, [version], sqltype, [datetime]) "
// + "SELECT '" + deviceid + "', '" + phonetype + "', '" + version + "', '" + sqltype + "', GETDATE() ";
string StrSQL = "INSERT INTO userDownloadLog(deviceid, phonetype, [version], sqltype, [datetime]) "
+ "SELECT @deviceid, @phonetype, @version,@sqltype, GETDATE() ";
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(StrSQL, db.getOcnn());
sqlCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@deviceid", MDS.Utility.NUtility.checkString(deviceid)));
sqlCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@phonetype", MDS.Utility.NUtility.checkString(phonetype)));
sqlCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@version", MDS.Utility.NUtility.checkString(version)));
sqlCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sqltype", MDS.Utility.NUtility.checkString(sqltype)));
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(StrSQL);
db.CommitTranscation();
int nAffectedRowCount = -1;
myData.nRet = db.ExecNonQuerySQLCommand(sqlCmd, ref nAffectedRowCount);
//myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
db.DBDisconnect();
}
myData = null;
db = null;
}
public static CReturnData getProKind3(string strProKind3)//B1010
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
StringBuilder sbSQL = new StringBuilder();
System.Data.SqlClient.SqlCommand cmd = null;
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
try
{
using (cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = db.getOcnn();
sbSQL.Append("select count(*) from MIP_HAPPY where CKEY3=@CKEY3");
cmd.CommandText = sbSQL.ToString();
cmd.Parameters.Add(new SqlParameter("@CKEY3", strProKind3));
db.ExecQuerySQLCommand(cmd, ref dt);
myData.outMsg = dt.Rows[0][0].ToString();
}
}
catch (Exception)
{
throw;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
return(myData);
}
public static CReturnData getProKind(string strProKind)
{
CReturnData myData = new CReturnData();
MipSystemModule m = new MipSystemModule();
m.SQL = "select FunctionDesc,SysFuncID,b.ModuleDesc,a.SysModID from SystemFunction a, SystemModule b where a.SysModID=b.SysModID and SysFuncID=@SysFuncID order by a.iOrder ";
m.KEY = "SysModID";
m.NAME = "ModuleDesc";
m.ps = new System.Data.SqlClient.SqlParameter("@SysFuncID", strProKind);
List <Dictionary <string, string> > list = m.getProductList();
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
//string jsonResult = fastJSON.JSON.ToJSON(list);
string jsonResult = serializer.Serialize(list);
myData.returnData = jsonResult;
return(myData);
}
public CReturnData SendMSMQ(string QueName, object strXML)
{
CReturnData myData = new CReturnData();
//SendMSMQ START==========================================
try
{
MDSQueue queue = new MDSQueue(".", string.Format("{0}\\{1}", "Private$", QueName)); //2015-01-21 modify
queue.SendMesageQueue((object)strXML);
myData.nRet = 0;
myData.outMsg = "SendMSMQ操作成功";
}
catch (Exception ex)
{
myData.nRet = -1;
myData.outMsg = string.Format("SendMessageQueue failed, 錯誤訊息: {0}", ex.Message);
}
//SendMSMQ END==========================================
return(myData);
}
public static CReturnData Delete_NEWS(string strNewsList)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
int nRet = -1;
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
SqlTransaction sqlTrans = null;
try
{
string[] strDelProId = strNewsList.Split(new String[] { "^^" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string delId in strDelProId)
{
/*產生刪除 PushServiceData推播服務紀錄資料表的SQL*/
string strSQL = "DELETE FROM MIP_SYS_INFO WHERE CKEY=@CKEY ";
sqlTrans = db.getOcnn().BeginTransaction();
/*連線DB*/
SqlCommand SqlCom = new SqlCommand(strSQL, db.getOcnn(), sqlTrans);
SqlCom.Parameters.Add(new SqlParameter("@CKEY", delId));
Debug.Write("Transaction:" + SqlCom.Transaction);
nRet = SqlCom.ExecuteNonQuery();
string outMsg = db.outMsg;
Debug.Write("nRet:" + nRet);
Debug.Write("outMsg:" + outMsg);
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
sqlTrans.Commit();
if (nRet == -1)//失敗
{
throw new Exception(db.outMsg);
}
}//for
}
catch (Exception ex)
{
Debug.Write("YL0140Q->Delete_KV Exception :" + ex.Message);
if (sqlTrans != null)
{
sqlTrans.Rollback();
}
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
return(myData);
}
public static CReturnData Delete_Action(string strNewsList)
{
CReturnData myData = new CReturnData();
Database db = new Database();
int nRet = -1;
/*連線DB*/
SqlTransaction sqlTrans = null;
try
{
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
string[] strDelProId = strNewsList.Split(new String[] { "^^" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string delId in strDelProId)
{
string strSQL = "delete from SystemAction where SysActionID=@SysActionID ";
/*連線DB*/
SqlCommand SqlCom = new SqlCommand(strSQL, db.getOcnn());
SqlCom.Parameters.Add(new SqlParameter("@SysActionID", delId));
nRet = SqlCom.ExecuteNonQuery();
strSQL = "delete from SecurityUserAccount_ActionRole where SysActionID=@SysActionID ";
SqlCom = new SqlCommand(strSQL, db.getOcnn());
SqlCom.Parameters.Add(new SqlParameter("@SysActionID", delId));
nRet = SqlCom.ExecuteNonQuery();
string outMsg = db.outMsg;
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
if (nRet == -1)//失敗
{
throw new Exception(db.outMsg);
}
}//for
}
catch (Exception ex)
{
if (sqlTrans != null)
{
sqlTrans.Rollback();
}
throw ex;
}
finally
{
db.getOcnn().Close();
db.DBDisconnect();
}
return(myData);
}
private void updateHappyMain(
string str_radType
, string str_dlDataType
, string str_dlDataClass
, string str_txtTitle
, string str_txtUrl
, string str_txtOrder
, string str_radStatus
, string chk
, string str_chkTesterView)
{
string isChkALL = null;
if (Request.Params["_hidChkALL"] == "0")
{
isChkALL = "0";
}
else
{
isChkALL = "1";
}
string isRCorRM = "";
if (!string.IsNullOrEmpty((Request.Params["_isRcOrRm"])))
{
isRCorRM = (Request.Params["_isRcOrRm"]);
}
else
{
isRCorRM = strDEPT_ID;
}
int nRet = -1;
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
StringBuilder sbSQL = new StringBuilder();
SqlTransaction sqlTrans = null;
System.Data.SqlClient.SqlCommand cmd = null;
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
System.Data.SqlClient.SqlConnection connection = db.getOcnn();
sqlTrans = connection.BeginTransaction();
try
{
using (cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.Transaction = sqlTrans;
cmd.Parameters.Clear();
//新增
sbSQL.Append(" UPDATE MIP_Happy ");
sbSQL.Append(" SET ");
sbSQL.Append(" CSTATUS = @CSTATUS ");
sbSQL.Append(" , APPLY_TARGET = @APPLY_TARGET ");
sbSQL.Append(" , CKEY1 = @CKEY1 ");
sbSQL.Append(" , CKEY2 = @CKEY2 ");
sbSQL.Append(" , CKEY3 = @CKEY3 ");
sbSQL.Append(" , TITLE = @TITLE ");
sbSQL.Append(" , CORDER = @CORDER ");
sbSQL.Append(" , LDATE = @LDATE ");
sbSQL.Append(" , LUSER = @LUSER ");
sbSQL.Append(" , SELECTALL = @SELECTALL ");
sbSQL.Append(" , ISTESTER = @ISTESTER ");
sbSQL.Append(" where HAPPY_ID = @HAPPY_ID ");
/*連線DB*/
cmd.Parameters.Add(new SqlParameter("@HAPPY_ID", proIdPK)); //主表key
cmd.Parameters.Add(new SqlParameter("@CSTATUS", str_radStatus)); //啟用
cmd.Parameters.Add(new SqlParameter("@FILE_KIND", DBNull.Value)); //0:檔案 1:連結 2:階有
cmd.Parameters.Add(new SqlParameter("@APPLY_TARGET", DBNull.Value)); //
cmd.Parameters.Add(new SqlParameter("@CKEY1", str_radType)); //資料分類一
cmd.Parameters.Add(new SqlParameter("@CKEY2", str_dlDataType)); //資料分類二
cmd.Parameters.Add(new SqlParameter("@CKEY3", str_dlDataClass)); //資料分類三
cmd.Parameters.Add(new SqlParameter("@TITLE", str_txtTitle)); //
cmd.Parameters.Add(new SqlParameter("@CORDER", str_txtOrder)); //排序
cmd.Parameters.Add(new SqlParameter("@SELECTALL", isChkALL)); //全選 或 非全選
cmd.Parameters.Add(new SqlParameter("@LDATE", MIPUtil.getDataTimeNow()));
cmd.Parameters.Add(new SqlParameter("@LUSER", HttpContext.Current.Session["UserID"].ToString()));
cmd.Parameters.Add(new SqlParameter("@ISTESTER", str_chkTesterView));
cmd.CommandText = sbSQL.ToString();
nRet = cmd.ExecuteNonQuery();
//新增成功 開始新增 發送對像
if (nRet != -1)
{
nRet = insert_MIP_MSG_TARGET(cmd, isChkALL, proIdPK, isRCorRM, chk);
if (nRet == -1)
{
MessageBox("新增資料失敗!!");
cmd.Transaction.Rollback();
}
}
//新增成功 開始新增檔案 若有檔案便刪除
if (nRet != -1)
{
//儲存上傳檔案
nRet = uploadFileProcess(cmd);
if (nRet == -1)
{
MessageBox("新增資料失敗!!");
cmd.Transaction.Rollback();
}
else
{
cmd.Transaction.Commit();
uploadOK = "SUCCESS";
}
}
}
}
catch (Exception ex)
{
MessageBox("新增資料失敗!!");
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
}
public static CReturnData AddNewsProcess(string strActionID, string strActionDesc, string strButtonID, string strFunctionID, string strModID)
{
int nRet = -1;
Database db = new Database();
DataTable dt = new DataTable();
CReturnData myData = new CReturnData();
string StrSQL = " ";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
//key
try
{
////功能設定維護
StrSQL = @"INSERT INTO SystemAction (SysActionID,SysFuncID,ButtonID,SysModID,ActionDesc)
values(
@strActionID
,@strFunctionID
,@strButtonID
,@strModID
,@strActionDesc
)";
SqlCommand SqlCom = new SqlCommand(StrSQL, db.getOcnn());
SqlCom.Parameters.Add(new SqlParameter("@strActionID", strActionID));
SqlCom.Parameters.Add(new SqlParameter("@strFunctionID", strFunctionID));
SqlCom.Parameters.Add(new SqlParameter("@strButtonID", strButtonID));
SqlCom.Parameters.Add(new SqlParameter("@strModID", strModID));
SqlCom.Parameters.Add(new SqlParameter("@strActionDesc", strActionDesc));
nRet = SqlCom.ExecuteNonQuery();
//nRet = db.ExecQuerySQLCommand(StrSQL, ref dt);
string outMsg = db.outMsg;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
return(myData);
}
public static CReturnData EdtProcess(string ContactID, string Str_oGroupList_selected, string oContactName, string oTitle, string oTel1, string oTel2, string oTel3, string oEMail, string oMemo, string myAccountName, string myAccountID)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
string myGUID = System.Guid.NewGuid().ToString();
string ModuleDesc = ParseWording("B0136");
string FunctionDesc = ParseWording("B0139");
string ActionDesc = ParseWording("B0059");
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
string StrSQL = "/*取得修改前的 群組與聯絡人關連表 字串 */ " +
"DECLARE @STR_GroupID varchar(max) " +
"SET @STR_GroupID = '' " +
"SELECT @STR_GroupID = @STR_GroupID + GroupID + ',' FROM SecurityGroup_ContactRelation WHERE ContactID = " + ContactID + " " +
"IF LEN(@STR_GroupID) > 0 BEGIN SET @STR_GroupID = LEFT(@STR_GroupID, LEN(@STR_GroupID) - 1) END " +
"/*操作紀錄-修改畫面 */ " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0012") + "', @STR_GroupID, N'" + Str_oGroupList_selected.Replace("||", ",") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0007") + "', ContactName, N'" + oContactName.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM SecurityGroup_Contact WHERE ContactID = " + ContactID + " " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0008") + "', Title, N'" + oTitle.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM SecurityGroup_Contact WHERE ContactID = " + ContactID + " " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("A0019") + "(1)', Tel1, N'" + oTel1 + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM SecurityGroup_Contact WHERE ContactID = " + ContactID + " " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("A0019") + "(2)', Tel2, N'" + oTel2 + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM SecurityGroup_Contact WHERE ContactID = " + ContactID + " " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("A0019") + "(3)', Tel3, N'" + oTel3 + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM SecurityGroup_Contact WHERE ContactID = " + ContactID + " " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0060") + "', EMail, N'" + oEMail.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM SecurityGroup_Contact WHERE ContactID = " + ContactID + " " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("A0021") + "', Memo, N'" + oMemo.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() FROM SecurityGroup_Contact WHERE ContactID = " + ContactID + " " +
"/*聯絡人資料 更新 */ " +
"UPDATE SecurityGroup_Contact " +
"SET ContactName = N'" + oContactName.Replace("'", "''") + "' " +
", Title = N'" + oTitle.Replace("'", "''") + "' " +
", Tel1 = '" + oTel1 + "' " +
", Tel2 = '" + oTel2 + "' " +
", Tel3 = '" + oTel3 + "' " +
", EMail = '" + oEMail + "' " +
", Memo = N'" + oMemo.Replace("'", "''") + "' " +
"WHERE ContactID = " + ContactID + " " +
"/*群組與聯絡人關連表 更新 */ " +
"DELETE FROM SecurityGroup_ContactRelation WHERE ContactID = " + ContactID + " " +
"INSERT INTO SecurityGroup_ContactRelation(GroupID, ContactID) " +
"SELECT [Value], " + ContactID + " FROM dbo.UTILfn_Split('" + Str_oGroupList_selected + "', ',') ";
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(StrSQL);
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
db.DBDisconnect();
// string filepath = @"c:\temp\posttestLog\";
// System.IO.File.WriteAllText(filepath + "EDTProcess.txt",
// "\nContactID " + ContactID
// + "\nStr_oGroupList_selected " + Str_oGroupList_selected
//+ "\n oContactName " + oContactName
//+ "\n oTitle " + oTitle
//+ "\n oTel1 " + oTel1
//+ "\n oTel2 " + oTel2
//+ "\n oTel3 " + oTel3
//+ "\n oEMail " + oEMail
//+ "\n oMemo " + oMemo
//+ "\n myAccountName " + myAccountName
//+ "\n myAccountID " + myAccountID
// + "\n<>" + StrSQL);
}
return(myData);
}
public static CReturnData AddProcess(
string GroupID, string AccountID, string rePwd
, string Name, string Description
, string Startup, string PWType
, string cRoleID, string HiddenUserDefine
, string HiddenFunctionList, string HiddenActionList, string cCallID
, string GroupDESC, string PWTypeDESC, string cRoleDESC, string myAccountName, string myAccountID)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
string myGUID = System.Guid.NewGuid().ToString();
string ModuleDesc = ParseWording("B0136");
string FunctionDesc = ParseWording("B0137");
string ActionDesc = ParseWording("A0001");
string StrStartup = (Startup == "1") ? "V" : "";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
string StrSQL = "/*使用者資料表*/ " +
"INSERT INTO SecurityUserAccount(GroupID, AccountID, Name, Description, Password, Startup, PWLastUpdateTime, PWType, CreateTime, dModifyTime, iFailTimes, dLockTime, cRoleID, AD_CheckFlag, cCallID, cPWD) " +
"SELECT '" + GroupID + "', '" + MDS.Utility.NUtility.trimBad(AccountID) + "', N'" + Name.Replace("'", "''") + "', N'" + Description.Replace("'", "''") + "', CONVERT(varbinary, '" + rePwd + "'), '" + Startup + "', NULL, " + PWType + ", GETDATE(), GETDATE(), 0, NULL, " + cRoleID + ", 0, '" + cCallID + "', '" + cCallID + "' " +
"/*連離線狀態表*/ " +
"INSERT INTO LinetStatus(DateTime, AccountID, Type, Status, TxDateTime, TransDateTime) " +
"SELECT GETDATE(), '" + AccountID + "', 1, 2, null, null " +
"/*個人密碼紀錄表*/ " +
"INSERT INTO SecurityUserPwd(AccountID, bPassword, iOrder, kind, Ldate ) " +
"SELECT '" + AccountID + "', CONVERT(varbinary, '" + AccountID + System.DateTime.Now.ToString("yyyymmdd") + "'), 1 , 0, GETDATE() " +
"/*使用者進行權限微調*/ " +
"IF (" + HiddenUserDefine + " = 1) BEGIN " +
"INSERT INTO SecurityUserAccount_FunctionRole(AccountID, SysModID, SysFuncID) " +
"SELECT '" + AccountID + "', SystemFunction.SysModID, [Value] " +
"FROM dbo.UTILfn_Split('" + HiddenFunctionList + "', '||') AS tblFunction " +
"INNER JOIN SystemFunction ON tblFunction.Value = SystemFunction.SysFuncID " +
"INSERT INTO SecurityUserAccount_ActionRole(AccountID, SysModID, SysFuncID, SysActionID) " +
"SELECT '" + AccountID + "', SystemAction.SysModID, SystemAction.SysFuncID, [Value] " +
"FROM dbo.UTILfn_Split('" + HiddenActionList + "', '||') AS tblAction " +
"INNER JOIN SystemAction ON tblAction.Value = SystemAction.SysActionID " +
"END " +
"/*預設權限*/ " +
"ELSE BEGIN " +
"INSERT INTO SecurityUserAccount_FunctionRole(AccountID, SysModID, SysFuncID) " +
"SELECT '" + AccountID + "', SysModID, SysFuncID " +
"FROM DMSRoleFunction " +
"WHERE DMSRoleID = " + cRoleID + " " +
"INSERT INTO SecurityUserAccount_ActionRole(AccountID, SysModID, SysFuncID, SysActionID) " +
"SELECT '" + AccountID + "', SysModID, SysFuncID, SysActionID " +
"FROM DMSRoleAction " +
"WHERE DMSRoleID = " + cRoleID + " " +
"END " +
"/*操作紀錄-新增畫面*/" +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0012") + "', '', N'" + GroupDESC + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0006") + "', '', N'" + AccountID + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0007") + "', '', N'" + Name.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0008") + "', '', N'" + Description.Replace("'", "''") + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0011") + "', '', N'" + StrStartup + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0013") + "', '', N'" + PWTypeDESC + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0010") + "', '', N'" + cRoleDESC + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() " +
"/*操作紀錄-新增畫面-權限微調*/" +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "'" +
", N'" + ModuleDesc + "'" +
", N'" + FunctionDesc + "'" +
", N'" + ActionDesc + "'" +
", N'" + ParseWording("B0015") + "' + '_' + SystemModule.ModuleDesc + '_' + SystemFunction.FunctionDesc " +
", ''" +
", (CASE " +
"WHEN SecurityUserAccount_FunctionRole.SysFuncID IS NULL THEN '' " +
"ELSE 'V' " +
"END) AS FunctionRoleCheck " +
", N'" + myAccountName + "'" +
", N'" + myAccountID + "'" +
", GETDATE() " +
"FROM SystemModule " +
"INNER JOIN SystemFunction ON SystemModule.SysModID = SystemFunction.SysModID " +
"INNER JOIN DMSRoleFunction ON SystemFunction.SysFuncID = DMSRoleFunction.SysFuncID AND DMSRoleFunction.DMSRoleID = " + cRoleID + " " +
"LEFT JOIN SecurityUserAccount_FunctionRole ON DMSRoleFunction.SysFuncID = SecurityUserAccount_FunctionRole.SysFuncID AND SecurityUserAccount_FunctionRole.AccountID = '" + AccountID + "' " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "'" +
", N'" + ModuleDesc + "'" +
", N'" + FunctionDesc + "'" +
", N'" + ActionDesc + "'" +
", N'" + ParseWording("B0015") + "' + '_' + SystemModule.ModuleDesc + '_' + SystemFunction.FunctionDesc + '_' + SystemAction.ActionDesc " +
", ''" +
", (CASE " +
"WHEN SecurityUserAccount_ActionRole.SysActionID IS NULL THEN '' " +
"ELSE 'V' " +
"END) AS ActionRoleCheck " +
", N'" + myAccountName + "'" +
", N'" + myAccountID + "'" +
", GETDATE() " +
"FROM SystemModule " +
"INNER JOIN SystemFunction ON SystemModule.SysModID = SystemFunction.SysModID " +
"INNER JOIN SystemAction ON SystemFunction.SysFuncID = SystemAction.SysFuncID " +
"INNER JOIN DMSRoleAction ON SystemAction.SysActionID = DMSRoleAction.SysActionID AND DMSRoleAction.DMSRoleID = " + cRoleID + " " +
"LEFT JOIN SecurityUserAccount_ActionRole ON DMSRoleAction.SysActionID = SecurityUserAccount_ActionRole.SysActionID AND SecurityUserAccount_ActionRole.AccountID = '" + AccountID + "' ";
/*判斷有無語音系統, 若有才寫入操作紀錄*/
if (CheckSysAttribute("System", "VoiceSystem") == "1")
{
StrSQL += "INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT '" + myGUID + "', N'" + ModuleDesc + "', N'" + FunctionDesc + "', N'" + ActionDesc + "', N'" + ParseWording("B0014") + "', '', N'" + cCallID + "', N'" + myAccountName + "', N'" + myAccountID + "', GETDATE() ";
}
/*寫入DB*/
db.BeginTranscation();
db.AddDmsSqlCmd(StrSQL);
db.CommitTranscation();
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
db.DBDisconnect();
}
return(myData);
}
//寫入db MIP_HAPPY
private void addMipLifeMain(
string str_radType
, string str_dlDataType
, string str_dlDataClass
, string str_txtTitle
, string str_txtUrl
, string str_txtOrder
, string str_radStatus
, string chk
, string str_chkTesterView)
{
string isChkALL = null;
if (Request.Params["_hidChkALL"] == "0")
{
isChkALL = "0";
}
else
{
isChkALL = "1";
}
string isRCorRM = "";
if (!string.IsNullOrEmpty((Request.Params["_isRcOrRm"])))
{
isRCorRM = (Request.Params["_isRcOrRm"]);
}
int nRet = -1;
CReturnData myData = new CReturnData();
//string mlireply = "";
Database db = new Database();
DataTable dt = new DataTable();
StringBuilder sbSQL = new StringBuilder();
SqlTransaction sqlTrans = null;
System.Data.SqlClient.SqlCommand cmd = null;
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
HAPPY_ID = MIPUtil.getFILE_INDEX_SEQ(db.getOcnn());
if (myData.nRet == 0)
{
System.Data.SqlClient.SqlConnection connection = db.getOcnn();
sqlTrans = connection.BeginTransaction();
try
{
using (cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = connection;
cmd.Transaction = sqlTrans;
cmd.Parameters.Clear();
//key
//int strLifeId = MIPUtil.getFILE_INDEX_SEQ(db.getOcnn(), sqlTrans);
//新增
sbSQL.Append(" insert into ");
sbSQL.Append(" MIP_HAPPY ");
sbSQL.Append(" (HAPPY_ID, CSTATUS , APPLY_TARGET, CKEY1, CKEY2, CKEY3, TITLE, CORDER, LDATE, LUSER, SELECTALL, ISTESTER) ");
sbSQL.Append(" values(@HAPPY_ID, @CSTATUS, @APPLY_TARGET, @CKEY1, @CKEY2, @CKEY3, @TITLE, @CORDER, @LDATE, @LUSER, @SELECTALL, @ISTESTER) ");
// 宣告DAO
MIP_HAPPY mip_happy_impl = new MIP_HAPPY();
// 設定參數
mip_happy_impl.HAPPY_ID = HAPPY_ID;
mip_happy_impl.CSTATUS = int.Parse(str_radStatus);
mip_happy_impl.APPLY_TARGET = 0;
mip_happy_impl.CKEY1 = str_radType;
mip_happy_impl.CKEY2 = str_dlDataType;
mip_happy_impl.CKEY3 = str_dlDataClass;
mip_happy_impl.TITLE = str_txtTitle;
mip_happy_impl.CORDER = int.Parse(str_txtOrder);
mip_happy_impl.SELECTALL = int.Parse(isChkALL);
mip_happy_impl.LDATE = DateTime.Now;
mip_happy_impl.LUSER = HttpContext.Current.Session["UserID"].ToString();
mip_happy_impl.ISTESTER = int.Parse(str_chkTesterView);
// 執行
nRet = mip_happy_impl.Insert(cmd);
//新增成功
if (nRet != -1)
{
nRet = insert_MIP_MSG_TARGET(cmd, isChkALL, HAPPY_ID, isRCorRM, chk);
if (nRet == -1)
{
MessageBox("新增資料失敗!!");
cmd.Transaction.Rollback();
}
}
//新增成功
if (nRet != -1)
{
//儲存上傳檔案
nRet = uploadFileProcess(cmd);
if (nRet == -1)
{
MessageBox("新增資料失敗!!");
cmd.Transaction.Rollback();
}
else
{
cmd.Transaction.Commit();
uploadOK = "SUCCESS";
}
}
}
}
catch (Exception ex)
{
ex.StackTrace.ToString();
MessageBox("新增資料失敗!!");
cmd.Transaction.Rollback();
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
}
public CReturnData AddPushProcessToMSMQForICare(string pushType, string phonetype, string limtKind, string limtCndi, string msgalert, string msgsound, string status, string MQueueName)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
string jobid = System.Guid.NewGuid().ToString();
string Strmsgsound = "";
if (msgsound == "1")
{
Strmsgsound = "default";
}
/*
* if (phonetype == "IOS")
* {
* phonetype = "iphone";
* }
*/
if (limtKind.Equals("0"))
{
limtCndi = "";
}
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
/*
* string StrSQL = "INSERT INTO PushServiceData(jobid, appname, phonetype, msgalert, msgbadge, msgsound, status, createdatetime) "
+ "SELECT '" + jobid + "', '" + appname + "', '" + phonetype + "', '" + msgalert.Replace("'", "''") + "', NULL, '" + Strmsgsound + "', " + status + ", GETDATE() ";
*/
string appname = "";
if (pushType.StartsWith("iCare"))
{
appname = "icare";
}
else if (pushType.StartsWith("iAgent"))
{
appname = "iagent";
}
else
{
//全部
appname = "iall";
}
string StrSQL = "INSERT INTO PushServiceData(jobid, appname, phonetype, msgalert, msgbadge, msgsound, status, createdatetime,pushtype,limtKind,limtCndi) "
+ "SELECT @jobid , @appname , @phonetype , "
+ " @msgalert , NULL, @Strmsgsound , @status , GETDATE(), @pushType , @limtKind , @limtCndi ";
/*寫入DB*/
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand(StrSQL, db.getOcnn());
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@jobid", jobid));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@appname", MDS.Utility.NUtility.trimBad(appname)));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@phonetype", MDS.Utility.NUtility.trimBad(phonetype)));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@msgalert", MDS.Utility.NUtility.trimBad(msgalert)));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Strmsgsound", MDS.Utility.NUtility.trimBad(Strmsgsound)));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@status", MDS.Utility.NUtility.trimBad(status)));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@pushType", MDS.Utility.NUtility.trimBad(pushType)));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@limtKind", MDS.Utility.NUtility.trimBad(limtKind)));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@limtCndi", MDS.Utility.NUtility.trimBad(limtCndi)));
//呼叫傳入參數式sqlcmd的方法
int affectRows = 0;
myData.nRet = db.ExecNonQuerySQLCommand(SqlCom, ref affectRows);
myData.outMsg = db.outMsg;
//如果新增的資料為啟用, 進行MSMQ動作 .
if (status == "1")
{
//產生推播服務所需要的XML .
StringBuilder sb = new StringBuilder("");
sb.Append(@"DECLARE @tempSourceData TABLE(RowID int identity(1,1) primary key not null, PushID varchar(max),phonetype varchar(50))
INSERT INTO @tempSourceData (PushID,phonetype)
SELECT DISTINCT pushid,phonetype
FROM MobileDeviceData
WHERE 1=1
AND status = 1
AND pushid <> '' ");
if (appname.ToLower().Equals("icare"))
{
if (pushType.Equals("iCareAll"))
{//iCare全部
sb.Append(" AND appname = 'icare' ");
}
else if (pushType.Equals("iCareInsured"))
{//iCare保戶 有AccountID
sb.Append(" AND ( appname = 'icare' AND AccountID IS NOT NULL AND AccountID <> '' ) ");
}
else if (pushType.Equals("iCarePublic"))
{//iCare大眾 無AccountID
sb.Append(" AND ( appname = 'icare' AND ( AccountID IS NULL OR AccountID = '' ) ) ");
}
}
else if (appname.ToLower().Equals("iagent"))
{
sb.Append(" AND appname = 'iagent' ");
}
else
{
//全部
}
if (phonetype != null && !phonetype.Equals(""))
{
if (phonetype.ToLower().ToString().Equals("ios"))
{
sb.Append(" AND LOWER(phonetype) in ('ios','iphone','ipad') ");
}
else if (phonetype.ToLower().ToString().Equals("android"))
{
sb.Append(" AND LOWER(phonetype) in ('android') ");
}
else
{
sb.Append(" AND LOWER(phonetype) in ('ios','iphone','ipad','android') ");
}
}
if (limtKind.Equals("1"))
{
sb.Append(" AND AccountID=@limtCndi");
}
if (limtKind.Equals("2"))
{
sb.Append(" AND deviceid=@limtCndi");
}
logger.Debug("推播對象:" + sb.ToString());
sb.Append(" SELECT * from @tempSourceData ");
StrSQL = sb.ToString();
System.Data.SqlClient.SqlCommand SqlCom2 = new System.Data.SqlClient.SqlCommand(MDS.Utility.NUtility.checkString(StrSQL), db.getOcnn());
SqlCom2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@limtCndi", MDS.Utility.NUtility.trimBad(limtCndi)));
//呼叫傳入參數式sqlcmd的方法
myData.nRet = db.ExecQuerySQLCommand(SqlCom2, ref dt);
myData.outMsg = db.outMsg;
try
{
List <PushVo> pushVoList = new List <PushVo>();
foreach (DataRow row in dt.Rows)
{
PushVo pushVo = new PushVo();
//傳入參數1.可識別ID 2.推播的內容 3,目的的Queue名稱
//payload.DeviceToken = row["PushID"].ToString();
//payload.Message = msgalert.Replace("'", "''");
//PushHelper.SendNotification(jobID, payload, "icare");
pushVo.sound = Strmsgsound;
pushVo.deviceToken = row["PushID"].ToString();
pushVo.message = msgalert.Replace("'", "''");
string qphonetype = row["phonetype"].ToString();
if (qphonetype.ToLower().ToString().Equals("ios"))
{
pushVo.deviceType = "ios";
}
else
{
pushVo.deviceType = "android";
}
if (appname.Equals("icare"))
{
pushVo.mQueueName = "icare";
}
else
{
pushVo.mQueueName = "iagent";
}
//logger.Debug("qphonetype=" + qphonetype);
//logger.Debug("pushVo.message=" + pushVo.message);
//logger.Debug("pushVo.deviceType=" + pushVo.deviceType);
//logger.Debug("pushVo.mQueueName=" + pushVo.mQueueName);
//logger.Debug("pushVo.deviceToken=" + pushVo.deviceToken);
//logger.Debug("pushVo.sound=" + pushVo.sound);
pushVoList.Add(pushVo);
}//end foreach
MIPPushUtil.pushMessage(pushVoList);
int rowCnt = dt.Rows.Count;
/*未啟用過的才動作*/
StrSQL = "UPDATE PushServiceData "
+ " SET status = 1,rowcnt= @rowCnt WHERE "
+ " jobid =@jobid";
System.Data.SqlClient.SqlCommand SqlCom3 = new System.Data.SqlClient.SqlCommand(StrSQL, db.getOcnn());
SqlCom3.Parameters.Add(new System.Data.SqlClient.SqlParameter("@rowCnt", rowCnt));
SqlCom3.Parameters.Add(new System.Data.SqlClient.SqlParameter("@jobid", jobid));
//呼叫傳入參數式sqlcmd的方法
int affectedRowCnt = 0;
myData.nRet = db.ExecNonQuerySQLCommand(SqlCom3, ref affectedRowCnt);
myData.outMsg = db.outMsg;
myData.nRet = 0;
myData.outMsg = "成功將推播資訊送至MSMQ！";
}
catch (Exception ex)
{
//this._txtInfo.Text += "error=" + ex.Message;
myData.outMsg = ex.Message;
myData.nRet = -1;
}
finally
{
dt.Clear();
dt.Dispose();
dt = null;
db.DBDisconnect();
}
}
else
{
myData.nRet = 0;
myData.outMsg = "沒有立即需要推播的資料!!";
}
}
return(myData);
}
public static CReturnData LoadData(CParam param)
{
CReturnData result = new CReturnData();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
if (!UserAcc.UserExpired())
{
result.incData = new cDataInc();
result.lstComplaint = new List <cComplaint>();
result.lstStatus = new List <sysGlobalClass.T_TEPI_Workflow>();
int nIndID = SystemFunction.GetIntNullToZero(param.sIndID);
int nOprtID = SystemFunction.GetIntNullToZero(param.sOprtID);
int nFacID = SystemFunction.GetIntNullToZero(param.sFacID);
result.hdfPRMS = SystemFunction.GetPermission_EPI_FROMS(nIndID, nFacID) + "";
string sYear = param.sYear;
var itemPrdInc = db.mTProductIndicator.FirstOrDefault(w => w.IDIndicator == 1);
result.incData.ProductID = itemPrdInc.ProductID;
result.incData.sUnit = itemPrdInc.sUnit;
result.incData.sProductName = itemPrdInc.ProductName;
result.incData.nUnitID = 66;
result.incData.sTarget = "";
#region EPI_FORM
var itemEPI_FORM = db.TEPI_Forms.FirstOrDefault(w => w.sYear == sYear && w.IDIndicator == nIndID && w.OperationTypeID == nOprtID && w.FacilityID == nFacID);
if (itemEPI_FORM != null)
{
var itemDataComplaint = db.TComplaint_Product.FirstOrDefault(w => w.FormID == itemEPI_FORM.FormID);
#region Data Indicator
if (itemDataComplaint != null)
{
var itemUnit = db.mTUnit.FirstOrDefault(w => w.UnitID == itemDataComplaint.UnitID);
result.incData.nUnitID = itemDataComplaint.UnitID;
result.incData.sUnit = itemUnit != null ? itemUnit.UnitName : result.incData.sUnit;
result.incData.sTarget = itemDataComplaint.Target;
result.incData.M1 = itemDataComplaint.M1;
result.incData.M2 = itemDataComplaint.M2;
result.incData.M3 = itemDataComplaint.M3;
result.incData.M4 = itemDataComplaint.M4;
result.incData.M5 = itemDataComplaint.M5;
result.incData.M6 = itemDataComplaint.M6;
result.incData.M7 = itemDataComplaint.M7;
result.incData.M8 = itemDataComplaint.M8;
result.incData.M9 = itemDataComplaint.M9;
result.incData.M10 = itemDataComplaint.M10;
result.incData.M11 = itemDataComplaint.M11;
result.incData.M12 = itemDataComplaint.M12;
result.incData.IsCheckM1 = itemDataComplaint.IsCheckM1;
result.incData.IsCheckM2 = itemDataComplaint.IsCheckM2;
result.incData.IsCheckM3 = itemDataComplaint.IsCheckM3;
result.incData.IsCheckM4 = itemDataComplaint.IsCheckM4;
result.incData.IsCheckM5 = itemDataComplaint.IsCheckM5;
result.incData.IsCheckM6 = itemDataComplaint.IsCheckM6;
result.incData.IsCheckM7 = itemDataComplaint.IsCheckM7;
result.incData.IsCheckM8 = itemDataComplaint.IsCheckM8;
result.incData.IsCheckM9 = itemDataComplaint.IsCheckM9;
result.incData.IsCheckM10 = itemDataComplaint.IsCheckM10;
result.incData.IsCheckM11 = itemDataComplaint.IsCheckM11;
result.incData.IsCheckM12 = itemDataComplaint.IsCheckM12;
}
#endregion
#region Data Complaint
result.lstComplaint = db.TComplaint.Where(w => w.FormID == itemEPI_FORM.FormID).Select(s => new cComplaint
{
nComplaintID = s.nComplaintID,
nComplaintTypeID = s.nComplaintTypeID,
ComplaintDate = s.ComplaintDate.HasValue ? s.ComplaintDate.Value : (DateTime?)null,
sIssueBy = s.sIssueBy,
sSubject = s.sSubject,
nComplaintByID = s.nComplaintByID,
sComplaintByOther = s.sComplaintByOther,
sDetail = s.sDetail,
sCorrectiveAction = s.sCorrectiveAction,
sStatus = s.sStatus,
nMonth = s.ComplaintDate.HasValue ? s.ComplaintDate.Value.Month : 0,
IsDel = false,
IsNew = false,
IsSubmited = true,
IsShow = true,
}).ToList();
result.lstComplaint.ForEach(f =>
{
f.sIssueDate = f.ComplaintDate.HasValue ? f.ComplaintDate.Value.ToString("dd/MM/yyyy") : "";
f.ComplaintDate = null;
});
var lstFileComplaint = db.TComplaint_File.Where(w => w.FormID == itemEPI_FORM.FormID).ToList();
var lstImpact = db.TComplaint_Impact.Where(w => w.FormID == itemEPI_FORM.FormID).ToList();
foreach (var itemComplaint in result.lstComplaint)
{
itemComplaint.lstImpact = new List <TComplaint_Impact>();
itemComplaint.lstImpact = lstImpact.Where(w => w.nComplaintID == itemComplaint.nComplaintID).Select(s => new TComplaint_Impact
{
nImpactTypeID = s.nImpactTypeID,
sOther = s.sOther
}).ToList();
itemComplaint.lstFile = new List <sysGlobalClass.FuncFileUpload.ItemData>();
itemComplaint.lstFile = lstFileComplaint.Where(w => w.nComplaintID == itemComplaint.nComplaintID).Select(s => new sysGlobalClass.FuncFileUpload.ItemData
{
ID = s.nFileID,
FileName = s.sFileName,
SaveToFileName = s.sSysFileName,
SaveToPath = s.sPath,
url = s.sPath + s.sSysFileName,
IsNewFile = false,
IsCompleted = true,
sDelete = "N",
sDescription = s.sDescription,
}).ToList();
}
#endregion
#region Status Month
result.lstStatus = db.TEPI_Workflow.Where(w => w.FormID == itemEPI_FORM.FormID).Select(s => new sysGlobalClass.T_TEPI_Workflow {
nMonth = s.nMonth, nStatusID = s.nStatusID
}).ToList();
#endregion
}
else
{
for (int i = 1; i <= 12; i++)
{
result.lstStatus.Add(new sysGlobalClass.T_TEPI_Workflow {
nMonth = i, nStatusID = 0
});
}
result.incData.sTarget = "";
result.incData.M1 = "";
result.incData.M2 = "";
result.incData.M3 = "";
result.incData.M4 = "";
result.incData.M5 = "";
result.incData.M6 = "";
result.incData.M7 = "";
result.incData.M8 = "";
result.incData.M9 = "";
result.incData.M10 = "";
result.incData.M11 = "";
result.incData.M12 = "";
result.incData.IsCheckM1 = "N";
result.incData.IsCheckM2 = "N";
result.incData.IsCheckM3 = "N";
result.incData.IsCheckM4 = "N";
result.incData.IsCheckM5 = "N";
result.incData.IsCheckM6 = "N";
result.incData.IsCheckM7 = "N";
result.incData.IsCheckM8 = "N";
result.incData.IsCheckM9 = "N";
result.incData.IsCheckM10 = "N";
result.incData.IsCheckM11 = "N";
result.incData.IsCheckM12 = "N";
}
#endregion
result.Status = SystemFunction.process_Success;
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
/// <summary>
/// 取得並產生群組選項;
/// </summary>
/// <param name="ContactID">要修改的聯絡人ID</param>
private void GetGroupList(string ContactID)
{
/*建立DB*/
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
string strParentGroupID = Session["ParentGroupID"] == null ? "" : Session["ParentGroupID"].ToString();
strParentGroupID = MDS.Utility.NUtility.trimBad(strParentGroupID);
/*Get oGroupList SQL*/
string StrSQL = "SELECT tblA.GroupID, tblA.GroupID + ' ' + tblA.GroupName AS GroupInfo, ISNULL(SecurityGroup_ContactRelation.ContactID, 0) AS ContactID " +
"FROM SecurityGroup AS tblA " +
"INNER JOIN dbo.fn_GetGroupTree(@ParentGroupID) AS tblT ON tblA.GroupID = tblT.GroupID " +
"LEFT JOIN SecurityGroup_ContactRelation ON tblA.GroupID = SecurityGroup_ContactRelation.GroupID AND SecurityGroup_ContactRelation.ContactID =@ContactID";
StringBuilder options = new StringBuilder("");
try
{
/*查詢DB*/
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand(StrSQL, db.getOcnn());
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ParentGroupID", strParentGroupID));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactID", ContactID));
//呼叫傳入參數式sqlcmd的方法
myData.nRet = db.ExecQuerySQLCommand(SqlCom, ref dt);
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["ContactID"].ToString() == "0")
{
options.Append(String.Format("<option value='{0}'>{1}</option>", dt.Rows[i]["GroupID"].ToString(), dt.Rows[i]["GroupInfo"].ToString()));
// oGroupList.Items.Add(new ListItem(dt.Rows[i]["GroupInfo"].ToString(), dt.Rows[i]["GroupID"].ToString()));
}
else
{
options.Append(String.Format("<option value='{0}' selected='selected'>{1}</option>", dt.Rows[i]["GroupID"].ToString(), dt.Rows[i]["GroupInfo"].ToString()));
//oGroupList_selected.Items.Add(new ListItem(dt.Rows[i]["GroupInfo"].ToString(), dt.Rows[i]["GroupID"].ToString()));
}
}
}
else
{
MessageBox(db.outMsg);
}
}
catch (Exception ex)
{
}
finally
{
db.DBDisconnect();
}
/*有錯誤則跳出警示視窗*/
if (myData.nRet != 0)
{
MessageBox(myData.nRet.ToString());
}
oGroupList = options.ToString();
}
public static CReturnData Delete_ContactID(string ContactIDList, string myAccountName, string myAccountID)
{
CReturnData myData = new CReturnData();
Database db = new Database();
DataTable dt = new DataTable();
string ModuleDesc = ParseWording("B0136");
string FunctionDesc = ParseWording("B0139");
string ActionDesc = ParseWording("B0151");
/*產生刪除帳號的SQL*/
string StrSQL = "/*先取得要刪除的資料*/ " +
"DECLARE @tmpTable TABLE(myGUID varchar(50), GroupInfo nvarchar(110), ContactName nvarchar(30)) " +
"INSERT INTO @tmpTable " +
"SELECT newid(), tblA.GroupID + ' ' + SecurityGroup.GroupName, SecurityGroup_Contact.ContactName " +
"FROM SecurityGroup_ContactRelation AS tblA " +
"INNER JOIN SecurityGroup ON tblA.GroupID = SecurityGroup.GroupID " +
"INNER JOIN SecurityGroup_Contact ON tblA.ContactID = SecurityGroup_Contact.ContactID " +
"WHERE tblA.GroupID + '##' + CONVERT(varchar, tblA.ContactID) IN ( " +
"SELECT [Value] FROM dbo.UTILfn_Split(@ContactIDList, '^^') " +
") " +
"/*群組與聯絡人關連表*/ " +
"DELETE FROM SecurityGroup_ContactRelation " +
"WHERE GroupID + '##' + CONVERT(varchar, ContactID) IN ( " +
"SELECT [Value] FROM dbo.UTILfn_Split(@ContactIDList2, '^^') " +
") " +
"/*聯絡人資料(補刪除SecurityGroup_ContactRelation筆數為0的資料即可)*/ " +
"DELETE FROM SecurityGroup_Contact WHERE (SELECT COUNT(*) FROM SecurityGroup_ContactRelation WHERE ContactID = SecurityGroup_Contact.ContactID) = 0 " +
"/*操作紀錄-刪除*/" +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT myGUID, @ModuleDesc1, @FunctionDesc1, @ActionDesc1, @B00122, GroupInfo, '', @myAccountName1, @myAccountID1, GETDATE() FROM @tmpTable " +
"INSERT INTO SecurityUserAccount_OperateRecord(RecordID, ModuleDesc, FunctionDesc, ActionDesc, FieldDesc, FieldBefore, FieldAfter, Name, AccountID, cDatetime) " +
"SELECT myGUID, @ModuleDesc2, @FunctionDesc2, @ActionDesc2, @B0036, ContactName, '', @myAccountName2, @myAccountID12, GETDATE() FROM @tmpTable ";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand(StrSQL, db.getOcnn());
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactIDList", ContactIDList));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactIDList2", ContactIDList));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModuleDesc1", ModuleDesc));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FunctionDesc1", FunctionDesc));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ActionDesc1", ActionDesc));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@B00122", ParseWording("B0012")));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myAccountName1", myAccountName));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myAccountID1", myAccountID));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ModuleDesc2", ModuleDesc));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FunctionDesc2", FunctionDesc));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ActionDesc2", ActionDesc));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@B0036", ParseWording("B0036")));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myAccountName2", myAccountName));
SqlCom.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myAccountID12", myAccountID));
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
/*寫入DB*/
//db.BeginTranscation();
//db.AddDmsSqlCmd(StrSQL);
// db.CommitTranscation();
myData.nRet = db.ExecQuerySQLCommand(SqlCom, ref dt);
myData.nRet = db.nRet;
myData.outMsg = db.outMsg;
/*組回傳結果字串*/
if (myData.nRet == 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["CountGroup"].ToString() != "0")
{
myData.returnData += dt.Rows[i]["GroupInfo"].ToString() + ParseWording("B0056") + "\n";
}
else if (dt.Rows[i]["CountUser"].ToString() != "0")
{
myData.returnData += dt.Rows[i]["GroupInfo"].ToString() + ParseWording("B0055") + "\n";
}
else if (dt.Rows[i]["CountDevice"].ToString() != "0")
{
myData.returnData += dt.Rows[i]["GroupInfo"].ToString() + ParseWording("B0067") + "\n";
}
else
{
myData.returnData += dt.Rows[i]["GroupInfo"].ToString() + ParseWording("B0057") + "\n";
}
}
/*去掉最後一個斷行符號*/
if (myData.returnData.Length != 0)
{
myData.returnData = myData.returnData.Substring(0, (myData.returnData.Length - 1));
}
}
}
db.DBDisconnect();
}
return(myData);
}
public static CReturnData AddNewsProcess(string strAppType, string strNewsKind, string strAPP4, string strNewsTitle, string strInfo)
{
int nRet = -1;
Database db = new Database();
DataTable dt = new DataTable();
CReturnData myData = new CReturnData();
string StrSQL = " ";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
//key
int buildId = MIPUtil.getFILE_INDEX_SEQ(db.getOcnn());
try
{
//最新公告新增
StrSQL = "insert into MIP_KV(CKEY,CVALUE,CNOTE,CSTATUS,APPLY4)values" +
"(@CKEY,@CVALUE,@CNOTE,@CSTATUS,@APPLY4)";
/*連線DB*/
SqlCommand SqlCom = new SqlCommand(StrSQL, db.getOcnn());
SqlCom.Parameters.Add(new SqlParameter("@CKEY", strAppType)); //商品索引
SqlCom.Parameters.Add(new SqlParameter("@CSTATUS", strNewsKind)); //公告標題
SqlCom.Parameters.Add(new SqlParameter("@APPLY4", strAPP4)); //公告內容
SqlCom.Parameters.Add(new SqlParameter("@CNOTE", strNewsTitle)); //應用程式類別
SqlCom.Parameters.Add(new SqlParameter("@CVALUE", strInfo)); //種類 A01 熱門,A02 新聞,A03 保戸,A04 新知,A05 重要
nRet = SqlCom.ExecuteNonQuery();
//nRet = db.ExecQuerySQLCommand(StrSQL, ref dt);
string outMsg = db.outMsg;
Debug.Write("nRet:" + nRet);
Debug.Write("outMsg:" + outMsg);
}
catch (Exception ex)
{
Debug.Write("YL0140A Exception :" + ex.Message);
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
return(myData);
}
public static CReturnData AddNewsProcess(string strSysModID, string strFunctionDesc, string strPageLInk, string strPic, string strOrder,
string striDisplay)
{
int nRet = -1;
Database db = new Database();
DataTable dt = new DataTable();
CReturnData myData = new CReturnData();
string StrSQL = " ";
/*連線DB*/
myData.nRet = db.DBConnect();
myData.outMsg = db.outMsg;
if (myData.nRet == 0)
{
//key
int buildId = MIPUtil.getFILE_INDEX_SEQ(db.getOcnn());
try
{
////功能設定維護
StrSQL = " INSERT INTO SystemFunction(SysFuncID, SysModID, FunctionDesc, PageLink, Pic, iOrder, iDisplay) " +
" VALUES(NEWID(), @SysModID,@FunctionDesc, @PageLink, @Pic, @iOrder, @iDisplay); ";
SqlCommand SqlCom = new SqlCommand(StrSQL, db.getOcnn());
SqlCom.Parameters.Add(new SqlParameter("@SysModID", strSysModID)); //模組
SqlCom.Parameters.Add(new SqlParameter("@FunctionDesc", strFunctionDesc)); //公告內容
SqlCom.Parameters.Add(new SqlParameter("@PageLink", strPageLInk)); //uri連結
SqlCom.Parameters.Add(new SqlParameter("@pic", strPic)); //預設圖片
SqlCom.Parameters.Add(new SqlParameter("@iOrder", strOrder)); //排序
SqlCom.Parameters.Add(new SqlParameter("@iDisplay", striDisplay)); //公告內容
nRet = SqlCom.ExecuteNonQuery();
//nRet = db.ExecQuerySQLCommand(StrSQL, ref dt);
string outMsg = db.outMsg;
Debug.Write("nRet:" + nRet);
Debug.Write("outMsg:" + outMsg);
}
catch (Exception ex)
{
Debug.Write("FunctionMenuSetting_List_A Exception :" + ex.Message);
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
return(myData);
}
