/// <summary>
/// 获取操作model
/// </summary>
/// <returns></returns>
public async Task <List <ActionCharacteristicsModel> > GetOperateModels()
{
using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection())
{
return((await conn.QueryAsync <ActionCharacteristicsModel>("select * from t_utility_operate")).AsList());
}
}
public async Task <IEnumerable <Stock_TransactionDTO> > GetStock_Tran_list()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var sql = $"select tns.*,src.*, dest.*, stck.*,sr_itm.* from DTH.tbl_stock_trans tns " +
"Inner join DTH.tbl_dealers src on tns.trans_source_dealer_id = src.Idbase " +
"Inner join DTH.tbl_dealers dest on tns.trans_dest_dealer_id = dest.Idbase " +
"Inner join DTH.tbl_master_stock stck on tns.trans_item_stock_id = stck.Idbase " +
"Inner join DTH.tbl_master_serialitem sr_itm on tns.trans_item_ser_id = sr_itm.Idbase";
var items = await connection.QueryAsync <Stock_TransactionDTO>(
sql,
new[] { typeof(Stock_TransactionDTO),
typeof(DealerDTO),
typeof(DealerDTO),
typeof(Master_StockDTO),
typeof(master_serialitemDTO) }
, obj => {
Stock_TransactionDTO _dto = obj[0] as Stock_TransactionDTO;
_dto.trans_source_dealer = obj[1] as DealerDTO;
_dto.trans_dest_dealer = obj[2] as DealerDTO;
_dto.trans_item_stock = obj[3] as Master_StockDTO;
_dto.trans_item_ser = obj[4] as master_serialitemDTO;
return(_dto);
}, splitOn : "Idbase"
);
return(items);
}
}
private async Task <IEnumerable <dynamic> > GetDbUsersAsync()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection("Server=127.0.0.1;Database=demo;Uid=root;Pwd=admin;SslMode=None;"))
{
return(await connection.QueryAsync("SELECT * FROM user"));
}
}
/// <summary>
/// 创建Sql
/// </summary>
/// <param name="dateSql">时间sql</param>
/// <param name="merchantGuid">商户GUID</param>
/// <param name="merchantName">商户名称</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <returns></returns>
public async Task <List <ProjectSoldReportRankingAsyncResponseDto> > ProjectSoldReportRankingAsync(ProjectSoldReportRankingAsyncRequestDto requestDto)
{
string sqlstring = $@"SELECT
a.merchant_guid,
b.product_guid,
b.product_name,
sum( b.product_count ) AS SoldCount
FROM
t_mall_order a,
t_mall_order_detail b
WHERE
(
( a.payment_date >= @StartTime AND a.payment_date <= @EndTime )
OR
( a.creation_date >= @StartTime AND a.creation_date <= @EndTime )
)
AND a.merchant_guid = @MerchantGuid
AND a.order_status in ('Received', 'Completed', 'Shipped' )
AND a.order_guid = b.order_guid
GROUP BY
b.product_guid,
b.product_name
ORDER BY
SoldCount DESC
LIMIT 10 ";
using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection())
{
return((await conn.QueryAsync <ProjectSoldReportRankingAsyncResponseDto>(sqlstring, new { requestDto.MerchantGuid, requestDto.StartTime, requestDto.EndTime }))?.AsList());
}
}
private static async Task <string> GetAnyGridFromSqlQuery(string query)
{
string json = string.Empty;
var dd = new DynamicData();
var rows = new List <AnyModel>();
//Send Query To Database
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(DbHelper.Conn()))
{
await conn.OpenAsync();
rows = (await conn.QueryAsync <AnyModel>(query)).ToList();
int count = 0;
dd.COLUMNS.Add(new Column("Id"));
dd.COLUMNS.Add(new Column("Title"));
dd.COLUMNS.Add(new Column("City"));
dd.COLUMNS.Add(new Column("Singer"));
dd.COLUMNS.Add(new Column("Nationality"));
dd.COLUMNS.Add(new Column("Year"));
foreach (var v in rows)
{
var d = new List <string>();
d.Add(v.Id.ToString());
d.Add(v.Title);
d.Add(v.City);
d.Add(v.Singer);
d.Add(v.Nationality);
d.Add(v.Year.ToString());
dd.DATA.Add(d);
count++;
}
json = JsonConvert.SerializeObject(dd);
}
return(json);
}
public async static Task <IEnumerable <int> > NoOfLaps(BoatsTidy boat, Calendar cal)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
return(await connection.QueryAsync <int>("select max(racelap) from races where name = @name and " +
"summary = @summary", new
{
name = boat.Name,
summary = cal.Summary
}));
}
}
public async static Task <IEnumerable <int> > NoOfLaps(Calendar cal)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
return(await connection.QueryAsync <int>("select max(racelap) from races where " +
"summary = @summary and eventStart = @eventStart", new
{
summary = cal.Summary,
eventStart = cal.DateTime
}));
}
}
private static async Task <ICollection <string> > GetListFromSqlQuery(string query)
{
var dropDownList = new List <string>();
//Send Query To Database
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(DbHelper.Conn()))
{
await conn.OpenAsync();
dropDownList = (await conn.QueryAsync <string>(query)).ToList();
}
return(dropDownList);
}
private static async Task <List <AnyModel> > Create_CoinInterfaceView(string query)
{
var rows = new List <AnyModel>();
//Send Query To Database
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(DbHelper.Conn()))
{
await conn.OpenAsync();
rows = (await conn.QueryAsync <AnyModel>(query)).ToList();
}
return(rows);
}
/// <summary>
/// 创建Sql
/// </summary>
/// <param name="dateSql">时间sql</param>
/// <param name="merchantGuid">商户GUID</param>
/// <param name="merchantName">商户名称</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <returns></returns>
private async Task <List <MerchantFlowingReportResponseDto> > SelectFlowingReportAsync(string dateSql, string merchantGuid, string merchantName, DateTime startTime, DateTime endTime)
{
string sqlstring = $@"SELECT
m.merchant_guid AS MerchantGuid,
m.merchant_name AS MerchantName,
m.date AS Date,
mfs.product_count AS ProductCount,
mfs.amount AS Amount,
mfs.order_count AS OrderCount,
ccs.consumption_count AS ConsumptionCount
FROM ({dateSql}) AS m
LEFT JOIN(
SELECT
o.merchant_guid,
DATE_FORMAT( o.payment_date, '%Y-%m-%d' ) AS date,
SUM( o.product_count ) AS product_count,
COUNT( o.order_guid ) AS order_count,
Sum( o.paid_amount ) AS amount
FROM
t_mall_order AS o
WHERE
( o.payment_date >=@startTime AND o.payment_date <=@endTime )
AND o.order_status IN ( 'Received', 'Completed', 'Shipped' )
AND o.merchant_guid = @merchantGuid
GROUP BY
o.merchant_guid,
DATE_FORMAT( o.payment_date, '%Y-%m-%d' )
) AS mfs ON mfs.merchant_guid=m.merchant_guid and mfs.date=m.date
LEFT JOIN(
SELECT
cc.merchant_guid,
DATE_FORMAT(cc.consumption_end_date, '%Y-%m-%d') AS date,
COUNT(cc.consumption_guid) AS consumption_count
FROM t_consumer_consumption cc
WHERE
cc.merchant_guid=@merchantGuid
AND cc.consumption_status in ('Completed' )
AND cc.consumption_end_date >= @startTime
AND cc.consumption_end_date <= @endTime
GROUP BY cc.merchant_guid,date
) AS ccs ON ccs.merchant_guid=m.merchant_guid and ccs.date=m.date
ORDER BY m.date
";// 'Booked','Arrive',
using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection())
{
return((await conn.QueryAsync <MerchantFlowingReportResponseDto>(sqlstring, new { merchantGuid, merchantName, startTime, endTime }))?.AsList());
}
}
public async static Task <IEnumerable <BoatsTidy> > GetRacersReel(Calendar cal)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
return(await connection.QueryAsync <BoatsTidy>("select name, boat, boatNumber, crew, py, notes from signonlists where" +
"summary = @summary and dateTime = @dateTime and sailingClub = @sailingClub"
, new
{
summary = cal.Summary,
dateTime = cal.DateTime,
sailingClub = "Whitefriars Sailing Club"
}));
}
}
/*
* /// <summary>
* /// Gets the list of distinct boats which have been sailed before.
* /// </summary>
* /// <returns>Array of all boats used before.</returns>
* public static String[] GetBoats()
* {
*
* using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
* {
*
* // Query.
* return connection.Query<string>("call returnboats").Distinct().ToArray();
*
* }
*
* }*/
/// <summary>
/// Adds a new boat/person
/// </summary>
/// <param name="boat">Boat data to add.</param>
public async static Task <IEnumerable <dynamic> > SetNewFullBoat(Boats boat)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
//Appends together the query, stops SQL injection.
// Query.
return(await connection.QueryAsync("insert into fulllist value (@name, @boatName, " +
"@boatNumber, @py, @sailingClub)", new {
name = boat.Name,
boatName = boat.BoatName,
boatNumber = boat.BoatNumber,
py = boat.Py,
sailingClub = "Whitefriars Sailing Club"
}));
}
}
public async Task <Stock_TransactionDTO> GetStock_Tran(Int64 id)
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var sql = $"select tns.*,src.*, dest.*, stck.*,sr_itm.* from DTH.tbl_stock_trans tns " +
"Inner join DTH.tbl_dealers src on tns.trans_source_dealer_id = src.Idbase " +
"Inner join DTH.tbl_dealers dest on tns.trans_dest_dealer_id = dest.Idbase " +
"Inner join DTH.tbl_master_stock stck on tns.trans_item_stock_id = stck.Idbase " +
"Inner join DTH.tbl_master_serialitem sr_itm on tns.trans_item_ser_id = sr_itm.Idbase WHERE tns.Idbase={id}";
//Func<Stock_TransactionDTO, DealerDTO, DealerDTO> p = (oi, i, v) =>
// {
// oi = oi; oi.trans_source_dealer = i; oi.trans_dest_dealer = v;
// return oi;
// };
var items = await connection.QueryAsync <Stock_TransactionDTO>(
sql,
new[] { typeof(Stock_TransactionDTO),
typeof(DealerDTO),
typeof(DealerDTO),
typeof(Master_StockDTO),
typeof(master_serialitemDTO) }
, obj => {
Stock_TransactionDTO _dto = obj[0] as Stock_TransactionDTO;
_dto.trans_source_dealer = obj[1] as DealerDTO;
_dto.trans_dest_dealer = obj[2] as DealerDTO;
_dto.trans_item_stock = obj[3] as Master_StockDTO;
_dto.trans_item_ser = obj[4] as master_serialitemDTO;
return(_dto);
}, splitOn : "Idbase"
);
return(items.FirstOrDefault());
//var result = await connection.QueryAsync<Stock_TransactionDTO>(
// @"select * from DTH.tbl_stock_trans
// WHERE Idbase=@id"
// , new { id }
// );
//if (result.AsList().Count == 0)
// throw new KeyNotFoundException();
//return result.FirstOrDefault();
}
}
public async Task <IEnumerable <DealerDTO> > GetDealers()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <DealerDTO>(
@"select * from DTH.tbl_dealers ");
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(result);
}
}
public async Task <CustomerDTO> GetCustomersAsync()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <dynamic>(
@"select * from DTH.tblCustomer ");
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(MapCustomerItems(result));
}
}
public async Task <IEnumerable <Master_StockDTO> > GetMaster_StockItems()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <Master_StockDTO>(
@"SELECT Idbase, stock_name, if(stock_type = 1001, 'Serial', 'NonSerial') as stock_type, stock_unit, stock_cost, stock_detail FROM DTH.tbl_master_stock");
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(result);
}
}
public async Task <dynamic> GetProjectsByUserId(int userId)
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <dynamic>(
@"select *from project where userid = @userId"
, new { userId }
);
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(result);
}
}
public async Task <List <T> > GetAsync <T>(string connection, string sql, object parameters)
{
var result = new List <T>();
//var test = new MySql.Data.MySqlClient()
using (var c = new MySqlData.MySqlClient.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"SELECT: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}");
await c.OpenAsync();
var query = await c.QueryAsync <T>(sql, parameters);
result = query.ToList();
await c.CloseAsync();
}
return(result.ToList());
}
public async Task <Master_StockDTO> GetMaster_StockItem(Int64 id)
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <Master_StockDTO>(
@"select * from DTH.tbl_master_stock
WHERE Idbase=@id"
, new { id }
);
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(result.FirstOrDefault());
}
}
public async Task <DealerDTO> GetDealer(int id)
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <DealerDTO>(
@"select * from DTH.tbl_dealers
WHERE Idbase=@id"
, new { id }
);
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(result.FirstOrDefault());
}
}
public async Task <Customer> GetCustomerAsync(int id)
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <dynamic>(
@"select * from DTH.tblCustomer
WHERE o.Idbase=@id"
, new { id }
);
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return((Customer)result);
}
}
/// <summary>
/// 获取积分规则Model
/// </summary>
/// <param name="userType">用户类型</param>
/// <param name="actionCharacteristics">行为特征枚举</param>
/// <returns></returns>
public async Task <List <GetActionRulesResponseDto> > GetScoreRulesModel(UserType userType, ActionCharacteristicsEnum actionCharacteristics = ActionCharacteristicsEnum.Action)
{
string sqlstring = $@"select
sr.rules_guid AS RulesGuid,
ac.action_characteristics_code AS ActionCharacteristicsCode,
ac.action_characteristics_name AS ActionCharacteristicsName,
ac.action_characteristics_type AS ActionCharacteristicsType
from t_utility_score_rules as sr
inner join t_utility_user_action as ua on sr.user_action_guid=ua.user_action_guid
inner join t_utility_action_characteristics as ac on ac.action_characteristics_guid=ua.action_guid
where ac.action_characteristics_type=@action_characteristics_type
and ua.user_type_guid=@user_type_guid";
DynamicParameters parameters = new DynamicParameters();
parameters.Add("action_characteristics_type", actionCharacteristics.ToString(), System.Data.DbType.String);
parameters.Add("user_type_guid", userType.ToString(), System.Data.DbType.String);
using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection())
{
return((await conn.QueryAsync <GetActionRulesResponseDto>(sqlstring, parameters)).AsList());
}
}