C# Model.Replace4MySQL方法代码示例

        /// <summary>
        /// 查詢點數抵用列表頁的數據
        /// </summary>
        /// <param name="query">點數抵用的Model</param>
        /// <param name="total">數據的總數</param>
        /// <returns></returns>
        public List<Model.Query.PromotionsDeductRateQuery> QueryAll(Model.Query.PromotionsDeductRateQuery query, out int total)
        {
            query.Replace4MySQL();
            string sql = "SELECT `PDR`.`id`,`PDR`.`active`, `PDR`.`name`,`PDR`.`condition_id`,`PDR`.`group_id`, `PDR`.`amount`, `PDR`.`bonus_type`,CONCAT( `PDR`.`dollar`,'/',`PDR`.`point`) as points,`PDR`.`rate`,PDR.dollar,PDR.point, `PDR`.`start` as startdate, `PDR`.`end` , `PDR`.`created`, `PDR`.`modified`, `PDR`.`status`,VUG.group_name,PDR.muser,mu.user_username ";
            string sqlfrom = "FROM `promotions_deduct_rate` AS `PDR` left join vip_user_group as VUG on PDR.group_id=VUG.group_id LEFT JOIN manage_user mu ON PDR.muser=mu.user_id WHERE `status` = 1 ";
            if (query.expired == 0)
            {
                sqlfrom += " AND `end` <'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            else
            {
                sqlfrom += " AND `end` >'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            total = 0;
            string sqlfrom2 = sqlfrom + " order by PDR.id desc " + " limit " + query.Start + "," + query.Limit;
            System.Data.DataTable _dt = _access.getDataTable("select count(PDR.id) as total " + sqlfrom);
            if (_dt != null)
            {
                total = Convert.ToInt32(_dt.Rows[0]["total"]);
            }
            try
            {
                return _access.getDataTableForObj<PromotionsDeductRateQuery>(sql + sqlfrom2);
            }
            catch (Exception ex)
            {

                throw new Exception("PromotionsDeductRateDao-->QueryAll-->" + ex.Message + sql + sqlfrom2, ex);
            }
           
        } 

        public string AddOrUpdate(Model.EventPromoAmountGift model)
        {
            model.Replace4MySQL();
            StringBuilder sql = new StringBuilder();
            try
            {
                if (model.row_id == 0)
                {
                    sql.Append(@"insert into event_promo_amount_gift(event_name,event_desc,event_start,event_end,event_type,event_id,
                             site_id,create_user,create_time,modify_user,modify_time,user_condition_id,condition_type,
                             device,event_status)");
                    sql.AppendFormat(" values('{0}','{1}','{2}','{3}','{4}','{5}'", model.event_name, model.event_desc, Common.CommonFunction.DateTimeToString(model.event_start), Common.CommonFunction.DateTimeToString(model.event_end), model.event_type, model.event_id);
                    sql.AppendFormat(",'{0}','{1}','{2}','{3}','{4}','{5}','{6}'", model.site_id, model.create_user, Common.CommonFunction.DateTimeToString(model.create_time), model.modify_user, Common.CommonFunction.DateTimeToString(model.modify_time), model.user_condition_id, model.condition_type);
                    sql.AppendFormat(",'{0}','{1}');select @@identity;", model.device, model.event_status);
                }
                else
                {
                    sql.AppendFormat("update event_promo_amount_gift set  event_name='{0}',event_desc='{1}',event_start='{2}',event_end='{3}',event_type='{4}',", model.event_name, model.event_desc, Common.CommonFunction.DateTimeToString(model.event_start), Common.CommonFunction.DateTimeToString(model.event_end), model.event_type);
                    sql.AppendFormat(" site_id='{0}',modify_user='{1}',modify_time='{2}',user_condition_id='{3}',condition_type='{4}',", model.site_id, model.modify_user, Common.CommonFunction.DateTimeToString(model.modify_time), model.user_condition_id, model.condition_type);
                    sql.AppendFormat(" device='{0}',event_status='{1}' where row_id='{2}';", model.device, model.event_status, model.row_id);
                }
                return sql.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("EventPromoAmountGiftMgr-->AddOrUpdate-->" + ex.Message, ex);
            }

        }

        //修改站台價格不再更新price_master表，而是更新price_master_ts表  add by xiangwang0413w 2014/07/16
        public string UpdateTs(Model.PriceMaster pM)
        {
            try
            {
                pM.Replace4MySQL();
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("set sql_safe_updates=0;delete from price_master_ts where price_master_id={0};set sql_safe_updates=1;", pM.price_master_id);

                strSql.Append(@"insert into price_master_ts(`price_master_id`,`product_id`,`site_id`,`user_level`,`user_id`,`product_name`,
                `accumulated_bonus`,`bonus_percent`,`default_bonus_percent`,`bonus_percent_start`,`bonus_percent_end`,
                `same_price`,`event_start`,`event_end`,`price_status`, `price`,`event_price`,
                `child_id`,`apply_id`,`cost`,`event_cost`,`max_price`,`max_event_price`,`valid_start`,`valid_end`)");
                strSql.AppendFormat(" select `price_master_id`,{0} as `product_id`,{1} as `site_id`,{2} as `user_level`,{3} as `user_id`,'{4}' as `product_name`,"
                    , pM.product_id, pM.site_id, pM.user_level, pM.user_id, pM.product_name);
                strSql.AppendFormat("{0} as `accumulated_bonus`,{1} as `bonus_percent`,{2} as `default_bonus_percent`,{3} as `bonus_percent_start`,{4} as `bonus_percent_end`,"
                    , pM.accumulated_bonus, pM.bonus_percent, pM.default_bonus_percent,pM.bonus_percent_start,pM.bonus_percent_end);
                strSql.AppendFormat("{0} as `same_price`,{1} as `event_start`,{2} as `event_end`,{3} as `price_status`, {4} as `price`,{5} as `event_price`,"
                    ,pM.same_price,pM.event_start,pM.event_end,pM.price_status,pM.price,pM.event_price);
                strSql.AppendFormat("{0} as `child_id`,{1} as `apply_id`,{2} as `cost`,{3} as `event_cost`,{4} as `max_price`,{5} as `max_event_price`,{6} as `valid_start`,{7} as `valid_end`" 
                    , pM.child_id, pM.apply_id, pM.cost, pM.event_cost, pM.max_price, pM.max_event_price, pM.valid_start,pM.valid_end);
                strSql.AppendFormat(" from price_master where price_master_id={0};", pM.price_master_id);
                return strSql.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("PriceMasterDao.UpdateTs-->" + ex.Message, ex);
            }
        }

 public int AddOrUpdate(Model.Query.EventPromoUserConditionQuery epQuery)
 {
     StringBuilder sb = new StringBuilder();
     epQuery.Replace4MySQL();
     try
     {
         if (epQuery.condition_id > 0)//編輯
         {
             sb.AppendFormat(@"update event_promo_user_condition set condition_name='{0}',level_id='{1}',first_buy_time='{2}',reg_start='{3}',reg_end='{4}',buy_times_min='{5}',buy_times_max='{6}',buy_amount_min='{7}',
                              buy_amount_max='{8}', group_id='{9}',modify_user='{10}',modify_time='{11}' where condition_id='{12}'; ",
                              epQuery.condition_name, epQuery.level_id, epQuery.first_buy_time, epQuery.reg_start, epQuery.reg_end, epQuery.buy_times_min, epQuery.buy_times_max, epQuery.buy_amount_min,
                              epQuery.buy_amount_max, epQuery.group_id, epQuery.modify_user, Common.CommonFunction.DateTimeToString(epQuery.modify_time), epQuery.condition_id);
             return _dbAccess.execCommand(sb.ToString());
         }
         else
         {
             sb.AppendFormat(@"INSERT INTO event_promo_user_condition(condition_name,level_id,first_buy_time,reg_start,reg_end,buy_times_min,buy_times_max,
             buy_amount_min,buy_amount_max,group_id,create_user,create_time,modify_user,modify_time)");
             sb.AppendFormat("VALUES('{0}','{1}',{2},'{3}',{4},'{5}','{6}',", epQuery.condition_name, epQuery.level_id, epQuery.first_buy_time, epQuery.reg_start, epQuery.reg_end, epQuery.buy_times_min, epQuery.buy_times_max);
             sb.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}');", epQuery.buy_amount_min, epQuery.buy_amount_max, epQuery.group_id, epQuery.create_user, Common.CommonFunction.DateTimeToString(epQuery.create_time), epQuery.modify_user, Common.CommonFunction.DateTimeToString(epQuery.modify_time));
             return _dbAccess.execCommand(sb.ToString());
         }
     }
     catch (Exception ex)
     {
         throw new Exception("EventpromoUserConditionDao-->AddOrUpdate--" + ex.Message, ex);
     }
 }

 public List<Model.ProductCategory> QueryAll(Model.ProductCategory query)
 {
     query.Replace4MySQL();
     StringBuilder sql = new StringBuilder("select category_id,category_name,category_father_id from product_category where 1=1");
     if (query.category_id != 0)
     {
         sql.AppendFormat(" and category_id = {0}", query.category_id);
     }
     if (!string.IsNullOrEmpty(query.category_name))
     {
         sql.AppendFormat(" and category_name = '{0}'", query.category_name);
     }
     if (query.category_father_id != 0)
     {
         sql.AppendFormat("  and category_father_id = {0}", query.category_father_id);
     }
     if (query.category_display != 0)//顯示與否
     {
         sql.AppendFormat(" and category_display = {0}", query.category_display);
     }
     if (query.status != 0)//啟用/禁用
     {
         sql.AppendFormat(" and status = {0}", query.status);
     }
     return _access.getDataTableForObj<Model.ProductCategory>(sql.ToString());
 }

 public int Insert(Model.WebContentType6 model)
 {
     model.Replace4MySQL();
     StringBuilder sb = new StringBuilder();
     try
     {
         if (model.content_status == 1)//啟用
         {
             WebContentTypeSetupDao _setDao = new WebContentTypeSetupDao(_connStr);
             WebContentTypeSetup smodel = new WebContentTypeSetup();
             smodel.site_id = model.site_id;
             smodel.page_id = model.page_id;
             smodel.area_id = model.area_id;
             smodel.web_content_type = "web_content_type6";
             _setDao.UpdateLimitStatus(smodel);////當前已啟用的個數超過5筆時，使最舊的不啟用，
         }
       
         sb.AppendFormat(@"INSERT INTO web_content_type6(`site_id`,`page_id`,`area_id`,`home_title`,`content_title`,`content_html`,`home_image`,`content_default`,`content_status`,`link_url`,`link_mode`,`update_on`,`created_on`,keywords)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}'); select @@identity  ; ", model.site_id, model.page_id, model.area_id, model.home_title, model.content_title, model.content_html, model.home_image, model.content_default, model.content_status, model.link_url, model.link_mode, CommonFunction.DateTimeToString(model.update_on), CommonFunction.DateTimeToString(model.created_on), model.keywords);
         DataTable dt = _access.getDataTable(sb.ToString());
         model.content_id = Convert.ToInt32(dt.Rows[0][0].ToString());
         return model.content_id;
     }
     catch (Exception ex)
     {
         throw new Exception("WebContentType6Dao.Insert-->" + ex.Message + sb.ToString(), ex); 
     }
    
 }

        public string AddOrUpdate(Model.Query.EventPromoGiftQuery epQuery)
        {
            StringBuilder sql = new StringBuilder();
            epQuery.Replace4MySQL();
            try
            {
                if (epQuery.gift_id == 0)
                {
                    sql.Append("insert into event_promo_gift(gift_name,product_id,product_num,bonus,bonus_multiple,welfare,welfare_multiple");
                    sql.Append(",gift_type,quantity,amount,event_id,create_user,create_time,modify_user,modify_time)");
                    sql.AppendFormat(" values ('{0}','{1}','{2}','{3}','{4}',", epQuery.gift_name, epQuery.product_id, epQuery.product_num, epQuery.bonus, epQuery.bonus_multiple);
                    sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", epQuery.welfare, epQuery.welfare_multiple, epQuery.gift_type, epQuery.quantity, epQuery.amount);
                    sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}');", epQuery.event_id, epQuery.create_user, Common.CommonFunction.DateTimeToString(epQuery.create_time), epQuery.modify_user, Common.CommonFunction.DateTimeToString(epQuery.modify_time));

                }
                else
                {
                    sql.AppendFormat(" update event_promo_gift set gift_name='{0}',product_id='{1}',product_num='{2}',bonus='{3}',bonus_multiple='{4}'", epQuery.gift_name, epQuery.product_id, epQuery.product_num, epQuery.bonus, epQuery.bonus_multiple);
                    sql.AppendFormat(" ,welfare='{0}',welfare_multiple='{1}',gift_type='{2}',quantity='{3}',amount='{4}'", epQuery.welfare, epQuery.welfare_multiple, epQuery.gift_type, epQuery.quantity, epQuery.amount);
                    sql.AppendFormat(" ,event_id='{0}',modify_user='{1}',modify_time='{2}' where gift_id='{3}';", epQuery.event_id, epQuery.modify_user, Common.CommonFunction.DateTimeToString(epQuery.modify_time), epQuery.gift_id);

                }
                return sql.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("EventPromoGiftDao-->AddOrUpdate-->" + ex.Message + sql.ToString(), ex);
            }
        }

        public List<Model.Query.WebContentType3Query> QueryAll(Model.Query.WebContentType3Query query, out int totalCount)
        {
            query.Replace4MySQL();
            StringBuilder str = new StringBuilder();
            StringBuilder strcondition = new StringBuilder();
            try
            {
                str.Append(@"select w3.* , t_site.parameterName as site_name,t_page.parameterName as page_name,t_area.parameterName as area_name,v_b.brand_name as brand_name from web_content_type3 w3 ");
                str.AppendFormat(" left join t_parametersrc t_site on t_site.parameterType='site_id' and t_site.parameterCode=w3.site_id");
                str.AppendFormat(" left join t_parametersrc t_page on t_page.parameterType='page_id' and t_page.parameterCode=w3.page_id and t_page.topValue=t_site.rowid");
                str.AppendFormat(" left join t_parametersrc t_area on t_area.parameterType='area_id' and t_area.parameterCode=w3.area_id and t_area.topValue=t_page.rowid");
                str.AppendFormat(" left join vendor_brand v_b on v_b.brand_id=w3.brand_id");
                strcondition.AppendFormat(" where 1=1 order by content_id desc ");
                totalCount = 0;

                if (query.IsPage)
                {
                    DataTable dt = _access.getDataTable(@"SELECT count(*) as totalcount from web_content_type3 where 1=1 ");
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(dt.Rows[0]["totalcount"]);
                    }
                    strcondition.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
                }
                string strs = str.ToString() + strcondition.ToString();
                return _access.getDataTableForObj<WebContentType3Query>(strs.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("WebContentType3Dao.QueryAll-->" + ex.Message + str.ToString(), ex); ;
            }
           
        }

        public int Insert(Model.WebContentType5 model)
        {
            model.Replace4MySQL();

            StringBuilder sb = new StringBuilder();
            try
            {
                if (model.content_status == 1)//啟用
                {
                    WebContentTypeSetupDao _setDao = new WebContentTypeSetupDao(_connStr);
                    WebContentTypeSetup smodel = new WebContentTypeSetup();
                    smodel.site_id = model.site_id;
                    smodel.page_id = model.page_id;
                    smodel.area_id = model.area_id;
                    smodel.web_content_type = "web_content_type5";
                    _setDao.UpdateLimitStatus(smodel);////當前已啟用的個數超過5筆時，使最舊的不啟用，
                }

                sb.AppendFormat(@"INSERT INTO web_content_type5(`site_id`,`page_id`,`area_id`,`type_id`,`brand_id`,`content_title`,`content_image`,`content_default`,`content_status`,`link_url`,`link_mode`,`update_on`,`created_on`) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}') ", model.site_id, model.page_id, model.area_id, model.type_id, model.brand_id, model.content_title, model.content_image, model.content_default, model.content_status, model.link_url, model.link_mode, CommonFunction.DateTimeToString(model.update_on), CommonFunction.DateTimeToString(model.created_on));
                return _access.execCommand(sb.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("WebContentType5Dao.Insert-->" + ex.Message + sb.ToString(), ex); 
            }
           
        }

        public string AddOrUpdate(Model.EventPromoCategory epQuery)
        {
            epQuery.Replace4MySQL();
            StringBuilder sql = new StringBuilder();
            try
            {
                if (epQuery.row_id == 0)
                {
                    sql.Append("insert into event_promo_category(event_type,site_id,category_id,event_id");
                    sql.Append(",event_status,create_user,create_time,modify_user,modify_time,event_start,event_end)");
                    sql.AppendFormat(" values ('{0}','{1}','{2}','{3}','{4}',", epQuery.event_type, epQuery.site_id, epQuery.category_id, epQuery.event_id, epQuery.event_status);
                    sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", epQuery.create_user, Common.CommonFunction.DateTimeToString(epQuery.create_time), epQuery.modify_user, Common.CommonFunction.DateTimeToString(epQuery.modify_time), Common.CommonFunction.DateTimeToString(epQuery.event_start));
                    sql.AppendFormat("'{0}');", Common.CommonFunction.DateTimeToString(epQuery.event_end));

                }
                else
                {
                    sql.AppendFormat(" update event_promo_category set event_type='{0}',site_id='{1}',category_id='{2}',event_id='{3}',event_status='{4}'", epQuery.event_type, epQuery.site_id, epQuery.category_id, epQuery.event_id, epQuery.event_status);
                    sql.AppendFormat(" ,modify_user='{0}',modify_time='{1}',event_start='{2}'", epQuery.modify_user, Common.CommonFunction.DateTimeToString(epQuery.modify_time), Common.CommonFunction.DateTimeToString(epQuery.event_start));
                    sql.AppendFormat(" ,event_end='{0}' where row_id='{1}';", Common.CommonFunction.DateTimeToString(epQuery.event_end), epQuery.row_id);

                }
                return sql.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("EventPromoCategoryDao-->AddOrUpdate" + ex.Message, ex);
            }
        }

 public int Insert(Model.WebContentType1 model)
 {
     model.Replace4MySQL();
     StringBuilder sb = new StringBuilder();
     try
     {
             if (model.content_status == 1)//啟用
             {
                 WebContentTypeSetupDao _setDao = new WebContentTypeSetupDao(_connStr);
                 WebContentTypeSetup smodel = new WebContentTypeSetup();
                 smodel.site_id = model.site_id;
                 smodel.page_id = model.page_id;
                 smodel.area_id = model.area_id;
                 smodel.web_content_type = "web_content_type1";
                 _setDao.UpdateLimitStatus(smodel);////當前已啟用的個數超過5筆時，使最舊的不啟用，
             }
           
             sb.AppendFormat(@"insert into web_content_type1(site_id,page_id,area_id,type_id,content_title,content_image,content_default,content_status,link_url,link_page,link_mode,update_on,created_on) 
         values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", model.site_id, model.page_id, model.area_id, model.type_id, model.content_title, model.content_image, model.content_default, model.content_status, model.link_url, model.link_page, model.link_mode, CommonFunction.DateTimeToString(model.update_on), CommonFunction.DateTimeToString(model.created_on));
             return _access.execCommand(sb.ToString());
     }
     catch (Exception ex)
     {
         throw new Exception("WebContentType1Dao.Insert-->" + ex.Message + sb.ToString(), ex);
     }
    
 }

 public string SaveNoPrid(Model.ProductMigrationMap pMap)
 {
     pMap.Replace4MySQL();
     StringBuilder stb = new StringBuilder("insert into product_migration_map(`product_id`,`temp_id`) values({0},");
     stb.AppendFormat("'{0}')",pMap.temp_id);
     return stb.ToString();
 }

 public int Save(Model.Vgroup vg)
 {
    vg.Replace4MySQL();
     if (vg.rowid == 0)
     {
         if (_access.execCommand(string.Format("select rowid from t_fgroup2 where (groupname='{0}' or groupcode='{1}')", vg.groupName, vg.groupCode)) > 0)
         {
             return -1;//群組名稱或群組編碼已存在
         }
         else
         {
             _access.execCommand(string.Format("insert into t_fgroup2(groupname,groupcode,remark,kuser,kdate) values('{0}','{1}','{2}','{3}',now())", vg.groupName, vg.groupCode, vg.remark,vg.kuser));
             return 1;//新增成功
         }
     }
     else
     {
         if (_access.execCommand(string.Format("select rowid from t_fgroup2 where (groupname='{1}' or groupcode='{2}') and rowid <> {0}", vg.rowid, vg.groupName, vg.groupCode)) > 0)
         {
             return -1;//群組名稱或群組編碼已存在
         }
         else
         {
             _access.execCommand(string.Format("update t_fgroup2 set groupname='{1}',groupcode='{2}',remark='{3}' where rowid={0}",vg.rowid, vg.groupName, vg.groupCode, vg.remark));
             return 2;//修改成功
         }
     }
 }

        public int Update(Model.WebContentType1 model)
        {
            model.Replace4MySQL(); 
            StringBuilder sb = new StringBuilder();
            try
            {
                         WebContentType1 oldModel = GetModel(model);
                        if (model.content_status == 1 && oldModel.content_status != 1)//啟用
                        {
                            WebContentTypeSetupDao _setDao = new WebContentTypeSetupDao(_connStr);
                            WebContentTypeSetup smodel = new WebContentTypeSetup();
                            smodel.site_id = model.site_id;
                            smodel.page_id = model.page_id;
                            smodel.area_id = model.area_id;
                            smodel.web_content_type = "web_content_type1";
                            _setDao.UpdateLimitStatus(smodel);////當前已啟用的個數超過5筆時，使最舊的不啟用，
                        }
                       
                        sb.AppendFormat(@"update web_content_type1  set site_id='{0}',page_id='{1}',area_id='{2}',type_id='{3}',content_title='{4}',content_image='{5}',`content_default`='{6}',content_status='{7}',link_url='{8}',link_page='{9}',link_mode='{10}',update_on='{11}' where content_id={12}",
                            model.site_id, model.page_id, model.area_id, model.type_id, model.content_title, model.content_image, model.content_default, model.content_status, model.link_url, model.link_page, model.link_mode, CommonFunction.DateTimeToString(model.update_on), model.content_id);
                        return _access.execCommand(sb.ToString());
            }
            catch (Exception ex)
            {

                throw new Exception("WebContentType1Dao.Update-->" + ex.Message + sb.ToString(), ex);
            }
          
        }

        /// <summary>
        /// 獲取所有顯示的但不包括促銷的所有類別
        /// add by shuangshuang0420j 20141023 15:21
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<Model.ProductCategory> GetProductCate(Model.ProductCategory query)
        {
            query.Replace4MySQL();
            StringBuilder sql = new StringBuilder("select category_id,category_name,category_father_id from product_category where  category_display <> 0 ");
            sql.Append(" and category_id <> (select parameterProperty from t_parametersrc where parametercode='CXXM')");

            return _access.getDataTableForObj<Model.ProductCategory>(sql.ToString());
        }