public JsonResult GetTaxiSummaryInfo(string code)
{
var dataList = "";
var dataList2 = "";
var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code];
var orgName = cm.Organization;
var fleet = cm.MotorcadeName;
var cabVMLicense = cm.CabVMLicense;
//var orgName = "第一服务中心";
//var fleet = "仁强";
JObject jObject = new JObject();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
var date1 = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
var date2 = date1.TryToDate().AddDays(-1).ToString("yyyy-MM-dd");
//最新数据
dataList = _dbMsSql.SqlQueryJson(@"select * from t_taximeter_data where 日期='" + date1 + " 00:00:00' and 车牌号='" + cabVMLicense + "'");
//前一天数据
dataList2 = _dbMsSql.SqlQueryJson(@"select * from t_taximeter_data where 日期='" + date2 + " 00:00:00' and 车牌号='" + cabVMLicense + "'");
if (dataList != null && dataList != "" && dataList.Count() > 2)
{
Regex rgx = new Regex(@"(?i)(?<=\[)(.*)(?=\])"); //中括号[]
string tmp1 = rgx.Match(dataList).Value; //中括号[]
string tmp2 = rgx.Match(dataList2).Value; //中括号[]
JObject jo1 = (JObject)JsonConvert.DeserializeObject(tmp1);
JObject jo2 = (JObject)JsonConvert.DeserializeObject(tmp2);
jObject = jo1;
//计算上涨或者下跌率
string rate = "";
List <string> strValue = new List <string>()
{
"营收", "差次", "线上营收", "线上差次"
};
foreach (var item in strValue)
{
var value1_1 = jo1[item].TryToDecimal();
var value2_1 = jo2[item].TryToDecimal();
if (value1_1 > value2_1 && value2_1 != 0)
{
rate = "↑" + (decimal.Round((value1_1 - value2_1) / value2_1 * 100, 2)) + "%";
}
else if (value1_1 < value2_1 && value2_1 != 0)
{
rate = "↓" + (decimal.Round((value2_1 - value1_1) / value2_1 * 100, 2)) + "%";
}
else
{
rate = "0%";
}
jObject.Add(item + "率", rate);
}
}
}
return(Json(jObject.ObjToString(), JsonRequestBehavior.AllowGet));
}
public string GetTaxiInfo(string code)
{
var dataList = "";
var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code + "K"];
var cabVMLicense = cm.CabVMLicense;
JObject jObject = new JObject();
var value1_1 = "";
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
var date1 = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
//最新数据
dataList = _dbMsSql.SqlQueryJson(@"select * from t_taximeter_data where 日期='" + date1 + " 00:00:00' and 车牌号='" + cabVMLicense + "'");
if (dataList != null && dataList != "" && dataList.Count() > 2)
{
Regex rgx = new Regex(@"(?i)(?<=\[)(.*)(?=\])"); //中括号[]
string tmp1 = rgx.Match(dataList).Value; //中括号[]
JObject jo1 = (JObject)JsonConvert.DeserializeObject(tmp1);
jObject = jo1;
//计算上涨或者下跌率
List <string> strValue = new List <string>()
{
"营收", "差次", "线上营收", "线上差次"
};
value1_1 = jo1[strValue[0]].TryToString();
}
}
return(value1_1);
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())
{
db.SerializerDateFormat = "yyyy-mm/dd";
var jsonStr = db.Queryable <INSERTTEST>().OrderBy("id").Take(1).ToJson();
var jsonStr2 = db.Sqlable().From <INSERTTEST>("t").SelectToJson(" top 1 *");
var jsonStr3 = db.SqlQueryJson("select top 1 * from InsertTest");
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())
{
db.SerializerDateFormat = "yyyy-mm/dd";
var jsonStr = db.Queryable <InsertTest>().OrderBy("id").Take(1).ToJson();
var jsonStr2 = db.Sqlable().From <InsertTest>("t").SelectToPageList <InsertTest>("*", "id", 1, 1);//取第一条
var jsonStr3 = db.SqlQueryJson("select * from InsertTest limit 0,1 ");
}
}
public void Init()
{
Console.WriteLine("启动SerializerDateFormat.Init");
using (SqlSugarClient db = SugarDao.GetInstance())
{
db.SerializerDateFormat = "yyyy-mm/dd";
var jsonStr = db.Queryable <InsertTest>().OrderBy("id").Take(1).ToJson();
var jsonStr2 = db.Sqlable().From <InsertTest>("t").SelectToJson(" top 1 *");
var jsonStr3 = db.SqlQueryJson("select top 1 * from InsertTest");
}
}
public JsonResult GetTaxiSummaryInfo(string fleet, string dateSearch, string code)
{
var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code];
var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code);
if (cm.DepartmenManager == "12")
{
fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeNameRemark, code);
}
var orgName = cm.Organization;
var dataList = "";
var dataList2 = "";
JObject jObject = new JObject();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
var date1 = dateSearch.TryToDate();
var date2 = date1.AddDays(-1).ToString("yyyy-MM-dd");
if (fleet == "0")
{
fleet = fleetAll;
}
else
{
if (cm.DepartmenManager == "12")
{
fleet = _dbMsSql.SqlQuery <string>(@"select Remark from DZ_Organization where status=0 and OrganizationName=@OrganizationName", new { OrganizationName = fleet }).ToList().FirstOrDefault();;
}
fleet = "'" + fleet + "'";
}
if (cm.DepartmenManager == "12")
{
var count = _dbMsSql.SqlQuery <int>(@"select count(上线司机数) from t_taxi_summary where 日期='" + dateSearch + "' and 公司 in (" + fleet + ")").FirstOrDefault();
if (count == 0)
{
//没有数据时,避免除0报错
count = 1;
}
//最新数据
dataList = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数,
isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收,
isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程
from t_taxi_summary where 日期='" + dateSearch + "' and 公司 in (" + fleet + ") ");
//前一天数据
dataList2 = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数,
isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收,
isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程
from t_taxi_summary where 日期='" + date2 + "' and 公司 in (" + fleet + ") ");
}
else
{
var count = _dbMsSql.SqlQuery <int>(@"select count(上线司机数) from t_taxi_summary where 日期='" + dateSearch + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'").FirstOrDefault();
if (count == 0)
{
//没有数据时,避免除0报错
count = 1;
}
//最新数据
dataList = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数,
isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收,
isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程
from t_taxi_summary where 日期='" + dateSearch + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'");
//前一天数据
dataList2 = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数,
isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收,
isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程
from t_taxi_summary where 日期='" + date2 + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'");
}
if (dataList.Count() > 2)
{
Regex rgx = new Regex(@"(?i)(?<=\[)(.*)(?=\])"); //中括号[]
string tmp1 = rgx.Match(dataList).Value; //中括号[]
string tmp2 = rgx.Match(dataList2).Value; //中括号[]
JObject jo1 = (JObject)JsonConvert.DeserializeObject(tmp1);
JObject jo2 = (JObject)JsonConvert.DeserializeObject(tmp2);
jObject = jo1;
//计算上涨或者下跌率
string rate = "";
List <string> strValue = new List <string>()
{
"总营收", "总差次", "营运车辆总数", "总线上营收", "总线上差次",
"车均营收", "车均差次", "车均线上营收", "车均线上差次", "车均行驶里程", "车均营运里程", "车均空驶里程"
};
foreach (var item in strValue)
{
var value1_1 = jo1[item].TryToDecimal();
var value2_1 = jo2[item].TryToDecimal();
if (value1_1 > value2_1 && value2_1 != 0)
{
rate = "↑" + (decimal.Round((value1_1 - value2_1) / value2_1 * 100, 2)) + "%";
}
else if (value1_1 < value2_1 && value2_1 != 0)
{
rate = "↓" + (decimal.Round((value2_1 - value1_1) / value2_1 * 100, 2)) + "%";
}
else
{
rate = "0%";
}
jObject.Add(item + "率", rate);
}
}
}
return(Json(jObject.ObjToString(), JsonRequestBehavior.AllowGet));
}
public string SqlQueryJson(string sql, object whereObj = null)
{
return(_db.SqlQueryJson(sql, whereObj));
}
public JsonResult GetTaxiSummaryInfo(string fleet, string code)
{
var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code + "K"];
var orgName = cm.Organization;
var fleetAll = getSqlInValue(cm.MotorcadeName, code);
if (cm.DepartmenManager == "12")
{
fleetAll = getSqlInValue(cm.MotorcadeNameRemark, code);
}
var dataList = "";
var dataList2 = "";
//var orgName = "第一服务中心";
//fleet = "仁强";
JObject jObject = new JObject();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
#region 查询车辆信息
var date = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");//昨天
//date = "2021-05-12";
//dataList = _dbMsSql.SqlQueryJson(@"select * from t_taxi_summary where 日期=@Date",new { Date = date });
var date1 = date.TryToDate();
var date2 = date1.AddDays(-1).ToString("yyyy-MM-dd");//前天
//date1 = date.TryToDate();
//date2 = date1.AddDays(-1).ToString("yyyy-MM-dd");//前天
if (fleet == "0")
{
fleet = fleetAll;
}
else
{
if (cm.DepartmenManager == "12")
{
fleet = _dbMsSql.SqlQuery <string>(@"select Remark from DZ_Organization where status=0 and OrganizationName=@OrganizationName", new { OrganizationName = fleet }).ToList().FirstOrDefault();;
}
fleet = "'" + fleet + "'";
}
if (cm.DepartmenManager == "12")
{
var count = _dbMsSql.SqlQuery <int>(@"select count(上线司机数) from t_taxi_summary where 日期='" + date + "' and 公司 in (" + fleet + ")").FirstOrDefault();
if (count == 0)
{
//没有数据时,避免除0报错或者用nullif(0,0)函数
count = 1;
}
//最新数据
dataList = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),上线司机数)),0) as 上线司机数,
isnull(Sum(convert(decimal(18,2),上线车辆数)),0) as 上线车辆数,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均在线时长)),0)/" + count + @") as 车均在线时长
from t_taxi_summary where 日期='" + date + "' and 公司 in (" + fleet + ")");
//前一天数据
dataList2 = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),上线司机数)),0) as 上线司机数,
isnull(Sum(convert(decimal(18,2),上线车辆数)),0) as 上线车辆数,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均在线时长)),0)/" + count + @") as 车均在线时长
from t_taxi_summary where 日期='" + date2 + "' and 公司 in (" + fleet + ")");
LogHelper.WriteLog(string.Format("级别：{0},查询日期:{1},上线司机总数：{2},查询日期最新数据：{3}", cm.DepartmenManager, date, count, dataList));
}
else
{
var count = _dbMsSql.SqlQuery <int>(@"select count(上线司机数) from t_taxi_summary where 日期='" + date + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'").FirstOrDefault();
if (count == 0)
{
//没有数据时,避免除0报错
count = 1;
}
//最新数据
dataList = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),上线司机数)),0) as 上线司机数,
isnull(Sum(convert(decimal(18,2),上线车辆数)),0) as 上线车辆数,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均在线时长)),0)/" + count + @") as 车均在线时长
from t_taxi_summary where 日期='" + date + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'");
//前一天数据
dataList2 = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),上线司机数)),0) as 上线司机数,
isnull(Sum(convert(decimal(18,2),上线车辆数)),0) as 上线车辆数,
isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次,
convert(decimal(18,2),
isnull(Sum(convert(decimal(18,2),车均在线时长)),0)/" + count + @") as 车均在线时长
from t_taxi_summary where 日期='" + date2 + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'");
LogHelper.WriteLog(string.Format("级别：{0},查询日期:{1},上线司机总数：{2},查询日期最新数据：{3}", cm.DepartmenManager, date, count, dataList));
}
if (dataList.Count() > 2)
{
Regex rgx = new Regex(@"(?i)(?<=\[)(.*)(?=\])"); //中括号[]
string tmp1 = rgx.Match(dataList).Value; //中括号[]
string tmp2 = rgx.Match(dataList2).Value; //中括号[]
JObject jo1 = (JObject)JsonConvert.DeserializeObject(tmp1);
JObject jo2 = (JObject)JsonConvert.DeserializeObject(tmp2);
jObject = jo1;
//计算上涨或者下跌率
string rate = "";
List <string> strValue = new List <string>()
{
"上线司机数", "上线车辆数", "总差次", "车均营收", "车均差次", "车均在线时长"
};
foreach (var item in strValue)
{
var value1_1 = jo1[item].TryToDecimal();
var value2_1 = jo2[item].TryToDecimal();
if (value1_1 > value2_1 && value2_1 != 0)
{
rate = "↑" + (decimal.Round((value1_1 - value2_1) / value2_1 * 100, 2)) + "%";
}
else if (value1_1 < value2_1 && value2_1 != 0)
{
rate = "↓" + (decimal.Round((value2_1 - value1_1) / value2_1 * 100, 2)) + "%";
}
else
{
rate = "0%";
}
jObject.Add(item + "率", rate);
}
}
#endregion
}
return(Json(jObject.ObjToString(), JsonRequestBehavior.AllowGet));
}