/// <summary>
/// 對附件的操作
/// </summary>
/// <param name="attachements"></param>
/// <param name="userid"></param>
public void UpdateToDoAttachMents(DataTable UpdateItems, DataTable attachements, string userid)
{
string SQL = "";
using (SQLHelper conn = new SQLHelper())
{
conn.OpenConnection();
try
{
string tmp_LRTD00H = "tmp_LRTD00H_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
string tmp_LRTDFILE = "tmp_LRTDFILE_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
SQLHelper connTemp = new SQLHelper();
//開啟tempdb連接
connTemp.OpenConnection("tempdb");
connTemp.ExecuteSQL("create table " + tmp_LRTD00H + "(td_no varchar(20) not null default '')");
connTemp.ExecuteSQL("create table " + tmp_LRTDFILE + "(" + conn.GetTableStruct("lrtdfile") + ")");
connTemp.CloseConnection();
tmp_LRTDFILE = "tempdb.." + tmp_LRTDFILE;
tmp_LRTD00H = "tempdb.." + tmp_LRTD00H;
foreach (DataRow dr in UpdateItems.Rows)
{
SQL = "insert into " + tmp_LRTD00H + "(td_no) values('" + dr["td_no"].ToString() + "')";
conn.ExecuteSQL(SQL);
}
foreach (DataRow dr in attachements.Rows)
{
SQL =
"insert into " + tmp_LRTDFILE + "(td_no,td_file,td_guid,td_type,td_remk) " +
"select '" + dr["td_no"].ToString() + "',N'" + dr["td_file"].ToString() + "'," +
"'" + dr["td_guid"].ToString() + "','" + dr["td_type"].ToString() + "',N'" + dr["td_remk"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
conn._Transaction = conn._Connection.BeginTransaction();
string ymd = DateTime.Now.ToString("yyyy/MM/dd");
string time = DateTime.Now.ToString("HH:mm:ss");
#region 對附件的操作
SQL =
" delete a " +
" from lrtdfile a " +
" join lrtd00h b(nolock) on b.td_no = a.td_no " +
" where b.td_owner = '" + userid + "'" +
" and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=b.td_no)" +
" and not exists(select 1 from " + tmp_LRTDFILE + " x where x.td_guid=a.td_guid)";
conn.ExecuteSQL(SQL);
SQL =
" insert into lrtdfile(ie_ymd,ie_time,ie_user,td_no,td_file,td_guid,td_type,td_remk) " +
" select '" + ymd + "', '" + time + "','" + userid + "',a.td_no," + "\r\n" +
" a.td_file,a.td_guid,a.td_type,a.td_remk " +
" from " + tmp_LRTDFILE + " a " +
" join lrtd00h b on a.td_no =b.td_no " +
" where b.td_owner = '" + userid + "'" +
" and not exists(select 1 from lrtdfile x(nolock) where x.td_guid=a.td_guid)";
conn.ExecuteSQL(SQL);
#endregion 對附件的操作
conn._Transaction.Commit();
conn.CloseConnection();
}
catch (System.Exception ex)
{
conn._Transaction.Rollback();
conn.CloseConnection();
WriteLog(userid, ex.Message, "ErrLog");
throw ex;
}
}
}
/// <summary>
/// DataTable 里存放的是有異動的資料
/// </summary>
/// <param name="Contacts"></param>
/// <param name="GMembers"></param>
/// <param name="userid"></param>
public void UpdateContacts(DataTable Contacts, DataTable GMembers, string userid)
{
string SQL = "";
using (SQLHelper conn = new SQLHelper())
{
conn.OpenConnection();
try
{
string tmp_LRCONTACT = "tmp_LRCONTACT_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
string tmp_LRGMEMBER = "tmp_LRGMEMBER_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
SQLHelper connTemp = new SQLHelper();
//開啟tempdb連接
connTemp.OpenConnection("tempdb");
connTemp.ExecuteSQL("create table " + tmp_LRCONTACT + "(" + conn.GetTableStruct("lrcontact") + ",contact_modify varchar(1) not null default ('9'))");
connTemp.ExecuteSQL("create table " + tmp_LRGMEMBER + "(" + conn.GetTableStruct("lrgmember") + ")");
connTemp.CloseConnection();
tmp_LRCONTACT = "tempdb.." + tmp_LRCONTACT;
tmp_LRGMEMBER = "tempdb.." + tmp_LRGMEMBER;
foreach (DataRow dr in Contacts.Rows)
{
byte[] by;
if (dr["contact_img"] == null)
{
by = (byte[])dr["contact_img"];
SQL =
"insert into " + tmp_LRCONTACT + "(user_no,contact_id,contact_name,contact_company," +
"contact_position,contact_modify,contact_sex,contact_img) " + "\r\n" +
" select '" + dr["user_no"].ToString() + "','" + dr["contact_id"].ToString() + "','" + dr["contact_name"].ToString() + "','" + dr["contact_company"].ToString() + "','" + dr["contact_position"].ToString() + "','" + dr["contact_modify"].ToString() + "','" + dr["contact_sex"].ToString() + "',@image";
conn.ExecuteSQLImage(SQL, "@image", by);
}
else
{
SQL =
"insert into " + tmp_LRCONTACT + "(user_no,contact_id,contact_name,contact_company," +
"contact_position,contact_modify,contact_sex) " + "\r\n" +
" select '" + dr["user_no"].ToString() + "','" + dr["contact_id"].ToString() + "','" + dr["contact_name"].ToString() + "','" + dr["contact_company"].ToString() + "','" + dr["contact_position"].ToString() + "','" + dr["contact_modify"].ToString() + "','" + dr["contact_sex"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
}
foreach (DataRow dr in GMembers.Rows)
{
SQL =
"insert into " + tmp_LRGMEMBER + "(contact_id,group_id) " + "\r\n" +
"select '" + dr["contact_id"].ToString() + "','" + dr["group_id"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
conn._Transaction = conn._Connection.BeginTransaction();
string ymd = DateTime.Now.ToString("yyyy/MM/dd");
string time = DateTime.Now.ToString("HH:mm:ss");
//Add = 0,//新增的
//Delete =1,//刪除的
//Update = 2,//有更新的
//1.刪除要標記為Delete的資料
SQL =
" delete a " + "\r\n" +
" from lrcontact a " + "\r\n" +
" join " + tmp_LRCONTACT + " b on a.contact_id = b.contact_id and a.user_no = b.user_no " + "\r\n" +
" where b.contact_modify = '1'";
conn.ExecuteSQL(SQL);
//2.更新要標記為Update的資料的資料
SQL =
" update a set " + "\r\n" +
" a.contact_id = b.contact_id," + "\r\n" +
" a.contact_name = b.contact_name," + "\r\n" +
" a.contact_company = b.contact_company," + "\r\n" +
" a.contact_position = b.contact_position," + "\r\n" +
" a.contact_sex = b.contact_sex," + "\r\n" +
" a.contact_img = b.contact_img," + "\r\n" +
" a.ie_lymd ='" + ymd + "'," + "\r\n" +
" a.ie_ltime ='" + time + "'," + "\r\n" +
" a.ie_luser ='******' " + "\r\n" +
" from lrcontact a " + "\r\n" +
" join " + tmp_LRCONTACT + " b on a.contact_id = b.contact_id and a.user_no = b.user_no " + "\r\n" +
" where b.contact_modify = '2'";
conn.ExecuteSQL(SQL);
//3.新增要標記為Add的資料
SQL =
" insert into lrcontact(ie_ymd,ie_time,ie_user,user_no,contact_id,contact_name," + "\r\n" +
" contact_company,contact_position,contact_sex) " + "\r\n" +
" select '" + ymd + "','" + time + "','" + userid + "',user_no,contact_id,contact_name," + "\r\n" +
" contact_company,contact_position,contact_sex" + "\r\n" +
" from " + tmp_LRCONTACT + " b " + "\r\n" +
" where b.contact_modify = '0'";
conn.ExecuteSQL(SQL);
//最後清楚對照關係
//已經刪除的group 要清除
SQL =
" delete a from lrgmember a " +
" left join lrgroup b on a.group_id=b.group_id" +
" where b.uid is null ";
conn.ExecuteSQL(SQL);
//新增新的群組
SQL =
"insert into lrgmember(ie_ymd,ie_time,ie_user,group_id,contact_id) " +
"select '" + ymd + "','" + time + "','" + userid + "',group_id,contact_id " +
"from " + tmp_LRGMEMBER + " a " +
"where not exists(select 1 from lrgmember x where x.group_id=a.group_id and x.contact_id=a.contact_id)";
conn.ExecuteSQL(SQL);
//刪除不存在的聯絡人
//沒有對照關係的要刪除
SQL =
" delete a from lrgmember a " + "\r\n" +
" join lrgroup b on a.group_id= b.group_id " + "\r\n" +
" where not exists(select 1 from " + tmp_LRGMEMBER + " c " + "\r\n" +
" where a.group_id=c.group_id and a.contact_id=c.contact_id) " + "\r\n" +
" and exists(select 1 from " + tmp_LRGMEMBER + " x where x.contact_id=a.contact_id) and b.user_no ='" + userid + "'";
conn.ExecuteSQL(SQL);
//聯絡人 沒有了要刪除
SQL =
" delete a from lrgmember a " +
" join lrgroup c on a.group_id = c.group_id " +
" left join lrcontact b on a.contact_id = b.contact_id and c.user_no = b.user_no " +
" where b.uid is null and c.user_no ='" + userid + "' ";
conn.ExecuteSQL(SQL);
SQL =
" delete a from lrgmember a " +
" left join lrgroup b on a.group_id = b.group_id" +
" where b.uid is null";
conn.ExecuteSQL(SQL);
conn._Transaction.Commit();
conn.CloseConnection();
}
catch (System.Exception ex)
{
conn._Transaction.Rollback();
conn.CloseConnection();
WriteLog(userid, ex.Message, "ErrLog");
throw ex;
}
}
}
public void UpdateToDoItems(DataTable UpdateItems, DataTable members, DataTable tags, string userid)
{
string SQL = "";
using (SQLHelper conn = new SQLHelper())
{
conn.OpenConnection();
try
{
#region 資料及臨時表準備
string tmpExecutor = "tmp_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");//原來的執行者
string tmp_LRTD00H = "tmp_LRTD00H_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
string tmp_LRTD00D1 = "tmp_LRTD00D1_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
string tmp_LRTD00D2 = "tmp_LRTD00D2_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
//string tmp_LRTDFILE = "tmp_LRTDFILE_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
SQLHelper connTemp = new SQLHelper();
//開啟tempdb連接
connTemp.OpenConnection("tempdb");
connTemp.ExecuteSQL("create table " + tmp_LRTD00H + "(" + conn.GetTableStruct("lrtd00h") + ",justread varchar(1) not null default '',canback varchar(1) not null default '',td_dir varchar(10) not null default '')");
connTemp.ExecuteSQL("create table " + tmp_LRTD00D1 + "(" + conn.GetTableStruct("lrtd00d1") + ")");
connTemp.ExecuteSQL("create table " + tmp_LRTD00D2 + "(" + conn.GetTableStruct("lrtd00d2") + ")");
//connTemp.ExecuteSQL("create table " + tmp_LRTDFILE +"("+ conn.GetTableStruct("lrtdfile")+")");
connTemp.ExecuteSQL("create table " + tmpExecutor + "(td_no varchar(20) not null default '',td_executor varchar(50) not null default '',send_msg varchar(1) not null default '')");
connTemp.CloseConnection();
tmp_LRTD00H = "tempdb.." + tmp_LRTD00H;
tmp_LRTD00D1 = "tempdb.." + tmp_LRTD00D1;
tmp_LRTD00D2 = "tempdb.." + tmp_LRTD00D2;
//tmp_LRTDFILE = "tempdb.." + tmp_LRTDFILE;
tmpExecutor = "tempdb.." + tmpExecutor;
foreach (DataRow dr in UpdateItems.Rows)
{
string _td_sylog = dr["td_sylog"].ToString();
SQL =
"insert into " + tmp_LRTD00H + "(td_no,td_name,td_owner,td_description,td_prdate,td_prtime," +
"td_fno,td_priority,td_code,td_fdate,td_rsdate,td_rstime,td_redate," +
"td_retime,td_state,td_sylog,td_relno,td_type,td_ctype," +
"td_prsdate,td_predate,td_sdate,td_edate,td_top,td_hold,canback,td_dir,justread,td_flag) " +
"select '" + dr["td_no"].ToString() + "',N'" + dr["td_name"].ToString() + "'," +
"'" + dr["td_owner"].ToString() + "',N'" + dr["td_description"].ToString() + "'," +
"'" + dr["td_prdate"].ToString() + "','" + dr["td_prtime"].ToString() + "'," +
"'" + dr["td_fno"].ToString() + "','" + dr["td_priority"].ToString() + "'," +
"'" + dr["td_code"].ToString() + "','" + dr["td_fdate"].ToString() + "'," +
"'" + dr["td_rsdate"].ToString() + "','" + dr["td_rstime"].ToString() + "'," +
"'" + dr["td_redate"].ToString() + "','" + dr["td_retime"].ToString() + "'," +
"'" + dr["td_state"].ToString() + "','" + _td_sylog.Substring(0, _td_sylog.Length > 500 ? 500 : _td_sylog.Length) + "'," +
"'" + dr["td_relno"].ToString() + "','" + dr["td_type"].ToString() + "'," +
"'" + dr["td_ctype"].ToString() + "','" + dr["td_prsdate"].ToString() + "'," +
"'" + dr["td_predate"].ToString() + "','" + dr["td_sdate"].ToString() + "'," +
"'" + dr["td_edate"].ToString() + "','" + dr["td_top"].ToString() + "'," +
"'" + dr["td_hold"].ToString() + "','" + dr["canback"].ToString() + "'," +
"'" + dr["td_dir"].ToString() + "','" + dr["justread"].ToString() + "','" + dr["td_flag"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
foreach (DataRow dr in members.Rows)
{
string _td_sylog = dr["td_sylog"].ToString();
SQL =
"insert into " + tmp_LRTD00D1 + "(td_no,td_member,td_role,td_rsdate,td_rstime,td_redate," +
"td_retime,td_state,td_prsdate,td_predate,td_sdate,td_edate,td_top,td_sylog,td_hold,td_flag) " +
"select '" + dr["td_no"].ToString() + "','" + dr["td_member"].ToString() + "'," +
"'" + dr["td_role"].ToString() + "','" + dr["td_rsdate"].ToString() + "'," +
"'" + dr["td_rstime"].ToString() + "','" + dr["td_redate"].ToString() + "'," +
"'" + dr["td_retime"].ToString() + "','" + dr["td_state"].ToString() + "'," +
"'" + dr["td_prsdate"].ToString() + "','" + dr["td_predate"].ToString() + "'," +
"'" + dr["td_sdate"].ToString() + "','" + dr["td_edate"].ToString() + "'," +
"'" + dr["td_top"].ToString() + "','" + _td_sylog.Substring(0, _td_sylog.Length > 500 ? 500 : _td_sylog.Length) + "'," +
"'" + dr["td_hold"].ToString() + "','" + dr["td_flag"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
foreach (DataRow dr in tags.Rows)
{
SQL =
"insert into " + tmp_LRTD00D2 + "(td_tag,td_no) " +
"select N'" + dr["td_tag"].ToString() + "','" + dr["td_no"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
//foreach (DataRow dr in attachements.Rows)
//{
// SQL =
// "insert into " + tmp_LRTDFILE + "(td_no,td_file,td_guid,td_type,td_remk) " +
// "select '" + dr["td_no"].ToString() + "',N'" + dr["td_file"].ToString() + "'," +
// "'" + dr["td_guid"].ToString() + "','" + dr["td_type"].ToString() + "',N'" + dr["td_remk"].ToString() + "'";
// conn.ExecuteSQL(SQL);
//}
conn.ExecuteSQL(
"insert into " + tmpExecutor + "(td_no,td_executor) " + "\r\n" +
"select a.td_no,isnull(b.td_member,a.td_owner) td_executor " + "\r\n" +
"from lrtd00h a " + "\r\n" +
"left join lrtd00d1 b(nolock) on a.td_no=b.td_no and b.td_role ='2' " + "\r\n" +
"join " + tmp_LRTD00H + " c on a.td_no = c.td_no ");
#endregion 資料及臨時表準備
conn._Transaction = conn._Connection.BeginTransaction();
//1.當前修改人員是owner
#region 只有owner才可以變更表頭
string ymd = DateTime.Now.ToString("yyyy/MM/dd");
string time = DateTime.Now.ToString("HH:mm:ss");
//JustRead = 1 表示只是狀態的變化
SQL =
" update a set a.td_name = b.td_name," + "\r\n" +
" a.td_rsdate = b.td_rsdate," + "\r\n" +
" a.td_rstime = b.td_rstime," + "\r\n" +
" a.td_redate =b.td_redate," + "\r\n" +
" a.td_retime =b.td_retime, " + "\r\n" +
" a.td_state=b.td_state," + "\r\n" +
" a.td_hold=b.td_hold," + "\r\n" +
" a.td_top=b.td_top," + "\r\n" +
" a.td_flag=b.td_flag," + "\r\n" +
" a.ie_lymd = '" + ymd + "', " + "\r\n" +
" a.ie_ltime = '" + time + "', " + "\r\n" +
" a.ie_luser = '******' " + "\r\n" +
" from lrtd00h a(nolock) " + "\r\n" +
" join " + tmp_LRTD00H + " b on a.td_no=b.td_no " + "\r\n" +
" where a.td_owner = '" + userid + "' and b.justread ='1'";
conn.ExecuteSQL(SQL);
//JustRead = 0 表示異動了狀態外的其他屬性
SQL =
" update a set a.td_name = b.td_name," + "\r\n" +
" a.td_description =b.td_description, " + "\r\n" +
" a.td_priority = b.td_priority, " + "\r\n" +
" a.td_prdate = b.td_prdate, " + "\r\n" +
" a.td_prtime = b.td_prtime," + "\r\n" +
" a.td_fdate = b.td_fdate, " + "\r\n" +
" a.td_fno = b.td_fno," + "\r\n" +
" a.td_rsdate = b.td_rsdate," + "\r\n" +
" a.td_rstime = b.td_rstime," + "\r\n" +
" a.td_redate = b.td_redate," + "\r\n" +
" a.td_retime = b.td_retime, " + "\r\n" +
" a.td_code = b.td_code," + "\r\n" +
" a.td_ctype = b.td_ctype, " + "\r\n" +
" a.td_state=b.td_state," + "\r\n" +
" a.td_hold=b.td_hold," + "\r\n" +
" a.td_top=b.td_top," + "\r\n" +
" a.td_flag=b.td_flag," + "\r\n" +
" a.td_prsdate =b.td_prsdate," + "\r\n" +
" a.td_predate = b.td_predate," + "\r\n" +
" a.td_sdate = case when c.td_role is null then b.td_sdate else '' end," + "\r\n" +
" a.td_edate = case when c.td_edate is null then b.td_edate else '' end, " + "\r\n" +
" a.td_sylog = b.td_sylog," + "\r\n" +
" a.ie_lymd = '" + ymd + "', " + "\r\n" +
" a.ie_ltime = '" + time + "', " + "\r\n" +
" a.ie_luser = '******' " + "\r\n" +
" from lrtd00h a(nolock)" + "\r\n" +
" join " + tmp_LRTD00H + " b on a.td_no=b.td_no " + "\r\n" +
" left join " + tmp_LRTD00D1 + " c on a.td_no = c.td_no and c.td_role='2' " + "\r\n" +
" where a.td_owner = '" + userid + "' and b.justread ='0'";
//表頭信息
conn.ExecuteSQL(SQL);
#endregion 只有owner才可以變更表頭
//2.表身的變更
#region owner 的操作
ymd = DateTime.Now.ToString("yyyy/MM/dd");
time = DateTime.Now.ToString("HH:mm:ss");
#region owner 對角色的改變
//刪除已經不存在的資料
SQL =
" delete a " +
" from lrtd00d1 a(nolock) " +
" join " + tmp_LRTD00H + " b on a.td_no = b.td_no " +
" where not exists(select 1 from " + tmp_LRTD00D1 + " x where x.td_no = a.td_no and x.td_member = a.td_member)";
conn.ExecuteSQL(SQL);
//owner 改變角色
SQL =
" update a set " + "\r\n" +
" a.td_role = b.td_role, " + "\r\n" +
" a.ie_lymd = '" + ymd + "', " + "\r\n" +
" a.ie_ltime = '" + time + "', " + "\r\n" +
" a.ie_luser = '******' " + "\r\n" +
" from lrtd00d1 a(nolock)" +
" join " + tmp_LRTD00D1 + " b on a.td_no = b.td_no and a.td_member = b.td_member " +
" join lrtd00h c on a.td_no = c.td_no " + "\r\n" +
" where c.td_owner = '" + userid + "'";
conn.ExecuteSQL(SQL);
SQL =
" update a set " + "\r\n" +
" a.td_edate = b.td_edate " + "\r\n" +
" from lrtd00d1 a(nolock) " + "\r\n" +
" join " + tmp_LRTD00D1 + " b on a.td_no = b.td_no and a.td_member = b.td_member " +
" join lrtd00h c(nolock) on a.td_no = c.td_no " + "\r\n" +
" join " + tmp_LRTD00H + " d on c.td_no = d.td_no " + "\r\n" +
" where c.td_owner = '" + userid + "' and d.canback = '1' and a.td_role = '2'";
conn.ExecuteSQL(SQL);
//新增
SQL =
" insert into lrtd00d1(ie_ymd,ie_time,ie_user,td_no,td_member,td_role,td_flag) " + "\r\n" +
" select '" + ymd + "','" + time + "','" + userid + "',a.td_no,a.td_member,a.td_role,a.td_flag" + "\r\n" +
" from " + tmp_LRTD00D1 + " a " + "\r\n" +
" where not exists(select 1 from lrtd00d1 x where x.td_no = a.td_no " + "\r\n" +
" and x.td_member = a.td_member)";
conn.ExecuteSQL(SQL);
#endregion owner 對角色的改變
#region owner 對關鍵字的改變
SQL =
" delete a " +
" from lrtd00d2 a " +
" join " + tmp_LRTD00H + " b on a.td_no = b.td_no " +
" join lrtd00h c(nolock) on c.td_no = b.td_no " +
" where b.td_owner = '" + userid + "'" +
" and not exists(select 1 from " + tmp_LRTD00D2 + " x where x.td_no = a.td_no and x.td_tag=a.td_tag)";
conn.ExecuteSQL(SQL);
SQL =
" insert into lrtd00d2(ie_ymd,ie_time,ie_user,td_no,td_tag) " + "\r\n" +
" select distinct '" + ymd + "','" + time + "','" + userid + "',a.td_no,a.td_tag" +
" from " + tmp_LRTD00D2 + " a " +
" join lrtd00h b on a.td_no =b.td_no " +
" where b.td_owner = '" + userid + "' and a.td_tag <> '' " +
" and not exists(select 1 from lrtd00d2 x(nolock) where x.td_no=a.td_no and x.td_tag=a.td_tag)";
conn.ExecuteSQL(SQL);
#endregion owner 對關鍵字的改變
#region 對附件的操作 分開操作
//SQL =
// " delete a " +
// " from lrtdfile a " +
// " join " + tmp_LRTD00H + " b on a.td_no = b.td_no " +
// " join lrtd00h c(nolock) on c.td_no = b.td_no " +
// " where b.td_owner = '" + userid + "'" +
// " and not exists(select 1 from " + tmp_LRTDFILE + " x where x.td_guid=a.td_guid)";
//conn.ExecuteSQL(SQL);
//SQL =
// " insert into lrtdfile(ie_ymd,ie_time,ie_user,td_no,td_file,td_guid,td_type,td_remk) " +
// " select '" + ymd + "', '" + time + "','" + userid + "',a.td_no," + "\r\n" +
// " a.td_file,a.td_guid,a.td_type,a.td_remk "+
// " from " + tmp_LRTDFILE + " a " +
// " join lrtd00h b on a.td_no =b.td_no " +
// " where b.td_owner = '" + userid + "'" +
// " and not exists(select 1 from lrtdfile x(nolock) where x.td_guid=a.td_guid)";
//conn.ExecuteSQL(SQL);
#endregion 對附件的操作
#endregion owner 的操作
//3.非Onwer操作
#region 非owner 的操作
SQL =
" update a set " + "\r\n" +
" a.ie_lymd='" + ymd + "', " + "\r\n" +
" a.ie_ltime='" + time + "', " + "\r\n" +
" a.ie_luser='******'," + "\r\n" +
" a.td_rsdate=b.td_rsdate," + "\r\n" +
" a.td_rstime=b.td_rstime," + "\r\n" +
" a.td_redate=b.td_redate," + "\r\n" +
" a.td_retime=b.td_retime," + "\r\n" +
//更新當前用戶資料 預計完成 實際完成
" a.td_prsdate=b.td_prsdate," + "\r\n" +
" a.td_predate=b.td_predate," + "\r\n" +
" a.td_sdate=b.td_sdate," + "\r\n" +
" a.td_edate=b.td_edate," + "\r\n" +
" a.td_hold=b.td_hold," + "\r\n" +
" a.td_top=b.td_top," + "\r\n" +
" a.td_flag=b.td_flag," + "\r\n" +
//日誌
" a.td_sylog=b.td_sylog," + "\r\n" +
//讀取屬性
" a.td_state='1' " + "\r\n" +
" from lrtd00d1 a(nolock) " + "\r\n" +
" join " + tmp_LRTD00D1 + " b on a.td_no=b.td_no and a.td_member=b.td_member and a.td_role=b.td_role " + "\r\n" +
" join lrtd00h c(nolock) on a.td_no=c.td_no " + "\r\n" +
" where c.td_owner<>'" + userid + "' and a.td_member='" + userid + "'" +
" and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')";
conn.ExecuteSQL(SQL);
//如果是自己的提醒時間 / 狀態的變化
//那麼就不要更新日誌 並且 只更新狀態的欄位
SQL =
" update a set " + "\r\n" +
" a.ie_lymd='" + ymd + "', " + "\r\n" +
" a.ie_ltime='" + time + "', " + "\r\n" +
" a.ie_luser='******'," + "\r\n" +
" a.td_rsdate=b.td_rsdate, " + "\r\n" +
" a.td_rstime=b.td_rstime, " + "\r\n" +
" a.td_redate=b.td_redate, " + "\r\n" +
" a.td_retime=b.td_retime, " + "\r\n" +
" a.td_top=b.td_top, " + "\r\n" +
" a.td_flag=b.td_flag," + "\r\n" +
" a.td_hold=b.td_hold, " + "\r\n" +
//讀取屬性
" a.td_state='1' " + "\r\n" +
" from lrtd00d1 a(nolock) " + "\r\n" +
" join " + tmp_LRTD00D1 + " b on a.td_no=b.td_no and a.td_member=b.td_member and a.td_role=b.td_role " + "\r\n" +
" join lrtd00h c(nolock) on a.td_no=c.td_no " + "\r\n" +
" where c.td_owner<>'" + userid + "' and a.td_member='" + userid + "'" +
" and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '1')";
conn.ExecuteSQL(SQL);
#endregion 非owner 的操作
//4.更新狀態
#region 更新所有人的狀態為未讀
//是owner 且 本次修改不是狀態修改
conn.ExecuteSQL(
" update a set " + "\r\n" +
" td_state = '0', " + "\r\n" +
" ie_lymd = '" + ymd + "', " + "\r\n" +
" ie_ltime = '" + time + "', " + "\r\n" +
" ie_luser = '******' " + "\r\n" +
" from lrtd00d1 a(nolock) " + "\r\n" +
" join lrtd00h b on a.td_no = b.td_no" + "\r\n" +
" where b.td_owner = '" + userid + "' " + "\r\n" +
" and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')");
// 不是owner 且 本次修改不是狀態修改
conn.ExecuteSQL(
" update a set " + "\r\n" +
" a.td_state = '0', " + "\r\n" +
" a.ie_lymd = '" + ymd + "', " + "\r\n" +
" a.ie_ltime = '" + time + "', " + "\r\n" +
" a.ie_luser = '******' " + "\r\n" +
" from lrtd00d1 a(nolock) " + "\r\n" +
" join lrtd00h b on a.td_no = b.td_no" + "\r\n" +
" where b.td_owner <> '" + userid + "' and td_member <> '" + userid + "'" + "\r\n" +
" and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')");
// 不是owner 且 本次修改不是狀態修改
conn.ExecuteSQL(
" update a set a.td_state = '0'," +
" a.ie_lymd='" + ymd + "'," +
" a.ie_ltime='" + time + "'," +
" a.ie_luser='******' " +
" from lrtd00h a(nolock) " +
" where a.td_owner <> '" + userid + "'" +
" and exists(select 1 from " + tmp_LRTD00H + " x where x.td_no=a.td_no and x.justread = '0')");
#endregion 更新所有人的狀態為未讀
//5.用戶目錄修改
#region 用戶目錄
//狀態變化 不需要更新附件
//刪除不一樣的
SQL =
" delete a " +
" from lrtdship a(nolock) " +
" join " + tmp_LRTD00H + " b on a.td_no=b.td_no " +
" where a.user_no='" + userid + "' and a.td_dir <> b.td_dir ";
conn.ExecuteSQL(SQL);
//新增不存在的
SQL =
" insert into lrtdship(ie_ymd,ie_time,ie_user,td_no,user_no,td_dir) " +
" select '" + ymd + "', '" + time + "','" + userid + "',a.td_no,'" + userid + "',a.td_dir " +
" from " + tmp_LRTD00H + " a " +
" where a.td_dir <> '' " +
" and not exists(select 1 from lrtdship x where x.td_no = a.td_no and x.user_no ='" + userid + "')";
conn.ExecuteSQL(SQL);
#endregion 目錄跟用戶有關
//6.發送郵件
#region 發郵件
//如果td_edate<>'' 且 canback = '1' 且 owner 不是 執行者 表示 有回饋完成
#region 完成日期的回饋/否決
//執行者回饋完成
SQL =
" select '" + ymd + "','" + time + "','" + userid + "',isnull(b.user_email,'') toAddr," +
" isnull((select isnull(ss.user_email,tt.td_member)+';' " + "\r\n" +
" from lrtd00d1 tt (nolock) " + "\r\n" +
" left join lrtduser ss (nolock) on tt.td_member=ss.user_no " + "\r\n" +
" where tt.td_role <> '2' and tt.td_no = a.td_no " + "\r\n" +
" for xml path('')),'') ccAddr," +
" '[待辦事項] 來自 [' + isnull(b.user_name,a.td_owner) + ']' lr_subject," + "\r\n" +
" 'To All:<br> 待辦事項單號: '+a.td_no+' ('+a.td_name+') 已經完成<br> 事項說明:<br> '+a.td_description lr_content,'2','1' " + "\r\n" +
" from " + tmp_LRTD00H + " a(nolock) " + "\r\n" +
" left join lrtduser b on a.td_owner = b.user_no " + "\r\n" +
" left join lrtd00d1 c on a.td_no = c.td_no and c.td_role = '2' " + "\r\n" +
" left join lrtduser d on c.td_member = d.user_no " + "\r\n" +
" where a.canback = '1' and a.td_edate <> '' and isnull(d.user_no,a.td_owner) = '" + userid + "'" +
" and a.td_owner <> isnull(d.user_no,a.td_owner) and isnull(b.user_email,'') <> ''";//完成
conn.ExecuteSQL("insert into lrtdmail(ie_ymd,ie_time,ie_user,lr_to,lr_cc,lr_subject,lr_content,lr_type,lr_ftype) " + SQL);
//DataTable emails = conn.OpenDataTable(SQL, CommandType.Text);
//foreach (DataRow dr in emails.Rows)
//{
// SendEmail(dr["toAddr"].ToString(), dr["ccAddr"].ToString(), "To All:<br> 待辦事項單號:" + dr["td_no"].ToString() + "(" + dr["td_name"].ToString() + ") 已經完成<br> 事項說明:<br> " + dr["td_description"].ToString(), "[待辦事項] 來自 [" + dr["executor_name"].ToString() + "]", "");
//}
//如果td_edate='' 且 canback = '1' 且 owner 不是 執行者 表示 被否決完成了
//owner 的否決才算
SQL =
" select '" + ymd + "','" + time + "','" + userid + "',isnull(d.user_email,d.user_no) toAddr," +
" isnull((select isnull(ss.user_email,tt.td_member)+';' " + "\r\n" +
" from lrtd00d1 tt (nolock) " + "\r\n" +
" left join lrtduser ss (nolock) on tt.td_member=ss.user_no " + "\r\n" +
" where tt.td_role <> '2' and tt.td_no = a.td_no " + "\r\n" +
" for xml path('')),'') ccAddr," +
" '[待辦事項] 來自 [' + isnull(b.user_name,a.td_owner) + ']' lr_subject," + "\r\n" +
" 'To All:<br> 待辦事項單號:' + a.td_no + '(' + a.td_name + ') 已被[' + isnull(b.user_name,a.td_owner) + ']否決結果,請重新執行該項目!<br> 事項說明:<br> ' + a.td_description lr_content,'2','1' " + "\r\n" +
" from " + tmp_LRTD00H + " a(nolock) " + "\r\n" +
" left join lrtduser b on a.td_owner = b.user_no " + "\r\n" +
" left join lrtd00d1 c on a.td_no = c.td_no and c.td_role = '2' " + "\r\n" +
" left join lrtduser d on c.td_member = d.user_no " + "\r\n" +
" where a.canback = '1' and a.td_edate = '' and a.td_owner = '" + userid + "'" +
" and a.td_owner <> isnull(d.user_no,a.td_owner) and isnull(b.user_email,'') <> ''";//否決
conn.ExecuteSQL("insert into lrtdmail(ie_ymd,ie_time,ie_user,lr_to,lr_cc,lr_subject,lr_content,lr_type,lr_ftype) " + SQL);
//emails = conn.OpenDataTable(SQL, CommandType.Text);
//foreach (DataRow dr in emails.Rows)
//{
// SendEmail(dr["toAddr"].ToString(), dr["ccAddr"].ToString(), "To All:<br> 待辦事項單號:" + dr["td_no"].ToString() + "(" + dr["td_name"].ToString() + ") 已被[" + dr["ownername"].ToString() + "]否決結果,請重新執行該項目!<br> 事項說明:<br> " + dr["td_description"].ToString(), "[待辦事項] 來自 [" + dr["ownername"].ToString() + "]", "");
//}
#endregion 完成日期的回饋/否決
//Owner有變更執行者的 send_msg
conn.ExecuteSQL(
" update d set d.send_msg = 'Y' " +
" from " + tmpExecutor + " d(nolock) " +
" join " +
" (" +
" select a.td_no,b.td_member td_executor " + "\r\n" +
" from lrtd00h a(nolock) " + "\r\n" +
" join lrtd00d1 b(nolock) on a.td_no=b.td_no and b.td_role ='2' " + "\r\n" +
" left join " + tmp_LRTD00H + " c on a.td_no = c.td_no " + "\r\n" +
" where a.td_owner = '" + userid + "'" + "\r\n" +
" ) x on d.td_no = x.td_no " +
" where d.td_executor<> x.td_executor");
SendMsgAndEmail(conn, tmpExecutor, true, false, userid);
#endregion 發郵件
#region 最後寫日誌
DataTable Msgs = new DataTable("t1");
Msgs.Columns.Add("td_no", typeof(string));
Msgs.Columns.Add("msg_text", typeof(string));
DataTable dt = conn.OpenDataTable(
" select td_no,td_sylog " +
" from " + tmp_LRTD00H +
" where justread = '0' and td_owner = '" + userid + "'" +
" union all " +
" select a.td_no,a.td_sylog " +
" from " + tmp_LRTD00D1 + " a(nolock) " +
" join " + tmp_LRTD00H + " b(nolock) on a.td_no=b.td_no " +
" where b.td_owner<>'" + userid + "' and b.justread = '0' and a.td_member = '" + userid + "'", CommandType.Text
);
foreach (DataRow dr in dt.Rows)
{
DataRow newRow = Msgs.NewRow();
newRow["td_no"] = dr["td_no"].ToString();
newRow["msg_text"] = dr["td_sylog"].ToString();
Msgs.Rows.Add(newRow);
}
InsertMsgs(userid, DateTime.Now, Msgs, conn);
#endregion 寫日誌
conn._Transaction.Commit();
conn.CloseConnection();
}
catch (System.Exception ex)
{
conn._Transaction.Rollback();
conn.CloseConnection();
WriteLog(userid, ex.Message, "ErrLog");
throw ex;
}
}
}
public void UpdateGroup(DataTable groups, string userid)
{
string SQL = "";
using (SQLHelper conn = new SQLHelper())
{
conn.OpenConnection();
try
{
string tmp_LRGROUP = "tmp_LRGROUP_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
SQLHelper connTemp = new SQLHelper();
//開啟tempdb連接
connTemp.OpenConnection("tempdb");
connTemp.ExecuteSQL("create table " + tmp_LRGROUP + "(" + conn.GetTableStruct("lrgroup") + ",group_modify varchar(1) not null default '9')");
connTemp.CloseConnection();
tmp_LRGROUP = "tempdb.." + tmp_LRGROUP;
foreach (DataRow dr in groups.Rows)
{
SQL =
"insert into " + tmp_LRGROUP + "(user_no,group_id,group_name,group_modify) " + "\r\n" +
"select '" + dr["user_no"].ToString() + "','" + dr["group_id"].ToString() + "','" + dr["group_name"].ToString() + "','" + dr["group_modify"].ToString() + "'";
conn.ExecuteSQL(SQL);
}
conn._Transaction = conn._Connection.BeginTransaction();
string ymd = DateTime.Now.ToString("yyyy/MM/dd");
string time = DateTime.Now.ToString("HH:mm:ss");
//Add = 0,//新增的
//Delete =1,//刪除的
//Update = 2,//有更新的
//1.刪除要清空的資料
SQL =
" delete a " + "\r\n" +
" from lrgroup a " + "\r\n" +
" join " + tmp_LRGROUP + " b on a.group_id = b.group_id " + "\r\n" +
" where b.group_modify = '1'";
conn.ExecuteSQL(SQL);
//2.更新要更新的資料
SQL =
" update a set " + "\r\n" +
" a.group_name = b.group_name," + "\r\n" +
" a.ie_lymd ='" + ymd + "'," + "\r\n" +
" a.ie_ltime ='" + time + "'," + "\r\n" +
" a.ie_luser ='******' " + "\r\n" +
" from lrgroup a " + "\r\n" +
" join " + tmp_LRGROUP + " b on a.group_id = b.group_id " + "\r\n" +
" where b.group_modify = '2'";
conn.ExecuteSQL(SQL);
//3.新增要新增的資料
SQL =
" insert into lrgroup(ie_ymd,ie_time,ie_user,user_no,group_id,group_name) " + "\r\n" +
" select '" + ymd + "','" + time + "','" + userid + "',user_no,group_id,group_name " +
" from " + tmp_LRGROUP + " b " + "\r\n" +
" where b.group_modify = '0'";
conn.ExecuteSQL(SQL);
//最後清楚對照關係
//已經刪除的group 要清除
SQL =
" delete a from lrgmember a where not exists(select 1 from lrgroup b where a.group_id=b.group_id)";
conn.ExecuteSQL(SQL);
conn._Transaction.Commit();
conn.CloseConnection();
}
catch (System.Exception ex)
{
conn._Transaction.Rollback();
conn.CloseConnection();
WriteLog(userid, ex.Message, "ErrLog");
throw ex;
}
}
}