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