public int ExisTaxId(VipUserGroupQuery query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.AppendFormat("select tax_id from vip_user_group where tax_id='{0}' and group_committe_promotion !='' ", query.tax_id); if (query.group_id != 0) { sql.AppendFormat(" and group_id!='{0}';",query.group_id); } DataTable _dt = _accessMySql.getDataTable(sql.ToString()); if (_dt != null && _dt.Rows.Count > 0) { return 1; } else { return 0; } } catch (Exception ex) { throw new Exception("VipUserGroupDao->ExisTaxId->" + sql.ToString() + ex.Message, ex); } }
public string SaveVipUserGroup(VipUserGroupQuery query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.AppendFormat("set sql_safe_updates=0;update vip_user_group set group_name='{0}',eng_name='{1}',tax_id='{2}',group_code='{3}',group_capital='{4}',check_iden='{5}',", query.group_name, query.eng_name, query.tax_id, query.group_code, query.group_capital,query.check_iden); sql.AppendFormat("group_emp_number='{0}',group_emp_age='{1}',group_emp_gender='{2}',group_benefit_type='{3}',group_benefit_desc='{4}',", query.group_emp_number, query.group_emp_age, query.group_emp_gender, query.group_benefit_type, query.group_benefit_desc); sql.AppendFormat("group_subsidiary='{0}',group_hq_name='{1}',group_hq_code='{2}',", query.group_subsidiary, query.group_hq_name, query.group_hq_code); sql.AppendFormat("group_committe_name='{0}',group_committe_code='{1}',",query.group_committe_name,query.group_committe_code); sql.AppendFormat("group_committe_promotion='{0}',group_committe_desc='{1}',m_user='******',m_date='{3}',file_name='{4}' where group_id='{5}';set sql_safe_updates=1;", query.group_committe_promotion, query.group_committe_desc, query.m_user, CommonFunction.DateTimeToString(DateTime.Now), query.file_name,query.group_id); return sql.ToString(); } catch (Exception ex) { throw new Exception("VipUserGroupDao-->SaveVipUserGroup-->" + ex.Message + sql.ToString(), ex); } }
public List<VipUserGroupQuery> GetVipUserGList(VipUserGroupQuery query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); query.Replace4MySQL(); totalCount = 0; try { sql.Append(" select vug.group_id, vug.group_name, vug.domain , vug.tax_id ,vug.group_code,vug.group_capital ,vug.group_emp_number ,vug.group_emp_age ,vug.group_emp_gender ,vug.group_benefit_type,vug.group_benefit_desc ,vug.group_subsidiary ,vug.group_hq_name ,vug.group_hq_code ,vug.group_committe_name,vug.group_committe_code ,vug.group_committe_promotion,vug.group_committe_desc ,vug.image_name ,vug.gift_bonus ,vug.createdate ,vug.group_category,vug.bonus_rate ,vug.bonus_expire_day ,vug.eng_name ,vug.check_iden ,vug.site_id,vug.group_status ,vug.file_name,vug.k_user ,vug.k_date ,vug.m_user ,vug.m_date,mu1.user_username as 'create_user',mu2.user_username as 'update_user' "); sqlFrom.Append(" from vip_user_group vug "); sqlFrom.Append(" LEFT JOIN manage_user mu1 on vug.k_user=mu1.user_id "); sqlFrom.Append(" LEFT JOIN manage_user mu2 on vug.m_user=mu2.user_id "); sqlWhere.Append(" where 1=1 "); if (query.group_id != 0) { sqlWhere.AppendFormat(" and vug.group_id='{0}' ", query.group_id); } sqlWhere.Append(" and vug.group_committe_promotion !='' "); if (query.tax_name != "") { sqlWhere.AppendFormat(" and (vug.group_name like '%{0}%' or group_code like '%{0}%') ",query.tax_name); } if (query.group_id != 0) { sqlWhere.AppendFormat(" and vug.group_id='{0}' ", query.group_id); } if (query.IsPage) { sqlCount.Append(" select count(vug.group_id) as 'totalCount' " + sqlFrom.ToString() + sqlWhere.ToString()); DataTable _dt = _accessMySql.getDataTable(sqlCount.ToString()); if (_dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0][0]); } } sqlWhere.AppendFormat(" order by vug.group_id desc limit {0},{1}; ", query.Start, query.Limit); sql.Append(sqlFrom.ToString() + sqlWhere.ToString()); return _accessMySql.getDataTableForObj<VipUserGroupQuery>(sql.ToString()); } catch (Exception ex) { throw new Exception("VipUserGroupDao-->GetVipUserGList-->" + sql.ToString() + ex.Message, ex); } }
public string InsertVipUserGroup(VipUserGroupQuery query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.Append("insert into vip_user_group(group_id,group_name,domain,tax_id,group_code, "); sql.Append("group_capital,group_emp_number,group_emp_age,group_emp_gender,group_benefit_type, "); sql.Append("group_benefit_desc,group_subsidiary,group_hq_name,group_hq_code,group_committe_name,"); sql.Append("group_committe_code,group_committe_promotion,"); sql.Append("group_committe_desc, image_name,gift_bonus,createdate,group_category, "); sql.Append("bonus_rate,bonus_expire_day,eng_name,check_iden,site_id,"); sql.Append("group_status,k_user,k_date,m_user,m_date) values( "); sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',",query.group_id,query.group_name,query.domain,query.tax_id,query.group_code); sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',",query.group_capital,query.group_emp_number,query.group_emp_age,query.group_emp_gender,query.group_benefit_type); sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.group_benefit_desc, query.group_subsidiary, query.group_hq_name, query.group_hq_code, query.group_committe_name); sql.AppendFormat("'{0}','{1}',", query.group_committe_code, query.group_committe_promotion); sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',",query.group_committe_desc, query.image_name, query.gift_bonus, query.createdate, query.group_category); sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.bonus_rate, query.bonus_expire_day, query.eng_name, query.check_iden, query.site_id); sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}');", query.group_status, query.k_user,CommonFunction.DateTimeToString(query.k_date), query.m_user,CommonFunction.DateTimeToString(query.m_date)); return sql.ToString(); } catch (Exception ex) { throw new Exception("VipUserGroupDao-->InsertVipUserGroup-->" + ex.Message + sql.ToString(), ex); } }