//寫入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 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);
}
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 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);
}
public static CReturnData UpdateNewsProcess(string strSysModID, string strSysFuncID, string strFunctionDesc, string strPageLInk, string strPic, string strOrder,
string striDisplay)
{
Console.WriteLine("striDisplay ...................");
Console.WriteLine(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 = " UPDATE SystemFunction set " +
"PageLink=@PageLink," +
"pic=@pic," +
"SysModID=@SysModID," +
"iOrder=@iOrder," +
"iDisplay=@iDisplay, " +
"FunctionDesc = @FunctionDesc " +
" WHERE SysFuncID=@SysFuncID ";
SqlCommand SqlCom = new SqlCommand(StrSQL, db.getOcnn());
SqlCom.Parameters.Add(new SqlParameter("@PageLink", strPageLInk)); //uri連結
SqlCom.Parameters.Add(new SqlParameter("@pic", strPic)); //預設圖片
SqlCom.Parameters.Add(new SqlParameter("@SysModID", strSysModID)); //模組
SqlCom.Parameters.Add(new SqlParameter("@iOrder", strOrder)); //排序
SqlCom.Parameters.Add(new SqlParameter("@iDisplay", striDisplay)); //公告內容
SqlCom.Parameters.Add(new SqlParameter("@FunctionDesc", strFunctionDesc)); //公告內容
SqlCom.Parameters.Add(new SqlParameter("@SysFuncID", strSysFuncID));
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("YL0010M Exception :" + ex.Message);
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
return(myData);
}
public static CReturnData UpdateNewsProcess(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());
//strAppType = row["CKEY"].ToString();//SEQUENCE
//strNewsKind = row["CSTATUS"].ToString();//種類
//strApp4 = row["APPLY4"].ToString();//公告標題
//strNewsTitle = row["CNOTE"].ToString();//公告內容
//strInfo = row["CVALUE"].ToString();//分類項目排序
try
{
//最新公告更新
StrSQL = "UPDATE MIP_SYS_INFO set " +
"CSTATUS=@CSTATUS," +
"APPLY4=@APPLY4," +
"CNOTE=@CNOTE," +
"CVALUE=@CVALUE" +
" WHERE "
+ " CKEY = @CKEY";
//key
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("YL0010M Exception :" + ex.Message);
throw ex;
}
finally
{
dt.Dispose();
dt = null;
db.getOcnn().Close();
db.DBDisconnect();
}
}
return(myData);
}
private void QueryList()
{
Database db = new Database();
try {
db.DBConnect();
str_hidInsertKey = MIPUtil.getFILE_INDEX_SEQ(db.getOcnn()).ToString();
} catch (Exception ex) {
throw ex;
} finally {
db.getOcnn().Close();
db.DBDisconnect();
}
if (oListView == null)
{
ContentPlaceHolder MySecondContent = (ContentPlaceHolder)this.Master.FindControl("ContentPlaceHolder1");
oListView = MySecondContent.FindControl("oListView") as ASP.dmscontrol_olistview_ascx;
//StrSearch = MySecondContent.FindControl("StrSearch") as System.Web.UI.HtmlControls.HtmlInputText;
}
if (oListView != null)
{
//加入欄位Start
oListView.AddCol("表單類別", "d_note", "CENTER", "auto");
oListView.AddCol("表單名稱", "cont", "LEFT", "40%");
oListView.AddCol("最後更新日期", " aldate", "CENTER", "auto");
oListView.AddCol("排序", " Corder", "CENTER", "auto");
oListView.AddCol("狀態", "cstatus", "CENTER", "auto");
//加入欄位End
//設定Key值欄位
oListView.DataKeyNames = "HAPPY_ID"; //Key以,隔開
////設定是否顯示CheckBox(預設是true);
////BosomServiceList.IsUseCheckBox = false;
StringBuilder sbSQL = new StringBuilder();
//設定SQL
sbSQL.Append(@"SELECT
a.HAPPY_ID
, (CASE WHEN a.CSTATUS = 1 THEN '停用' WHEN a.CSTATUS = 0 THEN '啟用' ELSE '未定義'END) cstatus
,a.Corder
,b.CNAME as b_note
,c.CNAME as c_note
,d.CNAME as d_note
,d.CNOTE as Cnote
,(CASE WHEN a.FILE_KIND = 0 THEN '標題：'+a.TITLE+' 檔名：'+ a.F_NAME WHEN a.FILE_KIND = 1 THEN '標題：'+ a.TITLE +' 網址：'+ a.URL ELSE '尋寶圖 URL：' +a.url END) cont
,a.LDATE as aldate
,a.LUSER
,(CASE WHEN a.ISTESTER = 1 THEN '否' WHEN a.ISTESTER = 0 THEN '是' ELSE '未定義' END) as ISTESTER
FROM MIP_HAPPY a, MIP_CODES b ,MIP_CODES c, MIP_CODES D
where
b.CKEY = a.CKEY1
and c.CKEY = a.CKEY2
and D.CKEY = a.CKEY3
and a.CKEY2 != 'B2020'
and d.Cnote = '0' ");
if (!string.IsNullOrEmpty(MDS.Utility.NUtility.trimBad(Request.QueryString["_dlType"])))
{
if (MDS.Utility.NUtility.trimBad(Request.QueryString["_dlType"]) != "請選擇")
{
str_dlType = MDS.Utility.NUtility.trimBad(Request.QueryString["_dlType"]);
string[] str_dlTypes = str_dlType.Split(new String[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
//sbSQL.Append(" and b.CNAME=@b_note ");
//oListView.putQueryParameter("b_note", str_dlTypes[0]);
//sbSQL.Append(" and c.CNAME=@c_note ");
//oListView.putQueryParameter("Cnote", str_dlTypes[1]);
if (str_dlTypes.Length == 2)
{
sbSQL.Append(" and d.CNAME =@dCNAME ");
oListView.putQueryParameter("dCNAME", str_dlTypes[0]);
_dlType.SelectedValue = (MDS.Utility.NUtility.trimBad(str_dlTypes[1]));
}
}
}
if (!string.IsNullOrEmpty(MDS.Utility.NUtility.trimBad(Request.QueryString["_txtSrch"])))
{
str_txtSrch = MDS.Utility.NUtility.trimBad(Request.QueryString["_txtSrch"]);
sbSQL.Append(" and ( a.TITLE like '%'+@txtSrch+'%' or a.F_NAME like '%'+@txtSrch+'%') ");
oListView.putQueryParameter("txtSrch", str_txtSrch);
}
if (!string.IsNullOrEmpty(Request.QueryString["_chkIC"]) && string.IsNullOrEmpty(Request.QueryString["_chkRM"]))
{
sbSQL.Append("and exists(select * from MIP_HAPPY_TARGET e where e.HAPPY_ID=a.HAPPY_ID and e.DTYPE=0 and e.DEPT_ID='IC') ");
oListView.putQueryParameter("DEPT_ID", Request.QueryString["_chkIC"]);
}
else if (!string.IsNullOrEmpty(Request.QueryString["_chkRM"]) && string.IsNullOrEmpty(Request.QueryString["_chkIC"]))
{
sbSQL.Append("and exists(select * from MIP_HAPPY_TARGET e where e.HAPPY_ID=a.HAPPY_ID and e.DTYPE=0 and e.DEPT_ID='RM') ");
oListView.putQueryParameter("DEPT_ID", Request.QueryString["_chkRM"]);
}
sbSQL.Append(" order by a.CKEY1, a.CKEY2, a.CKEY3, a.CORDER, a.LDATE ");
//sbSQL.Append(" ORDER BY a.LDATA DESC,a.VDATE DESC ");
//取得SQL;
oListView.SelectString = sbSQL.ToString();
oListView.prepareStatement();
string showSql = sbSQL.ToString();
Debug.Write(showSql);
//設定每筆資料按下去的Javascript function
oListView.OnClickExecFunc = "DoEdt()";
//設定每頁筆數
oListView.PageSize = 26;
//接來自Request的排序欄位、排序方向、目前頁數
ListViewSortKey = Request.Params["ListViewSortKey"];
ListViewSortDirection = Request.Params["ListViewSortDirection"];
PageNo = Request.Params["PageNo"];
//設定排序欄位及方向
if (!string.IsNullOrEmpty(ListViewSortKey) && !string.IsNullOrEmpty(ListViewSortDirection))
{
oListView.ListViewSortKey = ListViewSortKey;
oListView.ListViewSortDirection = (SortDirection)Enum.Parse(typeof(SortDirection), ListViewSortDirection);
}
//設定目前頁數
if (!string.IsNullOrEmpty(PageNo))
{
oListView.PageNo = int.Parse(PageNo);
}
}
}
