/// <summary>
/// 連接管理=>群組列表：連接點管理匯出
/// </summary>
/// <param name="query">綁定擴展實體進行查詢</param>
/// <param name="totalcount">可以進行分頁</param>
/// <param name="str">擴展的sql語句</param>
/// <returns>List<RedirectQuery>集合</returns>
public DataTable GetRedirectList(RedirectQuery query, out int totalcount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlfield = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
StringBuilder sqlgroup = new StringBuilder();
try
{
sqlfield.AppendFormat(@" SELECT (select group_name from redirect_group where group_id = '{0}') as group_name, ", query.group_id);
sqlfield.AppendFormat(@" redirect_id,group_id,redirect_name,redirect_url,redirect_status as 'status', redirect_total,");
sqlfield.AppendFormat(@" FROM_UNIXTIME(redirect_createdate) AS redirect_createdate,FROM_UNIXTIME(redirect_updatedate) AS redirect_updatedate,redirect_ipfrom");
sqlfrom.AppendFormat(@" from redirect ");
sqlwhere.AppendFormat(" where 1=1 and group_id ='{0}' ", query.group_id);
sql.Append(sqlfield.ToString() + sqlfrom.ToString() + sqlwhere.ToString());
sqlgroup.AppendFormat(@" ORDER BY redirect_id DESC ,redirect_status ASC,redirect_name ASC ");
totalcount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select redirect_id " + sqlfrom.ToString() + sqlwhere.ToString());
totalcount = _dt.Rows.Count;
sqlgroup.AppendFormat(@" limit {0},{1}", query.Start, query.Limit);
}
sql.Append(sqlgroup.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("RedirectDao.GetRedirectList-->" + ex.Message + sql.ToString(), ex);
}
}
public List<RedirectQuery> GetRedirect(RedirectQuery query, out int totalcount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlfield = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
StringBuilder sqlgroup = new StringBuilder();
try
{
sqlfield.AppendFormat(@" SELECT (select group_name from redirect_group where group_id = '{0}') as group_name, ", query.group_id);
sqlfield.AppendLine(@"redirect_id,group_id,user_group_id,redirect_name,redirect_url,redirect_status,redirect_total,");
sqlfield.AppendLine(@"redirect_note,redirect_createdate,redirect_updatedate,redirect_ipfrom");
sqlfrom.AppendLine(@" FROM redirect where 1=1");
if (query.group_id > 0)
{
sqlwhere.AppendFormat(" and group_id ='{0}' ", query.group_id);
}
sql.Append(sqlfield.ToString() + sqlfrom.ToString() + sqlwhere.ToString());
sqlgroup.AppendFormat(@" ORDER BY redirect_id DESC ,redirect_status ASC,redirect_name ASC ");
totalcount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select Count(redirect_id) as 'sum' " + sqlfrom.ToString() + sqlwhere.ToString());
totalcount = int.Parse(_dt.Rows[0]["sum"].ToString());
sqlgroup.AppendFormat(@" limit {0},{1}", query.Start, query.Limit);
}
sql.Append(sqlgroup.ToString());
return _access.getDataTableForObj<RedirectQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("RedirectDao.GetRedirectList-->" + ex.Message + sql.ToString(), ex);
}
}
public List<RedirectQuery> GetRedirect(RedirectQuery query, out int totalcount)
{
try
{
return _redirectDao.GetRedirect(query, out totalcount);
}
catch (Exception ex)
{
throw new Exception("RedirectMgr-->IRedirectImplDao-->" + ex.Message, ex);
}
}
public void RedirectExport()
{
string json = string.Empty;
StringBuilder sb = new StringBuilder();
string jsonStr = String.Empty;
try
{
_redirectMgr = new RedirectMgr(mySqlConnectionString);
RedirectQuery query = new RedirectQuery();
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = uint.Parse(Request.Params["group_id"]);
}
dt = _redirectMgr.GetRedirectListCSV(query);
DataTable dtCsv = new DataTable();
string newExcelName = string.Empty;
dtCsv.Columns.Add("群組", typeof(String));
dtCsv.Columns.Add("編號", typeof(String));
dtCsv.Columns.Add("連結名稱", typeof(String));
dtCsv.Columns.Add("目的連結", typeof(String));
dtCsv.Columns.Add("點閱次數", typeof(String));
dtCsv.Columns.Add("狀態", typeof(String));
dtCsv.Columns.Add("統計連結位置", typeof(String));
//string[] colname = { "群組", "編號", "連結名稱", "目的連結", "點閱次數", "狀態", "統計連結位址" };
string newName = string.Empty;
string fileName = "連接點管理匯出" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
newName = Server.MapPath(excelPath) + fileName;
foreach (DataRow dr in dt.Rows)
{
dr["status_name"] = (dr["status"].ToString() == "1" ? "正常" : "停用");
dr["redirect"] = LinkPath+"/public/link.php?r=" + dr["redirect_id"].ToString();
}
dt.Columns.Remove("status");
foreach (DataRow dr in dt.Rows)
{
DataRow drr = dtCsv.NewRow();
drr[0] = dr["group_name"];
drr[1] = dr["redirect_id"];
drr[2] = dr["redirect_name"];
drr[3] = dr["redirect_url"];
drr[4] = dr["redirect_total"];
drr[5] = dr["status_name"].ToString();
drr[6] = dr["redirect"];
dtCsv.Rows.Add(drr);
}
if (System.IO.File.Exists(newName))
{
//設置文件的屬性，以防刪除文件的時候因為文件的屬性造成無法刪除
System.IO.File.SetAttributes(newName, FileAttributes.Normal);
System.IO.File.Delete(newName);
}
//CsvHelper.ExportDataTableToCsv(dt, newName, colname, true);
StringWriter sw = ExcelHelperXhf.SetCsvFromData(dtCsv, "連結管理.csv");
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + "連結管理_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.Default;
Response.Write(sw);
Response.End();
}
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 = "{success:false,data:[]}";
}
}
//excel
public HttpResponseBase RedirectUploadExcel()
{
string newName = string.Empty;
string json = string.Empty;
_redirectMgr = new RedirectMgr(mySqlConnectionString);
try
{
DTExcels.Clear();
DTExcels.Columns.Clear();
DTExcels.Columns.Add("連結名稱", typeof(String));
DTExcels.Columns.Add("目的連結", typeof(String));
DTExcels.Columns.Add("連結狀態", typeof(String));//1表示正常2表示停用 其他都為正常
int count = 0;//總匯入數
int errorcount = 0;//數據異常個數
int create_user = (Session["caller"] as Caller).user_id;
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()) && !string.IsNullOrEmpty(dt.Rows[0][1].ToString()))
{
foreach (DataRow dr in dt.Rows)
{
if (!string.IsNullOrEmpty(dr[0].ToString()) && !string.IsNullOrEmpty(dr[1].ToString()))
{
#region 匯入數據
RedirectQuery rd = new RedirectQuery();
rd.group_id = Convert.ToUInt32(Request.Params["group_id"]);//得到群組
try
{
int linkstatus = 0;
if (!int.TryParse(dr[2].ToString(), out linkstatus))
{
linkstatus = 1;
}
if (linkstatus == 2)
{
linkstatus = 2;
rd.redirect_name = dr[0].ToString();//連接名稱
rd.redirect_url = dr[1].ToString();//目的連接
rd.redirect_status = Convert.ToUInt32(linkstatus);//連接狀態
}
else
{
linkstatus = 1;
rd.redirect_name = dr[0].ToString();
rd.redirect_url = dr[1].ToString();
rd.redirect_status = Convert.ToUInt32(linkstatus);
}
rd.redirect_createdate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
rd.redirect_updatedate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
rd.redirect_ipfrom = CommonFunction.GetClientIP();
//獲取Serial裡面的redirect_id
_serialMgr = new SerialMgr(mySqlConnectionString);
rd.redirect_id = uint.Parse((_serialMgr.GetSerialById(4).Serial_Value + 1).ToString());
}
catch (Exception ex)
{//不是正常數據時候直接帶1(正常)
int linkstatus = 1;
rd.redirect_name = dr[0].ToString();
rd.redirect_url = dr[1].ToString();
rd.redirect_status = Convert.ToUInt32(linkstatus);
}
int result = 0;//如果獲取到的連結名稱和url為空就不新增
if (!string.IsNullOrEmpty(rd.redirect_name) && !string.IsNullOrEmpty(rd.redirect_url))//&& Regex.IsMatch(rd.redirect_url,'')
{
result = _redirectMgr.EnterInotRedirect(rd);
}
if (result > 0)
{//插入成功一條并修改serial表數據
count++;
Serial serial = new Serial();
serial.Serial_id = 4;
serial.Serial_Value = rd.redirect_id;
_serialMgr.Update(serial);
}
else
{
DataRow drs = DTExcels.NewRow();
drs[0] = dr[0].ToString();
drs[1] = dr[1].ToString();
drs[2] = dr[2].ToString();
DTExcels.Rows.Add(drs);
errorcount++;//插入失敗一條
}
#endregion
}
json = "{success:true,count:" + count + ",errorcount:" + errorcount + "}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + 0 + ",entercount:" + 0 + "}";
}
}
}
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 = "{success:false,data:" + "" + "}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetRedirectList()
{
DataTable dt = new DataTable();
List<RedirectQuery> list = new List<RedirectQuery>();
string json = string.Empty;
try
{
RedirectQuery query = new RedirectQuery();
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = uint.Parse(Request.Params["group_id"]);
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
_redirectMgr = new RedirectMgr(mySqlConnectionString);
int totalCount = 0;
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
// dt = _redirectMgr.GetRedirectList(query, out totalCount);
list = _redirectMgr.GetRedirect(query, out totalCount);
foreach (var item in list)
{
item.sredirect_createdate = CommonFunction.GetNetTime(item.redirect_createdate);
item.sredirect_updatedate = CommonFunction.GetNetTime(item.redirect_updatedate);
query.redirect_id = item.redirect_id;
query.selsum = 0;
item.user = _redirectMgr.GetSum(query);
query.selsum = 1;
item.order = _redirectMgr.GetSum(query);
}
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(list, Formatting.Indented, timeConverter) + "}";
}
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 = "{success:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public void GroupExportCSV()
{
string newCsvName = string.Empty;
string json = string.Empty;
_rdGroupMgr = new RedirectGroupMgr(mySqlConnectionString);
DataTable dt = new DataTable();
DataTable dtCsv = new DataTable();
string newExcelName = string.Empty;
try
{
_redirectMgr = new RedirectMgr(mySqlConnectionString);
RedirectQuery query = new RedirectQuery();
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"].ToString());
}
#region 歷史
//RedirectGroup rgModel = new RedirectGroup();
//rgModel.group_id = Convert.ToUInt32(Request.Params["group_id"].ToString());
//List<Redirect> rli = _rdGroupMgr.QueryRedirectAll(rgModel.group_id);
//dt.Columns.Add("日期");
//// Array rename = rli.GroupBy(m => m.redirect_name).Select(m => m.Key).ToArray();
//// Array reID = rli.GroupBy(m => m.redirect_id).Select(m => m.Key).ToArray();
//string[] reArray = null;
//foreach (var li in rli)
//{
// dt.Columns.Add(li.redirect_name);
// reArray[li.redirect_id] = li.redirect_name;
//}
//// 查詢點率次數
//List<RedirectClick> reCli = _rdGroupMgr.QueryRedirectClictAll(null);
//uint minClick = Convert.ToUInt32(reCli.Min(m => m.click_id).ToString());
//if (minClick > 3000123123)
//{
// minClick = 3000123123;
//}
//string[][] reclickArray = null;
//foreach (var cli in reCli)
//{
// //minClick = minClick > cli.click_id ? cli.click_id : minClick;
// int redid = (int)cli.redirect_id;
// string d = cli.click_year + "-" + cli.click_month.ToString().Substring(0, 2) + "-" + cli.click_day.ToString().Substring(0, 2);
// if (reclickArray['d'][redid] != null)
// {
// reclickArray['d'][redid] = reclickArray['a'][redid] + cli.click_total;
// }
// else
// {
// reclickArray['d'][redid] = cli.click_total.ToString();
// }
//}
//// int count = 0;
#endregion
int totalCount = 0;
query.IsPage = false;
dt = _redirectMgr.GetRedirectList(query, out totalCount);
dtCsv = dt.DefaultView.ToTable(false, new string[] { "group_id", "group_name", "redirect_url", "redirect_total", "redirect_status" });
for (int i = 0; i < dtCsv.Rows.Count; i++)
{
switch (dtCsv.Rows[i]["redirect_status"].ToString())
{
case "1":
dtCsv.Rows[i]["invoice_status"] = "正常";
break;
case "2":
dtCsv.Rows[i]["invoice_status"] = "停用";
break;
default:
break;
}
}
string[] colname = { "群組編號", "群組名稱", "目的連結", "點閱次數", "狀態" };
string filename = "group_click_" + DateTime.Now.ToString("yyyyMMdd") + ".csv";
newExcelName = Server.MapPath(excelPath) + filename;
if (System.IO.File.Exists(newExcelName))
{
//設置文件的屬性，以防刪除文件的時候因為文件的屬性造成無法刪除
System.IO.File.SetAttributes(newExcelName, FileAttributes.Normal);
System.IO.File.Delete(newExcelName);
}
StringWriter sw = ExcelHelperXhf.SetCsvFromData(dt, filename);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(filename));
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.Default;
Response.Write(sw);
Response.End();
}
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";
}
}
public void RedirectClickExport()
{
string newCsvName = string.Empty;
string json = string.Empty;
_rdGroupMgr = new RedirectGroupMgr(mySqlConnectionString);
DataTable dt = new DataTable();
DataTable dtCsv = new DataTable();
string newExcelName = string.Empty;
try
{
_redirectMgr = new RedirectMgr(mySqlConnectionString);
RedirectQuery query = new RedirectQuery();
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"].ToString());
}
int totalCount = 0;
query.IsPage = false;
dt = _redirectMgr.GetRedirectList(query, out totalCount);
dtCsv = dt.DefaultView.ToTable(false, new string[] { "group_id", "group_name", "redirect_url", "redirect_total", "status" });
string[] colname = { "群組編號", "群組名稱", "目的連結", "點閱次數", "狀態" };
string filename = "group_click_" + DateTime.Now.ToString("yyyyMMdd") + ".csv";
newExcelName = Server.MapPath(excelPath) + filename;
if (System.IO.File.Exists(newExcelName))
{
System.IO.File.SetAttributes(newExcelName, FileAttributes.Normal);
System.IO.File.Delete(newExcelName);
}
StringWriter sw = ExcelHelperXhf.SetCsvFromData(dt, filename);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(filename));
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.Default;
Response.Write(sw);
Response.End();
}
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";
}
}
public DataTable GetRedirectListCSV(RedirectQuery query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlfield = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
StringBuilder sqlgroup = new StringBuilder();
try
{
sqlfield.AppendFormat(@" SELECT (select group_name from redirect_group where group_id = '{0}') as group_name, ", query.group_id);
sqlfield.Append(@" redirect_id,redirect_name,redirect_url,redirect_total,redirect_status as status,'' as status_name,'' as redirect ");
sqlfrom.Append(@" from redirect ");
sqlwhere.AppendFormat(" where 1=1 and group_id ='{0}' ", query.group_id);
sql.Append(sqlfield.ToString() + sqlfrom.ToString() + sqlwhere.ToString());
sqlgroup.AppendFormat(@" ORDER BY redirect_id DESC ,redirect_status ASC,redirect_name ASC ");
sql.Append(sqlgroup.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("RedirectDao.GetRedirectListCSV-->" + ex.Message + sql.ToString(), ex);
}
}
public string GetSum(RedirectQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append("select count(r.redirect_id) as Sum from redirect r ");
if (query.selsum == 0)
{
sql.Append("LEFT JOIN users u ON r.redirect_id = u.source_trace ");
}
else if (query.selsum == 1)
{
sql.Append("LEFT JOIN order_master u ON r.redirect_id = u.source_trace ");
}
sql.AppendFormat(" where r.redirect_id='{0}'; ", query.redirect_id);
return _access.getDataTable(sql.ToString()).Rows[0]["Sum"].ToString();
}
catch (Exception ex)
{
throw new Exception("RedirectDao.GetUserSum-->" + ex.Message + sql.ToString(), ex);
}
}
public int EnterInotRedirect(RedirectQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.Append(" INSERT INTO redirect(redirect_id,group_id,user_group_id,redirect_name,redirect_url,");
sql.Append(" redirect_status,redirect_total, redirect_note,redirect_createdate,redirect_updatedate,redirect_ipfrom ) ");
sql.AppendFormat(" VALUES ('{0}','{1}','{2}','{3}','{4}',", query.redirect_id, query.group_id, query.user_group_id, query.redirect_name, query.redirect_url);
sql.AppendFormat(" '{0}','{1}','{2}','{3}','{4}','{5}')", query.redirect_status, query.redirect_total, query.redirect_note, query.redirect_createdate, query.redirect_updatedate, query.redirect_ipfrom);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("RedirectDao-->Save-->" + ex.Message + sql.ToString(), ex);
}
}
public DataTable GetRedirectListCSV(RedirectQuery query)
{
try
{
return _redirectDao.GetRedirectListCSV(query);
}
catch (Exception ex)
{
throw new Exception("RedirectMgr.GetRedirectListCSV-->" + ex.Message, ex);
}
}
public string GetSum(RedirectQuery query)
{
try
{
return _redirectDao.GetSum(query);
}
catch (Exception ex)
{
throw new Exception("RedirectMgr.GetSum-->" + ex.Message, ex);
}
}
public int EnterInotRedirect(RedirectQuery query)
{
try
{
return _redirectDao.EnterInotRedirect(query);
}
catch (Exception ex)
{
throw new Exception("RedirectMgr.EnterInotRedirect-->" + ex.Message, ex);
}
}