/// <summary>
/// 添加 Customer
/// </summary>
public bool CustomerInsert(Customer model)
{
string sql = @"
INSERT INTO Customer
(
Id,CustomerName,AddTime,IsDelete,CustomerLevel,
PhoneNo,Token,VipPrice,Remark
)
VALUES
(
@Id,@CustomerName,@AddTime,@IsDelete,@CustomerLevel,
@PhoneNo,@Token,@VipPrice,@Remark
)
";
DynamicParameters param = new DynamicParameters();
param.Add("@Id", model.Id, DbType.Guid);
param.Add("@CustomerName", model.CustomerName, DbType.String);
param.Add("@AddTime", model.AddTime, DbType.DateTime);
param.Add("@IsDelete", model.IsDelete, DbType.Boolean);
param.Add("@CustomerLevel", model.CustomerLevel, DbType.Byte);
param.Add("@PhoneNo", model.PhoneNo, DbType.String);
param.Add("@Token", model.Token, DbType.String);
param.Add("@VipPrice", model.VipPrice, DbType.Double);
param.Add("@Remark", model.Remark, DbType.String);
var result = DataBaseAccessCommand.ExecuteCommand(sql, param);
return result < 0 ? false : true;
}
public static async Task<long> Insert(Device device)
{
const string commandText = @" Insert into Devices (Id, ClientId, RequestDeviceId, IsActived, DeviceKey, DeviceSecret, SerialNumber, IMEI, Manufacturer, Model, Platform, PlatformVersion, DeviceName, DeviceDescription)
values (@id, @clientId, @requestDeviceId, @isActived, @deviceKey, @deviceSecret, @serialNumber, @iMEI, @manufacturer, @model, @platform, @platformVersion, @deviceName, @deviceDescription)";
var parameters = new DynamicParameters();
if (device.Id > 0)
parameters.Add("id", device.Id);
else
parameters.Add("id",0);
parameters.Add("clientId", device.ClientId);
parameters.Add("requestDeviceId", device.RequestDeviceId);
parameters.Add("isActived", device.IsActived ? 1 : 0);
parameters.Add("deviceKey", device.DeviceKey);
parameters.Add("deviceSecret", device.DeviceSecret);
parameters.Add("serialNumber", device.SerialNumber);
parameters.Add("iMEI", device.IMEI);
parameters.Add("manufacturer", device.Manufacturer);
parameters.Add("model", device.Model);
parameters.Add("platform", device.Platform);
parameters.Add("platformVersion", device.PlatformVersion);
parameters.Add("deviceName", device.DeviceName);
parameters.Add("deviceDescription", device.DeviceDescription);
long results;
using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
{
var data = await conn.QueryAsync<long>(commandText, parameters);
results = data.FirstOrDefault();
}
return results;
}
public void a_menu_is_good()
{
//Arrange
string SqlQuery;
//IEnumerable<SearchTime> fav;
SearchTime fav;
Stopwatch stopwatch = new Stopwatch();
//act
stopwatch.Start();
using (DbConnection sqlConnection = GetOpenConnection(connection))
{
//sqlConnection.Open();
SqlQuery = "Web_MainFormSearch";
var p = new DynamicParameters();
p.Add("@SearchTerm", "Ray Stantz");
p.Add("@SearchGhost", true);
p.Add("@SearchVehicles", true);
//p.Add("@RetVal", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
fav = sqlConnection.Query<SearchTime>(SqlQuery, p, commandType: CommandType.StoredProcedure).First();
}
stopwatch.Stop();
int differ = (int)stopwatch.ElapsedMilliseconds;
//Asserts
Assert.Greater(fav.SearchName.Length, 1);
Assert.Greater(fav.GroupType.Length, 1);
//Assert.Greater(1200, differ);
}
public static async Task<long> Update(Device device)
{
var parameters = new DynamicParameters();
const string commandText = "Update Devices Set " + "ClientId = @clientId, " + "IsActived = @isActived, " +
"DeviceKey = @deviceKey, " + "DeviceSecret = @deviceSecret, " +
"SerialNumber = @serialNumber, " + "IMEI = @iMEI, " + "Manufacturer = @manufacturer, " +
"Model = @model, " + "Platform = @platform, " + "PlatformVersion = @platformVersion, " +
"DeviceName = @deviceName, " + "DeviceDescription = @deviceDescription " +
"where Id = @id";
parameters.Add("id", device.Id);
parameters.Add("clientId", device.ClientId);
parameters.Add("@isActived", (device.IsActived ? 1 : 0));
parameters.Add("deviceKey", device.DeviceKey);
parameters.Add("deviceSecret", device.DeviceSecret);
parameters.Add("serialNumber", device.SerialNumber);
parameters.Add("iMEI", device.IMEI);
parameters.Add("manufacturer", device.Manufacturer);
parameters.Add("model", device.Model);
parameters.Add("platform", device.Platform);
parameters.Add("platformVersion", device.PlatformVersion);
parameters.Add("@deviceName", device.DeviceName);
parameters.Add("deviceDescription", device.DeviceDescription);
long results;
using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
{
var data = await conn.QueryAsync<long>(commandText, parameters);
results = data.FirstOrDefault();
}
return results;
}
public void TestSave()
{
var cedarContext = Cedar.CedarAppStore.Instance.GetContextOf("IGD");
long uid = 0;
if (cedarContext.IsSetupSchemaRequired)
{
cedarContext.SetupSchema(new ShardStartegyData() { StrategyType = Strategy.Sequential });
}
uid = cedarContext.CurrentShard;
long? uId = 0;
var worker = new Cedar.IdWorker(uid);
uId = worker.GetUniqueId();
using (ICedarSession cedarSession = new Cedar.AppContext("IGD").GetSession(uId.Value))
{
var parameters = new DynamicParameters();
parameters.Add("firstName", "TArun");
parameters.Add("lastName", "Kumar");
cedarSession.Insert("usp_addemployee", parameters, commandType: Cedar.CommandType.StoredProcedure);
}
}
/// <summary>
/// 论坛版块添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static ForumType ForumType_ADD(ForumType model)
{
try
{
using (var conn = DbHelper.CCService())
{
var p = new DynamicParameters();
p.Add("@ForumTypeID", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@OCID", model.OCID);
p.Add("@CourseID", model.CourseID);
p.Add("@Title", model.Title);
p.Add("@IsEssence", model.IsEssence);
p.Add("@Brief", model.Brief);
p.Add("@TeachingClassID", model.TeachingClassID);
p.Add("@IsPublic", model.IsPublic);
p.Add("@UserID", model.UserID);
conn.Execute("ForumType_ADD", p, commandType: CommandType.StoredProcedure);
model.ForumTypeID = p.Get<int>("@ForumTypeID");
return model;
}
}
catch (Exception e)
{
return null;
}
}
public OverviewDataModel GetReleaseOverviewData(string includedReleasePathIDs)
{
var data = new OverviewDataModel { LastRefresh = DateTime.Now };
string[] pathIDs = includedReleasePathIDs != null ? includedReleasePathIDs.Split(new[] { ',' }) : null;
DynamicParameters queryArguments = new DynamicParameters();
if (pathIDs != null)
{
for (int paramNo = 0; paramNo < pathIDs.Length; paramNo++)
{
queryArguments.Add(String.Format("p{0}", paramNo), pathIDs[paramNo]);
}
}
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ReleaseManagement"].ConnectionString))
{
var sql = GenerateOverviewSQL(pathIDs != null ? pathIDs.Length : 0);
using (var multi = connection.QueryMultiple(sql, queryArguments))
{
data.ReleasePaths = multi.Read<ReleasePath>().ToList();
data.Stages = multi.Read<OverviewStage>().ToList();
data.StageDetails = multi.Read<OverviewStageDetails>().ToList();
data.ReleaseComponents = multi.Read<Component>().ToList();
}
}
return data;
}
public IList<CommonData> GetDropDownData(string type = "")
{
IList<CommonData> res = null;
try
{
var sql = "[dbo].[GetDropDownData]";
DynamicParameters param = new DynamicParameters();
param.Add("type", type);
cn.Open();
res = cn.Query<CommonData>(sql, param, commandType: CommandType.StoredProcedure).ToList();
cn.Close();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
return res;
}
public void Dispose()
{
if (_completed) return;
_completed = true;
var parameters = new DynamicParameters();
parameters.Add("@Resource", _resource);
parameters.Add("@LockOwner", LockOwner);
parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
_connection.Execute(
@"sp_releaseapplock",
parameters,
commandType: CommandType.StoredProcedure);
var releaseResult = parameters.Get<int>("@Result");
if (releaseResult < 0)
{
throw new SqlServerDistributedLockException(
String.Format(
"Could not release a lock on the resource '{0}': Server returned the '{1}' error.",
_resource,
releaseResult));
}
}
public void UploadExcelFromSqlToDropBox(string savedQuery, string sqlscript, string targetpath, string filename)
{
using (var db2 = NewDataContext())
{
var accesstoken = db2.Setting("DropBoxAccessToken", ConfigurationManager.AppSettings["DropBoxAccessToken"]);
var script = db2.Content(sqlscript, "");
if (!script.HasValue())
throw new Exception("no sql script found");
var p = new DynamicParameters();
foreach (var kv in dictionary)
p.Add("@" + kv.Key, kv.Value);
if (script.Contains("@qtagid"))
{
int? qtagid = null;
if (savedQuery.HasValue())
{
var q = db2.PeopleQuery2(savedQuery);
var tag = db2.PopulateSpecialTag(q, DbUtil.TagTypeId_Query);
qtagid = tag.Id;
}
p.Add("@qtagid", qtagid);
}
var bytes = db2.Connection.ExecuteReader(script, p).ToExcelBytes(filename);
var wc = new WebClient();
wc.Headers.Add($"Authorization: Bearer {accesstoken}");
wc.Headers.Add("Content-Type: application/octet-stream");
wc.Headers.Add($@"Dropbox-API-Arg: {{""path"":""{targetpath}/{filename}"",""mode"":""overwrite""}}");
wc.UploadData("https://content.dropboxapi.com/2-beta-2/files/upload", bytes);
}
}
public IEnumerable<dynamic> QuerySql(string sql, object p1, Dictionary<string, string> d)
{
var cn = GetReadonlyConnection();
var parameters = new DynamicParameters();
parameters.Add("@p1", p1 ?? "");
if (d != null)
foreach (var kv in d)
parameters.Add("@" + kv.Key, kv.Value);
if (sql.Contains("@UserPeopleId"))
parameters.Add("@UserPeopleId", data.PeopleId ?? Util.UserPeopleId);
if (sql.Contains("@CurrentOrgId"))
parameters.Add("@CurrentOrgId", data.OrgId ?? db.CurrentOrgId0);
if (sql.Contains("@BlueToolbarTagId"))
if (dictionary.ContainsKey("BlueToolbarGuid"))
{
var guid = (dictionary["BlueToolbarGuid"] as string).ToGuid();
if (!guid.HasValue)
throw new Exception("missing BlueToolbar Information");
var j = db.PeopleQuery(guid.Value).Select(vv => vv.PeopleId).Take(1000);
var tag = db.PopulateTemporaryTag(j);
parameters.Add("@BlueToolbarTagId", tag.Id);
}
return cn.Query(sql, parameters, commandTimeout: 300);
}
private Customer AddOrUpdateCustomer(Customer toAddOrUpdate)
{
Customer toReturn = null;
try
{
using (var msSql = DBController.GetDBConnection())
{
DynamicParameters p = new DynamicParameters();
p.Add("CustomerID", toAddOrUpdate.CustomerID);
p.Add("FirstName", toAddOrUpdate.FirstName);
p.Add("LastName", toAddOrUpdate.LastName);
p.Add("MiddleName", toAddOrUpdate.MiddleName);
p.Add("EmailAddress", toAddOrUpdate.EmailAddress);
p.Add("Street", toAddOrUpdate.Street);
p.Add("Housenumber", toAddOrUpdate.Housenumber);
p.Add("PostalCode", toAddOrUpdate.PostalCode);
p.Add("City", toAddOrUpdate.City);
toReturn = msSql.Query<Customer>("up_AddOrUpdateCustomer", param: p, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
catch (Exception ex)
{
logger.Error("Error retrieving DB customer", ex);
}
return toReturn;
}
public ILoginTableModal GetLogin(string procedureName)
{
var dynamic = new DynamicParameters();
dynamic.Add(Constants.User, Modal.UserName);
dynamic.Add(Constants.Pass, Modal.Password);
return GetQueryiedFirst<LoginTableModal>(procedureName, dynamic);
}
public void EditUser(EditUserRequest editUser)
{
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@UserId", editUser.LmsUser.UserId);
p.Add("@IsStudent", editUser.IsStudent ? 1 : 0);
p.Add("@IsParent", editUser.IsParent ? 1 : 0);
p.Add("@IsTeacher", editUser.IsTeacher ? 1 : 0);
p.Add("@IsAdmin", editUser.IsAdmin ? 1 : 0);
cn.Execute("spUpdateUserRoles", p, commandType: CommandType.StoredProcedure);
var p2 = new DynamicParameters();
p2.Add("@UserId", editUser.LmsUser.UserId);
p2.Add("@LastName", editUser.LmsUser.LastName);
p2.Add("@FirstName", editUser.LmsUser.FirstName);
p2.Add("@Email", editUser.LmsUser.Email);
p2.Add("@SuggestedRole", editUser.LmsUser.SuggestedRole);
p2.Add("@GradeLevelId", editUser.LmsUser.GradeLevelId);
cn.Execute("spUpdateUserDetails", p2, commandType: CommandType.StoredProcedure);
}
}
// Add new team to database. Team object receieves a TeamID
public void AddTeam(Team team)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
try
{
p.Add("TeamName", team.TeamName);
p.Add("ManagerName", team.ManagerName);
p.Add("LeagueID", team.LeagueID);
p.Add("TeamID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("CreateTeam", p, commandType: CommandType.StoredProcedure);
team.TeamID = p.Get<int>("TeamID");
}
//catch (Exception e)
//{
// var ep = new DynamicParameters();
// ep.Add("ExceptionType", e.GetType());
// ep.Add("ExceptionMessage", e.Message);
// ep.Add("Input", String.Format("TeamName = {0}, ManagerName = {1}, LeagueID = {2}",
// team.TeamName, team.ManagerName, team.LeagueID));
// cn.Execute("AddError", ep, commandType: CommandType.StoredProcedure);
//}
finally
{
cn.Close();
}
}
}
public IEnumerable<EventData> Get(Guid aggregateRootId)
{
var @params = new DynamicParameters();
@params.Add("@AggregateRootId", aggregateRootId);
return _context.Connection.Query<EventData>("[EventStore].[GetEvents]", @params, _databaseTransaction, commandType: CommandType.StoredProcedure);
}
public bool CanAddPlaylist(int userId)
{
try
{
using (var smartTimer = new SmartTimer((x, u) => GatewayLoggerInfo("Exit CanAddPlaylist", userId, x.Elapsed)))
{
GatewayLoggerInfo("CanAddPlaylist", userId);
using (var connection = _provider.Create())
{
var parameters = new DynamicParameters();
parameters.Add("@userId", userId);
parameters.Add("@canAdd", dbType: DbType.Boolean, direction: ParameterDirection.Output);
connection.Execute("user.CanAddPlaylist", parameters, commandType: CommandType.StoredProcedure);
return parameters.Get<bool>("@canAdd");
}
}
}
catch (System.Exception ex)
{
logger.Error(ex);
throw;
}
}
public static ScoreManageInfo ScoreManageInfo_Add(ScoreManageInfo model)
{
try
{
using (var conn = DbHelper.CCService())
{
var p = new DynamicParameters();
p.Add("@output", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@OCID", model.OCID);
p.Add("@UserID", model.UserID);
p.Add("@UserName", model.UserName);
p.Add("@CourseID", model.CourseID);
p.Add("@StartDate", model.StartDate);
p.Add("@EndDate", model.EndDate);
p.Add("@Name", model.Name);
p.Add("@ScoreTypeID", model.ScoreTypeID);
conn.Execute("Score_Test_ADD", p, commandType: CommandType.StoredProcedure);
model.TestID = p.Get<int>("output");
return model;
}
}
catch (Exception e)
{
return null;
}
}
public int Insert(GroupSaleVehicle poco, IDbConnection connection)
{
var dynamicParams = new DynamicParameters(mapper.Map(poco));
dynamicParams.Add("@VehicleID", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("InsertSaleVehicle7", dynamicParams ,commandType: CommandType.StoredProcedure);
return dynamicParams.Get<int>("@VehicleID");
}
public SqlServerDistributedLock(string resource, TimeSpan timeout, IDbConnection connection)
{
if (String.IsNullOrEmpty(resource)) throw new ArgumentNullException("resource");
if (connection == null) throw new ArgumentNullException("connection");
_resource = resource;
_connection = connection;
var parameters = new DynamicParameters();
parameters.Add("@Resource", _resource);
parameters.Add("@DbPrincipal", "public");
parameters.Add("@LockMode", LockMode);
parameters.Add("@LockOwner", LockOwner);
parameters.Add("@LockTimeout", timeout.TotalMilliseconds);
parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
connection.Execute(
@"sp_getapplock",
parameters,
commandType: CommandType.StoredProcedure);
var lockResult = parameters.Get<int>("@Result");
if (lockResult < 0)
{
throw new SqlServerDistributedLockException(
String.Format(
"Could not place a lock on the resource '{0}': {1}.",
_resource,
LockErrorMessages.ContainsKey(lockResult)
? LockErrorMessages[lockResult]
: String.Format("Server returned the '{0}' error.", lockResult)));
}
}
public bool InsertProductData(ProductDetails objPD)
{
bool res = false;
try
{
var sql = "[dbo].[InsertProductData]";
DynamicParameters param = new DynamicParameters();
param.Add("ProductName", objPD.ProductName);
param.Add("ProductType", objPD.ProductType);
param.Add("Unit", objPD.Unit);
param.Add("ProductRate", objPD.ProductRate);
param.Add("Descrption", objPD.Descrption);
param.Add("CreatedDate", objPD.CreatedDate);
cn.Open();
res = Convert.ToBoolean(cn.Execute(sql, param, commandType: CommandType.StoredProcedure));
cn.Close();
}
catch (Exception ex)
{
}
finally
{
}
return res;
}
public void EditPage(StaticPage staticPage)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
try
{
p.Add("StaticPageID", staticPage.StaticPageID);
p.Add("ButtonName", staticPage.ButtonName);
p.Add("UserID", staticPage.UserID);
p.Add("Body", staticPage.Body);
cn.Execute("EditPage", p, commandType: CommandType.StoredProcedure);
}
//catch (Exception e)
//{
// // Write failure to database
// var ep = new DynamicParameters();
// ep.Add("ExceptionType", e.GetType());
// ep.Add("ExceptionMessage", e.Message);
// cn.Execute("AddError", ep, commandType: CommandType.StoredProcedure);
//}
finally
{
cn.Close();
}
}
}
public Movie Find(int id)
{
var dbArgs = new DynamicParameters();
dbArgs.Add("id", id);
return this.db.Query<Movie>("select * from movies where id=@id", dbArgs).First();
}
public void DeletePost(int postID)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
try
{
p.Add("PostID", postID);
cn.Execute("DeletePost", p, commandType: CommandType.StoredProcedure);
}
//catch (Exception e)
//{
// // Write failure to database
// var ep = new DynamicParameters();
// ep.Add("ExceptionType", e.GetType());
// ep.Add("ExceptionMessage", e.Message);
// cn.Execute("AddError", ep, commandType: CommandType.StoredProcedure);
//}
finally
{
cn.Close();
}
}
}
public IList<CommonData> GetCommonData(Hashtable ht)
{
IList<CommonData> res = null;
try
{
var sql = "[dbo].[GetCommonData]";
DynamicParameters param = new DynamicParameters();
param.Add("name", ht["name"]);
param.Add("value", ht["value"]);
param.Add("date", ht["date"]);
param.Add("type", ht["type"]);
param.Add("startrow", ht["startrow"]);
param.Add("endrow", ht["endrow"]);
cn.Open();
res = cn.Query<CommonData>(sql, param, commandType: CommandType.StoredProcedure).ToList();
cn.Close();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
return res;
}
public bool CheckLoginnameAvaliablity(string Loginname)
{
int count = 0;
bool res = false;
try
{
var sql = "select count(*) from dbo.[User] where LoginName = @Loginname";
DynamicParameters param = new DynamicParameters();
param.Add("Loginname", Loginname);
cn.Open();
count = cn.Query<int>(sql, param).SingleOrDefault();
cn.Close();
if (count > 0)
{
res = true;
}
else
{
res = false;
}
}
catch (Exception ex)
{
}
finally
{
}
return res;
}
public XStockViewModel GetStock(int page, int size, string stockCode, string stockName, string store, int type,
int category, string enable)
{
var model = new XStockViewModel();
var paramss = new DynamicParameters();
paramss.Add("page", page);
paramss.Add("size", size);
paramss.Add("stockCode", stockCode);
paramss.Add("stockName", stockName);
paramss.Add("store", store);
paramss.Add("type", type);
paramss.Add("stockName", stockName);
paramss.Add("category", category);
paramss.Add("enable", enable);
paramss.Add("out", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (var sql = GetSqlConnection())
{
var data = sql.Query<XStock>("XGetListStock", paramss, commandType: CommandType.StoredProcedure);
sql.Close();
model.StockVs = data.ToList();
var total = paramss.Get<int>("out");
model.TotalRecords = total;
var totalTemp = Convert.ToDecimal(total) / Convert.ToDecimal(size);
model.TotalPages = Convert.ToInt32(Math.Ceiling(totalTemp));
}
return model;
}
public bool UpdateUnitWiseProductAllotment(Hashtable ht)
{
int res = 0;
try
{
var sql = "[dbo].[UnitWiseProductAllotmentUpdate]";
DynamicParameters param = new DynamicParameters();
param.Add("@userid", ht["userid"]);
param.Add("@ip", ht["ip"]);
param.Add("@id", ht["id"]);
param.Add("@field", ht["field"]);
param.Add("@value", ht["value"]);
cn.Open();
res = cn.Execute(sql, param, commandType: CommandType.StoredProcedure);
cn.Close();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
return Convert.ToBoolean(res);
}
public Region Retrieve(int id)
{
var p = new DynamicParameters();
p.Add("RegionID", id);
return _cn.Query<Region>("SELECT * FROM Region WHERE RegionID = @RegionID", p).SingleOrDefault();
}
public static SurveyQuestion SurveyQuestion_Edit(SurveyQuestion model)
{
try
{
using (var conn = DbHelper.CCService())
{
var p = new DynamicParameters();
p.Add("@QuestionID", model.QuestionID);
p.Add("@SurveyID", model.SurveyID);
p.Add("@Type", model.Type);
p.Add("@Conten", model.Conten);
p.Add("@IsMust", model.IsMust);
p.Add("@IsRandom", model.IsRandom);
p.Add("@MinSel", model.MinSel);
p.Add("@MaxSel", model.MaxSel);
p.Add("@UserID", model.UserID);
p.Add("@IsSample", model.IsSample);
p.Add("@ChoiceItems", model.ChoiceItems);
p.Add("@ChildQuestions", model.ChildQuestions);
p.Add("@Orde",model.Orde);
conn.Execute("SurveyQuestion_Edit", p, commandType: CommandType.StoredProcedure);
//model.QuestionID = p.Get<Int32>("QuestionID");
return model;
}
}
catch (Exception e)
{
return null;
}
}
public T Load <T>(string strSQL, Dapper.DynamicParameters pars)
{
using (SqlConnection con = new SqlConnection(_conString))
{
try
{
return(con.Query <T>(strSQL, pars).FirstOrDefault());
}catch (Exception e)
{
log_error(e, strSQL, pars);
return(default(T));
}
}
}
private void EvaluateExpression()
{
if (_evaluated)
{
return;
}
var state = new SqlBuilderState();
var expression = _expression;
while (expression != null)
{
if (expression is ConstantExpression)
{
var constantExpression = expression as ConstantExpression;
expression = null; // end of the line
// should be an IQueryable<T> where T is the resulting table
state.ElementType = constantExpression.Type.GetGenericArguments()[0];
}
else if (expression is MethodCallExpression)
{
var methodCallExpression = expression as MethodCallExpression;
if (methodCallExpression.Arguments.Count == 0)
{
throw new NotSupportedException("Method call expression must have at least one argument");
}
// up the stack of expressions
expression = methodCallExpression.Arguments[0];
// process each method
LinqProcessor.Process(methodCallExpression.Method.Name).Interpret(methodCallExpression, state);
}
else
{
throw new NotImplementedException("Expression is not handled");
}
}
// NICE: put the field list into a concurrent shared dictionary
_sql = BuildSqlStatement(state);
if (state.HasParameters)
{
_parameters = state.Parameters;
}
_evaluated = true;
}
/// <summary>
/// 检查历史库是否存在数据
/// </summary>
/// <returns></returns>
protected bool IsExistsInDBHistory(DateTime bTime, DateTime eTime, Guid userId)
{
string sql = @"SELECT count(0) FROM UserPrize WITH(NOLOCK)
WHERE (TypeId IN (4,11,13,14) AND IsReceive=1 and ReceiveDate between @btime and @etime or TypeId=3 and IsUsed=1 and UseDate between @btime and @etime) AND UserId=@UserID";
var para = new Dapper.DynamicParameters();
para.Add("@btime", bTime);
para.Add("@etime", eTime);
para.Add("@UserID", userId);
return
(TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <int>(TdConfig.ApplicationName, TdConfig.DBHistoryRead, sql,
ref para) > 0);
}
private bool QueryDetailSql(DetailRequestViewModel detailRequest)
{
// 設定主SQL, _sqlStr 中不可以包含 ORDER BY
_sqlStr = new StringBuilder();
//_sqlStr.Append("主要SQL語法 ");
//設定排序SQL
//_sqlOrderByStr = detailRequest.GetOrderByFromSort(DetailRequestViewModel.EnumSort.DEFAULT);
//設定參數
_sqlParams = new Dapper.DynamicParameters();
//_sqlParams.Add("參數", detailRequest.參數);
return(true);
}
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.lnItemGroup d, string userID = "", SqlTransaction transac = null)
{
var param = new Dapper.DynamicParameters();
param.Add("@Seq_Itemgroup", d.seq_itemgroup);
param.Add("@Item_Group", d.itemgroup);
param.Add("@Description", d.description);
string cmd = "INSERT INTO mcis.dbo.lnItemGroup " +
$" (Seq_Itemgroup, Item_Group, Description) " +
$"VALUES(@Seq_Itemgroup, @Item_Group, @Description); " +
$"SELECT SCOPE_IDENTITY();";
return(ExecuteScalar <int>(cmd, param));
}
//用户详细信息
private void GetUserDetailInfo()
{
// this.UserDetail = new UserBLL().GetUserDetailInfoByProjectId(this.model.Id, this.model.UserId.ToString());
userDetailInfoInfo UserDetail = null;
if (model.Type == 9 || model.Type == 26 || model.Type == 40)//车贷
{
string strSQL = @"SELECT Id,ProjectID,CarBrand,CarOrigin,CarType,ISNULL(CarPrice,0) AS CarPrice,CarKM,CarIsBigRepair,CarPlace,AddDate,CarDisplacement
FROM dbo.UserCarDetailInfo WITH(NOLOCK) WHERE ProjectID=@ProjectId";
Dapper.DynamicParameters par = new Dapper.DynamicParameters();
par.Add("@ProjectId", projectId);
mUserCarDetailInfoInfo = PublicConn.QuerySingle <UserCarDetailInfoInfo>(strSQL, ref par);
if (mUserCarDetailInfoInfo == null)
{
mUserCarDetailInfoInfo = new UserCarDetailInfoInfo();
UserDetail = new UserBLL().GetUserDetailInfoByProjectId(projectId.Value, model.UserId.Value.ToString());
mUserCarDetailInfoInfo.CarBrand = UserDetail.CarBrand;
mUserCarDetailInfoInfo.CarIsBigRepair = UserDetail.CarIsBigRepair.HasValue ? UserDetail.CarIsBigRepair.Value : false;
mUserCarDetailInfoInfo.CarKM = UserDetail.CarKM.HasValue ? UserDetail.CarKM.Value : 0;
mUserCarDetailInfoInfo.CarOrigin = UserDetail.CarOrigin;
mUserCarDetailInfoInfo.CarPlace = UserDetail.CarOrigin;
mUserCarDetailInfoInfo.CarPrice = UserDetail.CarPrice.HasValue ? UserDetail.CarPrice.Value : 0;
mUserCarDetailInfoInfo.CarType = UserDetail.CarType;
}
}
else
{//房贷
string strSQL = @"SELECT Id,ProjectID,HouseBuyDate,ISNULL(HouseArea,0) AS HouseArea,ISNULL(HousePrice,0) AS HousePrice,ISNULL(HouseYears,0)AS HouseYears,ISNULL(IsHouseRenovation,0) AS IsHouseRenovation,ISNULL(IsHouseLoan,0) AS IsHouseLoan
,ISNULL(HouseLoanAmount,0) AS HouseLoanAmount,ISNULL(HouseLoanYears,0) AS HouseLoanYears,AddDate FROM dbo.UserHouseDetailInfo WITH(NOLOCK) WHERE ProjectID=@ProjectId";
Dapper.DynamicParameters par = new Dapper.DynamicParameters();
par.Add("@ProjectId", projectId);
mUserHouseDetailInfoInfo = PublicConn.QuerySingle <UserHouseDetailInfoInfo>(strSQL, ref par);
if (mUserHouseDetailInfoInfo == null)
{
mUserHouseDetailInfoInfo = new UserHouseDetailInfoInfo();
UserDetail = new UserBLL().GetUserDetailInfoByProjectId(projectId.Value, model.UserId.Value.ToString());
mUserHouseDetailInfoInfo.HouseBuyDate = UserDetail.HouseBuyDate.Value;
mUserHouseDetailInfoInfo.HouseArea = UserDetail.HouseArea.HasValue ? UserDetail.HouseArea.Value : 0;
mUserHouseDetailInfoInfo.HousePrice = UserDetail.HousePrice.HasValue ? UserDetail.HouseArea.Value : 0;
mUserHouseDetailInfoInfo.HouseYears = UserDetail.HouseYears.HasValue ? UserDetail.HouseYears.Value : 0;
mUserHouseDetailInfoInfo.IsHouseRenovation = UserDetail.IsHouseRenovation.HasValue ? UserDetail.IsHouseRenovation.Value : false;
mUserHouseDetailInfoInfo.IsHouseLoan = UserDetail.IsHouseLoan.HasValue ? UserDetail.IsHouseLoan.Value : false;
mUserHouseDetailInfoInfo.HouseLoanAmount = UserDetail.HouseLoanAmount.HasValue ? UserDetail.HouseArea.Value : 0;
mUserHouseDetailInfoInfo.HouseLoanYears = UserDetail.HouseLoanYears.HasValue ? UserDetail.HouseLoanYears.Value : 0;
}
}
}
public IEnumerable <T> GetList <T>(string strSQL, Dapper.DynamicParameters pars)
{
using (SqlConnection con = new SqlConnection(_conString))
{
try
{
return(con.Query <T>(strSQL, pars));
} catch (Exception e)
{
log_error(e, strSQL, pars);
return(null);
}
}
}
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.msWorkStationGrp_Spare d, string userID = "")
{
var param = new Dapper.DynamicParameters();
param.Add("@WorkStationGrpCd", d.WorkStationGrpCd);
param.Add("@WorkStationGrpCd_Spare", d.WorkStationGrpCd_Spare);
param.Add("@User_Id", d.User_Id);
param.Add("@User_date", d.User_date);
string cmd = "INSERT INTO mcis.dbo.msWorkStationGrp_Spare " +
$" (WorkStationGrpCd, WorkStationGrpCd_Spare, User_Id, User_date) " +
$"VALUES(@WorkStationGrpCd, @WorkStationGrpCd_Spare, @User_Id, @User_date); " +
$"SELECT SCOPE_IDENTITY();";
return(ExecuteScalar <int>(cmd, param));
}
//获取复投宝的待收本息
public Tuple <decimal, decimal> GetFTBDueInAmountInterest(Guid userId)
{
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@UserId", userId);
dyParams.Add("@totalAmount", 0, DbType.Decimal, ParameterDirection.Output, 18, null, 2);
dyParams.Add("@totalInterest", 0, DbType.Decimal, ParameterDirection.Output, 18, null, 2);
string strSQL = "select @totalAmount=RecoverBorrowOut, @totalInterest=DueComeInterest from We_FundAccountInfo with(nolock) where UserId=@UserId";
PublicConn.ExecuteTD(PublicConn.DBWriteType.Read, strSQL, ref dyParams);
Tuple <decimal, decimal> result = new Tuple <decimal, decimal>(dyParams.Get <decimal?>("@totalAmount") ?? 0, dyParams.Get <decimal?>("@totalInterest") ?? 0);
return(result);
}
private bool QuerySearchListSql(SearchInfoViewModel searchInfo)
{
// 設定主SQL, _sqlStr 中不可以包含 ORDER BY
_sqlStr = new StringBuilder();
//_sqlStr.Append(" 主要SQL語法 ");
//設定排序SQL
_sqlOrderByStr = searchInfo.GetOrderByFromSort(SearchInfoViewModel.EnumSort.DEFAULT);
//設定參數
_sqlParams = new Dapper.DynamicParameters();
//_sqlParams.Add("參數", searchInfo.參數);
return(true);
}
protected void LoadData()
{
ProjectBLL bll = new ProjectBLL();
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@SubscribeId", TransferId);
dyParams.Add("@projectId", null, DbType.Guid, ParameterDirection.Output, 36);
dyParams.Add("@addDate", null, DbType.DateTime, ParameterDirection.Output, 0);
dyParams.Add("@refundedMonths", null, DbType.Int32, ParameterDirection.Output, 0);
string strSQL = @"select @projectId=ProjectId, @addDate=AddDate,@refundedMonths= RefundedMonths FROM dbo.Subscribe WITH(NOLOCK) where Id=@SubscribeId";
PublicConn.ExecuteTD(PublicConn.DBWriteType.Read, strSQL, ref dyParams);
Guid projectId = dyParams.Get <Guid>("@projectId");
//获取项目信息
model = bll.GetProjectDetailInfo(projectId);
DateTime investDate = dyParams.Get <DateTime>("@addDate");
int refundedMonths = dyParams.Get <Int32>("@refundedMonths");
string sql = string.Empty;
if (model.RepaymentType.Value == 1)//到期还本息
{
sql = "SELECT dateDiff(day,@BeginDate,getdate())-1";
}
else
{
sql = "SELECT dateDiff(day,dbo.f_GetRepaymentAdvance_Date(@BeginDate,@Month),getdate())";
}
dyParams = new Dapper.DynamicParameters();
dyParams.Add("@BeginDate", investDate);
dyParams.Add("@Month", refundedMonths);
DiffDay = PublicConn.QuerySingle <int>(sql, ref dyParams);
WebSettingBLL websetbll = new WebSettingBLL();
WebSettingEntity = websetbll.GetWebSettingInfo("588B23C6-56EC-40C1-80A6-09B19C6F21E1");
if (WebSettingEntity == null)
{
WebSettingEntity = new WebSettingInfo();
}
zqzrRateSet = websetbll.GetWebSettingInfo("63C10EF7-3961-4CA3-B277-147AAD8E7D42");
if (zqzrRateSet.Param1Value == "1" && DateTime.Parse(zqzrRateSet.Param2Value) < DateTime.Now && DateTime.Now < DateTime.Parse(zqzrRateSet.Param3Value))
{
IsOpenRateLimit = true;
}
}
public static List <LabelDataModel> LoadLabelData(string itemCode)
{
var p = new Dapper.DynamicParameters();
p.Add("@ItemCode", itemCode);
string sql = @"SELECT ili.loi_itemdesc AS PartDescription,
smbu.upc_no AS UPC,
iii.ibu_category AS BowModel
FROM itm_loi_itemhdr ili
INNER JOIN SAV_MINI_BOM_UDS smbu ON ili.loi_itemcode = smbu.Item_code
INNER JOIN itm_ibu_itemvarhdr iii on ili.loi_itemcode = iii.ibu_itemcode
WHERE ili.loi_itemcode = @ItemCode;";
return(SQLDataAccess.LoadDataSCMDB <LabelDataModel>(sql, p));
}
//获取交易额数据
protected List <InvestDayReport> GetInvestDayReport(int dayCount)
{
string result = string.Empty;
//if (IsRedis == "1")
//{
// result = mRedis.InvestDayReport("", true);
//}
List <InvestDayReport> list = new List <InvestDayReport>();
if (result != null)
{
list = JsonService.DeSerializeObject <List <InvestDayReport> >(result);
}
if (string.IsNullOrEmpty(result) || list.Where(m => m.Date.Day == DateTime.Now.AddDays(-1).Day).Count() == 0 || DateTime.Now < DateTime.Parse(DateTime.Now.ToShortDateString() + " 00:15:00"))
{
string sql = "select CONVERT(varchar(5),DayDate,110) as Date,Total as Amount from InvestDayReport(nolock) where DayDate >=dateadd(day,@dayCout,getdate()) AND DayDate<getdate()";
Dapper.DynamicParameters param = new Dapper.DynamicParameters();
param.Add("@dayCout", dayCount);
list = TuanDai.DB.TuanDaiDB.Query <InvestDayReport>(TuanDai.WXApiWeb.TdConfig.ApplicationName, TuanDai.WXApiWeb.TdConfig.DBRead, sql, ref param);
List <InvestDayReportModel> listDQ = WebSiteDataClient.GetInvestDayReport(System.Math.Abs(dayCount));//定期理财交易数据
if (list.Any() && listDQ.Any())
{
foreach (var item in list)
{
foreach (var itemDQ in listDQ)
{
if (item.Date == itemDQ.Date)
{
item.Amount += itemDQ.Amount;
}
}
}
}
if (IsRedis == "1" && list != null && list.Count > 0)
{
mRedis.InvestDayReport(JsonService.SerializeObject <List <InvestDayReport> >(list), false);
}
}
else
{
list = JsonService.DeSerializeObject <List <InvestDayReport> >(result);
}
return(list);
}
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.MsDrawingPath d, string userID = "")
{
var param = new Dapper.DynamicParameters();
param.Add("@DrawingCd", d.DrawingCd);
param.Add("@Itemno", d.Itemno);
param.Add("@PathName", d.PathName);
param.Add("@PathLink", d.PathLink);
param.Add("@EditUser", d.EditUser);
param.Add("@EditDate", d.EditDate);
string cmd = "INSERT INTO mcis.dbo.MsDrawingPath " +
$" (DrawingCd, Itemno, PathName, PathLink, EditUser, EditDate) " +
$"VALUES(@DrawingCd, @Itemno, @PathName, @PathLink, @EditUser, @EditDate); " +
$"SELECT SCOPE_IDENTITY();";
return(ExecuteScalar <int>(cmd, param));
}
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.msWorkStation d, string userID = "")
{
var param = new Dapper.DynamicParameters();
param.Add("@WorkStationCd", d.WorkStationCd);
param.Add("@WorkStationNm", d.WorkStationNm);
param.Add("@WSProfit", d.WSProfit);
param.Add("@WSProfit_NM", d.WSProfit_NM);
param.Add("@SelProfit", d.SelProfit);
param.Add("@CANCELFLAG", d.CANCELFLAG);
string cmd = "INSERT INTO .dbo.msWorkStation " +
$" (WorkStationCd, WorkStationNm, WSProfit, WSProfit_NM, SelProfit, CANCELFLAG) " +
$"VALUES(@WorkStationCd, @WorkStationNm, @WSProfit, @WSProfit_NM, @SelProfit, @CANCELFLAG); " +
$"SELECT SCOPE_IDENTITY();";
return(ExecuteScalar <int>(cmd, param));
}
/// <summary>
/// 获取订单记录 GetOrderList
/// </summary>
/// <param name="productid">产品Id</param>
/// <param name="pageIndex">当前页数</param>
/// <param name="pageSize">页大小</param>
/// <returns>返回订单列表</returns>
public List <WeOrderInfo> GetOrderList(Guid productid, int pageIndex, int pageSize)
{
var args = new Dapper.DynamicParameters();
args.Add("@productId", productid);
args.Add("@pageIndex", pageIndex);
args.Add("@pageSize", pageSize);
string sqlText = @"select UserName,NickName,TelNo,Amount,OrderDate,RepeatInvestType,DeviceType from (
select u.UserName,u.NickName,u.TelNo,o.Amount,o.OrderDate,ROW_NUMBER() over(order by o.OrderDate desc) as rownum,
isnull(RepeatInvestType,0) as RepeatInvestType,o.DeviceType,o.statusid
from We_Order o with(nolock)
left join UserBasicInfo u with(nolock) on o.UserId=u.Id where o.productId=@productId) t
where t.statusid in (0,1,2,4) and t.rownum>(@pageIndex-1)*@pageSize and t.rownum<=@pageIndex*@pageSize";
List <WeOrderInfo> list = TuanDaiDB.Query <WeOrderInfo>(TdConfig.ApplicationName, TdConfig.DBRead, sqlText, ref args);
return(list);
}
/// <summary>
/// 定期待收
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
private decimal GetDQDueInMoney(Guid userId)
{
string sql = "select sum(isnull(Amount,0))+sum(ISNULL(InterestAmout,0)) from SubscribeDetail with(nolock) where SubscribeUserId=@userid; ";
var para = new Dapper.DynamicParameters();
para.Add("@userid", userId);
decimal dueInMoney =
TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <decimal?>(TdConfig.ApplicationName, "/BD/dqread", sql, ref para) ??
0;
sql =
"select sum(isnull(cost,0))+sum(isnull(interest,0)) from OverDueRecord where SubscribeUserId=@userid and IsBorrow=0;";
dueInMoney +=
TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <decimal?>(TdConfig.ApplicationName, "/BD/dqread", sql, ref para) ??
0;
return(dueInMoney);
}
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.msUserSequence d, string userID = "", SqlTransaction transac = null)
{
var param = new Dapper.DynamicParameters();
param.Add("@USERCODE", d.USERCODE);
param.Add("@STDATE", d.STDATE);
param.Add("@ENDATE", d.ENDATE);
param.Add("@USER_ID", d.USER_ID);
param.Add("@USER_DATE", d.USER_DATE);
param.Add("@EDIT_TYPE", d.EDIT_TYPE);
string cmd = "INSERT INTO mcis.dbo.msUserSequence " +
$" (USERCODE, STDATE, ENDATE, USER_ID, USER_DATE, EDIT_TYPE) " +
$"VALUES(@USERCODE, @STDATE, @ENDATE, @USER_ID, @USER_DATE, @EDIT_TYPE); " +
$"SELECT SCOPE_IDENTITY();";
return(ExecuteScalar <int>(cmd, param));
}
/// <summary>
/// 获取累计收益最大用户
/// </summary>
protected void GetMaxTotalIncomeInvestUser()
{
string sql = "select top 1 InterestAmount,NickName from dbo.RealTime_InvestRank with(nolock) where Adddate > dateadd(day,-30,getdate()) order by CONVERT(VARCHAR(10), Adddate,23) desc,InterestAmount desc";
var param = new Dapper.DynamicParameters();
_MaxIncomeUser = TuanDai.DB.TuanDaiDB.Query <MaxIncomeUser>(TdConfig.ApplicationName, TdConfig.DBStatisticsWrite, sql, ref param).FirstOrDefault();
if (_MaxIncomeUser == null)
{
_MaxIncomeUser = new MaxIncomeUser();
_MaxIncomeUser.InterestAmount = 0;
_MaxIncomeUser.NickName = "";
}
else
{
_MaxIncomeUser.NickName = GetNickNameString(_MaxIncomeUser.NickName);
}
}
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.mtWorkOrderH d, string userID = "")
{
var param = new Dapper.DynamicParameters();
param.Add("@WorkOrderID", d.WorkOrderID);
param.Add("@MchProjectID", d.MchProjectID);
param.Add("@DrawingCd", d.DrawingCd);
param.Add("@WorkOrderDesc", d.WorkOrderDesc);
param.Add("@QtyAmt", d.QtyAmt);
param.Add("@FMDeptCd", d.FMDeptCd);
param.Add("@CreatedDate", d.CreatedDate);
param.Add("@WorkOrderStatus", d.WorkOrderStatus);
param.Add("@FstRcvrWs", d.FstRcvrWs);
param.Add("@PrintFlg", d.PrintFlg);
param.Add("@AmtUnit", d.AmtUnit);
param.Add("@DocListNo", d.DocListNo);
param.Add("@MainWS", d.MainWS);
param.Add("@SendStock", d.SendStock);
param.Add("@USER_ID", d.USER_ID);
param.Add("@USER_DATE", d.USER_DATE);
param.Add("@Date_Open", d.Date_Open);
param.Add("@PDate_Cls", d.PDate_Cls);
param.Add("@RDate_cls", d.RDate_cls);
param.Add("@CreateSPAuto", d.CreateSPAuto);
param.Add("@DM_COST", d.DM_COST);
param.Add("@DM_FOH", d.DM_FOH);
param.Add("@SEQUENCE_COST", d.SEQUENCE_COST);
param.Add("@SEQUENCE_PROFIT", d.SEQUENCE_PROFIT);
param.Add("@TOTPRC", d.TOTPRC);
param.Add("@TOTCOST", d.TOTCOST);
param.Add("@DM_COST_VERSION", d.DM_COST_VERSION);
param.Add("@DEL_FLAG", d.DEL_FLAG);
param.Add("@MATDAYWANT", d.MATDAYWANT);
param.Add("@SEQUENCE_ACT", d.SEQUENCE_ACT);
param.Add("@TOTCOST_ACT", d.TOTCOST_ACT);
param.Add("@SEQUENCE_DIF", d.SEQUENCE_DIF);
param.Add("@PROFIT_BEG", d.PROFIT_BEG);
param.Add("@STATION_GRP", d.STATION_GRP);
string cmd = "INSERT INTO mcis.dbo.mtWorkOrderH " +
$" (WorkOrderID, MchProjectID, DrawingCd, WorkOrderDesc, QtyAmt, FMDeptCd, CreatedDate, WorkOrderStatus, FstRcvrWs, PrintFlg, AmtUnit, DocListNo, MainWS, SendStock, USER_ID, USER_DATE, Date_Open, PDate_Cls, RDate_cls, CreateSPAuto, DM_COST, DM_FOH, SEQUENCE_COST, SEQUENCE_PROFIT, TOTPRC, TOTCOST, DM_COST_VERSION, DEL_FLAG, MATDAYWANT, SEQUENCE_ACT, TOTCOST_ACT, SEQUENCE_DIF, PROFIT_BEG, STATION_GRP) " +
$"VALUES(@WorkOrderID, @MchProjectID, @DrawingCd, @WorkOrderDesc, @QtyAmt, @FMDeptCd, @CreatedDate, @WorkOrderStatus, @FstRcvrWs, @PrintFlg, @AmtUnit, @DocListNo, @MainWS, @SendStock, @USER_ID, @USER_DATE, @Date_Open, @PDate_Cls, @RDate_cls, @CreateSPAuto, @DM_COST, @DM_FOH, @SEQUENCE_COST, @SEQUENCE_PROFIT, @TOTPRC, @TOTCOST, @DM_COST_VERSION, @DEL_FLAG, @MATDAYWANT, @SEQUENCE_ACT, @TOTCOST_ACT, @SEQUENCE_DIF, @PROFIT_BEG, @STATION_GRP); " +
$"SELECT SCOPE_IDENTITY();";
return(ExecuteScalar <int>(cmd, param));
}
/// <summary>
/// We计划
/// </summary>
/// <returns></returns>
private ShowModel GetWeModel()
{
var para = new Dapper.DynamicParameters();
string sql = string.Empty;
//We计划
sql = @"SELECT TOP 1 wp.Id, wp.ProductName,wp.IsWeFQB,isnull(wp.IsFTB,0) as IsFTB, wp.YearRate,wp.Deadline,wp.PlanAmount AS Amount,
wpd.RealOrderQty AS CastedShares,LowerUnit,wpd.TotalQty AS TotalShares,wpd.OrderCount,isnull(wp.TuandaiRedRate,0) as TuandaiRedRate,
isnull(wp.DeadType,1) as DeadType
FROM dbo.We_Product wp WITH(NOLOCK)
INNER JOIN dbo.We_ProductDetail wpd WITH(NOLOCK) ON wp.Id = wpd.ProductId
INNER JOIN dbo.We_ProductType wpt WITH(NOLOCK) ON wp.ProductTypeId = wpt.Id
WHERE wp.StartDate<=getdate() and wp.StatusId=1 and isnull(wp.IsNewHand,0)!=1 and wpd.TotalQty > wpd.RealOrderQty AND wpt.TypeWord NOT IN('D','E','F','R','Q','P','X','Y7','Z7'{0})
ORDER BY case when wp.IsFTB=1 then 0 else 1 end, YearRate DESC";
var isOpenFTB = ConfigHelper.getConfigString("IsOpenFTB", "0");
if (isOpenFTB == "0")
{
sql = string.Format(sql, ",'18','24'");
}
else
{
sql = string.Format(sql, "");
}
ShowModel model = PublicConn.QuerySingle <ShowModel>(sql, ref para);
if (model != null)
{
model.ProductName = model.ProductName.GetCharLeft("(");
if (model.IsWeFQB)
{
model.DetailUrl = "/pages/invest/WE/WeFqb_detail.aspx?id=" + model.Id;
}
else if (model.IsFTB)
{
model.DetailUrl = "/pages/invest/WE/WeFtb_detail.aspx?id=" + model.Id;
}
else
{
model.DetailUrl = "/pages/invest/WE/WE_detail.aspx?id=" + model.Id;
}
}
return(model);
}
/// <summary>
/// 普通标
/// </summary>
/// <returns></returns>
private ShowModel GetModel()
{
var para = new Dapper.DynamicParameters();
string sql = string.Empty;
//普通标
sql = @"select top 1 Id,0 AS IsWeFQB,InterestRate AS YearRate,Deadline,DeadType,Amount,CastedShares,LowerUnit,TotalShares,Type as TypeId from project with(nolock) where IsNewHand!=1 and type != 15 AND AddDate>dateadd(day,-15,getdate()) and Status =2 order by InterestRate desc";
ShowModel model = PublicConn.QuerySingle <ShowModel>(sql, ref para);
if (model != null)
{
model.ProductName = invest_list.GetTypeName(model.TypeId, 1);
model.DetailUrl = "/pages/invest/detail.aspx?id=" + model.Id;
model.OrderCount = CommUtils.GetSubscribeUserCount(model.Id);
}
return(model);
}
public void ToggleFavorite(int projectId, bool favorite)
{
using (SqlConnection conn = SqlHelper.GetOpenConnection())
{
var par = new Dapper.DynamicParameters();
par.Add("@ProjectId", projectId);
par.Add("@UserId", IdentityContext.Current.User.Id);
if (favorite)
{
conn.Execute(@"INSERT INTO ProjectFavorite (ProjectId, UserId) VALUES (@ProjectId, @UserId)", par);
}
else
{
conn.Execute(@"DELETE FROM ProjectFavorite WHERE ProjectId = @ProjectId AND UserId = @UserId", par);
}
}
}
public static List <LabelDataModel> LoadLabelDataWorkOrder(string workOrderNumber)
{
var p = new Dapper.DynamicParameters();
p.Add("@WorkOrderNumber", workOrderNumber);
string sql = @"SELECT woh_wo_no AS WorkOrderNumber,
woh_item_no AS PartNumber,
ptwh.woh_item_short_desc AS PartDescription,
ptwh.woh_order_qty AS Quantity,
ptwh.woh_so_no AS SalesOrderNumber,
smbu.upc_no AS UPC
FROM pmd_twoh_wo_headr ptwh
INNER JOIN SAV_MINI_BOM_UDS smbu ON ptwh.woh_item_no = smbu.Item_code
WHERE ptwh.woh_wo_no = @WorkOrderNumber;";
return(SQLDataAccess.LoadDataSCMDB <LabelDataModel>(sql, p));
}
/// <summary>
/// 获取个人佣金排名
/// huangbinglai 2016/08/26
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public Tuple <Int32, decimal> WXGetExtendEarnRank(Guid userid)
{
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@UserId", userid);
string querySql = @" SELECT AA.RowNum
from ( select ROW_NUMBER() OVER(ORDER BY TotalEranMoney DESC) AS RowNum ,UserId
from rp_ExtenderStatis with(nolock) )AA
where AA.UserId=@UserId ";
int? rowNum = TuanDai.DB.TuanDaiDB.Query <int?>(TdConfig.ApplicationName, TdConfig.DBReportWrite, querySql, ref dyParams).FirstOrDefault();
querySql = @" SELECT SUM([EarnMoney]) [EarnMoney] FROM dbo.ExtendEarnRecord
where UserID= @UserId ";
decimal?eranMoney = GetExtendEarn(userid);
Tuple <Int32, decimal> result = new Tuple <Int32, decimal>(rowNum ?? 0, eranMoney ?? 0);
return(result);
}
public void DeleteProgramRoleAdministrator(int userId, int programTypeId, int projectRoleId)
{
using (SqlConnection conn = SqlHelper.GetOpenConnection())
{
var par = new Dapper.DynamicParameters();
par.Add("@UserId", userId);
par.Add("@ProgramTypeId", programTypeId);
par.Add("@ProjectRoleId", projectRoleId);
var query = @"DELETE FROM ProgramRoleAdministration
WHERE
UserId = @UserId AND
ProgramTypeId = @ProgramTypeId AND
ProjectRoleId = @ProjectRoleId" ;
conn.Execute(query, par);
}
}
public int Delete(ASSETKKF_MODEL.Data.Mssql.bsicpers.rmEmployee d, string userID = "", SqlTransaction transac = null)
{
var param = new Dapper.DynamicParameters();
param.Add("@EmployeeID", d.EmployeeID.GetValue());
param.Add("@EmploType", d.EmploType.GetValue());
param.Add("@Status", d.Status.GetValue());
param.Add("@Shift", d.Shift.GetValue());
param.Add("@Weekend", d.Weekend.GetValue());
param.Add("@DepCode", d.DepCode.GetValue());
param.Add("@Position", d.Position.GetValue());
param.Add("@TitleName", d.TitleName.GetValue());
param.Add("@FirstName", d.FirstName.GetValue());
param.Add("@LastName", d.LastName.GetValue());
param.Add("@StartDate", d.StartDate);
param.Add("@EmploDate", d.EmploDate);
param.Add("@EndDate", d.EndDate);
param.Add("@EditDate", d.EditDate);
string cmd = "";
/*
* cmd = DELETE FROM bsicpers.dbo.rmEmployee "+
* "WHERE EmployeeID = @.EmployeeID "+
* " AND EmploType = @.EmploType "+
* " AND Status = @.Status "+
* " AND Shift = @.Shift "+
* " AND Weekend = @.Weekend "+
* " AND DepCode = @.DepCode "+
* " AND Position = @.Position "+
* " AND TitleName = @.TitleName "+
* " AND FirstName = @.FirstName "+
* " AND LastName = @.LastName "+
* " AND StartDate = @.StartDate "+
* " AND EmploDate = @.EmploDate "+
* " AND EndDate = @.EndDate "+
* " AND EditDate = @.EditDate "+
* " ";
*/
var res = ExecuteNonQuery(transac, cmd, param);
return(res);
}
/// <summary>
/// 获取新手标限制金额
/// </summary>
private void GetLimitInvestMoney()
{
string sql = "SELECT Param1Value FROM WebSetting with(nolock) where id='FC5BAE60-716E-4344-9C10-F1E808064FC7'";
var para = new Dapper.DynamicParameters();
limitInvest = PublicConn.QuerySingle <decimal>(sql, ref para);
if (limitInvest >= 10000)
{
limitInvestStr = ToolStatus.DeleteZero(Math.Floor(limitInvest / 10000)) + "万";
}
else if (limitInvest >= 1000 && limitInvest < 10000)
{
limitInvestStr = ToolStatus.DeleteZero(Math.Floor(limitInvest / 1000)) + "千";
}
else
{
limitInvestStr = Math.Floor(limitInvest).ToString();
}
}
public List <string> getDB_DRIVING_SITE(List <string> site_id)
{
string actionName = "checkSITEData";
List <string> pList = new List <string>();
string sql = "SELECT DISTINCT SITE_ID FROM " + DB_TABLE_NAME.DB_MC_SITE_DRIVING_TIME_INFO + " WHERE DATASTATUS = '1' ";
Dapper.DynamicParameters dp = new Dapper.DynamicParameters();
if (site_id.Count > 0)
{
sql += " AND SITE_ID in @SITE_ID";
dp.Add("SITE_ID", site_id);
}
pList = this.DBLink.DBA.getSqlDataTable <string>(sql, dp);
if (this.DBLink.DBA.hasLastError)
{
Com.Mayaminer.LogTool.SaveLogMessage(this.DBLink.DBA.lastError, actionName, this.csName);
}
return(pList);
}
public List <DB_MC_SITE_DRIVING_TIME_INFO> getDB_MC_SITE_DRIVING_TIME_INFO(string site_id = "")
{
string actionName = "checkSITEData";
List <DB_MC_SITE_DRIVING_TIME_INFO> pList = new List <DB_MC_SITE_DRIVING_TIME_INFO>();
string sql = "SELECT * FROM " + DB_TABLE_NAME.DB_MC_SITE_DRIVING_TIME_INFO + " WHERE DATASTATUS = '1' ";
Dapper.DynamicParameters dp = new Dapper.DynamicParameters();
if (!string.IsNullOrWhiteSpace(site_id))
{
sql += " AND SITE_ID =@SITE_ID";
dp.Add("SITE_ID", site_id);
}
pList = this.DBLink.DBA.getSqlDataTable <DB_MC_SITE_DRIVING_TIME_INFO>(sql, dp);
if (this.DBLink.DBA.hasLastError)
{
Com.Mayaminer.LogTool.SaveLogMessage(this.DBLink.DBA.lastError, actionName, this.csName);
}
return(pList);
}
