public string SaveVipUserGroup(VipUserGroupQuery query,DataTable _dt)
{
string json = "{success:'true'}";
ArrayList arrList = new ArrayList();
VipUser vQuery = new VipUser();
BtobEmp bQuery=new BtobEmp ();
int totalCount=0;
VipUserGroupQuery oldQuery = new VipUserGroupQuery();
oldQuery = _vipUserGroupDao.GetVipUserGList(query, out totalCount).FirstOrDefault();
try
{
if (query.file_name == "" && oldQuery != null)
{
query.file_name = oldQuery.file_name;
}
query.m_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
query.group_committe_promotion = query.group_committe_promotion + query.group_committe_other;
arrList.Add(_vipUserGroupDao.SaveVipUserGroup(query));
bQuery.group_id = query.group_id.ToString();
//excel檔匯入到btob_emp表
#region
if (_dt != null)
{
for (int i = 0; i < _dt.Rows.Count; i++)
{
if (_dt.Rows[i][0].ToString() != "")
{
bQuery.emp_id = _dt.Rows[i][0].ToString();
bQuery.id = Convert.ToUInt32(_vipUserGroupDao.GetIdFromBtob(bQuery));
if (bQuery.id != 0)
{
arrList.Add(_vipUserGroupDao.GetUpdateBtobSql(bQuery));
}
else
{
//emp_id是唯一索引，員工編號在整張表中是唯一的
if (!_vipUserGroupDao.IsEmpIdExist(bQuery))
{
arrList.Add(_vipUserGroupDao.GetInsertBtobSql(bQuery));
}
}
}
}
}
else
{
}
#endregion
if (arrList.Count > 0)
{
if (_vipUserGroupDao.execSql(arrList))
{
json = "{success:'true'}";
}
else
{
json = "{success:'false'}";
}
}
return json;
}
catch (Exception ex)
{
throw new Exception("VipUserGroupDao-->SaveVipUserGroup-->" + ex.Message, ex);
}
}
public bool IsEmpIdExist(BtobEmp query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select emp_id from btob_emp where emp_id='{0}';", query.emp_id);
DataTable _dt = _accessMySql.getDataTable(sql.ToString());
if (_dt.Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception("VipUserGroupDao->IsEmpIdExist->" + sql.ToString() + ex.Message, ex);
}
}
public string GetUpdateBtobSql(BtobEmp query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("update btob_emp set update_date='{0}' where id='{1}';", CommonFunction.GetPHPTime(),query.id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("VipUserGroupDao->GetUpdateBtobSql->" + sql.ToString() + ex.Message, ex);
}
}
public int GetIdFromBtob(BtobEmp query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select id from btob_emp where emp_id='{0}' and group_id='{1}';",query.emp_id,query.group_id);
DataTable _dt = _accessMySql.getDataTable(sql.ToString());
if (_dt.Rows.Count > 0)
{
return Convert.ToInt32(_dt.Rows[0][0]);
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw new Exception("VipUserGroupDao->GetIdFromBtob->" + sql.ToString() + ex.Message, ex);
}
}
public string GetInsertBtobSql(BtobEmp query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("insert into btob_emp (`emp_id`, `group_id`, `status`, `create_date`, `update_date`) values ('{0}','{1}','{2}','{3}','{4}');", query.emp_id, query.group_id, query.status, CommonFunction.GetPHPTime(), CommonFunction.GetPHPTime());
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("VipUserGroupDao->GetInsertBtobSql->" + sql.ToString() + ex.Message, ex);
}
}