public List<CountyModel> Get(CountyParameterModel parameter)
{
using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
{
var sql = new StringBuilder();
sql.AppendLine(@"SELECT
CityCode,
CityName,
CitySName,
CityAName,
CityEName,
CityHallLng,
CityHallLat,
GPSResult,
GPSDatetime,
Geographys,
Geometrys,
MinLng,
MinLat,
MaxLng,
MaxLat,
CLng,
CLat,
GeoString,
UseFlag,
UpdatDate
FROM ADDRCity WITH (NOLOCK)
WHERE UseFlag = 'Y'");
if (parameter != null)
{
var whereSql = new StringBuilder();
if (!string.IsNullOrWhiteSpace(parameter.CityCode))
{
whereSql.AppendLine(@" AND CityCode = @CityCode");
}
if (!string.IsNullOrWhiteSpace(parameter.CityName))
{
whereSql.AppendLine(@" AND CityName = @CityName");
}
if (whereSql.Length > 0)
{
sql.AppendLine(whereSql.ToString());
}
}
IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");
var counties = dapper.Query<CountyModel>(
dbConnection: conn,
sql: sql.ToString(),
param: parameter).ToList();
return counties;
}
}
public List<DistrictModel> Get(DistrictParameterModel parameter)
{
using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
{
var sql = new StringBuilder();
sql.AppendLine(@"SELECT
ZipID,
CityCode,
ZipCode,
ZipName,
ZipEName,
UseFlag,
GPSResult,
MinLng,
MinLat,
MaxLng,
MaxLat,
CLng,
CLat,
TownHallLng,
TownHallLat,
GeoString
FROM ADDRDistrict WITH (NOLOCK)
WHERE UseFlag = 'Y'
AND CityCode = @CityCode");
var whereSql = new StringBuilder();
if (parameter.ZipID != null)
{
whereSql.AppendLine(@" AND ZipID = @ZipID");
}
if (!string.IsNullOrWhiteSpace(parameter.ZipName))
{
whereSql.AppendLine(@" AND ZipName = @ZipName");
}
if (whereSql.Length > 0)
{
sql.AppendLine(whereSql.ToString());
}
IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");
var districts = dapper.Query<DistrictModel>(
dbConnection: conn,
sql: sql.ToString(),
param: parameter).ToList();
return districts;
}
}
public List<RoadModel> Get(RoadParameterModel parameter)
{
using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
{
var sql = new StringBuilder();
sql.AppendLine(@"SELECT
CityCode,
ZipCode,
ZipID,
RoadCode,
RoadName,
RoadEName,
Lng,
Lat,
UseFlag
FROM ADDRRoad WITH (NOLOCK)
WHERE UseFlag = 'Y'
AND ZipID = @ZipID");
var whereSql = new StringBuilder();
if (!string.IsNullOrWhiteSpace(parameter.RoadCode))
{
whereSql.AppendLine(@" AND RoadCode = @RoadCode");
}
if (!string.IsNullOrWhiteSpace(parameter.RoadName))
{
whereSql.AppendLine(@" AND RoadName = @RoadName");
}
if (whereSql.Length > 0)
{
sql.AppendLine(whereSql.ToString());
}
IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");
var roads = dapper.Query<RoadModel>(
dbConnection: conn,
sql: sql.ToString(),
param: parameter).ToList();
return roads;
}
}
public string GetCompanyName(int id)
{
var name = DapperHelper.SqlQuery <string>("select CompanyName from EM_Company where Id=@Id ", new { Id = id }).FirstOrDefault();
return(name ?? "");
}
public Repositorio(DapperHelper dapperHelper)
{
this.dapperHelper = dapperHelper;
}
/// <summary>
///
/// </summary>
/// <param name="dapper"></param>
public MenuByIdQueryHandler(DapperHelper dapper)
{
this._dapper = dapper;
}
public IEnumerable <T> QueryEnable <T>(SQLiteConnection conn, SQLiteTransaction trans)
{
return(DapperHelper.Query <T>(conn, $"select * from {tableName} where organSign='{CacheInfo.Drugstore.OrganSign}' and yn=1;", null, trans));
}
public async Task <ActionResult> BrowseInstructorList(JObject Parameter)
{
try
{
/*Validation Required parameter */
if (string.IsNullOrEmpty(Convert.ToString(Parameter["page_number"])))
{
return(BadRequest("Bad request, page number is required"));
}
string Sql = "";
DynamicParameters DyParam_InstructorList = new DynamicParameters();
/*Dynamic Parameter Alter*/
if (!string.IsNullOrEmpty(Convert.ToString(Parameter["name"])))
{
string Full_name = Convert.ToString(Parameter["name"]);
var Names = Full_name.Split(' ', '.', ',');
string First_name = Names[0];
string Last_name = Names[1];
Sql += "AND `first_name` = @first_name AND `last_name` = @last_name" + " ";
DyParam_InstructorList.Add("first_name", First_name);
DyParam_InstructorList.Add("last_name", Last_name);
}
if (!string.IsNullOrEmpty(Convert.ToString(Parameter["instructor_id"])))
{
string instructor_id = Convert.ToString(Parameter["instructor_id"]);
Sql += "AND `instructor_id` = @instructor_id ";
DyParam_InstructorList.Add("instructor_id", instructor_id);
}
if (Sql != "")
{
Sql = "WHERE 1 = 1 " + Sql;
}
/*calculate the total amount of records*/
string Original_zq61H4SJdL = XMLHelper.GetSql("SQL_zq61H4SJdL");// get recod amount
Original_zq61H4SJdL = Original_zq61H4SJdL.Replace("@dynamic_condition", Sql);
DataTable DT_zq61H4SJdL = await DapperHelper.ExecuteSqlDataTableAsync(Original_zq61H4SJdL, DyParam_InstructorList);
/*Get pagination */
DyParam_InstructorList.Add("page_number", (Convert.ToInt32(Parameter["page_number"]) - 1) * 10);
string Original_U83Eb0bl4Q = XMLHelper.GetSql("SQL_U83Eb0bl4Q");
Original_U83Eb0bl4Q = Original_U83Eb0bl4Q.Replace("@dynamic_condition", Sql);
DataTable DT_U83Eb0bl4Q = await DapperHelper.ExecuteSqlDataTableAsync(Original_U83Eb0bl4Q, DyParam_InstructorList);// get datas
JArray jArray_Data = JArray.Parse(JsonConvert.SerializeObject(DT_U83Eb0bl4Q));
JArray jArray_Columns = new JArray();
for (int i = 0; i < DT_U83Eb0bl4Q.Columns.Count; i++)
{
JObject Json = new JObject();
Json.Add("tittle", DT_U83Eb0bl4Q.Columns[i].ColumnName);
Json.Add("dataIndex", DT_U83Eb0bl4Q.Columns[i].ColumnName);
jArray_Columns.Add(Json);
}
for (int j = 0; j < jArray_Data.Count; j++)
{
((JObject)jArray_Data[j]).Add("key", j);
}
JObject Json_Result = new JObject();
Json_Result.Add("pageSum", Convert.ToInt32(DT_zq61H4SJdL.Rows[0]["pageSum"]));
Json_Result.Add("columnData", jArray_Columns);
Json_Result.Add("rowData", jArray_Data);
return(Ok(Json_Result));
}
catch (Exception ex)
{
return(BadRequest("Unknow Error"));
}
}//tested
public int UptRoles(Model.Roles a)
{
string sql = $"update Roles set RoleName='{a.RoleName}' where RoleId={a.RoleId})";
return(DapperHelper.Execute(sql));
}
/// <summary>
/// 返回月度数据（全部）
/// </summary>
/// <returns></returns>
public List <MonthAnalizeData> GetMonthAnalizeDatas(int Code, DateTime time)
{
using (OracleConnection conn = DapperHelper.GetConnString())
{
//传回的是用户id
string sql1 = string.Format("select a.region_code from Region a,\"User\" b where a.id=b.RegionId and b.id='" + Code + "'");
string collectList1 = conn.Query <string>(sql1, null).FirstOrDefault();
string code1 = collectList1;
//获取到行政区的编号
string code2 = code1;
//三表联查
//string sql = string.Format("select a.avgvalue,a.monitortime,c.PollutantName,d.PointName from MONTHDATA a ,MonitorPointPollutan b, Pollutant c,MonitorPoint d where a.Monitor_PollutionId=b.Id and b.PollutantId=c.id and b.pointid=d.id and d.RegionCode='" + code2 + "'");
//var collectList = conn.Query<MonthAnalizeData>(sql, null);
//List<MonthAnalizeData> list= collectList.ToList<MonthAnalizeData>();
//List<MonthAnalizeData> list2= list.Where(n => n.PointName == "PM2.5").ToList();
//List<MonthAnalizeData> list3 = list.Where(n => n.PointName == "PM10").ToList();
//List<MonthAnalizeData> list4 = list.Where(n => n.PointName == "O3").ToList();
//List<MonthAnalizeData> list5 = list.Where(n => n.PointName == "NO2").ToList();
//List<MonthAnalizeData> list6 = list.Where(n => n.PointName == "CO").ToList();
//List<MonthAnalizeData> list7 = list.Where(n => n.PointName == "SO2").ToList();
//4表连查
string sql = string.Format("select a.avgvalue,c.pointname,d.pollutantname from MonthData a,MonitorPointPollutan b,MonitorPoint c,Pollutant d where a.Monitor_Pollutionid=b.id and b.PointId=c.id and b.pollutantid=d.id and c.RegionCode='" + code2 + "' and a.monitortime=:time ");
//接收
var collectList = conn.Query <MonthAnalizeData>(sql, new { time });
//求上一年同月份，用于同比
DateTime time3 = time.AddYears(-1);
//求上个月份，用于环比
DateTime time2 = time.AddMonths(-1);
//获取同比数据的sql语句
string sql12 = string.Format("select a.avgvalue, c.pointname, d.pollutantname from MonthData a, MonitorPointPollutan b, MonitorPoint c, Pollutant d where a.Monitor_Pollutionid = b.id and b.PointId = c.id and b.pollutantid = d.id and c.RegionCode = '" + code2 + "' and a.monitortime =:times");
//用于同比的数据
var collectList2 = conn.Query <MonthAnalizeData>(sql12, new { times = time3 });
//获取环比数据的sql语句
string sql13 = string.Format("select a.avgvalue, c.pointname, d.pollutantname from MonthData a, MonitorPointPollutan b, MonitorPoint c, Pollutant d where a.Monitor_Pollutionid = b.id and b.PointId = c.id and b.pollutantid = d.id and c.RegionCode = '" + code2 + "' and a.monitortime =:timess");
//获取环比的数据
var collectList3 = conn.Query <MonthAnalizeData>(sql13, new { timess = time2 });
//数组接收所有的站点的名称
List <string> pointNames = (from c in collectList select c.PointName).Distinct().OrderBy(m => m).ToList();
//实例化一个数组用于返回
List <MonthAnalizeData> list = new List <MonthAnalizeData>();
//循环遍历 取出各个站点名称
foreach (string pointName in pointNames)
{
//实例化对象，用于存储数据
MonthAnalizeData monthAnalizeData = new MonthAnalizeData();
//赋值
monthAnalizeData.PointName = pointName;
//循环遍历同一站点的数据
foreach (MonthAnalizeData monthAnalizeData1 in collectList.Where(m => m.PointName == pointName))
{
//同比，因为上一年的数据会没有，所以要有一个非空判断
if (collectList2.Count() > 0)
{
//遍历同一站点的同比数据
foreach (MonthAnalizeData monthAnalizeData2 in collectList2.Where(m => m.PointName == pointName))
{
//遍历同一站点的环比数据
foreach (MonthAnalizeData monthAnalizeData3 in collectList3.Where(m => m.PointName == pointName))
{
///同一站点 污染物 的数据
if (monthAnalizeData1.PollutantName == "PM2.5" && monthAnalizeData2.PollutantName == "PM2.5" && monthAnalizeData3.PollutantName == "PM2.5")
{
//赋值
monthAnalizeData.PM2AVGValue = monthAnalizeData1.AVGValue;
//同比赋值
monthAnalizeData.TPM2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
//环比复制
monthAnalizeData.HPM2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
///同一站点 污染物 的数据
if (monthAnalizeData1.PollutantName == "PM10" && monthAnalizeData2.PollutantName == "PM10" && monthAnalizeData3.PollutantName == "PM10")
{
monthAnalizeData.PM10AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.TPM10AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
monthAnalizeData.HPM10AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "CO" && monthAnalizeData2.PollutantName == "CO" && monthAnalizeData3.PollutantName == "CO")
{
monthAnalizeData.COAVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.TCOAVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
monthAnalizeData.HCOAVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "NO" && monthAnalizeData2.PollutantName == "NO" && monthAnalizeData3.PollutantName == "NO")
{
monthAnalizeData.N02AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.TN02AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
monthAnalizeData.HN02AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "O3" && monthAnalizeData2.PollutantName == "O3" && monthAnalizeData3.PollutantName == "O3")
{
monthAnalizeData.O3AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.TO3AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
monthAnalizeData.HO3AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "SO2" && monthAnalizeData2.PollutantName == "SO2" && monthAnalizeData3.PollutantName == "SO2")
{
monthAnalizeData.SO2AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.TSO2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
monthAnalizeData.HSO2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
}
}
}
///判断环比数据是否为空
else if (collectList3.Count() > 0)
{
foreach (MonthAnalizeData monthAnalizeData3 in collectList3.Where(m => m.PointName == pointName))
{
if (monthAnalizeData1.PollutantName == "PM2.5")
{
monthAnalizeData.PM2AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.HPM2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "PM10")
{
monthAnalizeData.PM10AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.HPM10AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "CO")
{
monthAnalizeData.COAVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.HCOAVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "NO")
{
monthAnalizeData.N02AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.HN02AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "O3")
{
monthAnalizeData.O3AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.HO3AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
if (monthAnalizeData1.PollutantName == "SO2")
{
monthAnalizeData.SO2AVGValue = monthAnalizeData1.AVGValue;
monthAnalizeData.HSO2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
}
}
}
else
{
if (monthAnalizeData1.PollutantName == "PM2.5")
{
monthAnalizeData.PM2AVGValue = monthAnalizeData1.AVGValue;
}
if (monthAnalizeData1.PollutantName == "PM10")
{
monthAnalizeData.PM10AVGValue = monthAnalizeData1.AVGValue;
}
if (monthAnalizeData1.PollutantName == "CO")
{
monthAnalizeData.COAVGValue = monthAnalizeData1.AVGValue;
}
if (monthAnalizeData1.PollutantName == "NO")
{
monthAnalizeData.N02AVGValue = monthAnalizeData1.AVGValue;
}
if (monthAnalizeData1.PollutantName == "O3")
{
monthAnalizeData.O3AVGValue = monthAnalizeData1.AVGValue;
}
if (monthAnalizeData1.PollutantName == "SO2")
{
monthAnalizeData.SO2AVGValue = monthAnalizeData1.AVGValue;
}
}
}
//给list赋值
list.Add(monthAnalizeData);
}
return(list);
}
}
public int Update(Model.Delivery Model)
{
string sql = $"UPDATE Delivery SET DeliveryName='{Model.DeliveryName}',DeliveryPath='{Model.DeliveryPath}' WHERE DeliveryId={Model.DeliveryId}";
return(DapperHelper.Execute(sql));
}
public List <Model.Delivery> Query()
{
string sql = "SELECT * FROM Delivery a JOIN Users b ON a.UserId=b.UserId join Colonel c ON a.ColonelID=c.ColonelID";
return(DapperHelper.GetList <IOT.Core.Model.Delivery>(sql));
}
public int Insert(Model.Delivery Model)
{
string sql = $"INSERT INTO Delivery VALUES(NULL,{Model.UserId},{Model.ColonelID},'{Model.DeliveryPath}','{Model.DeliveryName}')";
return(DapperHelper.Execute(sql));
}
public int Delete(string ids)
{
string sql = $"DELETE FROM Delivery WHERE DeliveryId IN({ids})";
return(DapperHelper.Execute(sql));
}
/// <summary>
///
/// </summary>
/// <param name="dapper"></param>
public RoleByIdQueryHandle(DapperHelper dapper)
{
_dapper = dapper;
}
public int AddRoles(Model.Roles a)
{
string sql = $"insert into Roles values (null,'{a.RoleName}')";
return(DapperHelper.Execute(sql));
}
// 显示
public List <Model.Warehouse> ShowWarehouse()
{
string sql = "select * from Warehouse";
return(DapperHelper.GetList <Model.Warehouse>(sql));
}
public List <Model.Roles> ShowRoles()
{
string sql = "select * from Roles";
return(DapperHelper.GetList <Model.Roles>(sql));
}
// 删除
public int DelWarehouse(string id)
{
string sql = $"delete from Warehouse where WarehouseId={id}";
return(DapperHelper.Execute(sql));
}
public List <Model.ApplicationType> Query(string sql)
{
return(DapperHelper.GetList <Model.ApplicationType>(sql));
}
public List <Users> UsersList(string name)
{
List <Users> TestList = DapperHelper.GetList <Users>("SELECT * From users where AccountName='" + name + "'");
return(TestList);
}
public async Task <ActionResult> BrowseCourseList(JObject Parameter)
{
//try
//{
//}
//catch (Exception ex) { return BadRequest("Get course list failed."); }
/*Validation Required parameter */
if (string.IsNullOrEmpty(Convert.ToString(Parameter["page_number"])))
{
return(BadRequest("Bad request, page number is required"));
}
string Sql = "";
DynamicParameters DyParam_courseList = new DynamicParameters();
/*Dynamic Parameter Alter*/
if (!string.IsNullOrEmpty(Convert.ToString(Parameter["course_code"])))
{
string course_code = Convert.ToString(Parameter["course_code"]);
Sql += "AND `course_code` = @course_code ";
DyParam_courseList.Add("course_code", course_code);
}
if (!string.IsNullOrEmpty(Convert.ToString(Parameter["program"])))
{
string program = Convert.ToString(Parameter["program"]);
Sql += "AND `program` = @program";
DyParam_courseList.Add("program", program);
}
if (Sql != "")
{
Sql = "WHERE 1 = 1 " + Sql;
}
/*calculate the total amount of records*/
string Original_8aN4nnYYVz = XMLHelper.GetSql("SQL_8aN4nnYYVz");// get recod amount
Original_8aN4nnYYVz = Original_8aN4nnYYVz.Replace("@dynamic_condition", Sql);
DataTable DT_8aN4nnYYVz = await DapperHelper.ExecuteSqlDataTableAsync(Original_8aN4nnYYVz, DyParam_courseList);
/*Get pagination */
DyParam_courseList.Add("page_number", (Convert.ToInt32(Parameter["page_number"]) - 1) * 10);
string Original_yZZSnuIMA4 = XMLHelper.GetSql("SQL_yZZSnuIMA4");
Original_yZZSnuIMA4 = Original_yZZSnuIMA4.Replace("@dynamic_condition", Sql);
DataTable DT_yZZSnuIMA4 = await DapperHelper.ExecuteSqlDataTableAsync(Original_yZZSnuIMA4, DyParam_courseList);// get datas
JArray jArray_Data = JArray.Parse(JsonConvert.SerializeObject(DT_yZZSnuIMA4));
JArray jArray_Columns = new JArray();
for (int i = 0; i < DT_yZZSnuIMA4.Columns.Count; i++)
{
JObject Json = new JObject();
Json.Add("tittle", DT_yZZSnuIMA4.Columns[i].ColumnName);
Json.Add("dataIndex", DT_yZZSnuIMA4.Columns[i].ColumnName);
jArray_Columns.Add(Json);
}
for (int j = 0; j < jArray_Data.Count; j++)
{
((JObject)jArray_Data[j]).Add("key", j);
}
JObject Json_Result = new JObject();
Json_Result.Add("pageSum", Convert.ToInt32(DT_8aN4nnYYVz.Rows[0]["pageSum"]));
Json_Result.Add("columnData", jArray_Columns);
Json_Result.Add("rowData", jArray_Data);
return(Ok(Json_Result));
}//tested
public List <Model.SeckillCom> Query()
{
string sql = "select a.*,b.ActivityId,c.CommodityPic,b.ActivityName,b.BeginTime,b.EndTime,c.ShopPrice,NOW() times,c.CommodityName FROM SeckillCom a JOIN Activity b ON a.ActivityId=b.ActivityId JOIN Commodity c ON a.CommodityId=c.CommodityId";
return(DapperHelper.GetList <IOT.Core.Model.SeckillCom>(sql));
}
/// <summary>
/// 小程序显示
/// </summary>
/// <returns></returns>
public List<Model.MiniProgram> MiniPrograms()
{
string sql = $" select * from MiniProgram ";
return DapperHelper.GetList<Model.MiniProgram>(sql);
}
/// <summary>
/// 清空当前serverid所有记录
/// </summary>
public void ClearTaskRunLog()
{
DapperHelper.Execute(@"delete from task_run_log where taskserverid=@serverid", Context,
new { serverid = _runnerConfig.TaskServerId });
}
public int DelRoles(string id)
{
string sql = $"delete from Roles where RoleId={id}";
return(DapperHelper.Execute(sql));
}
public SongMarkRepository()
{
helper = new DapperHelper <SqlConnection>(WebConfig.SqlConnection);
}
public BookController(DapperHelper helper)
{
this._helper = helper;
}
/// <summary>
/// 显示
/// </summary>
/// <returns></returns>
public List <Model.Activity> ShowActivity()
{
string sql = "select * from Activity";
return(DapperHelper.GetList <Model.Activity>(sql));
}
public void DailyMenu_Test()
{
IRepository repo = new Repository();
if (!repo.Any <Menu>(x => x.MenuDate == DateTime.Today))
{
// Today's menu no exist
var menuL1 = new Menu
{
MenuDate = DateTime.Today,
MenuType = MenuTypeEnum.Lunch,
MenuFlag = "A",
Meat = "红烧大排",
MeatSmall = "芙蓉鸡片",
Vegetable1 = "酸辣土豆丝",
Vegetable2 = "麻婆豆腐",
CreateTime = DateTime.Now,
CreateUser = "******",
IsApproved = false,
IsActive = true,
Remark = "TEST Lunch A"
};
var menuL2 = new Menu
{
MenuDate = DateTime.Today,
MenuType = MenuTypeEnum.Lunch,
MenuFlag = "B",
Meat = "咖喱牛肉",
MeatSmall = "肉饼蒸蛋",
Vegetable1 = "油焖笋",
Vegetable2 = "凉拌马兰头",
CreateTime = DateTime.Now,
CreateUser = "******",
IsApproved = false,
IsActive = true,
Remark = "TEST Lunch B"
};
var menuS1 = new Menu
{
MenuDate = DateTime.Today,
MenuType = MenuTypeEnum.Supper,
MenuFlag = "A",
Meat = "啤酒鸭",
MeatSmall = "番茄炒蛋",
Vegetable1 = "上汤菠菜",
Vegetable2 = "清炒冬瓜",
CreateTime = DateTime.Now,
CreateUser = "******",
IsApproved = false,
IsActive = true,
Remark = "TEST Supper A"
};
var menuS2 = new Menu
{
MenuDate = DateTime.Today,
MenuType = MenuTypeEnum.Supper,
MenuFlag = "B",
Meat = "香煎带鱼",
MeatSmall = "雪菜炒鸡",
Vegetable1 = "香菇青菜",
Vegetable2 = "手撕包心菜",
CreateTime = DateTime.Now,
CreateUser = "******",
IsApproved = false,
IsActive = true,
Remark = "TEST Supper B"
};
using (IDapperHelper dapper = DapperHelper.GetInstance())
{
var trans = dapper.BeginTransaction();
try
{
repo.Insert(menuL1);
repo.Insert(menuL2);
repo.Insert(menuS1);
repo.Insert(menuS2);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
Assert.IsTrue(repo.Count <Menu>(x => x.MenuDate == DateTime.Today) == 4 ||
repo.Count <Menu>(x => x.MenuDate == DateTime.Today) == 2);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int DelActivity(string id)
{
string sql = $"delete from Activity where ActivityId={id}";
return(DapperHelper.Execute(sql));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="a"></param>
/// <returns></returns>
public int AddActivity(Model.Activity a)
{
string sql = $"insert into Activity values (null,'{a.ActivityName}', '{a.BeginTime}', '{a.EndTime}', '{a.Slideshow}', {a.State},'{a.CreateDate}', {a.ActivityTime})";
return(DapperHelper.Execute(sql));
}
public ContentsRepository(DapperHelper dapperHelper) : base(dapperHelper)
{
}
/// <summary>
///
/// </summary>
/// <param name="dapper"></param>
public MonthPieDataQueryHandler(DapperHelper dapper)
{
_dapper = dapper;
}
public List<VillageModel> Get(VillageParameterModel parameter)
{
using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
{
var sql = @"SELECT
VillageID,
ZipID,
CityCode,
ZipCode,
VillageName,
VillageEName,
GPSResult,
MinLng,
MinLat,
MaxLng,
MaxLat,
CLng,
CLat,
GeoString,
UseFlag
FROM ADDRVillage WITH (NOLOCK)
WHERE UseFlag = 'Y'
AND ZipID = @ZipID";
IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");
var villages = dapper.Query<VillageModel>(
dbConnection: conn,
sql: sql,
param: parameter).ToList();
return villages;
}
}
