public List<PromoAdditionalPriceQuery> QueryAll(PromoAdditionalPriceQuery query, out int totalCount) {//促銷商品類別和銀行沒有加! StringBuilder str = new StringBuilder(); StringBuilder strall = new StringBuilder(); StringBuilder strcounts = new StringBuilder(); try { strcounts.AppendFormat("select count(PA.id) as totalcounts from promo_additional_price as PA "); //strall.AppendFormat("SELECT CONCAT(PA.event_type ,right(CONCAT('00000000',PA.id),6)) as 'event_id',PA.id,PA.deliver_type,PA.website,PA.device,event_name,event_desc,PC.banner_image,PC.category_link_url,VUG.group_name,fixed_price,buy_limit,TP.parameterName as deliver_name,TP1.parameterName as device_name,`start` as starts,`end`,active,PA.condition_id,PA.category_id,PA.discount,PA.left_category_id,PA.right_category_id,PA.url_by from promo_additional_price AS PA "); strall.AppendFormat("SELECT PA.event_type,PA.id,PA.deliver_type,PA.website,PA.device,event_name,event_desc,PC.banner_image,PC.category_link_url,VUG.group_name,fixed_price,buy_limit,`start` as starts,`end`,active,PA.condition_id,PA.category_id,PA.discount,PA.left_category_id,PA.right_category_id,PA.url_by,PA.muser,mu.user_username from promo_additional_price AS PA "); str.AppendFormat(" left join vip_user_group as VUG on PA.group_id=VUG.group_id "); str.AppendFormat(" LEFT JOIN product_category as PC ON PA.category_id=PC.category_id "); //str.AppendFormat(" LEFT JOIN t_parametersrc as TP on PA.deliver_type = TP.parameterCode AND TP.parameterType='product_freight'"); //str.AppendFormat(" left join t_parametersrc as TP1 on PA.device = TP1.parameterCode AND TP1.parameterType='device'"); //str.AppendFormat(" left join (select * from t_parametersrc where parameterType='event_type' ) ET ON PA.event_type = ET.parameterCode"); str.Append(" LEFT JOIN manage_user mu ON PA.muser=mu.user_id"); str.AppendFormat(" where PA.status=1 and PA.event_type= '{0}'", query.event_type); totalCount = 0; if (query.expired == 1)//是未過期 { str.AppendFormat(" and end >= '{0}'", CommonFunction.DateTimeToString(DateTime.Now)); } else if (query.expired == 0) { str.AppendFormat(" and end < '{0}'", CommonFunction.DateTimeToString(DateTime.Now)); } totalCount = 0; if (query.IsPage) { System.Data.DataTable _dt = _access.getDataTable(strcounts.ToString() + str.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]); } str.AppendFormat("order by PA.id DESC limit {0},{1} ", query.Start, query.Limit); } return _access.getDataTableForObj<PromoAdditionalPriceQuery>(strall.ToString() + str.ToString()); } catch (Exception ex) { throw new Exception("PromoAdditionalPriceDao-->QueryAll-->" + ex.Message + strall.ToString() + str.ToString(), ex); } }
public int DeletLessThen(PromoAdditionalPriceQuery m, int types) { try { return _PAProceDao.DeletLessThen(m, types); } catch (Exception ex) { throw new Exception("PromoAdditionalPriceMgr-->DeletLessThen-->" + ex.Message, ex); } }
public int DeletLessThen(PromoAdditionalPriceQuery m, int types) { StringBuilder sb = new StringBuilder(); try { sb.AppendFormat(" select pm.product_id from product_category_set pcs "); if (types == 1) { sb.AppendFormat(" inner join promo_additional_price pap on pcs.category_id=pap.category_id "); } else if (types == 2) { sb.AppendFormat(" inner join promo_additional_price pap on pcs.category_id=pap.right_category_id "); } sb.AppendFormat(" inner join price_master pm on pm.product_id= pcs.product_id "); sb.AppendFormat(" where 1=1 and pap.id='{0}' and pm.price<'{1}' and pm.price_master_id in({2}); ", m.id, m.fixed_price, m.price_master_in); DataTable dt_R = _access.getDataTable(sb.ToString()); sb.Clear(); string product_id_in = ""; if (dt_R.Rows.Count > 0) { for (int i = 0; i < dt_R.Rows.Count; i++) { product_id_in += dt_R.Rows[i]["product_id"].ToString() + ","; } product_id_in = product_id_in.TrimEnd(','); if (types == 1) { sb.AppendFormat("delete from product_category_set where category_id =(select category_id from promo_additional_price where id='{0}') ", m.id); } else if (types == 2) { sb.AppendFormat("delete from product_category_set where category_id =(select right_category_id from promo_additional_price where id='{0}') ", m.id); } sb.AppendFormat(" and product_id in ({0}) ", product_id_in); _access.execCommand(sb.ToString()); return 1; } else { return 0; } } catch (Exception ex) { throw new Exception("PromoAdditionalPriceDao-->DeletLessThen-->" + ex.Message + sb.ToString(), ex); } }
public int Delete(int i, string str) { int j = 0; MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); string sqlSW = ""; try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; PromoAdditionalPriceQuery query = new PromoAdditionalPriceQuery(); if (i != 0) {//更新主表數據 設置status為0 query = Select(i); if (query.condition_id != 0) {//軟刪除表user_conditon sqlSW += _usconDao.DeleteUserCon(query.condition_id); } if (query.category_id != 0) {//軟刪除product_cate 和 product_category_set //sqlSW += _proCateDao.Delete(query.category_id); //sqlSW += _prodCategSetDao.DelProdCateSet(query.category_id); } if (!string.IsNullOrEmpty(str)) {//軟刪除promo_all 和 prod_promo sqlSW += _prodPromoDao.DelProdProm(str); sqlSW += _promoAllDao.DeletePromAll(str); } //軟刪除主表 sqlSW += Deletepromoadditionalprice(query.id); } mySqlCmd.CommandText = sqlSW.ToString(); j += mySqlCmd.ExecuteNonQuery(); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("PromoAdditionalPriceDao-->Delete-->" + ex.Message + mySqlCmd.CommandText, ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } return i; }
public int Update(PromoAdditionalPrice m, PromoAdditionalPriceQuery mq) { int id = 0; StringBuilder sb = new StringBuilder(); MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; sb.Append(_prodPromoDao.DeleteProdProm(mq.event_id)); sb.AppendFormat(@"UPDATE promo_additional_price SET condition_id='{1}',group_id='{2}',`start`='{3}',`end`='{4}',modified='{5}',deliver_type='{6}',device='{7}',muser='******',website='{9}',event_name='{10}',event_desc='{11}',fixed_price='{12}',buy_limit='{13}',url_by='{14}',discount='{15}',active={16} where id={0} ; ", m.id, m.condition_id, m.group_id, CommonFunction.DateTimeToString(m.starts), CommonFunction.DateTimeToString(m.end), CommonFunction.DateTimeToString(m.modified), m.deliver_type, m.device, m.muser, m.website, m.event_name, m.event_desc, m.fixed_price, m.buy_limit, m.url_by, m.discount, m.active); #region 操作修改ProductCategory ProductCategoryDao _categoryDao = new ProductCategoryDao(connStr); ProductCategory pcmodel = _categoryDao.GetModelById(Convert.ToUInt32(m.category_id)); pcmodel.category_id = Convert.ToUInt32(m.category_id); pcmodel.banner_image = mq.banner_image; pcmodel.category_link_url = mq.category_link_url; pcmodel.category_name = m.event_name; pcmodel.category_display = Convert.ToUInt32(m.status); pcmodel.category_ipfrom = mq.category_ipfrom; pcmodel.category_updatedate = (uint)BLL.gigade.Common.CommonFunction.GetPHPTime(); sb.Append(_proCateDao.UpdateProdCate(pcmodel)); #endregion mySqlCmd.CommandText = sb.ToString(); id = Convert.ToInt32(mySqlCmd.ExecuteScalar()); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("PromoAdditionalPriceDao-->Update-->" + ex.Message + mySqlCmd.CommandText.ToString(), ex); } finally { mySqlConn.Close(); } return id; }
public HttpResponseBase PromoAdditionalPriceList() { PromoAdditionalPriceQuery query = new PromoAdditionalPriceQuery(); List<PromoAdditionalPriceQuery> store = new List<PromoAdditionalPriceQuery>(); string json = string.Empty; try { query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); if (!string.IsNullOrEmpty(Request.Params["limit"])) { query.Limit = Convert.ToInt32(Request.Params["limit"]); } if (!string.IsNullOrEmpty(Request.Params["ddlSel"])) { query.expired = Convert.ToInt32(Request.Params["ddlSel"]); } query.event_type = Request.Params["event_type"]; _promoadditionproceMgr = new PromoAdditionalPriceMgr(mySqlConnectionString); int totalCount = 0; store = _promoadditionproceMgr.Query(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; List<Parametersrc> ProductFreightStore = new List<Parametersrc>(); List<Parametersrc> DeviceStore = new List<Parametersrc>(); List<Parametersrc> EventTypeStore = new List<Parametersrc>(); _ptersrc = new ParameterMgr(mySqlConnectionString); ProductFreightStore = _ptersrc.GetElementType("product_freight");//---deliver_type DeviceStore = _ptersrc.GetElementType("device");//--device EventTypeStore = _ptersrc.GetElementType("event_type");//--event_type foreach (var item in store) { item.event_id = CommonFunction.GetEventId(item.event_type, item.id.ToString()); for (int i = 0; i < ProductFreightStore.Count; i++) { if (int.Parse(ProductFreightStore[i].ParameterCode) == item.deliver_type) { item.deliver_name = ProductFreightStore[i].parameterName; } } for (int i = 0; i < DeviceStore.Count; i++) { if (DeviceStore[i].ParameterCode == item.device) { item.device_name = DeviceStore[i].parameterName; } } //for (int i = 0; i < EventTypeStore.Count; i++) //{ // if (int.Parse(EventTypeStore[i].ParameterCode) == item.event_type) // { // item.device_name = EventTypeStore[i].parameterName; // } //} if (item.banner_image != "") { item.banner_image = imgServerPath + promoPath + item.banner_image; } else { item.banner_image = defaultImg; } } 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); json = "{success:false,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase PromoAdditionalPriceEdit() { string jsonStr = String.Empty; _promoadditionproceMgr = new PromoAdditionalPriceMgr(mySqlConnectionString); PromoAdditionalPrice model = new PromoAdditionalPrice(); PromoAdditionalPrice oldermodel = new PromoAdditionalPrice(); PromoAdditionalPriceQuery PPQuery = new PromoAdditionalPriceQuery(); ProductCategory olderpcmodel = new ProductCategory(); PromoAdditionalPriceQuery oldPPQuery = new PromoAdditionalPriceQuery(); if (!String.IsNullOrEmpty(Request.Params["rowid"])) { try { model.id = Convert.ToInt32(Request.Params["rowid"].ToString()); oldermodel = _promoadditionproceMgr.GetModel(model.id); model.category_id = oldermodel.category_id; olderpcmodel = _produCateMgr.GetModelById(Convert.ToUInt32(model.category_id)); model.event_name = Request.Params["event_name"].ToString(); model.event_desc = Request.Params["event_desc"].ToString(); model.event_type = oldermodel.event_type; if (!string.IsNullOrEmpty(Request.Params["url_by"])) { model.url_by =Convert.ToInt32(Request.Params["url_by"]); } #region 會員群組 會員條件 if (Request.Params["group_id"].ToString() != "") { try//group_id { model.group_id = Convert.ToInt32(Request.Params["group_id"].ToString()); } catch (Exception) { model.group_id = oldermodel.group_id; } model.condition_id = 0; } if (Request.Params["condition_id"].ToString() != "" && Request.Params["condition_id"].ToString() != "0") { try//condition_id { model.condition_id = Convert.ToInt32(Request.Params["condition_id"].ToString()); } catch (Exception) { model.condition_id = oldermodel.condition_id; } model.group_id = 0; } #endregion model.deliver_type = Convert.ToInt32(Request.Params["deliver_id"].ToString()); model.device = Request.Params["device_id"].ToString(); model.website = Request.Params["side"].ToString(); PPQuery.event_id = GetEventId(model.event_type, model.id.ToString()); try { model.fixed_price = Convert.ToInt32(Request.Params["fixed_price"].ToString()); } catch (Exception) { model.fixed_price = oldermodel.fixed_price; } try { model.buy_limit = Convert.ToInt32(Request.Params["buy_limit"].ToString()); } catch (Exception) { model.fixed_price = oldermodel.fixed_price; } model.starts = Convert.ToDateTime(Request.Params["starts"].ToString()); //string s = Request.Params["starts"]; //model.starts = DateTime.Parse(Request.Params["starts"]); model.end = Convert.ToDateTime(Request.Params["end"].ToString()); model.muser = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString(); model.modified = DateTime.Now; //20140925 add 折扣 if (model.event_type == "A3") { model.discount = Int32.Parse(Request.Params["discount"].ToString()); } else { model.discount = 0; } PPQuery.category_ipfrom = Request.UserHostAddress; PPQuery.event_id = GetEventId(model.event_type, model.id.ToString()); try//存連接地址 { PPQuery.category_link_url = Request.Params["banner_url"].ToString(); if (PPQuery.category_link_url == "" || PPQuery.category_link_url == null) { model.url_by = 0; //刪除上傳的圖片 string oldFileName = olderpcmodel.banner_image; FTP ftp = new FTP(imgLocalPath + promoPath, ftpuser, ftppwd); List<string> tem = ftp.GetFileList(); if (tem.Contains(oldFileName)) { FTP ftps = new FTP(imgLocalPath + promoPath + oldFileName, ftpuser, ftppwd); ftps.DeleteFile(imgLocalPath + promoPath + oldFileName);//刪除ftp:71.159上的舊圖片 DeletePicFile(Server.MapPath(imgLocalServerPath + promoPath) + oldFileName);//刪除本地圖片 } PPQuery.category_link_url = ""; PPQuery.banner_image = ""; } else { model.url_by = 1; #region 上傳圖片 try { string path = Server.MapPath(xmlPath); SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path); SiteConfig extention_config = _siteConfigMgr.GetConfigByName("PIC_Extention_Format"); SiteConfig minValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_MinValue"); SiteConfig maxValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_MaxValue"); SiteConfig admin_userName = _siteConfigMgr.GetConfigByName("ADMIN_USERNAME"); SiteConfig admin_passwd = _siteConfigMgr.GetConfigByName("ADMIN_PASSWD"); //擴展名、最小值、最大值 string extention = extention_config.Value == "" ? extention_config.DefaultValue : extention_config.Value; string minValue = minValue_config.Value == "" ? minValue_config.DefaultValue : minValue_config.Value; string maxValue = maxValue_config.Value == "" ? maxValue_config.DefaultValue : maxValue_config.Value; string localPromoPath = imgLocalPath + promoPath;//圖片存儲地址 Random rand = new Random(); int newRand = rand.Next(1000, 9999); FileManagement fileLoad = new FileManagement(); for (int iFile = 0; iFile < Request.Files.Count; iFile++) { HttpPostedFileBase file = Request.Files[iFile]; string fileName = string.Empty;//當前文件名 string fileExtention = string.Empty;//當前文件的擴展名 fileName = Path.GetFileName(file.FileName); // string returnName = imgServerPath; bool result = false; string NewFileName = string.Empty; fileExtention = fileName.Substring(fileName.LastIndexOf(".")).ToLower(); NewFileName = PPQuery.event_id + newRand + fileExtention; string ServerPath = string.Empty; //判斷目錄是否存在,不存在則創建 //string[] mapPath = new string[1]; //mapPath[0] = promoPath.Substring(1, promoPath.Length - 2); //string s = localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1); CreateFolder(localPromoPath.Substring(0, localPromoPath.Length - promoPath.Length + 1), promoPath.Substring(1, promoPath.Length - 2).Split('/')); // returnName += promoPath + NewFileName; fileName = NewFileName; NewFileName = localPromoPath + NewFileName;//絕對路徑 ServerPath = Server.MapPath(imgLocalServerPath + promoPath); string ErrorMsg = string.Empty; //上傳之前刪除已有的圖片 string oldFileName = olderpcmodel.banner_image; FTP ftp = new FTP(localPromoPath, ftpuser, ftppwd); List<string> tem = ftp.GetFileList(); if (tem.Contains(oldFileName)) { FTP ftps = new FTP(localPromoPath + oldFileName, ftpuser, ftppwd); ftps.DeleteFile(localPromoPath + oldFileName);//刪除ftp:71.159上的舊圖片 DeletePicFile(ServerPath + oldFileName);//刪除本地圖片 } try { //上傳 result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd); if (result)//上傳成功 { PPQuery.banner_image = fileName; } } catch (Exception) { PPQuery.banner_image = olderpcmodel.banner_image; } } } catch (Exception) { PPQuery.banner_image = olderpcmodel.banner_image; } #endregion } } catch (Exception) { PPQuery.category_link_url = oldPPQuery.category_link_url; } model.active = false; if (_promoadditionproceMgr.CategoryID(model).ToString() == "true") { _promoadditionproceMgr.Update(model, PPQuery); jsonStr = "{success:true}"; } else { jsonStr = "{success:false,msg:3}"; } } 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); jsonStr = "{success:false}"; } } this.Response.Clear(); this.Response.Write(jsonStr); this.Response.End(); return this.Response; }
public HttpResponseBase DeletLessThen() { uint producateid = Convert.ToUInt32(Request.Params["producateid"]); //uint key_id = 0; string product_name = string.Empty; int types = 0; if (!string.IsNullOrEmpty(Request.Params["types"]))//變動3 { types = int.Parse(Request.Params["types"]); } string site = "0";//站台改成多個站台的site_id字符串 edit by shuangshuang0420j 20140925 13:40 if (!string.IsNullOrEmpty(Request.Params["websiteid"])) { site = Request.Params["websiteid"]; } ProductCategorySetMgr _categorySetMgr = new ProductCategorySetMgr(mySqlConnectionString); List<ProductCategorySet> categoryList = _categorySetMgr.QueryMsg(new ProductCategorySetQuery { Category_Id = producateid }); ProductMgr _prodMgr = new ProductMgr(mySqlConnectionString); int totalCount = 0; PromotionsMaintainDao pmDao = new PromotionsMaintainDao(mySqlConnectionString); string pricemaster_in = ""; foreach (ProductCategorySet pcs in categoryList) { QueryVerifyCondition query = new QueryVerifyCondition(); query.name_number = pcs.Product_Id.ToString(); query.site_ids = site; if (!string.IsNullOrEmpty(product_name)) { query.product_name = product_name; } List<QueryandVerifyCustom> tempPros = pmDao.QueryByProSite(query, out totalCount, 0); if (1 <= tempPros.Count) { pricemaster_in += tempPros[0].price_master_id + ","; } } pricemaster_in = pricemaster_in.TrimEnd(','); string jsonStr = String.Empty; try { PromoAdditionalPriceQuery model = new PromoAdditionalPriceQuery(); _promoadditionproceMgr = new PromoAdditionalPriceMgr(mySqlConnectionString); model.id = Convert.ToInt32(Request.Params["rowid"].ToString()); model.fixed_price = Convert.ToInt32(Request.Params["fixed_price"].ToString()); model.price_master_in = pricemaster_in; if (string.IsNullOrEmpty(pricemaster_in)) { jsonStr = "{success:true,\"delcount\":\"" + 0 + "\" }"; } else { int result = _promoadditionproceMgr.DeletLessThen(model, types); if (result > 0) { jsonStr = "{success:true,\"delcount\":\"" + 1 + "\" }"; } else if (result == 0) { jsonStr = "{success:true,\"delcount\":\"" + 0 + "\" }"; } else { jsonStr = "{success:false}"; } } } 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); jsonStr = "{success:false}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }