public string GetOpenIdByCode(VisitorsSearchModel v)
{
if (v.js_code != "" && v.js_code != null)
{
//string url = "https://api.weixin.qq.com/sns/jscode2session?appid=" + AppId + "&secret=" + AppSecret + "&js_code=" + v.Code + "&grant_type=authorization_code";
string url = "https://api.weixin.qq.com/sns/jscode2session";
string p = "";
p = "appid=" + v.appid + "&secret=" + v.secret + "&js_code=" + v.js_code + "&grant_type=" + v.grant_type;
string result = HttpHelper.HttpPost(url, p);
return(ConvertHelper.resultJson(0, result));
}
return(ConvertHelper.resultJson(0, "系统出错了"));
}
/// <summary>
/// flag (time:按照时间查询 top:按照次数查询)
/// </summary>
public string GetListByTimeAndOpenId(VisitorsSearchModel v)
{
string sql = "";
SqlParameter[] pms = null;
string timeStart = v.StartDate + " 00:00:01";
string timeEnd = v.EndDate + " 23:59:59";
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@OpenId", SqlDbType.NVarChar)
{
Value = (v.OpenId)
}
};
sql += " select * from XXCLOUDVisitor.dbo.Table_Visitors V";
sql += " left join XXCLOUD.dbo.T_StaffInf S";
sql += " on V.SNo = S.SNo";
sql += " where CreateTime between @timeStart and @timeEnd and V.OpenId = @OpenId";
DataTable dt;
try
{
dt = SQLHelper.ExecuteDataTable(sql, CommandType.Text, pms);
return("{\"code\":1,\"count\":" + dt.Rows.Count + ",\"data\":" + ConvertHelper.DataTableToJson(dt) + "}");
}
catch (Exception e)
{
//在webapi中要想抛出异常必须这样抛出，否则只抛出一个默认500的异常
var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError)
{
Content = new StringContent(e.ToString()),
ReasonPhrase = "error"
};
throw new HttpResponseException(resp);
}
}
/// <summary>
/// flag (time:按照时间查询 top:按照次数查询)
/// </summary>
public string getListByTimeAndPhone(VisitorsSearchModel v)
{
//string Flag = "Phone";
//if (number.Length == 11)
//{
// Flag = "Phone";
//}
//else {
// Flag = "SActualNo";
//}
string sql = "";
SqlParameter[] pms = null;
string timeStart = v.StartDate + " 00:00:01";
string timeEnd = v.EndDate + " 23:59:59";
if (v.Type == "in")
{
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@SMPhone", SqlDbType.NVarChar)
{
Value = (v.Phone)
}
};
sql += " select * from XXCLOUDVisitor.dbo.Table_Visitors V";
sql += " left join XXCLOUD.dbo.T_StaffInf S";
sql += " on V.SNo = S.SNo";
sql += " where CreateTime between @timeStart and @timeEnd and SMPhone = @SMPhone";
}
else if (v.Type == "out")
{
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@Phone", SqlDbType.NVarChar)
{
Value = (v.Phone)
}
};
sql += " select * from XXCLOUDVisitor.dbo.Table_Visitors V";
sql += " left join XXCLOUD.dbo.T_StaffInf S";
sql += " on V.SNo = S.SNo";
sql += " where CreateTime between @timeStart and @timeEnd and Phone = @Phone";
}
//new SqlParameter("@Phone",SqlDbType.NVarChar){Value = (v.Phone)},
//string sql_str =
//sql = "select * from XXCLOUDVisitor.dbo.Table_Visitors where CreateTime between @timeStart and @timeEnd and Phone = @Phone";
DataTable dt;
try
{
dt = SQLHelper.ExecuteDataTable(sql, CommandType.Text, pms);
return("{\"code\":1,\"count\":" + dt.Rows.Count + ",\"data\":" + ConvertHelper.DataTableToJson(dt) + "}");
}
catch (Exception e)
{
//在webapi中要想抛出异常必须这样抛出，否则只抛出一个默认500的异常
var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError)
{
Content = new StringContent(e.ToString()),
ReasonPhrase = "error"
};
throw new HttpResponseException(resp);
}
}
/// <summary>
///
/// </summary>
public string GetListByNameOrPhone4Checked(VisitorsSearchModel v)
{
string sql = "";
SqlParameter[] pms = null;
string timeStart = v.StartDate;
string timeEnd = v.EndDate;
if (v.Type == "check")
{
if (!string.IsNullOrEmpty(v.Name) && string.IsNullOrEmpty(v.Phone))
{
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@Name", SqlDbType.NVarChar)
{
Value = (v.Name)
},
new SqlParameter("@CheckStatus", SqlDbType.NVarChar)
{
Value = (v.CheckStatus)
}
};
sql += " select * from XXCLOUDVisitor.dbo.Table_Visitors V";
sql += " left join XXCLOUD.dbo.T_StaffInf S";
sql += " on V.SNo = S.SNo";
sql += " where Name = @Name and CheckStatus in ('1','-1') and CheckDate between @timeStart and @timeEnd";
}
else if (string.IsNullOrEmpty(v.Name) && !string.IsNullOrEmpty(v.Phone))
{
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@Phone", SqlDbType.NVarChar)
{
Value = (v.Phone)
},
new SqlParameter("@CheckStatus", SqlDbType.NVarChar)
{
Value = (v.CheckStatus)
}
};
sql += " select * from XXCLOUDVisitor.dbo.Table_Visitors V";
sql += " left join XXCLOUD.dbo.T_StaffInf S";
sql += " on V.SNo = S.SNo";
sql += " where Phone = @Phone and CheckStatus in ('1','-1') and CheckDate between @timeStart and @timeEnd";
}
else if (!string.IsNullOrEmpty(v.Name) && !string.IsNullOrEmpty(v.Phone))
{
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@Name", SqlDbType.NVarChar)
{
Value = (v.Name)
},
new SqlParameter("@Phone", SqlDbType.NVarChar)
{
Value = (v.Phone)
},
new SqlParameter("@CheckStatus", SqlDbType.NVarChar)
{
Value = (v.CheckStatus)
}
};
sql += " select * from XXCLOUDVisitor.dbo.Table_Visitors V";
sql += " left join XXCLOUD.dbo.T_StaffInf S";
sql += " on V.SNo = S.SNo";
sql += " where Name = @Name and Phone = @Phone and CheckStatus in ('1','-1') and CheckDate between @timeStart and @timeEnd";
}
else
{
pms = new SqlParameter[] {
new SqlParameter("@timeStart", SqlDbType.NVarChar)
{
Value = (timeStart)
},
new SqlParameter("@timeEnd", SqlDbType.NVarChar)
{
Value = (timeEnd)
},
new SqlParameter("@CheckStatus", SqlDbType.NVarChar)
{
Value = (v.CheckStatus)
}
};
sql += " select * from XXCLOUDVisitor.dbo.Table_Visitors V";
sql += " left join XXCLOUD.dbo.T_StaffInf S";
sql += " on V.SNo = S.SNo";
sql += " where CheckStatus in ('1','-1') and CheckDate between @timeStart and @timeEnd";
}
}
DataTable dt;
try
{
dt = SQLHelper.ExecuteDataTable(sql, CommandType.Text, pms);
return("{\"code\":1,\"count\":" + dt.Rows.Count + ",\"data\":" + ConvertHelper.DataTableToJson(dt) + "}");
}
catch (Exception e)
{
//在webapi中要想抛出异常必须这样抛出，否则只抛出一个默认500的异常
var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError)
{
Content = new StringContent(e.ToString()),
ReasonPhrase = "error"
};
throw new HttpResponseException(resp);
}
}
public string UpdatePassword(VisitorsSearchModel v)
{
string sql = "";
SqlParameter[] pms = null;
int result;
object obj;
pms = new SqlParameter[] {
new SqlParameter("@Id", SqlDbType.Int)
{
Value = (v.Id)
},
new SqlParameter("@SMPhone", SqlDbType.NVarChar)
{
Value = (v.SMPhone)
},
new SqlParameter("@SInitialPassword", SqlDbType.NVarChar)
{
Value = (v.SInitialPassword)
},
new SqlParameter("@NewPassword", SqlDbType.NVarChar)
{
Value = (v.NewPassword)
}
};
//核实密码
sql = "select count(*) from XXCLOUD.dbo.T_StaffInf where Id=@Id and SMPhone=@SMPhone and SInitialPassword=@SInitialPassword";
obj = SQLHelper.ExecuteScalar(sql, System.Data.CommandType.Text, pms);
if (Convert.ToInt32(obj) == 1)
{
SqlParameter[] pms2 = new SqlParameter[] {
new SqlParameter("@Id", SqlDbType.Int)
{
Value = (v.Id)
},
new SqlParameter("@NewPassword", SqlDbType.NVarChar)
{
Value = (v.NewPassword)
}
};
// 修改密码
string sql2 = "update XXCLOUD.dbo.T_StaffInf set SInitialPassword=@NewPassword where Id=@Id";
try
{
result = SQLHelper.ExecuteNonQuery(sql2, System.Data.CommandType.Text, pms2);
return(ConvertHelper.IntToJson(result));
}
catch (Exception e)
{
//在webapi中要想抛出异常必须这样抛出，否则只抛出一个默认500的异常
var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError)
{
Content = new StringContent(e.ToString()),
ReasonPhrase = "error"
};
throw new HttpResponseException(resp);
}
}
else
{
return("{\"code\":0,\"msg\":" + "旧密码错误" + "}");
}
}
public string Check(VisitorsSearchModel v)
{
string sql = "";
SqlParameter[] pms = null;
DateTime dt = DateTime.Now;
int RandKey = 1000;
//new SqlParameter("@Phone",SqlDbType.NVarChar){Value = (v.Phone)},
//string sql_str =
if (v.CheckStatus == "1")
{
bool is_ec_ok = false;
while (!is_ec_ok)
{
Random ran = new Random();
RandKey = ran.Next(1000, 9999);
string sqlIsExistEC = "select count(*) from XXCLOUDVisitor.dbo.Table_Visitors where EnterCode=@EnterCode";
SqlParameter[] pms4EC = new SqlParameter[] {
new SqlParameter("@EnterCode", SqlDbType.NVarChar)
{
Value = RandKey.ToString()
}
};
object obj = SQLHelper.ExecuteScalar(sqlIsExistEC, System.Data.CommandType.Text, pms4EC);
if (Convert.ToInt32(obj) == 0)
{ //说明此EnterCode已经存在
is_ec_ok = true;
}
}
pms = new SqlParameter[] {
new SqlParameter("@Id", SqlDbType.Int)
{
Value = (v.Id)
},
new SqlParameter("@CheckStatus", SqlDbType.NVarChar)
{
Value = (v.CheckStatus)
},
new SqlParameter("@Checker", SqlDbType.NVarChar)
{
Value = (v.Checker)
},
new SqlParameter("@CheckDate", SqlDbType.NVarChar)
{
Value = dt.ToString("yyyy-MM-dd")
},
new SqlParameter("@EnterCode", SqlDbType.NVarChar)
{
Value = RandKey.ToString()
}
};
sql = "update XXCLOUDVisitor.dbo.Table_Visitors set CheckStatus=@CheckStatus,Checker=@Checker,CheckDate=@CheckDate,EnterCode=@EnterCode where Id=@Id";
}
else
{
pms = new SqlParameter[] {
new SqlParameter("@Id", SqlDbType.Int)
{
Value = (v.Id)
},
new SqlParameter("@CheckStatus", SqlDbType.NVarChar)
{
Value = (v.CheckStatus)
},
new SqlParameter("@Checker", SqlDbType.NVarChar)
{
Value = (v.Checker)
},
new SqlParameter("@CheckDate", SqlDbType.NVarChar)
{
Value = dt.ToString("yyyy-MM-dd")
}
};
sql = "update XXCLOUDVisitor.dbo.Table_Visitors set CheckStatus=@CheckStatus,Checker=@Checker,CheckDate=@CheckDate where Id=@Id";
}
try
{
int result = SQLHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, pms);
return(ConvertHelper.IntToJson(result));
}
catch (Exception e)
{
//在webapi中要想抛出异常必须这样抛出，否则只抛出一个默认500的异常
var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError)
{
Content = new StringContent(e.ToString()),
ReasonPhrase = "error"
};
throw new HttpResponseException(resp);
}
}
