internal object GetTptShipmentCountRedis(ReportTvModels olap, Microsoft.Extensions.Options.IOptions <AppSettings> settings)
{
string connectstring = settings.Value.redisMap.Host + ":" + settings.Value.redisMap.Post;
var redisManger = new RedisManagerPool(connectstring); //Redis的连接字符串
var redis = redisManger.GetClient(); //获取一个Redis Client
var remainingTodos = redis.GetValue("shipmentcount");
return(remainingTodos);
}
/// <summary>
/// 大屏展示 地图坐标 弃用 MapOrder
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
internal DataTable GetTvMap(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SX")
{
olap.sql = sqlMapXy;
olap.connect = settings.Value.SqlConnectionString.Test193;//OnlineTjSX;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
/// <summary>
/// 运单总数
/// </summary>
/// <returns></returns>
internal DataTable GetTptShipmentCount(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SX")
{
olap.sql = oraTptShipmentCount;
olap.connect = settings.Value.OraConnectionString.TestScmpOdbc;//OnlineTjSX;
}
db = new DbUtility(olap, DbProvider.ORACLE);
return(db.ExecuteReader(olap));
}
/// <summary>
/// 返回结果拼接
/// </summary>
/// <param name="restring">返回数据Data</param>
/// <param name="olap">前台传递参数</param>
/// <param name="mess">返回查询结果消息成功消息,错误消息</param>
/// <param name="n">返回查询结果状态</param>
/// <param name="count">返回数据结果数量</param>
/// <returns></returns>
public string GetResultMessage(string restring, ReportTvModels olap, string mess, int n, int count)
{
olap.pageSize = olap.pageSize != 0 ? olap.pageSize : 0;
olap.pageIndex = olap.pageIndex != 0 ? olap.pageIndex :0;
restring = "{ \"data\":" + restring + ","; //成功的得到的数据
restring += "\"message\":\"" + mess + "\","; //成功消息,错误消息
restring += "\"resultCode\":" + n + ","; //返回成功与否 成功给正整数 1,2,3 这样 错误给负数 -1 -2 -3
restring += "\"pageSize\":" + olap.pageSize + ","; //每页条数
restring += "\"pageIndex\":" + olap.pageIndex + ","; //当前页
restring += "\"counts\":" + count + "}"; //总记录数 没有翻页默认给0
return(restring);
}
/// <summary>
/// 执行查询sql语句
/// </summary>
/// <param name="olap">查询sql语句</param>
/// <returns>返回一个表</returns>
public DataTable ExecuteReader(ReportTvModels olap)
{
DataTable dt = new DataTable();
using (cmd = DbFactory.CreateDbCommand(olap.sql, CreatConnection()))
{
using (dr = cmd.ExecuteReader())
{
dt.Load(dr);
}
}
conn.Close();
return(dt);
}
/// <summary>
/// 大屏展示 预约信息
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
internal DataTable getresultTable(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SCNJ")
{
olap.sql = sqlReserveDt;
olap.connect = settings.Value.SqlConnectionString.Test127sc;
}
else if (olap.name == "SX")
{
olap.sql = sqlReserveDt;
olap.connect = settings.Value.SqlConnectionString.Test127sx;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
/// <summary>
/// 大屏展示 储位信息
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
public DataTable getresult(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SCNJ")
{
olap.sql = string.Format(sqlStorageDetN, olap.name);
olap.connect = settings.Value.SqlConnectionString.Test127sc;
}
if (olap.name == "SX")
{
olap.sql = sqlStorageDet;
olap.sql += string.IsNullOrWhiteSpace(olap.warehouse) ? "" : string.Format(@" and warehouse='{0}'", olap.warehouse);
olap.connect = settings.Value.SqlConnectionString.Test127sx;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
public HttpResponseMessage GetReportVacancy([FromBody] VacancyDTO req)
{
ReportTvModels olap = _mapper.Map <ReportTvModels>(req);
string restring = string.Empty;
int count = 0;
if (string.IsNullOrWhiteSpace(olap.name))
{ //当条件为空 返回-2
restring = Remess.GetResultMessage("[]", olap, "条件Name不能为空！", -2, 0);
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
try
{
restring = JsonConvert.SerializeObject(dbGet.GetVacancy(olap, _settings), new DataTableConverter());
//count = Convert.ToInt32(dbGet.GetTptShipmentCount(olap, _settings).Rows[0][0].ToString());
// restring = restring.Replace("\"[{\\", "[{").Replace("\\\"", "\"").Replace("}]\"", "}]").Replace(":\"[[", ":[[").Replace("]]\"}", "]]}");
}
catch (Exception ex)
{
//当查询语句报错 返回-3
restring = Remess.GetResultMessage("[]", olap, "defeated", -3, count);
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
//查询语句正确
if (restring.Length > 2)
{
//参数正确有回传结果 1
restring = Remess.GetResultMessage(restring, olap, "success", 1, count);
}
else
{
//参数正确 回传结果为空 -1
restring = Remess.GetResultMessage(restring, olap, "defeated", -1, count);
}
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
public HttpResponseMessage GetReport([FromBody] GetReportDTO req)
{
ReportTvModels olap = _mapper.Map <ReportTvModels>(req);
string restring = string.Empty;
if (string.IsNullOrWhiteSpace(olap.name))
{ //当条件为空 返回-2
restring = Remess.GetResultMessage("[]", olap, "条件Name不能为空！", -2, 0);
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
try
{
//restring =Json.DataTableToJsonWithJavaScriptSerializer(dbGet.getresult(olap.name, ""));
restring = JsonConvert.SerializeObject(dbGet.getresult(olap, _settings), new DataTableConverter());
restring = restring.Replace("\\\"", "\"").Replace("Value\":\"{", "Value\":[{").Replace("]\"},", "]},{").Replace("},{{", "},{").Replace("]\"}]", "]}]");
}
catch (Exception)
{
//当查询语句报错 返回-3
restring = Remess.GetResultMessage("[]", olap, "defeated", -3, 0);
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
//查询语句正确
if (restring.Length > 2)
{
//参数正确有回传结果 1
restring = Remess.GetResultMessage(restring, olap, "success", 1, 0);
}
else
{
//参数正确 回传结果为空 -1
restring = Remess.GetResultMessage(restring, olap, "defeated", -1, 0);
}
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
public HttpResponseMessage GetReportTvMapRedis([FromBody] GetReportTableDTO req)
{
ReportTvModels olap = _mapper.Map <ReportTvModels>(req);
string restring = string.Empty;
if (string.IsNullOrWhiteSpace(olap.name))
{ //当条件为空 返回-2
restring = Remess.GetResultMessage("[]", olap, "条件Name不能为空！", -2, 0);
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
try
{
restring = JsonConvert.SerializeObject(dbRedis.GetTvMapRedis(olap, _settings), new DataTableConverter());
count = Convert.ToInt32(dbRedis.GetTptShipmentCountRedis(olap, _settings));
restring = restring.Replace('\'', '\"').Replace("\"{\"", "{\"").Replace("\"}\"", "}").Replace("\"[[", "[[").Replace("\"None\"", "\"\"").Replace("None", "\"\"").Replace("}\",{", "},{");
}
catch (Exception)
{
//当查询语句报错 返回-3
restring = Remess.GetResultMessage("[]", olap, "defeated", -3, count);
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
//查询语句正确
if (restring.Length > 2)
{
//参数正确有回传结果 1
restring = Remess.GetResultMessage(restring, olap, "success", 1, count);
}
else
{
//参数正确 回传结果为空 -1
restring = Remess.GetResultMessage(restring, olap, "defeated", -1, 0);
}
result.Content = new StringContent(restring, Encoding.GetEncoding("UTF-8"), "application/json");
return(result);
}
/// <summary>
/// 大屏展示 储位明细信息
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
internal DataTable getresultDet(ReportTvModels olap, IOptions <AppSettings> settings)
{
if (olap.name == "SCNJ")
{
olap.sql =
sqlStorageDetl += string.IsNullOrWhiteSpace(olap.name + olap.location) ? "" :
string.Format(@" and Location='{0}'", olap.name + olap.location);
olap.connect = settings.Value.SqlConnectionString.Test127sc;
}
else if (olap.name == "SX")
{
olap.sql = sqlStorageDetl2;
olap.sql += string.IsNullOrWhiteSpace(olap.warehouse) ? "" : string.Format(@" and warehouse ='{0}'", olap.warehouse);
olap.sql += string.IsNullOrWhiteSpace(olap.positions) ? "" : string.Format(@" and positions ='{0}'", olap.positions);
olap.sql += string.IsNullOrWhiteSpace(olap.location) ? "" : (olap.location == "down" ? @" and dan <=20" : " and dan >20");
olap.connect = settings.Value.SqlConnectionString.Test127sx;
}
db = new DbUtility(olap);
return(db.ExecuteReader(olap));
}
/// <summary>
/// 地图坐标和看货狗编号
/// </summary>
/// <param name="olap"></param>
/// <param name="settings"></param>
/// <returns></returns>
internal object GetTvMapRedis(ReportTvModels olap, Microsoft.Extensions.Options.IOptions <AppSettings> settings)
{
string connectstring = settings.Value.redisMap.Host + ":" + settings.Value.redisMap.Post;
var redisManger = new RedisManagerPool(connectstring); //Redis的连接字符串
var redis = redisManger.GetClient(); //获取一个Redis Client
//redisTodos.Store(newTodo); //把newTodo实例保存到数据库中 增
//ResponseMapDTO saveTodo = redisTodos.GetById(newTodo.ROWNO); //根据Id查询 查
//"Saved Todo: {0}".Print(saveTodo.Dump());
//saveTodo.Done = true; //改
//redisTodos.Store(saveTodo);
//var updateTodo = redisTodos.GetById(newTodo.ROWNO); //查
// "Updated Todo: {0}".Print(updateTodo.Dump());
/* redisTodos.DeleteById(newTodo.ROWNO); */ //删除
//var remainingTodos = redis.GetRangeFromSortedSet(settings.Value.redisMap.key, (olap.pageIndex - 1) * olap.pageSize, (olap.pageIndex * olap.pageSize)-1);
// "No more Todos:".Print(remainingTodos.Dump());
var remainingTodos = redis.GetRangeFromSortedList(settings.Value.redisMap.key, (olap.pageIndex - 1) * olap.pageSize, (olap.pageIndex * olap.pageSize));
return(remainingTodos);
}
/// <summary>
/// 创建数据库连接
/// </summary>
public DbUtility(ReportTvModels olap, DbProvider type = DbProvider.SQLSERVER)
{
conn = DbFactory.CreateDbConnection(type, olap.connect);
}
public DataTable GetTvMap(ReportTvModels olap, IOptions <AppSettings> settings) => resultGetTableTV.GetTvMap(olap, settings);
internal DataTable GetTodOrder(ReportTvModels olap, IOptions <AppSettings> settings) => resultGetTableTV.GetTodOrder(olap, settings);
internal DataTable GetTransPortaTion(ReportTvModels olap, IOptions <AppSettings> settings) => resultGetTableTV.GetTransPortaTion(olap, settings);
internal DataTable getresultDet(ReportTvModels olap, IOptions <AppSettings> settings) => resultGetTableTV.getresultDet(olap, settings);
internal DataTable GetTptShipmentCount(ReportTvModels olap, IOptions <AppSettings> settings) => resultGetTableTV.GetTptShipmentCount(olap, settings);
internal object GetVacancy(ReportTvModels olap, IOptions <AppSettings> settings) => resultGetTableTV.GetVacancy(olap, settings);
internal object GetTptShipmentCountRedis(ReportTvModels olap, IOptions <AppSettings> settings) => resultGetRedis.GetTptShipmentCountRedis(olap, settings);
