public List<ArrivalNoticeQuery> ArrivalNoticeList(ArrivalNoticeQuery query, out int totalCount)
{
try
{
return _IArrivalNoticeDao.ArrivalNoticeList(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("ArrivalNoticeMgr-->ArrivalNoticeList-->"+ex.Message,ex);
}
}
public List<ArrivalNoticeQuery> ArrivalNoticeList(ArrivalNoticeQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
totalCount = 0;
try
{
sqlCount.Append("select count(an.user_id) as totalCount ");
sql.Append(" select an.id,an.user_id,u.user_email, u.user_name,an.item_id,pi.item_stock,an.product_id,p.product_name, an.`status`,an.create_time,an.coming_time ");
sqlFrom.Append(" from arrival_notice as an ");
sqlFrom.Append(" LEFT JOIN users u on an.user_id=u.user_id ");
sqlFrom.Append(" LEFT JOIN product p on an.product_id=p.product_id ");
sqlFrom.Append(" left JOIN product_item pi on an.item_id=pi.item_id ");
sqlWhere.Append(" where 1=1 ");
if (query.condition == 1 && query.searchCon != "")
{
sqlWhere.AppendFormat(" and u.user_name like '%{0}%' ", query.searchCon);
}
else if (query.condition == 2 && query.searchCon != "")
{
sqlWhere.AppendFormat(" and p.product_name like '%{0}%' ", query.searchCon);
}
if (query.status != -1)
{
sqlWhere.AppendFormat(" and an.`status` ={0} ", query.status);
}
sqlWhere.AppendFormat(" and pi.item_stock >{0} ", query.item_stock);
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
sqlWhere.AppendFormat("limit {0},{1}", query.Start, query.Limit);
}
return _access.getDataTableForObj<ArrivalNoticeQuery>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("ArrivalNoticeDao-->ArrivalNoticeList-->" + ex.Message + sql.ToString() + sqlWhere.ToString(), ex);
}
}
public string IgnoreNotice(ArrivalNoticeQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("UPDATE arrival_notice SET status='{0}' ,create_time='{1}' ", query.status, CommonFunction.GetPHPTime());
if (query.status == 3 && query.coming_time != 0)
{
sql.AppendFormat(",coming_time='{0}' ", query.coming_time);
}
sql.AppendFormat(" where id='{0}';", query.id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("ArrivalNoticeDao-->IgnoreNotice-->" + ex.Message + sql.ToString(), ex);
}
}
public List<ArrivalNoticeQuery> GetArrNoticeList(ArrivalNoticeQuery query, out int totalCount)
{
try
{
List<ArrivalNoticeQuery> store = new List<ArrivalNoticeQuery>();
store = _IArrivalNoticeDao.GetArrNoticeList(query, out totalCount);
foreach (var item in store)
{
item.product_spec = item.spec_title_1;
item.product_spec += string.IsNullOrEmpty(item.spec_title_1) ? item.spec_title_2 : (string.IsNullOrEmpty(item.spec_title_2) ? "" : " / " + item.spec_title_2);
//if ((!string.IsNullOrEmpty(item.spec_title_1)) || (!string.IsNullOrEmpty(item.spec_title_2)))
//{
// item.product_spec = item.spec_title_1 + " / " + item.spec_title_2;
//}
}
return store;
}
catch (Exception ex)
{
throw new Exception("ArrivalNoticeMgr->GetArrNoticeList" + ex.Message);
}
}
public HttpResponseBase NoOrComing()
{
List<ArrivalNoticeQuery> list = new List<ArrivalNoticeQuery>();
ArrivalNoticeQuery query = null;
string json = string.Empty;
string mailBody;
MailHelper mailHelper = new MailHelper();
try
{
if (!string.IsNullOrEmpty(Request.Params["rowID"]))
{
string select_id = Request.Params["rowID"];
if (select_id.IndexOf("∑") != -1)
{
foreach (string id in select_id.Split('∑'))
{
if (!string.IsNullOrEmpty(id))
{
query = new ArrivalNoticeQuery();
string[] data = id.Split(',');
query.status = Convert.ToInt32(Request.Params["type"]);
query.id = Convert.ToUInt32(data[0]);
query.user_email = _usmgr.getModel(Convert.ToInt32(data[1].ToString())).user_email;
query.product_name = data[2];
if (!string.IsNullOrEmpty(Request.Params["coming_time"]))
{
query.coming_time = Convert.ToInt32(CommonFunction.GetPHPTime(Request.Params["coming_time"].ToString()));
}
if (!string.IsNullOrEmpty(Request.Params["recommend"]))
{
query.recommend = Request.Params["recommend"].ToString();
}
list.Add(query);
}
}
}
}
_IArrivalNoticeMgr = new ArrivalNoticeMgr(mySqlConnectionString);
if (_IArrivalNoticeMgr.IgnoreNotice(list))
{
FileStream fs = new FileStream(Server.MapPath("../ImportUserIOExcel/ArrivalNotice.html"), FileMode.OpenOrCreate, FileAccess.Read);
StreamReader sr = new StreamReader(fs, Encoding.UTF8);
string strTemp = sr.ReadToEnd();
for (int i = 0; i < list.Count; i++)
{
#region 不再補貨
if (list[i].status == 2)
{
if (!string.IsNullOrEmpty(list[i].recommend))
{
mailBody = "您關注補貨的商品" + "【" + list[i].product_name + "】" + "已不再進貨/販售，建議您購買我們推薦的類似商品：" + list[i].recommend;
}
else
{
mailBody = "您關注補貨的商品" + "【" + list[i].product_name + "】" + "已不再進貨/販售。 ";
}
strTemp = strTemp.Replace("{{$serviceAnwser$}}", mailBody);
}
#endregion
#region 預計補貨
if (list[i].status == 3)
{
mailBody = "您關注補貨的商品" + "【" + list[i].product_name + "】" + "將預計在" + CommonFunction.DateTimeToString(CommonFunction.GetNetTime(list[i].coming_time)) + "補貨，敬請及早購買，以免向隅。 ";
strTemp = strTemp.Replace("{{$serviceAnwser$}}", mailBody);
}
#endregion
try
{
mailHelper.SendMailAction(list[i].user_email, "吉甲地市集補貨通知信", strTemp);
}
catch (Exception ex)
{
json = "{success:true,msg:1}";
}
}
json = "{success:true,msg:0}";
}
else
{
json = "{failure:true}";
}
}
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 = "{failure:true}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetArrivalNoticeList()
{
List<ArrivalNoticeQuery> store = new List<ArrivalNoticeQuery>();
ArrivalNoticeQuery query = new ArrivalNoticeQuery();
int totalCount = 0;
string json = string.Empty;
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["condition"]))
{
query.condition = Convert.ToInt32(Request.Params["condition"]);
}
if (!string.IsNullOrEmpty(Request.Params["searchCon"]))
{
query.searchCon = Request.Params["searchCon"].ToString().Trim();
}
if (!string.IsNullOrEmpty(Request.Params["status"]))
{
query.status = Convert.ToInt32(Request.Params["status"]);
}
if (!string.IsNullOrEmpty(Request.Params["searchNum"]))
{
query.item_stock = Convert.ToInt32(Request.Params["searchNum"]);
}
_IArrivalNoticeMgr = new ArrivalNoticeMgr(mySqlConnectionString);
store = _IArrivalNoticeMgr.ArrivalNoticeList(query, out totalCount);
foreach (var item in store)
{
if (!string.IsNullOrEmpty(item.user_name.ToString()))
{
item.user_name = item.user_name.Substring(0, 1) + "**";
}
item.user_email = item.user_email.Split('@')[0] + "@***";
item.s_create_time = CommonFunction.GetNetTime(item.create_time);
if (item.coming_time != 0)
{
item.s_coming_time = CommonFunction.DateTimeToString(CommonFunction.GetNetTime(item.coming_time));
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, 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:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public List<ArrivalNoticeQuery> ShowArrByUserList(ArrivalNoticeQuery query, out int totalCount)
{
try
{
List<ArrivalNoticeQuery> store = new List<ArrivalNoticeQuery>();
store = _IArrivalNoticeDao.ShowArrByUserList(query, out totalCount);
foreach (var item in store)
{
// item.screate_time = CommonFunction.GetNetTime(item.create_time).ToString("yyyy-MM-dd HH:mm:ss");//一種顯示的格式
item.screate_time = CommonFunction.GetNetTime(item.create_time).ToString("yyyy-MM-dd");//把後台的int型的日期數據轉化為前台的string型的
item.ssend_notice_time = CommonFunction.GetNetTime(item.send_notice_time).ToString("yyyy-MM-dd");//把後台的int型的日期數據轉化為string型的
// 用戶狀態 1: 已通知 0:未通知 2:取消通知
if (item.user_status == 1)
{
item.sstatus = "已通知";
}
else if (item.user_status == 0)
{
item.sstatus = "未通知";
}
else if (item.user_status == 2)
{
item.sstatus = "取消通知";
}
//商品規格 規格1 規格2 多種規格時進行合併
if ((!string.IsNullOrEmpty(item.spec_title_1)) || (!string.IsNullOrEmpty(item.spec_title_2)))
{
item.product_spec = item.spec_title_1 + item.spec_title_2;
}
// source_type 訊息來源 1:前台 顯示的是user_name 2: 後台操作 顯示的是 manage表中的user_username
if (item.source_type == 1)
{
item.muser_name = item.user_name;// add by yachao1120j 2015-8-31
}
else if (item.source_type == 2)
{
item.muser_name = item.user_username;
}
}
return store;
}
catch (Exception ex)
{
throw new Exception("ReplenishmentInformStatisticsMgr->ShowArrByUserList" + ex.Message);
}
}
public void ExportCSV()
{
ArrivalNoticeQuery query = new ArrivalNoticeQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["vendor_name_full_OR_vendor_id"]))
{
query.vendor_name_full_OR_vendor_id = Request.Params["vendor_name_full_OR_vendor_id"];//供應商名稱/供應商編號
}
if (!string.IsNullOrEmpty(Request.Params["product_id_OR_product_name"]))//商品編號或商品名稱
{
query.product_id_OR_product_name = Request.Params["product_id_OR_product_name"];
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))//開始時間
{
query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00");
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))//結束時間
{
query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59");
}
DataTable dtHZ = new DataTable();
int totalcount = 0;
query.IsPage = false;
string newExcelName = string.Empty;
dtHZ.Columns.Add("商品編號", typeof(String));
dtHZ.Columns.Add("商品名稱", typeof(String));
dtHZ.Columns.Add("商品細項編號", typeof(String));
dtHZ.Columns.Add("商品規格", typeof(String));
dtHZ.Columns.Add("供應商編號", typeof(String));
dtHZ.Columns.Add("供應商名稱", typeof(String));
dtHZ.Columns.Add("補貨通知人數", typeof(String));
List<ArrivalNoticeQuery> list = new List<ArrivalNoticeQuery>();
arrivalnoticemgr = new ArrivalNoticeMgr(mySqlConnectionString);
list = arrivalnoticemgr.GetArrNoticeList(query, out totalcount);
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
DataRow dr = dtHZ.NewRow();
dr[0] = list[i].product_id;
dr[1] = list[i].product_name;
dr[2] = list[i].item_id;
dr[3] = list[i].product_spec;
dr[4] = list[i].vendor_id;
dr[5] = list[i].vendor_name_full;
dr[6] = list[i].ri_nums;
dtHZ.Rows.Add(dr);
}
string fileName = "補貨通知統計匯出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "");
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
else
{
Response.Clear();
this.Response.Write("無數據存在<br/>");
}
}
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);
}
}
public HttpResponseBase ShowArrByUserList()
{
string json = string.Empty;
int totalcount = 0;
ArrivalNoticeQuery query = new ArrivalNoticeQuery();
//Ris.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
//Ris.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
query.item_id = Convert.ToUInt32( Request.Params["item_id"]);
arrivalnoticemgr = new ArrivalNoticeMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["item_id"]))
{
string item_id = Request.Params["item_id"];
}
if (!string.IsNullOrEmpty(Request.Params["startTime"]))
{
query.start_time = Convert.ToDateTime(Request.Params["startTime"]).ToString("yyyy-MM-dd 00:00:00");
}
if (!string.IsNullOrEmpty(Request.Params["endTime"]))
{
query.end_time = Convert.ToDateTime(Request.Params["endTime"]).ToString("yyyy-MM-dd 23:59:59");
}
List<ArrivalNoticeQuery> List = arrivalnoticemgr.ShowArrByUserList(query, out totalcount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
timeConverter.DateTimeFormat = "yyyy-MM-dd";
json = "{success:true,totalCount:" + totalcount + ",data:" + JsonConvert.SerializeObject(List, Formatting.Indented,timeConverter) + "}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return Response;
}
public HttpResponseBase GetArrNoticeList()// createTime 2015/8/25 by yachao1120j
{
string json = string.Empty;
int totalcount = 0;
ArrivalNoticeQuery query = new ArrivalNoticeQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
arrivalnoticemgr = new ArrivalNoticeMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["vendor_name_full_OR_vendor_id"]))
{
query.vendor_name_full_OR_vendor_id = Request.Params["vendor_name_full_OR_vendor_id"];//供應商名稱/供應商編號
}
// 要修改商品編號 改為 商品編號/名稱
//if (!string.IsNullOrEmpty(Request.Params["product_id"]))//商品編號
//{
// query.product_id = Convert.ToUInt32(Request.Params["product_id"]);
//}
if (!string.IsNullOrEmpty(Request.Params["product_id_OR_product_name"]))
{
query.product_id_OR_product_name = Request.Params["product_id_OR_product_name"];//商品编号/名称
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))//開始時間
{
query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00");
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))//結束時間
{
query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59");
}
List<ArrivalNoticeQuery> list = arrivalnoticemgr.GetArrNoticeList(query, out totalcount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
timeConverter.DateTimeFormat = "yyyy-MM-dd";
json = "{success:true,totalCount:" + totalcount + ",data:" + JsonConvert.SerializeObject(list, Formatting.Indented, timeConverter) + "}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return Response;
}
public List<ArrivalNoticeQuery> GetArrNoticeList(ArrivalNoticeQuery query, out int totalCount)
{
StringBuilder str = new StringBuilder();//
StringBuilder strcont = new StringBuilder();//
totalCount = 0;
try
{
str.AppendFormat("SELECT an.id,p.product_id,p.product_name,an.item_id,CONCAT(p.spec_title_1,' ',ps1.spec_name) as spec_title_1,CONCAT(p.spec_title_2,' ',ps2.spec_name) as spec_title_2 ,v.vendor_id,v.vendor_name_full, COUNT(DISTINCT(an.id)) as 'ri_nums' from arrival_notice an ");
strcont.AppendFormat(" left JOIN product p on p.product_id=an.product_id ");
strcont.AppendFormat(" INNER JOIN product_item pi on pi.item_id=an.item_id ");
strcont.AppendFormat(" INNER JOIN vendor_brand vb on vb.brand_id=p.brand_id ");
strcont.AppendFormat(" INNER JOIN vendor v on v.vendor_id=vb.vendor_id ");
strcont.AppendFormat(" left JOIN product_spec ps1 on ps1.spec_id=pi.spec_id_1 ");
strcont.AppendFormat(" left JOIN product_spec ps2 on ps2.spec_id=pi.spec_id_2 ");
strcont.AppendFormat(" where 1=1 and status<>'2' ");
if (!string.IsNullOrEmpty(query.product_id_OR_product_name))//商品名稱或者商品編號或商品細項編號
{
int ID = 0;
if (int.TryParse(query.product_id_OR_product_name, out ID))
{
strcont.AppendFormat(" and ( p.product_id = '{0}' or pi.item_id = '{1}') ", query.product_id_OR_product_name, query.product_id_OR_product_name);
}
else
{
strcont.AppendFormat(" and p.product_name LIKE '%{0}%'", query.product_id_OR_product_name);
}
}
if (!string.IsNullOrEmpty(query.vendor_name_full_OR_vendor_id))//供應商名稱或者供應商編號
{
strcont.AppendFormat(" and (v.vendor_name_full LIKE '%{0}%' or v.vendor_id like '{1}') ", query.vendor_name_full_OR_vendor_id, query.vendor_name_full_OR_vendor_id);
}
if (!string.IsNullOrEmpty(query.start_time) && !string.IsNullOrEmpty(query.end_time))//報名開始日期 報名結束時間 都不為空的條件下
{
strcont.AppendFormat(" and an.create_time >='{0}' and an.create_time <='{1}' ", CommonFunction.GetPHPTime(query.start_time), CommonFunction.GetPHPTime(query.end_time));
}
strcont.AppendFormat("GROUP BY an.item_id ");
str.Append(strcont);
if (query.IsPage)//分頁
{
StringBuilder strpage = new StringBuilder();//
StringBuilder strcontpage = new StringBuilder();
strpage.AppendFormat("SELECT count(biao.item_id) as totalCount FROM(select an.item_id from arrival_notice an ");
strpage.Append(strcont);
strpage.AppendFormat(")as biao ");
DataTable _dt = _access.getDataTable(strpage.Append(strcontpage).ToString());
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
str.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
}
return _access.getDataTableForObj<ArrivalNoticeQuery>(str.ToString()); //獲取查詢記錄
}
catch (Exception ex)
{
throw new Exception("ArrivalNoticeDao-->GetArrNoticeList-->" + ex.Message);
}
}
public List<ArrivalNoticeQuery> ShowArrByUserList(ArrivalNoticeQuery query, out int totalCount)
{
StringBuilder str = new StringBuilder();
StringBuilder strcont = new StringBuilder();
totalCount = 0;
try
{
str.AppendFormat(" select an.id,an.item_id,u.user_id,u.user_name,mu.user_username,an.source_type,an.create_time,an.send_notice_time,an.status as user_status from arrival_notice an ");
strcont.AppendFormat(" LEFT JOIN users u on an.user_id=u.user_id ");
strcont.AppendFormat(" LEFT JOIN manage_user mu on mu.user_id=an.muser_id ");
strcont.AppendFormat(" where 1=1 and an.item_id={0} ", query.item_id);
if (!string.IsNullOrEmpty(query.start_time) && !string.IsNullOrEmpty(query.end_time))//報名開始日期 報名結束時間 都不為空的條件下
{
strcont.AppendFormat(" and an.create_time >='{0}' and an.create_time <='{1}' ", CommonFunction.GetPHPTime(query.start_time), CommonFunction.GetPHPTime(query.end_time));
}
str.Append(strcont);
return _access.getDataTableForObj<ArrivalNoticeQuery>(str.ToString()); //獲取查詢記錄
}
catch (Exception ex)
{
throw new Exception("ArrivalNoticeDao-->ShowArrByUserList-->" + ex.Message);
}
}