protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(1000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Sqlable().Form("Student", "s")
.Join("School","sc","sc.id","s.sch_id",JoinType.INNER)
.Join("subject","sb","sb.sid","s.id",JoinType.LEFT).SelectToList<Models.Student>("*");
}, m => { }, "sqlSugar SQL语法糖");
//sqlSugar
pt.Execute(i =>
{
db.SqlQuery<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "sqlSugar 纯SQL写法");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.ExecuteStoreQuery<WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "EF4.0+sql05 纯SQL写法");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
private void InsertMyRights(SqlSugarClient _db, Business_EquityAllocation equity, string PushObject)
{
List <Business_MyRights> myRightsList = new List <Business_MyRights>();
string[] userID = null;
var userData = _db.SqlQuery <Business_Personnel_Information>(@"select * from Business_Personnel_Information where DepartmenManager in ('1','2','3')").ToList();
if (PushObject.Contains("-"))
{
//部门
Guid pushObject = Guid.Parse(PushObject);
userData = userData.Where(x => x.OwnedFleet == pushObject).ToList();
}
else if (PushObject.Contains("|") || !PushObject.Contains("-"))
{
//一个或多人司机
List <Business_Personnel_Information> newDataLsit = new List <Business_Personnel_Information>();
userID = PushObject.Split(new char[1] {
'|'
});
foreach (var item in userID)
{
Business_Personnel_Information newData = new Business_Personnel_Information();
newData = userData.Where(x => x.UserID == item).FirstOrDefault();
newDataLsit.Add(newData);
}
userData = newDataLsit;
}
if (equity.ValidType == "周期")
{
getPeriodDate(equity, equity.Period);
}
foreach (var item in userData)
{
Business_MyRights myRights = new Business_MyRights();
myRights.RightsName = equity.RightsName;
myRights.Description = equity.Description;
myRights.Type = equity.Type;
myRights.StartValidity = equity.StartValidity;
myRights.EndValidity = equity.EndValidity;
myRights.UsageTime = null;
myRights.UserVGUID = item.Vguid.ToString();
myRights.Status = "草稿";
myRights.CreatedUser = CurrentUser.GetCurrentUser().LoginName;
myRights.CreatedDate = DateTime.Now;
myRights.ChangeDate = DateTime.Now;
myRights.ChangeUser = CurrentUser.GetCurrentUser().LoginName;
myRights.EquityVGUID = equity.VGUID.ToString();
myRights.VGUID = Guid.NewGuid();
myRightsList.Add(myRights);
}
_db.InsertRange(myRightsList, false);
}
public Business_CleaningCompany GetCleaningCompanyByVguid(string vguid)
{
Business_CleaningCompany cleaningCompany = new Business_CleaningCompany();
using (SqlSugarClient _db = SugarDao_MsSql.GetInstance())
{
cleaningCompany = _db.SqlQuery <Business_CleaningCompany>(@"select * from Business_CleaningCompany where Vguid=@VGUID",
new { VGUID = vguid }).ToList().FirstOrDefault();
}
return(cleaningCompany);
}
public JsonResult GetPointsListBySearch(string fleet, string name, string code)
{
var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code];
var ownedCompany = cm.Organization;
var ownedFleet = cm.MotorcadeName;
var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code);
List <DriverScore> driverScoreList = new List <DriverScore>();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
if (fleet == "0")
{
fleet = fleetAll;
}
else
{
fleet = "'" + fleet + "'";
}
if (cm.DepartmenManager == "12")
{
driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv
left join tb_query_score qs on qs.id_no=vdv.IdCard
where vdv.Organization in (" + fleetAll + @") and vdv.status='1' and qs.score is not null and qs.score != 0
order by DriverId asc").ToList();
}
else
{
driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv
left join tb_query_score qs on qs.id_no=vdv.IdCard
where vdv.Organization=@OwnedCompany and vdv.MotorcadeName in (" + fleet + @") and vdv.status='1' and qs.score is not null and qs.score != 0
order by DriverId asc",
new { OwnedCompany = ownedCompany }).ToList();
}
if (name != "" && name != null)
{
driverScoreList = driverScoreList.Where(x => x.Name.Contains(name)).ToList();
}
}
return(Json(driverScoreList, JsonRequestBehavior.AllowGet));
}
public JsonResult GetCompanyLocation(string vguid)
{
Business_CleaningCompany cleaning = new Business_CleaningCompany();
using (SqlSugarClient _db = SugarDao_MsSql.GetInstance())
{
cleaning = _db.SqlQuery <Business_CleaningCompany>(@"select * from Business_CleaningCompany where Vguid=@VGUID",
new { VGUID = vguid }).ToList().FirstOrDefault();
//cleaning = _db.Queryable<Business_CleaningCompany>().Where(x => x.Vguid == vguid).FirstOrDefault();
}
return(Json(cleaning, JsonRequestBehavior.AllowGet));
}
//***********************************************************************获取，修改数据***********************************************************************************************
//取得发送表数据，未审核(F_DealState=0)，未发送，未处理，发送时间已到，根据优先级排序(大的在前）
private List <SMC_SendSms> GetSMCSendSmsList()
{
List <SMC_SendSms> list_smc_sendsms = new List <SMC_SendSms>();
using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接
{
string sql = "select F_Id,F_MobileList,F_MobileCount,F_IsTimer,F_RootId,F_SendSign,F_CreatorUserId,F_SmsContent from SMC_SendSms"
+ " where F_DealState=0 and F_SendState=0 and F_OperateState=0 ORDER BY F_Priority DESC";
list_smc_sendsms = db.SqlQuery <SMC_SendSms>(sql);//F_CreatorUserId是用户的F_Id
}
return(list_smc_sendsms);
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(1000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Sqlable().Form("Student", "s")
.Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER)
.Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).SelectToList <Models.Student>("*");
}, m => { }, "sqlSugar SQL语法糖");
//sqlSugar
pt.Execute(i =>
{
db.SqlQuery <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "sqlSugar 纯SQL写法");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.ExecuteStoreQuery <WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "EF4.0+sql05 纯SQL写法");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
//*************************************************************************数据库操作方法***************************************************************************************
//获取Http发送方式的发送数据，Sev_SendDateDetail的处理状态（F_DealState)为0，可拆分.发送协议(F_ProtocolType)为1,http发送方式，根据优先级（F_Level）排序
private List <Sev_SendDateDetail> GetHttpSendList()
{
List <Sev_SendDateDetail> httpsendlist = new List <Sev_SendDateDetail>();
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
string sql = "select F_Id,SMC_F_Id,F_SmsContent,F_Level,F_SendTime from Sev_SendDateDetail" //要批量更新状态，只能全部取过来，不然会更新出bug
+ " where F_DealState=0 and F_ProtocolType=1 ORDER BY F_Level DESC";
httpsendlist = db.SqlQuery <Sev_SendDateDetail>(sql); //F_CreatorUserId是用户的F_Id
}
return(httpsendlist);
}
public DriverInfo getDriverInfo(Business_Personnel_Information personInfoModel)
{
DriverInfo pi = new DriverInfo();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
pi = _dbMsSql.SqlQuery <DriverInfo>(@"select DriverId,OrganizationId from [DZ_DW].[dbo].[Visionet_DriverInfo_View] where IdCard=@IDNumber
and status='1'"
, new { IDNumber = personInfoModel.IDNumber }).ToList().FirstOrDefault();
}
return(pi);
}
//获取审核条数
private int ReviewedNum(string F_Id)
{
List <Models.OC_UserInfo> list = new List <Models.OC_UserInfo>();
using (SqlSugarClient db = new SqlSugarClient(connStr)) //开启数据库链接
{
string sql = "select F_Reviewed from OC_UserInfo where F_UserFid = @F_Id"; //获取F_Reviewed
list = db.SqlQuery <Models.OC_UserInfo>(sql, new { F_Id = F_Id });
}
try { return(list[0].F_Reviewed.ObjToInt()); }
catch { return(0); }
}
public Personnel_Info getPersonnelInfo(Business_Personnel_Information personInfoModel)
{
Personnel_Info pi = new Personnel_Info();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
pi = _dbMsSql.SqlQuery <Personnel_Info>(@"select Name,IdCard,CabLicense,CabVMLicense,MotorcadeName,Organization from [DZ_DW].[dbo].[Visionet_DriverInfo_View] where IdCard=@IDNumber
and status='1'"
, new { IDNumber = personInfoModel.IDNumber }).ToList().FirstOrDefault();
}
return(pi);
}
//获取Http发送方式的扣量数据，Sev_SendDateDetail的处理状态（F_DealState)为3，可拆分.发送协议(F_ProtocolType)为1,http发送方式
private List <Sev_SendDateDetail> GetBuckleList()
{
List <Sev_SendDateDetail> buckleList = new List <Sev_SendDateDetail>();
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
string sql = "select * from Sev_SendDateDetail" //数据全取，为了之后好批量更新
+ " where F_DealState=3 and F_ProtocolType=1 and (( F_SendTime+'00:30:00')< GETDATE())"; //推迟30分钟模拟
buckleList = db.SqlQuery <Sev_SendDateDetail>(sql); //F_CreatorUserId是用户的F_Id
}
return(buckleList);
}
private static void SqlSugar(int eachCount)
{
GC.Collect(); //回收资源
System.Threading.Thread.Sleep(2000); //休息2秒
PerHelper.Execute(eachCount, "SqlSugar", () =>
{
using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
{
var list = conn.SqlQuery <Test>("select * from(select *,row_number() over(order by id) as r from test ) t where t.r between @b and @e ", new { b = 1000, e = 1010 });
}
});
}
public JsonResult GetOperationInfo(string fleet, string date, string carID, string code)
{
var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code];
var ownedCompany = cm.Organization;
var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code);
//ownedCompany = "第一服务中心";
List <Visionet_CabInfo> carList = new List <Visionet_CabInfo>();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
if (fleet == "0")
{
fleet = fleetAll;
}
else
{
fleet = "'" + fleet + "'";
}
if (cm.DepartmenManager == "12")
{
carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization in (" + fleet + @")
and OperationStatus=0 order by Motorcade asc").ToList();
}
else
{
carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization=@OwnedCompany and Motorcade in (" + fleet + @")
and OperationStatus=0 order by Motorcade asc",
new { OwnedCompany = ownedCompany, OwnedFleet = fleet }).ToList();
}
if (carID != null && carID != "")
{
carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization=@OwnedCompany and Motorcade in (" + fleet + @")
and OperationStatus=0 and CabLicense like '%" + carID + "%' order by Motorcade asc",
new { OwnedCompany = ownedCompany, OwnedFleet = fleet }).ToList();
}
}
return(Json(carList, JsonRequestBehavior.AllowGet));
}
public static string getAllOwnedCompany(string description, string level)
{
var str = "";
var fleetList = new List <string>();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
if (description != "" && level == "12" && description != "全部" && description != "R")
{
//查所配置公司备注
var description2 = getSqlInValue(description, "");
fleetList = _dbMsSql.SqlQuery <string>(@"select distinct Remark from DZ_Organization where OrganizationName in (" + description2 + ") and status=0").ToList();
}
else if (description == "全部" && level == "12")
{
//查全公司名称
fleetList = _dbMsSql.SqlQuery <string>(@"select distinct OrganizationName from DZ_Organization where status=0").ToList();
}
else if (description == "R" && level == "12")
{
//查全公司备注
fleetList = _dbMsSql.SqlQuery <string>(@"select distinct Remark from DZ_Organization where status=0").ToList();
}
else if (description != "" && level == "11")
{
//查全车队名称
fleetList = _dbMsSql.SqlQuery <string>(@"select CarTeamName from DZ_Organization where OrganizationName=@OrgName and status=0", new { OrgName = description }).ToList();
}
if (fleetList.Count > 0)
{
foreach (var item in fleetList)
{
str += item + ",";
}
str = str.Substring(0, str.Length - 1);
}
}
return(str);
}
public OrderPayRecordDTO GetModelByPayId(int id)
{
using (var db = new SqlSugarClient(Connection))
{
var model = db.SqlQuery <OrderPayRecordDTO>($@" SELECT P.*, P.R_Market_Id AS MarketId,
ISNULL(C.czdmmc00, '') AS CreateUserName
FROM R_OrderPayRecord P
LEFT JOIN dbo.czdm C ON C.Id = P.CreateUser
WHERE P.Id = " + id).FirstOrDefault();
return(model);
}
}
/// <summary>
/// 部门树形结构
/// </summary>
/// <returns></returns>
public List <Master_Organization> GetOrganizationTreeList()
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
var currentDepartment = CurrentUser.GetCurrentUser().Department;
var mainDepVguid = dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault();
var organizationList = dbMsSql.SqlQuery <Master_Organization>("exec usp_OrganizationDetail @orgVguid", new
{
orgVguid = currentDepartment ?? mainDepVguid.ToString()
});
return(organizationList);
}
}
/// <summary>
/// 获取问卷答题状况详情（饼图）
/// </summary>
/// <param name="vguid"></param>
/// <param name="departmentVguid">部门vguid</param>
/// <returns></returns>
public List <U_QuestionSsetsRate> GetQuestionRateDetail(string vguid, string departmentVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
Guid exerciseVguid = Guid.Parse(vguid);
List <U_QuestionSsetsRate> questionRateEntity = new List <U_QuestionSsetsRate>();
string sql = string.Format(@"EXEC usp_QuestionSsetsRate @ExercisesVGUID,@DepartmentVGUID");
DataTable dt = new DataTable();
string departVguid = string.Empty;
if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin")
{
departVguid = string.IsNullOrEmpty(departmentVguid) ? "" : departmentVguid;
}
else
{
departVguid = CurrentUser.GetCurrentUser().Department;
if (!string.IsNullOrEmpty(departmentVguid))
{
Guid dep = Guid.Parse(departVguid);
var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门
Guid depVguid = Guid.Parse(departmentVguid);
departVguid = listDep.Contains(depVguid) ? departmentVguid : departVguid;
}
}
//dt = _dbMsSql.GetDataTable(sql, new
//{
// ExercisesVGUID = exerciseVguid,
// DepartmentVGUID = departVguid
//});
//exerciseRateEntity = GetExerciseRateList(dt);
questionRateEntity = _dbMsSql.SqlQuery <U_QuestionSsetsRate>(sql, new { ExercisesVGUID = exerciseVguid, DepartmentVGUID = departVguid });
//存入操作日志表
//string logData = JsonHelper.ModelToJson<JsonResultModel<U_ScoreReport>>(jsonResult);
//_ll.SaveLog(3, 8, "习题列表", logData);
return(questionRateEntity);
}
}
public JsonResult GetCarViolationInfo(string fleet, string code)
{
//电子警察违章
var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code + "K"];
var orgName = cm.Organization;
var fleetAll = getSqlInValue(cm.MotorcadeName, code);
List <Electronic_police> resultInfo = new List <Electronic_police>();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
if (fleet == "0")
{
fleet = fleetAll;
}
else
{
fleet = "'" + fleet + "'";
}
if (cm.DepartmenManager == "12")
{
resultInfo = _dbMsSql.SqlQuery <Electronic_police>(@"select plate_no,peccancy_date,score,amercement,area,act from tb_electronic_police ep
left join [DZ_DW].[dbo].[Visionet_CabInfo_View] vcv on ep.plate_no=vcv.CabLicense
where vcv.Organization in (" + fleet + @")
and ep.status_cd='0'
order by peccancy_date desc", new { OrgName = orgName }).ToList();
}
else
{
resultInfo = _dbMsSql.SqlQuery <Electronic_police>(@"select plate_no,peccancy_date,score,amercement,area,act from tb_electronic_police ep
left join [DZ_DW].[dbo].[Visionet_CabInfo_View] vcv on ep.plate_no=vcv.CabLicense
where vcv.Organization=@OrgName and vcv.Motorcade in (" + fleet + @")
and ep.status_cd='0'
order by peccancy_date desc", new { OrgName = orgName }).ToList();
}
}
return(Json(resultInfo, JsonRequestBehavior.AllowGet));
}
public JsonResult GetEquityAllocationByVguid(string vguid)
{
Business_EquityAllocation equityAllocation = new Business_EquityAllocation();
using (SqlSugarClient _db = SugarDao_MsSql.GetInstance())
{
if (vguid != "" && vguid != null)
{
equityAllocation = _db.SqlQuery <Business_EquityAllocation>(@"select * from Business_EquityAllocation where VGUID=@VGUID",
new { VGUID = vguid }).ToList().FirstOrDefault();
}
}
return(Json(equityAllocation, JsonRequestBehavior.AllowGet));
}
public List <Master_Organization> GetUserOrganizationModel()
{
using (SqlSugarClient dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
string sql = string.Format(@";with cte as
(
select * from Master_Organization where Vguid = '{0}'
union all
select air.* from Master_Organization as air inner join cte on air.ParentVguid = cte.Vguid
)select * from cte", CurrentUser.GetCurrentUser().Department);
var organizationModel = dbMsSql.SqlQuery <Master_Organization>(sql);
return(organizationModel);
}
}
/// <summary>
/// 通过查询条件获取推送信息列表（已推送）
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public JsonResultModel <V_Business_WeChatPushMain_Information> GetWeChatPushListBySearch(SearchWeChatPushList searchParam, GridParams para)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
JsonResultModel <V_Business_WeChatPushMain_Information> jsonResult = new JsonResultModel <V_Business_WeChatPushMain_Information>();
//只查询三个月内的数据
DateTime endDate = DateTime.Now.AddMonths(-3);
var query = _dbMsSql.Queryable <V_Business_WeChatPushMain_Information>().Where(i => i.Status == 4 && i.PushDate > endDate && i.Title != "营收短信" && i.Title != "营收微信");
if (CurrentUser.GetCurrentUser().LoginName.ToLower() != "sysadmin")
{
var mainDep = _dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault(); //大众交通集团
var listChildDep = _dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == mainDep).Select(i => i.Vguid).ToList(); //大众出租租赁
listChildDep.Add(mainDep);
Guid currentDep = Guid.Parse(CurrentUser.GetCurrentUser().Department); //首先查询登录人的部门
if (!listChildDep.Contains(currentDep))
{
var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + currentDep + "')"); //找到该部门以及其所有子部门
var list = _dbMsSql.Queryable <Sys_User>().In(i => i.Department, listDep).Select(i => i.LoginName).ToList();
query.Where(i => list.Contains(i.PushPeople)); //找到该部门中人员所推送的消息
}
}
if (!string.IsNullOrEmpty(searchParam.Title))
{
query.Where(c => c.Title.Contains(searchParam.Title));//标题
}
if (!string.IsNullOrEmpty(searchParam.PushType.ToString()))
{
query.Where(c => c.PushType == searchParam.PushType);//推送类型
}
if (!string.IsNullOrEmpty(searchParam.Important.ToString()))
{
query.Where(c => c.Important == searchParam.Important);//是否重要
}
if (!string.IsNullOrEmpty(searchParam.PeriodOfValidity.ToString()))
{
DateTime pushDate = DateTime.Parse(searchParam.PushDate.ToString().Replace("0:00:00", "23:59:59"));
query.Where(c => c.PushDate < pushDate);//有效时间
}
query.OrderBy(para.sortdatafield + " " + para.sortorder);
int pageCount = 0;
jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize, ref pageCount);
jsonResult.TotalRows = pageCount;
string logData = JsonHelper.ModelToJson(jsonResult);
_ll.SaveLog(3, 20, CurrentUser.GetCurrentUser().LoginName, "已推送列表", logData);
return(jsonResult);
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="exerciseVguid">习题Vguid</param>
/// <param name="exportType">导出类型</param>
/// <param name="departmentVguid">部门vguid</param>
public void Export(string exerciseVguid, string exportType, string departmentVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
string sql = string.Format(@"EXEC usp_ExercisesAnswerUser @ExercisesVGUID,@Type,@DepartmentVGUID");
DataTable dt = new DataTable();
string departVguid = string.Empty;
if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin")
{
departVguid = string.IsNullOrEmpty(departmentVguid) ? "" : departmentVguid;
}
else
{
departVguid = CurrentUser.GetCurrentUser().Department;
if (!string.IsNullOrEmpty(departmentVguid))
{
Guid dep = Guid.Parse(departVguid);
var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门
Guid depVguid = Guid.Parse(departmentVguid);
departVguid = listDep.Contains(depVguid) ? departmentVguid : departVguid;
}
}
dt = _dbMsSql.GetDataTable(sql, new
{
ExercisesVGUID = exerciseVguid,
Type = exportType,
DepartmentVGUID = departVguid
});
dt.TableName = "table";
DataView dv = dt.DefaultView;
dv.Sort = "Department";
dt = dv.ToTable();
//因为导出模板不一样（exportType为3/4/5的是一个模板,其他的是一个模板）
if (exportType == "3" || exportType == "4" || exportType == "5")
{
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("ScoreTemplate");
Common.ExportExcel.ExportExcels("ScoreTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "ScoreTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
else
{
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("PersonReportTemplate");
Common.ExportExcel.ExportExcels("ReprotPersonTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "PersonReportTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
}
}
//获取用户的自动免审模板
private List <Models.OC_AutoExamineTmpl> GetAutoExamineTmpl(int RootId)
{
List <Models.OC_AutoExamineTmpl> list = new List <Models.OC_AutoExamineTmpl>();
try
{
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
string sql = "select F_Analysis from OC_AutoReviewTemplete where F_RootId = @RootId ";
list = db.SqlQuery <Models.OC_AutoExamineTmpl>(sql, new { RootId = RootId });
}
}
catch { list = null; }//避免空值报错
return(list);
}
//获取用户的人工免审模板
private List <Models.OC_ManualExamineTmpl> GetManualExamineTmpl(int RootId)
{
List <Models.OC_ManualExamineTmpl> list = new List <Models.OC_ManualExamineTmpl>();
try
{
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
string sql = "select F_RegexContent,F_Action,F_ChannelId from OC_ManualExamineTmplete where F_RootId = @RootId";
list = db.SqlQuery <Models.OC_ManualExamineTmpl>(sql, new { RootId = RootId });
}
}
catch { list = null; }//避免空值报错
return(list);
}
public JsonResult SaveCleaningInfo(string companyVGUID, string location, string type, string description, string code)
{
var model = new ActionResultModel <string>();
var cm = CacheManager <Business_Personnel_Information> .GetInstance()[PubGet.GetUserKey + code];
var cabLicense = "";
var cabOrgName = "";
Master_Organization organizationDetail = new Master_Organization();
organizationDetail = _ol.GetOrganizationDetail(cm.OwnedFleet.ToString());
var manOrgName = organizationDetail.OrganizationName;
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2())
{
var manData = _dbMsSql.SqlQuery <Personnel_Info>(@"select Organization,CabLicense from [DZ_DW].[dbo].[Visionet_DriverInfo_View]
where IdCard=@ID and status='1'", new { ID = cm.IDNumber }).FirstOrDefault();
if (manData != null)
{
cabLicense = manData.CabLicense;
cabOrgName = manData.Organization;
}
}
using (SqlSugarClient _db = SugarDao_MsSql.GetInstance())
{
Business_SecondaryCleaning cleaning = new Business_SecondaryCleaning();
cleaning.Vguid = Guid.NewGuid();
cleaning.Type = type;
cleaning.Description = description;
cleaning.Location = location;
cleaning.Personnel = cm.UserID;
cleaning.OperationDate = DateTime.Now;
cleaning.CompanyVguid = companyVGUID;
cleaning.CouponType = description;
cleaning.CabLicense = cabLicense;
cleaning.CabOrgName = cabOrgName;
cleaning.ManOrgName = manOrgName;
cleaning.CreatedUser = cm.Name;
cleaning.CreatedDate = DateTime.Now;
cleaning.ChangeDate = DateTime.Now;
cleaning.ChangeUser = cm.Name;
model.isSuccess = _db.Insert(cleaning, false) != DBNull.Value;
model.respnseInfo = model.isSuccess == true?cleaning.CreatedDate.TryToString() : "0";
//同时修改权益表中优惠券状态
UpdateMyRights(_db, description, cm.Vguid);
}
return(Json(model, JsonRequestBehavior.AllowGet));
}
//取得发送表数据，已审核(F_DealState=9)，未发送，未处理
private List <SMC_SendSms> GetOperateSmsList()
{
List <SMC_SendSms> list_smc_operasms = new List <SMC_SendSms>();
using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接
{
string sql = "select F_Id,F_IsTimer from SMC_SendSms"
+ " where F_DealState=9 and F_SendState=0 and F_OperateState=0 and F_IsTimer='true'";
list_smc_operasms = db.SqlQuery <SMC_SendSms>(sql);//F_CreatorUserId是用户的F_Id
}
try
{
return(list_smc_operasms);
}
catch { return(list_smc_operasms = null); }
}
/// <summary>
/// 获取所有数据库表名
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public List <string> GetTableNames(SqlSugarClient db)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(null);
var tableNameList = db.SqlQuery <string>(sql).ToList();
for (int i = 0; i < tableNameList.Count; i++)
{
var tableName = tableNameList[i];
tableNameList[i] = db.GetClassTypeByTableName(tableName);
}
db.IsEnableLogEvent = isLog;
return(tableNameList);
}
/// <summary>
/// 推送接收者树形结构
/// </summary>
/// <returns></returns>
public List <V_Business_Personnel_Information> GetOrganizationTreeList()
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
List <V_Business_Personnel_Information> organizationTreeList = new List <V_Business_Personnel_Information>();
if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin")
{
organizationTreeList = _dbMsSql.Queryable <V_Business_Personnel_Information>().OrderBy(it => it.UserID).ToList();
}
else
{
organizationTreeList = _dbMsSql.SqlQuery <V_Business_Personnel_Information>(" exec usp_getOrganization @vguid", new { vguid = CurrentUser.GetCurrentUser().Department });
}
return(organizationTreeList);
}
}
public List <SchemeDiscountDetailDTO> GetSchemeDetailListById(int discountId)
{
using (var db = new SqlSugarClient(Connection))
{
string detailSql = "SELECT DC.*, D.Id AS SchemeId, D.[Name] AS SchemeName, " +
"D.R_Area_Id AS AreaId, D.R_Market_Id AS MarketId, " +
"C.Id AS CategoryId, C.Name AS CategoryName " +
"FROM dbo.R_DiscountCategory DC " +
"INNER JOIN dbo.R_Discount D ON D.Id = DC.R_Discount_Id " +
"INNER JOIN dbo.R_Category C ON C.Id = DC.R_Category_Id " +
"WHERE DC.R_Discount_Id = @DiscountId";
var detailList = db.SqlQuery <SchemeDiscountDetailDTO>(detailSql, new { DiscountId = discountId });
return(detailList);
}
}
/// <summary>
/// 事务执行sql语句或存储过程
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="sql">sql语句</param>
/// <param name="whereObj">命令参数对应匿名对象</param>
/// <returns></returns>
public List <TResult> QueryBySqlTransactions <TResult>(string sql, object whereObj = null)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
try
{
dbClient.BeginTran();
return(dbClient.SqlQuery <TResult>(sql, whereObj));
}
catch (Exception ex)
{
dbClient.RollbackTran();
throw ex;
}
}
}
/// <summary>
/// 获取表结构信息
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public List<PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName)
{
string sql = SqlSugarTool.GetTtableColumnsInfo(tableName);
return db.SqlQuery<PubModel.DataTableMap>(sql);
}
/// <summary>
/// 获取所有需要生成多语言的视图名称
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
internal static List<string> GetLanguageViewNameList(SqlSugarClient db)
{
string key = "LanguageHelper.GetViewNameList";
var cm = CacheManager<List<string>>.GetInstance();
if (cm.ContainsKey(key))
{
return cm[key];
}
else
{
var list = db.SqlQuery<string>(@"
select a.name from sys.objects a
JOIN sys.sql_modules b on a.[object_id]=b.[object_id]
where [type]='v'
and b.[definition] like '%" + db.Language.ReplaceViewStringKey + @"%'
and a.name not like '%"+PreSuffix+@"%'
").ToList();
cm.Add(key, list, cm.Day);
return list;
}
}
protected void Page_Load(object sender, EventArgs e)
{
//连接字符串
string connStr = @"Server=.;uid=sa;pwd=sasa;database=SqlSugarTest";
using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接
{
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable<Student>().ToList();
var stud = new Student() { id = 1 };
//查询单条
var single = db.Queryable<Student>().Single(c => c.id==stud.id);
//取10-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
//---------Sqlable,创建多表查询---------//
//多表查询
List<School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 });
//多表分页
List<School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery<Student>("select * from Student");
//获取id
var id = db.SqlQuery<int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List<School> sList = new List<School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update<School>(new { name = "蓝翔2" }, it => it.id == id);
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete<School>(10);//注意主键必需为实体类的第一个属性
db.Delete<School>(it => it.id > 100);
db.Delete<School>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList<Student>(sql);
db.GetSingle<Student>(sql + " where id=1");
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
/// <summary>
/// 获取所有数据库表名
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public List<string> GetTableNames(SqlSugarClient db)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(null);
var tableNameList = db.SqlQuery<string>(sql).ToList();
for (int i = 0; i < tableNameList.Count; i++)
{
var tableName = tableNameList[i];
tableNameList[i] = db.GetClassTypeByTableName(tableName);
}
db.IsEnableLogEvent = isLog;
return tableNameList;
}
