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); } }