public static List <VehicleTypeCertificationAuditLogModel> SelectLogs(List <Guid> carIds)
{
if (!carIds.Any())
{
return(new List <VehicleTypeCertificationAuditLogModel>());
}
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
using (var cmd =
new SqlCommand(
$@"SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CarId ORDER BY CreateTime DESC) AS N,*
FROM Tuhu_log..VehicleTypeCertificationAuditLog WOTH(NOLOCK) WHERE CarId IN({string.Join(",",carIds.Select(_=>$"'{_}'"))})) AS T
WHERE T.N=1"))
{
cmd.CommandType = CommandType.Text;
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <VehicleTypeCertificationAuditLogModel>().ToList());
}
}
}
public static List <RebateConfigLog> SearchRebateConfigLog(string pkid)
{
const string sql = @"
SELECT rcl.PKID ,
rcl.IdentityId ,
rcl.Type ,
rcl.Msg ,
rcl.Remark ,
rcl.IPAddress ,
rcl.HostName ,
rcl.OperateUser ,
rcl.CreateDateTime ,
rcl.UpdateDateTime
FROM Tuhu_log..RebateConfigLog AS rcl WITH ( NOLOCK )
WHERE rcl.IdentityId = @IdentityId
Order BY rcl.PKID DESC; ";
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
var cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdentityId", pkid);
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <RebateConfigLog>().ToList());
}
}
public static List <FlashSaleRecordsLog> SelectFlashSalRecordsLog(Guid activityId)
{
var conn = ConfigurationManager.ConnectionStrings["SystemLog_AlwaysOnRead"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
var cmd = new SqlCommand(@"SELECT * FROM SystemLog..tbl_FlashSaleRecords WITH (NOLOCK) WHERE ActivityID=@ActivityID ");
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ActivityID", activityId);
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <FlashSaleRecordsLog>().ToList());
}
}
public static List <CouponActivityLog> SelectOperationLog(string objectId, string type)
{
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
var cmd = new SqlCommand(@"SELECT * FROM Tuhu_log..CouponActivityLog WITH (NOLOCK) WHERE ObjectId=@ObjectId AND Type=@Type ORDER BY CreatedTime DESC");
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ObjectId", objectId);
cmd.Parameters.AddWithValue("@Type", type.ToString());
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <CouponActivityLog>().ToList());
}
}
public static List <OrderCompletionJudgeCriteria> SelectOrderCompJudgeCriteria(string userId)
{
var conn = ConfigurationManager.ConnectionStrings["Gungnir_AlwaysOnRead"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
const string sql = "SELECT InstallShopId, Status, TBO.DeliveryStatus, TBO.DeliveryDatetime, InstallDatetime, TBOD.DeliveryStatus AS DeliveryStatusInLog, TBOD.DeliveryDatetime AS DeliveryDatetimeInLog FROM Gungnir..tbl_Order AS TBO WITH(NOLOCK) LEFT JOIN Gungnir.dbo.tbl_OrderDeliveryLog AS TBOD ON TBO.PKID = TBOD.OrderID WHERE TBO.UserID=@UserID";
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
var cmd = new SqlCommand(sql);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@UserID", userId);
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <OrderCompletionJudgeCriteria>().ToList());
}
}
public static List <VehicleTypeCertificationAuditLogModel> SelectLogsByCarId(Guid carId)
{
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
using (var cmd =
new SqlCommand(
$@"SELECT *
FROM Tuhu_log..VehicleTypeCertificationAuditLog WITH(NOLOCK) WHERE CarId=@carId ORDER BY CreateTime DESC"))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@carId", carId);
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <VehicleTypeCertificationAuditLogModel>().ToList());
}
}
}
public static DataTable GetTousuLog(string objectId, string objectType)
{
var conn = ConfigurationManager.ConnectionStrings["Tuhu_log"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
string sql =
@"SELECT * FROM Tuhu_log..TousuConfigLog WITH(NOLOCK) WHERE ObjectId=@ObjectId AND ObjectType=@ObjectType ORDER BY PKID DESC";
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@ObjectId", objectId);
cmd.Parameters.AddWithValue("@ObjectType", objectType);
return(dbHelper.ExecuteDataTable(cmd));
}
}
}
private string CheckPIDSamePriceInOtherActivity(ActivityProduct product)
{
var conn = ConfigurationManager.ConnectionStrings["Gungnir"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new Tuhu.Component.Common.SqlDbHelper(conn))
{
var cmd = new SqlCommand(@"SELECT FS.ActivityID,FSP.Price,FSP.PID
FROM Activity..tbl_FlashSale AS FS WITH ( NOLOCK )
JOIN Activity..tbl_FlashSaleProducts AS FSP WITH ( NOLOCK )
ON FSP.ActivityID = FS.ActivityID
WHERE FS.EndDateTime > GETDATE()
AND FSP.PID = @PID
AND FSP.Price <> @Price
AND FS.ActivityID<>@ActivityID
AND ActiveType IN ( 0, 1 )" );
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@PID", product.PID);
cmd.Parameters.AddWithValue("@Price", product.Price);
cmd.Parameters.AddWithValue("@ActivityID", product.ActivityID);
var dt = dbHelper.ExecuteDataTable(cmd);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
var wrongMsg = "";
foreach (DataRow item in dt.Rows)
{
wrongMsg += "PID:" + item["PID"].ToString() + " 活动ID:" + item["ActivityID"].ToString() + " 价格:" + Convert.ToDouble(item["Price"]).ToString("C") + "<br/>";
}
return(wrongMsg);
}
}