public DataTable GetVendorChangeLog(TableChangeLogQuery query, out int totalCount)
{
try
{
DataTable _dt = _tclDao.GetVendorChangeLog(query, out totalCount);
_dt.Columns.Add("kuser_name");
_dt.Columns.Add("muser_name");
if (_dt.Rows.Count > 0)
{
foreach (DataRow item in _dt.Rows)
{
Dao.ManageUserDao _muDao = new Dao.ManageUserDao(connStr);
if (!string.IsNullOrEmpty(item["kuser"].ToString()) && item["kuser"].ToString() != "0")
{
item["kuser_name"] = _muDao.GetManageUser(new Model.ManageUser { user_id = Convert.ToUInt32(item["kuser"].ToString()) }).FirstOrDefault().user_username;
}
if (!string.IsNullOrEmpty(item["muser"].ToString()) && item["muser"].ToString() != "0")
{
item["muser_name"] = _muDao.GetManageUser(new Model.ManageUser { user_id = Convert.ToUInt32(item["muser"].ToString()) }).FirstOrDefault().user_username;
}
}
}
return _dt;
}
catch (Exception ex)
{
throw new Exception("TableChangeLogMgr-->GetVendorChangeLog-->" + ex.Message, ex);
}
}
/// <summary>
/// 異動記錄匯出
/// </summary>
/// <returns></returns>
public HttpResponseBase VendorLogExport()
{
string newCsvName = string.Empty;
string json = string.Empty;
TableChangeLogQuery query = new TableChangeLogQuery();
query.change_table = "vendor";
if (!string.IsNullOrEmpty(Request.Params["ven_type"]))
{
query.key_type = Convert.ToInt32(Request.Params["ven_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["d_type"]))
{
query.d_type = Convert.ToInt32(Request.Params["d_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["search_con"]))
{
query.key = Request.Params["search_con"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["date_one"]))
{
query.date_one = Convert.ToDateTime(Request.Params["date_one"]);
// query.date_one = Convert.ToDateTime(query.date_one.ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["date_two"]))
{
query.date_two = Convert.ToDateTime(Request.Params["date_two"]);
// query.date_two = Convert.ToDateTime(query.date_two.ToString("yyyy-MM-dd 23:59:59"));
}
TableChangeLogMgr _tclMgr = new TableChangeLogMgr(connectionString);
DataTable dt = _tclMgr.VendorLogExport(query);
try
{
string filename = "供應商資料異動記錄_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
newCsvName = Server.MapPath(excelPath) + filename;
if (System.IO.File.Exists(newCsvName))
{
//設置文件的屬性，以防刪除文件的時候因為文件的屬性造成無法刪除
System.IO.File.SetAttributes(newCsvName, FileAttributes.Normal);
System.IO.File.Delete(newCsvName);
}
string[] colname = { "供應商編號", "供應商編碼", "供應商名稱", "建立人", "建立時間", "修改人", "修改時間", "變動欄位", "欄位中文名", "修改前值", "修改后值" };
CsvHelper.ExportDataTableToCsv(dt, newCsvName, colname, true);
json = "true," + filename + "," + excelPath;
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "false, ";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 修改記錄詳情
/// </summary>
/// <returns></returns>
public HttpResponseBase VendorChangeDetailList()
{
string json = "{success:false,data:0}";
try
{
TableChangeLogQuery query = new TableChangeLogQuery();
query.change_table = "vendor";
if (!string.IsNullOrEmpty(Request.Params["vendor_id"]))
{
query.pk_id = Convert.ToInt32(Request.Params["vendor_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["muser"]))
{
query.create_user = Convert.ToInt32(Request.Params["muser"]);
}
if (!string.IsNullOrEmpty(Request.Params["mdate"]))
{
query.create_time = Convert.ToDateTime(Request.Params["mdate"]);
}
TableChangeLogMgr _tclMgr = new TableChangeLogMgr(connectionString);
BLL.gigade.Model.Custom.TableChangeLogCustom store = _tclMgr.GetVendorChangeDetail(query);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 修改記錄
/// </summary>
/// <returns></returns>
public HttpResponseBase VendorChangeList()
{
string json = "{success:false,data:0}";
try
{
TableChangeLogQuery query = new TableChangeLogQuery();
query.change_table = "vendor";
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["ven_type"]))
{
query.key_type = Convert.ToInt32(Request.Params["ven_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["d_type"]))
{
query.d_type = Convert.ToInt32(Request.Params["d_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["search_con"]))
{
query.key = Request.Params["search_con"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["date_one"]))
{
query.date_one = Convert.ToDateTime(Request.Params["date_one"]);
// query.date_one = Convert.ToDateTime(query.date_one.ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["date_two"]))
{
query.date_two = Convert.ToDateTime(Request.Params["date_two"]);
//query.date_two = Convert.ToDateTime(query.date_two.ToString("yyyy-MM-dd 23:59:59"));
}
// query.IsPage = false;
TableChangeLogMgr _tclMgr = new TableChangeLogMgr(connectionString);
int totalCount = 0;
DataTable store = _tclMgr.GetVendorChangeLog(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public DataTable GetVendorChangeLog(TableChangeLogQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlW = new StringBuilder();
StringBuilder sqlW1 = new StringBuilder();
try
{
if (!string.IsNullOrEmpty(query.key))
{
switch (query.key_type)
{
case 1:
query.key = query.key.Trim().Replace(' ', ',').Replace('，', ',').ToString();
sqlW.AppendFormat(" and vendor_id in ('{0}')", query.key);
break;
case 2:
query.key = query.key.Trim().Replace(' ', ',').Replace('，', ',').ToString();
sqlW.AppendFormat(" and vendor_code in ('{0}')", query.key);
break;
case 3:
query.key = query.key.Trim().ToString();
sqlW.AppendFormat(" and vendor_name_full like N'%{0}%'", query.key);
break;
default:
break;
}
}
if (!string.IsNullOrEmpty(query.change_table))
{
sqlW1.AppendFormat(" and change_table='{0}'", query.change_table);
}
if (query.date_one != DateTime.MinValue && query.date_two != DateTime.MinValue)
{
if (query.d_type == 1)
{
sqlW.AppendFormat(" and kdate between '{0}' and '{1}'", Common.CommonFunction.DateTimeToString(query.date_one), Common.CommonFunction.DateTimeToString(query.date_two));
}
else
{
sqlW1.AppendFormat(" and create_time between '{0}' and '{1}'", Common.CommonFunction.DateTimeToString(query.date_one), Common.CommonFunction.DateTimeToString(query.date_two));
}
}
sql.Append(" SELECT DISTINCT v.vendor_id,v.vendor_code,v.vendor_name_full,v.kuser,v.kdate,c.create_user as muser,c.create_time as mdate");
sql.AppendFormat(" FROM (SELECT vendor_id,vendor_code,vendor_name_full,kuser,kdate from vendor where 1=1 {0} )v", sqlW.ToString());
sql.AppendFormat(" INNER JOIN(select change_table,pk_id,create_user,create_time from table_change_log where 1=1 {0} )c on c.pk_id=v.vendor_id", sqlW1.ToString());
sql.Append(" order by c.create_time desc");
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _accessMySql.getDataTable(@" select count(vendor_id) as totalCount from (" + sql.ToString() + ") a;");
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
sql.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
}
}
return _accessMySql.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TableChangeLogDao-->GetVendorChangeLog-->" + ex.Message + sql.ToString(), ex);
}
}
public DataTable GetVendorChangeDetail(TableChangeLogQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlW = new StringBuilder();
try
{
if (!string.IsNullOrEmpty(query.change_table))
{
sqlW.AppendFormat(" and change_table='{0}'", query.change_table);
}
if (query.pk_id != 0)
{
sqlW.AppendFormat(" and pk_id='{0}'", query.pk_id);
}
if (query.create_user != 0)
{
sqlW.AppendFormat(" and create_user='******'", query.create_user);
}
if (query.create_time != DateTime.MinValue)
{
sqlW.AppendFormat(" and create_time='{0}'", Common.CommonFunction.DateTimeToString(query.create_time));
}
sql.AppendFormat("select pk_id, change_field,field_ch_name,old_value,new_value,vendor_id,vendor_name_full from ( SELECT pk_id,change_field,field_ch_name,old_value,new_value from table_change_log where 1=1 {0} ) tcl ", sqlW.ToString());
sql.Append(" inner join vendor v on v.vendor_id=tcl.pk_id");
return _accessMySql.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TableChangeLogDao-->GetVendorChangeDetail-->" + ex.Message + sql.ToString(), ex);
}
}
public Model.Custom.TableChangeLogCustom GetVendorChangeDetail(TableChangeLogQuery query)
{
try
{
DataTable _dt = _tclDao.GetVendorChangeDetail(query);
Model.Custom.TableChangeLogCustom _model = new Model.Custom.TableChangeLogCustom();
if (_dt.Rows.Count > 0)
{
Dao.ParametersrcDao _pDao = new Dao.ParametersrcDao(connStr);
List<Model.Parametersrc> _pModel = _pDao.Query(new Model.Parametersrc { ParameterType = "ColumnChange", ParameterCode = "vendor" });
List<Model.Parametersrc> _slist = _pDao.QueryParametersrcByTypes("vendor_type");
if (_pModel.Count > 0)
{
_model.vendor_id = Convert.ToInt32(_dt.Rows[0]["vendor_id"].ToString());
_model.vendor_name_full = _dt.Rows[0]["vendor_name_full"].ToString();
List<Model.TableChangeLog> _list = new List<Model.TableChangeLog>();
foreach (DataRow dr in _dt.Rows)
{
Model.TableChangeLog _log = new Model.TableChangeLog();
_log.change_field = dr["change_field"].ToString();
if (_log.change_field == "vendor_status")
{
string[] status_str = { "", "啟用", "停用", "失格" };
_log.old_value = status_str[Convert.ToInt32(dr["old_value"].ToString())].ToString();
_log.new_value = status_str[Convert.ToInt32(dr["new_value"].ToString())].ToString();
}
else if (_log.change_field == "product_manage")
{
ManageUserMgr mu = new ManageUserMgr(connStr);
List<ManageUser> o_mlist = mu.GetManageUser(new ManageUser { user_id = Convert.ToUInt32(dr["old_value"].ToString()) });
List<ManageUser> n_mlist = mu.GetManageUser(new ManageUser { user_id = Convert.ToUInt32(dr["new_value"].ToString()) });
if (o_mlist.Count > 0)
{
_log.old_value = o_mlist[0].user_username;
}
if (n_mlist.Count > 0)
{
_log.new_value = n_mlist[0].user_username;
}
}
else if (_log.change_field.IndexOf("contact_type") > -1)
{
string[] type_str = { "", "負責人", "業務窗口", "圖/文窗口", "出貨負責窗口", "帳務連絡窗口", "客服窗口", "刪除" };
_log.old_value = type_str[Convert.ToInt32(dr["old_value"].ToString())].ToString();
if (dr["new_value"].ToString() != "")
{
_log.new_value = type_str[Convert.ToInt32(dr["new_value"].ToString())].ToString();
}
else
{
_log.new_value = type_str[0].ToString();
}
}
else if (_log.change_field == "vendor_type")//供應商類型
{
if (_slist.Count > 0)
{
var a_o = dr["old_value"].ToString().Split(',');
var a_n = dr["new_value"].ToString().Split(',');
string l_o = ""; string l_n = "";
for (int i = 0; i < a_o.Length; i++)
{
if (!string.IsNullOrEmpty(a_o[i].ToString()))
{
l_o = l_o + _slist.Find(m => m.ParameterCode == a_o[i].ToString()).parameterName + ",";
}
}
for (int i = 0; i < a_n.Length; i++)
{
if (!string.IsNullOrEmpty(a_n[i].ToString()))
{
l_n = l_n + _slist.Find(m => m.ParameterCode == a_n[i].ToString()).parameterName + ",";
}
}
_log.old_value = l_o.TrimEnd(',');
_log.new_value = l_n.TrimEnd(',');
}
}
else if (_log.change_field == "company_address" || _log.change_field == "invoice_address")//公司地址或發票地址
{
string o_zip = dr["old_value"].ToString().Split('&')[0];
string o_adress = dr["old_value"].ToString().Substring(dr["old_value"].ToString().IndexOf('&') + 1);
string n_zip = dr["new_value"].ToString().Split('&')[0];
string n_adress = dr["new_value"].ToString().Substring(dr["new_value"].ToString().IndexOf('&') + 1);
ZipMgr zip = new ZipMgr(connStr);
_log.old_value = zip.Getaddress(Convert.ToInt32(o_zip)) + o_adress;
_log.new_value = zip.Getaddress(Convert.ToInt32(n_zip)) + n_adress;
}
else
{
_log.old_value = dr["old_value"].ToString();
_log.new_value = dr["new_value"].ToString();
}
if (!string.IsNullOrEmpty(dr["field_ch_name"].ToString()))
{
_log.field_ch_name = dr["field_ch_name"].ToString();
}
else
{
_log.field_ch_name = _pModel.Find(m => m.parameterName == _log.change_field).remark;
}
_list.Add(_log);
}
_model.tclModel = _list;
}
}
return _model;
}
catch (Exception ex)
{
throw new Exception("TableChangeLogMgr-->GetVendorChangeDetail-->" + ex.Message, ex);
}
}
public DataTable VendorLogExport(TableChangeLogQuery query)
{
DataTable result = new DataTable();
result.Columns.Add("供應商編號", typeof(String));
result.Columns.Add("供應商編碼", typeof(String));
result.Columns.Add("供應商名稱", typeof(String));
result.Columns.Add("建立人", typeof(String));
result.Columns.Add("建立時間", typeof(String));
result.Columns.Add("修改人", typeof(String));
result.Columns.Add("修改時間", typeof(String));
result.Columns.Add("變動欄位", typeof(String));
result.Columns.Add("欄位中文名", typeof(String));
result.Columns.Add("修改前值", typeof(String));
result.Columns.Add("修改后值", typeof(String));
int totalCount = 0;
query.IsPage = false;
DataTable _dt = GetVendorChangeLog(query, out totalCount);
if (_dt.Rows.Count > 0)
{
foreach (DataRow item in _dt.Rows)
{
DataRow dr = result.NewRow();
dr[0] = item["vendor_id"].ToString();
dr[1] = item["vendor_code"].ToString();
dr[2] = item["vendor_name_full"].ToString();
dr[3] = item["kuser_name"].ToString();
dr[4] = item["kdate"].ToString();
dr[5] = item["muser_name"].ToString();
dr[6] = item["mdate"].ToString();
TableChangeLogQuery log = new TableChangeLogQuery();
log.change_table = "vendor";
log.pk_id = Convert.ToInt32(item["vendor_id"].ToString());
log.create_user = Convert.ToInt32(item["muser"].ToString());
log.create_time = Convert.ToDateTime(item["mdate"].ToString());
Model.Custom.TableChangeLogCustom tclc = GetVendorChangeDetail(log);
dr[7] = tclc.tclModel[0].change_field.ToString();
dr[8] = tclc.tclModel[0].field_ch_name.ToString();
dr[9] = tclc.tclModel[0].old_value.ToString();
dr[10] = tclc.tclModel[0].new_value.ToString();
result.Rows.Add(dr);
for (int i = 1; i < tclc.tclModel.Count; i++)
{
DataRow dr2 = result.NewRow();
dr2[7] = tclc.tclModel[i].change_field.ToString();
dr2[8] = tclc.tclModel[i].field_ch_name.ToString();
dr2[9] = tclc.tclModel[i].old_value.ToString();
dr2[10] = tclc.tclModel[i].new_value.ToString();
result.Rows.Add(dr2);
}
}
}
return result;
}