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