| public static ExecuteNonQuery ( string sql ) : int | ||
| sql | string | |
| return | int | |
/// <summary>
/// Inserts a row in the mp_PayPalLog table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="requestType"> requestType </param>
/// <param name="apiVersion"> apiVersion </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="token"> token </param>
/// <param name="payerId"> payerId </param>
/// <param name="transactionId"> transactionId </param>
/// <param name="paymentType"> paymentType </param>
/// <param name="paymentStatus"> paymentStatus </param>
/// <param name="pendingReason"> pendingReason </param>
/// <param name="reasonCode"> reasonCode </param>
/// <param name="currencyCode"> currencyCode </param>
/// <param name="exchangeRate"> exchangeRate </param>
/// <param name="cartTotal"> cartTotal </param>
/// <param name="payPalAmt"> payPalAmt </param>
/// <param name="taxAmt"> taxAmt </param>
/// <param name="feeAmt"> feeAmt </param>
/// <param name="settleAmt"> settleAmt </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string requestType,
string apiVersion,
string rawResponse,
string token,
string payerId,
string transactionId,
string paymentType,
string paymentStatus,
string pendingReason,
string reasonCode,
string currencyCode,
decimal exchangeRate,
decimal cartTotal,
decimal payPalAmt,
decimal taxAmt,
decimal feeAmt,
decimal settleAmt,
string providerName,
string returnUrl,
string serializedObject,
string pdtProviderName,
string ipnProviderName,
string response)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_PayPalLog (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("StoreGuid, ");
sqlCommand.Append("CartGuid, ");
sqlCommand.Append("RequestType, ");
sqlCommand.Append("ApiVersion, ");
sqlCommand.Append("RawResponse, ");
sqlCommand.Append("Token, ");
sqlCommand.Append("PayerId, ");
sqlCommand.Append("TransactionId, ");
sqlCommand.Append("PaymentType, ");
sqlCommand.Append("PaymentStatus, ");
sqlCommand.Append("PendingReason, ");
sqlCommand.Append("ReasonCode, ");
sqlCommand.Append("CurrencyCode, ");
sqlCommand.Append("ExchangeRate, ");
sqlCommand.Append("CartTotal, ");
sqlCommand.Append("PayPalAmt, ");
sqlCommand.Append("TaxAmt, ");
sqlCommand.Append("FeeAmt, ");
sqlCommand.Append("ProviderName, ");
sqlCommand.Append("ReturnUrl, ");
sqlCommand.Append("SerializedObject, ");
sqlCommand.Append("PDTProviderName, ");
sqlCommand.Append("IPNProviderName, ");
sqlCommand.Append("Response, ");
sqlCommand.Append("SettleAmt )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?StoreGuid, ");
sqlCommand.Append("?CartGuid, ");
sqlCommand.Append("?RequestType, ");
sqlCommand.Append("?ApiVersion, ");
sqlCommand.Append("?RawResponse, ");
sqlCommand.Append("?Token, ");
sqlCommand.Append("?PayerId, ");
sqlCommand.Append("?TransactionId, ");
sqlCommand.Append("?PaymentType, ");
sqlCommand.Append("?PaymentStatus, ");
sqlCommand.Append("?PendingReason, ");
sqlCommand.Append("?ReasonCode, ");
sqlCommand.Append("?CurrencyCode, ");
sqlCommand.Append("?ExchangeRate, ");
sqlCommand.Append("?CartTotal, ");
sqlCommand.Append("?PayPalAmt, ");
sqlCommand.Append("?TaxAmt, ");
sqlCommand.Append("?FeeAmt, ");
sqlCommand.Append("?ProviderName, ");
sqlCommand.Append("?ReturnUrl, ");
sqlCommand.Append("?SerializedObject, ");
sqlCommand.Append("?PDTProviderName, ");
sqlCommand.Append("?IPNProviderName, ");
sqlCommand.Append("?Response, ");
sqlCommand.Append("?SettleAmt )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[29];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new MySqlParameter("?StoreGuid", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new MySqlParameter("?CartGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new MySqlParameter("?RequestType", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = requestType;
arParams[7] = new MySqlParameter("?ApiVersion", MySqlDbType.VarChar, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = apiVersion;
arParams[8] = new MySqlParameter("?RawResponse", MySqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = rawResponse;
arParams[9] = new MySqlParameter("?Token", MySqlDbType.VarChar, 50);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = token;
arParams[10] = new MySqlParameter("?PayerId", MySqlDbType.VarChar, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = payerId;
arParams[11] = new MySqlParameter("?TransactionId", MySqlDbType.VarChar, 50);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = transactionId;
arParams[12] = new MySqlParameter("?PaymentType", MySqlDbType.VarChar, 10);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = paymentType;
arParams[13] = new MySqlParameter("?PaymentStatus", MySqlDbType.VarChar, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = paymentStatus;
arParams[14] = new MySqlParameter("?PendingReason", MySqlDbType.VarChar, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = pendingReason;
arParams[15] = new MySqlParameter("?ReasonCode", MySqlDbType.VarChar, 50);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = reasonCode;
arParams[16] = new MySqlParameter("?CurrencyCode", MySqlDbType.VarChar, 50);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = currencyCode;
arParams[17] = new MySqlParameter("?ExchangeRate", MySqlDbType.Decimal);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = exchangeRate;
arParams[18] = new MySqlParameter("?CartTotal", MySqlDbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = cartTotal;
arParams[19] = new MySqlParameter("?PayPalAmt", MySqlDbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = payPalAmt;
arParams[20] = new MySqlParameter("?TaxAmt", MySqlDbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = taxAmt;
arParams[21] = new MySqlParameter("?FeeAmt", MySqlDbType.Decimal);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = feeAmt;
arParams[22] = new MySqlParameter("?SettleAmt", MySqlDbType.Decimal);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = settleAmt;
arParams[23] = new MySqlParameter("?ProviderName", MySqlDbType.VarChar, 255);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = providerName;
arParams[24] = new MySqlParameter("?ReturnUrl", MySqlDbType.VarChar, 255);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = returnUrl;
arParams[25] = new MySqlParameter("?SerializedObject", MySqlDbType.Text);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = serializedObject;
arParams[26] = new MySqlParameter("?PDTProviderName", MySqlDbType.VarChar, 255);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = pdtProviderName;
arParams[27] = new MySqlParameter("?IPNProviderName", MySqlDbType.VarChar, 255);
arParams[27].Direction = ParameterDirection.Input;
arParams[27].Value = ipnProviderName;
arParams[28] = new MySqlParameter("?Response", MySqlDbType.VarChar, 255);
arParams[28].Direction = ParameterDirection.Input;
arParams[28].Value = response;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_PlugNPayLog table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="responseCode"> responseCode </param>
/// <param name="responseReasonCode"> responseReasonCode </param>
/// <param name="reason"> reason </param>
/// <param name="avsCode"> avsCode </param>
/// <param name="ccvCode"> ccvCode </param>
/// <param name="cavCode"> cavCode </param>
/// <param name="transactionId"> transactionId </param>
/// <param name="transactionType"> transactionType </param>
/// <param name="method"> method </param>
/// <param name="authCode"> authCode </param>
/// <param name="amount"> amount </param>
/// <param name="tax"> tax </param>
/// <param name="duty"> duty </param>
/// <param name="freight"> freight </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string rawResponse,
string responseCode,
string responseReasonCode,
string reason,
string avsCode,
string ccvCode,
string cavCode,
string transactionId,
string transactionType,
string method,
string authCode,
decimal amount,
decimal tax,
decimal duty,
decimal freight)
{
#region Bit Conversion
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_PlugNPayLog (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("StoreGuid, ");
sqlCommand.Append("CartGuid, ");
sqlCommand.Append("RawResponse, ");
sqlCommand.Append("ResponseCode, ");
sqlCommand.Append("ResponseReasonCode, ");
sqlCommand.Append("Reason, ");
sqlCommand.Append("AvsCode, ");
sqlCommand.Append("CcvCode, ");
sqlCommand.Append("CavCode, ");
sqlCommand.Append("TransactionId, ");
sqlCommand.Append("TransactionType, ");
sqlCommand.Append("Method, ");
sqlCommand.Append("AuthCode, ");
sqlCommand.Append("Amount, ");
sqlCommand.Append("Tax, ");
sqlCommand.Append("Duty, ");
sqlCommand.Append("Freight )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?StoreGuid, ");
sqlCommand.Append("?CartGuid, ");
sqlCommand.Append("?RawResponse, ");
sqlCommand.Append("?ResponseCode, ");
sqlCommand.Append("?ResponseReasonCode, ");
sqlCommand.Append("?Reason, ");
sqlCommand.Append("?AvsCode, ");
sqlCommand.Append("?CcvCode, ");
sqlCommand.Append("?CavCode, ");
sqlCommand.Append("?TransactionId, ");
sqlCommand.Append("?TransactionType, ");
sqlCommand.Append("?Method, ");
sqlCommand.Append("?AuthCode, ");
sqlCommand.Append("?Amount, ");
sqlCommand.Append("?Tax, ");
sqlCommand.Append("?Duty, ");
sqlCommand.Append("?Freight )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[21];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new MySqlParameter("?StoreGuid", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new MySqlParameter("?CartGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new MySqlParameter("?RawResponse", MySqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = rawResponse;
arParams[7] = new MySqlParameter("?ResponseCode", MySqlDbType.VarChar, 10);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = responseCode;
arParams[8] = new MySqlParameter("?ResponseReasonCode", MySqlDbType.VarChar, 20);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = responseReasonCode;
arParams[9] = new MySqlParameter("?Reason", MySqlDbType.Text);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = reason;
arParams[10] = new MySqlParameter("?AvsCode", MySqlDbType.VarChar, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = avsCode;
arParams[11] = new MySqlParameter("?CcvCode", MySqlDbType.VarChar, 10);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = ccvCode;
arParams[12] = new MySqlParameter("?CavCode", MySqlDbType.VarChar, 10);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = cavCode;
arParams[13] = new MySqlParameter("?TransactionId", MySqlDbType.VarChar, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = transactionId;
arParams[14] = new MySqlParameter("?TransactionType", MySqlDbType.VarChar, 50);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = transactionType;
arParams[15] = new MySqlParameter("?Method", MySqlDbType.VarChar, 20);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = method;
arParams[16] = new MySqlParameter("?AuthCode", MySqlDbType.VarChar, 50);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = authCode;
arParams[17] = new MySqlParameter("?Amount", MySqlDbType.Decimal);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = amount;
arParams[18] = new MySqlParameter("?Tax", MySqlDbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = tax;
arParams[19] = new MySqlParameter("?Duty", MySqlDbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = duty;
arParams[20] = new MySqlParameter("?Freight", MySqlDbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = freight;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// 向数据库中添加一条记录。
/// </summary>
/// <param name="entity">实体对象数据</param>
/// <param name="columnNames">目标表列名集合</param>
/// <returns>返回影响记录的行数,-1表示操作失败,大于-1表示成功</returns>
public virtual int Insert(T entity, params string[] columnNames)
{
SqlExpression sqlExpr = this.GenerateInsertSqlExpression(this.GetDataFieldMapTable(entity, columnNames), this._tableName);
return(MySqlHelper.ExecuteNonQuery(this._connectionString, sqlExpr.CommandText, sqlExpr.Parameters));
}
/// <summary>
/// 清空表中的所有数据,且不记录数据库日志。
/// </summary>
public virtual void Truncate()
{
string sqlCmd = string.Format("TRUNCATE TABLE {0}", this._tableName);
MySqlHelper.ExecuteNonQuery(this._connectionString, sqlCmd);
}
/// <summary>
/// Inserts a row in the sts_ga_PageData table. Returns rows affected count.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="date"> date </param>
/// <param name="pagePath"> pagePath </param>
/// <param name="pageViews"> pageViews </param>
/// <param name="visits"> visits </param>
/// <param name="newVisits"> newVisits </param>
/// <param name="bounces"> bounces </param>
/// <param name="entrances"> entrances </param>
/// <param name="exits"> exits </param>
/// <param name="timeOnPage"> timeOnPage </param>
/// <param name="timeOnSite"> timeOnSite </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
Guid pageGuid,
string profileId,
DateTime date,
string pagePath,
int pageViews,
int visits,
int newVisits,
int entrances,
int exits,
decimal bounceRate,
decimal timeOnPage,
decimal timeOnSite,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_PageData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("PageGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("PagePath, ");
sqlCommand.Append("PageViews, ");
sqlCommand.Append("Visits, ");
sqlCommand.Append("NewVisits, ");
sqlCommand.Append("BounceRate, ");
sqlCommand.Append("Entrances, ");
sqlCommand.Append("Exits, ");
sqlCommand.Append("TimeOnPage, ");
sqlCommand.Append("TimeOnSite, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?PageGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?PagePath, ");
sqlCommand.Append("?PageViews, ");
sqlCommand.Append("?Visits, ");
sqlCommand.Append("?NewVisits, ");
sqlCommand.Append("?BounceRate, ");
sqlCommand.Append("?Entrances, ");
sqlCommand.Append("?Exits, ");
sqlCommand.Append("?TimeOnPage, ");
sqlCommand.Append("?TimeOnSite, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[15];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?PageGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = profileId;
arParams[3] = new MySqlParameter("?AnalyticsDate", MySqlDbType.DateTime);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = date;
if (pagePath.Length > 255)
{
pagePath = pagePath.Substring(0, 255);
}
arParams[4] = new MySqlParameter("?PagePath", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pagePath;
arParams[5] = new MySqlParameter("?PageViews", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = pageViews;
arParams[6] = new MySqlParameter("?Visits", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = visits;
arParams[7] = new MySqlParameter("?NewVisits", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = newVisits;
arParams[8] = new MySqlParameter("?BounceRate", MySqlDbType.Decimal);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = bounceRate;
arParams[9] = new MySqlParameter("?Entrances", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = entrances;
arParams[10] = new MySqlParameter("?Exits", MySqlDbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = exits;
arParams[11] = new MySqlParameter("?TimeOnPage", MySqlDbType.Decimal);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = timeOnPage;
arParams[12] = new MySqlParameter("?TimeOnSite", MySqlDbType.Decimal);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = timeOnSite;
arParams[13] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = capturedUtc;
arParams[14] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = Utility.DateTonInteger(date);
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public void Update(TUser user)
{
using (var conn = new MySqlConnection(_connectionString))
{
var parameters = new Dictionary <string, object>
{
{ "@NewId", user.Id },
{ "@Email", (object)user.Email ?? DBNull.Value },
{ "@EmailConfirmed", user.EmailConfirmed },
{ "@PasswordHash", (object)user.PasswordHash ?? DBNull.Value },
{ "@SecurityStamp", (object)user.SecurityStamp ?? DBNull.Value },
{ "@PhoneNumber", (object)user.PhoneNumber ?? DBNull.Value },
{ "@PhoneNumberConfirmed", user.PhoneNumberConfirmed },
{ "@TwoFactorAuthEnabled", user.TwoFactorAuthEnabled },
{ "@LockoutEndDate", (object)user.LockoutEndDate ?? DBNull.Value },
{ "@LockoutEnabled", user.LockoutEnabled },
{ "@AccessFailedCount", user.AccessFailedCount },
{ "@UserName", user.UserName },
{ "@Nome", (object)user.Nome ?? DBNull.Value },
{ "@TipoPessoa", (object)user.TipoPessoa ?? DBNull.Value },
{ "@CpfCnpj", (object)user.CpfCnpj ?? DBNull.Value },
{ "@Rg", (object)user.Rg ?? DBNull.Value },
{ "@DataNascimento", user.DataNascimento },
{ "@EstadoCivil", (object)user.EstadoCivil ?? DBNull.Value },
{ "@Nascionalidade", (object)user.Nascionalidade ?? DBNull.Value },
{ "@Profissao", (object)user.Profissao ?? DBNull.Value },
{ "@Sexo", (object)user.Sexo ?? DBNull.Value },
{ "@Cep", (object)user.Cep ?? DBNull.Value },
{ "@Logradouro", (object)user.Logradouro ?? DBNull.Value },
{ "@Numero", (object)user.Numero ?? DBNull.Value },
{ "@Complemento", (object)user.Complemento ?? DBNull.Value },
{ "@Bairro", (object)user.Bairro ?? DBNull.Value },
{ "@Cidade", (object)user.Cidade ?? DBNull.Value },
{ "@Uf", (object)user.Uf ?? DBNull.Value },
{ "@TelefoneResidencial", (object)user.TelefoneResidencial ?? DBNull.Value },
{ "@TelefoneCelular", (object)user.TelefoneCelular ?? DBNull.Value },
{ "@TelefoneComercial", (object)user.TelefoneComercial ?? DBNull.Value },
{ "@Status", (object)user.Status ?? DBNull.Value },
{ "@IdUsuario", (object)user.IdUsuario ?? DBNull.Value },
{ "@Id", user.Id }
};
MySqlHelper.ExecuteNonQuery(conn, @"UPDATE aspnetusers SET
Id = @NewId,
Email=@Email,
EmailConfirmed=@EmailConfirmed,
PasswordHash=@PasswordHash,
SecurityStamp=@SecurityStamp,
PhoneNumber=@PhoneNumber,
PhoneNumberConfirmed=@PhoneNumberConfirmed,
TwoFactorEnabled=@TwoFactorAuthEnabled,
LockoutEndDateUtc=@LockoutEndDate,
LockoutEnabled=@LockoutEnabled,
AccessFailedCount=@AccessFailedCount,
UserName=@UserName,
Nome=@Nome,
TipoPessoa=@TipoPessoa,
CpfCnpj=@CpfCnpj,
Rg=@Rg,
DataNascimento=@DataNascimento,
EstadoCivil=@EstadoCivil,
Nascionalidade=@Nascionalidade,
Profissao=@Profissao,
Sexo=@Sexo,
Cep=@Cep,
Logradouro=@Logradouro,
Numero=@Numero,
Complemento=@Complemento,
Bairro=@Bairro,
Cidade=@Cidade,
Uf=@Uf,
TelefoneResidencial=@TelefoneResidencial,
TelefoneCelular=@TelefoneCelular,
TelefoneComercial=@TelefoneComercial,
Status=@Status,
IdUsuario =@IdUsuario
WHERE Id=@Id", parameters);
}
}
/// <summary>
/// 保存按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
var viewDisplayName = textBox1.Text.Trim();
Logger.Info("View Disply Name:{0}", viewDisplayName);
#region 输入检查
//查询控件的个数不能都为0
if (this.GetCircleQueries().Count == 0 && this.GetRectQueries().Count == 0)
{
MessageBox.Show("查询条件为空！");
return;
}
//圆域查询条件检查
foreach (var cquery in this.GetCircleQueries())
{
if (!cquery.IsLegalInput())
{
MessageBox.Show("圆形查询条件输入不完整！");
return;
}
}
//矩形域查询条件检查
foreach (var rquery in this.GetRectQueries())
{
if (!rquery.IsLegalInput())
{
MessageBox.Show("矩形查询条件输入不完整！");
return;
}
}
//查询条件名不能为空
if (textBox1.Text.Trim().Equals(string.Empty))
{
MessageBox.Show("请输入查询条件名！");
return;
}
#endregion
// 是否有重名的区域查询条件？
var commandText = "select count(*) from {0} where 用户编号={1} and 视图名称='{2}'";
commandText = string.Format(commandText, DaoObject.TnRQMItem(), this.User.ID, viewDisplayName);
Logger.Debug(commandText);
var n = Convert.ToInt32(MySqlHelper.ExecuteScalar(DaoObject.ConnectionString, commandText));
Logger.Info("查询是否存在名为 {0} 的视图 ？ {1}", viewDisplayName, n > 0);
if (n > 0)
{
MessageBox.Show("已经存在名称【" + viewDisplayName + "】，未保存！");
return;
}
// 生成视图创建语句，创建视图
// 视图名不允许有-，所以替换为_，视图名为用户名_GUID
var viewName = this.User.Name + "_" + Guid.NewGuid().ToString().Replace('-', '_');
Logger.Info("视图名：" + viewName);
var viewCommandText = string.Format("create view {0} as " + this.GetCommandText(), viewName);
MySqlHelper.ExecuteNonQuery(DaoObject.ConnectionString, viewCommandText);
Logger.Debug("视图创建语句：" + viewCommandText);
// 视图如果创建成功了，向数据库中写入记录
if (DaoObject.HasView(viewName))
{
commandText = "insert into {0}(用户编号,视图名称,视图体) values ({1},'{2}','{3}')";
commandText = string.Format(commandText, DaoObject.TnRQMItem(), this.User.ID, viewDisplayName, viewName);
Logger.Debug(commandText);
n = MySqlHelper.ExecuteNonQuery(DaoObject.ConnectionString, commandText);
Logger.Info("保存视图记录 " + (n > 0));
if (n > 0)
{
MessageBox.Show("保存成功！");
RefreshDataGridView();
}
}
else
{
MessageBox.Show("视图生成失败！");
}
}
/// <summary>
/// Updates a row in the mp_ContentMeta table. Returns true if row updated.
/// </summary>
/// <returns>bool</returns>
public static bool Update(
Guid guid,
string name,
string nameProperty,
string scheme,
string langCode,
string dir,
string metaContent,
string contentProperty,
int sortRank,
DateTime lastModUtc,
Guid lastModBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_ContentMeta ");
sqlCommand.Append("SET ");
sqlCommand.Append("Name = ?Name, ");
sqlCommand.Append("NameProperty = ?NameProperty, ");
sqlCommand.Append("Scheme = ?Scheme, ");
sqlCommand.Append("LangCode = ?LangCode, ");
sqlCommand.Append("Dir = ?Dir, ");
sqlCommand.Append("MetaContent = ?MetaContent, ");
sqlCommand.Append("ContentProperty = ?ContentProperty, ");
sqlCommand.Append("SortRank = ?SortRank, ");
sqlCommand.Append("LastModUtc = ?LastModUtc, ");
sqlCommand.Append("LastModBy = ?LastModBy ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?Guid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[11];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = name;
arParams[2] = new MySqlParameter("?Scheme", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = scheme;
arParams[3] = new MySqlParameter("?LangCode", MySqlDbType.VarChar, 10);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = langCode;
arParams[4] = new MySqlParameter("?Dir", MySqlDbType.VarChar, 3);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = dir;
arParams[5] = new MySqlParameter("?MetaContent", MySqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = metaContent;
arParams[6] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = sortRank;
arParams[7] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = lastModUtc;
arParams[8] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModBy.ToString();
arParams[9] = new MySqlParameter("?NameProperty", MySqlDbType.VarChar, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = name;
arParams[10] = new MySqlParameter("?ContentProperty", MySqlDbType.VarChar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = name;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams
);
return(rowsAffected > -1);
}
/// <summary>
/// 执行一条sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数列表</param>
/// <returns></returns>
public int ExecuteSql(string sql, params MySqlParameter[] param)
{
return(MySqlHelper.ExecuteNonQuery(this.ConnectionStr, sql, param));
}
public static int AddUserPage(
Guid userPageId,
Guid siteGuid,
int siteId,
Guid userGuid,
string pageName,
string pagePath,
int pageOrder)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_UserPages ");
sqlCommand.Append("( ");
sqlCommand.Append("UserPageID, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("PageName, ");
sqlCommand.Append("PagePath, ");
sqlCommand.Append("PageOrder ");
sqlCommand.Append(")");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?UserPageID, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?SiteID, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?PageName, ");
sqlCommand.Append("?PagePath, ");
sqlCommand.Append("?PageOrder ");
sqlCommand.Append(");");
MySqlParameter[] arParams = new MySqlParameter[7];
arParams[0] = new MySqlParameter("?UserPageID", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userPageId.ToString();
arParams[1] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteId;
arParams[2] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userGuid.ToString();
arParams[3] = new MySqlParameter("?PageName", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageName;
arParams[4] = new MySqlParameter("?PagePath", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pagePath;
arParams[5] = new MySqlParameter("?PageOrder", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = pageOrder;
arParams[6] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = siteGuid.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public void Start(object source, System.Timers.ElapsedEventArgs e)
{
int flowid = 179;
//遍历某个规则下面的所有符合条件的flow_run
//List<FlowRun> frlistbyruleid = flowrunall.Where(t => t.flow_id == flowid).ToList();
List <Flow179> flow179 = MySqlHelper.ExecuteObjectList <Flow179>(string.Format("SELECT flowr.run_id AS run_id,flowr.run_name AS run_name,byname AS user_id,data_95 AS starttime,data_96 AS endtime, data_10 AS i_versl FROM flow_data_{0} AS flowdata INNER JOIN flow_run AS flowr ON flowdata.run_id = flowr.run_id INNER JOIN USER AS u ON u.USER_ID = flowdata.begin_user WHERE FLOW_ID = {0} AND DEL_FLAG = 0 AND END_TIME IS NOT NULL AND SYNC_TIME IS NULL AND (TIMES<=2 or RETRY=1)", flowid)).ToList();
for (int i = 0; i < flow179.Count; i++)
{
string wsretlog = "";
int errtype = 0;//1接口错误 2ws返回结果错误 0正常 3数据错误
bool ifsuc = true;
WebReference.SAP_OA_JK17_HEAD oasaphead17 = new WebReference.SAP_OA_JK17_HEAD();
//人员编号 Head I_PERNR NUMC 8 123
//开始日期 Head I_BEGDA DATS 8 20170101
//结束日期 Head I_ENDDA DATS 8 20170101
//开始时间 Head I_BEGUZ TIMS 8 1700
//结束时间 Head I_ENDUZ TIMS 8 1800
//加班补偿类型 head I_VERSL CHAR 1 "空格：加班调休 1：加班结薪"
string st = flow179[i].starttime;
string et = flow179[i].endtime;
if (string.IsNullOrEmpty(st) || string.IsNullOrEmpty(et))
{
wsretlog = "日期为空";
errtype = 3;
MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, string.Format("UPDATE FLOW_RUN set TIMES=TIMES+1,RETRY=0 where RUN_ID={0} ", flow179[i].run_id), null);
MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, string.Format("INSERT INTO sync_flowlog (run_id,flow_id,run_name,sendlog,receivelog,sendtime,errtype) values ({0},{1},'{2}','{3}','{4}',sysdate(),{5}) ", flow179[i].run_id, flowid, flow179[i].run_name, "", wsretlog, errtype), null);
continue;
}
if (!string.IsNullOrEmpty(st) && !string.IsNullOrEmpty(et))
{
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-mm-dd hh24:mi:ss";
DateTime starttime = new DateTime();
DateTime endtime = new DateTime();
try
{
starttime = Convert.ToDateTime(st, dtFormat);
endtime = Convert.ToDateTime(et, dtFormat);
}
catch
{
wsretlog = "日期格式错误";
errtype = 3;
MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, string.Format("UPDATE FLOW_RUN set TIMES=TIMES+1,RETRY=0 where RUN_ID={0} ", flow179[i].run_id), null);
MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, string.Format("INSERT INTO sync_flowlog (run_id,flow_id,run_name,sendlog,receivelog,sendtime,errtype) values ({0},{1},'{2}','{3}','{4}',sysdate(),{5}) ", flow179[i].run_id, flowid, flow179[i].run_name, "", wsretlog, errtype), null);
continue;
}
if (endtime.CompareTo(starttime) > 0)
{
//开始日期
string startdatestr = st.Substring(0, 10).Replace("-", "").Replace("/", "");
//结束日期
string enddatestr = et.Substring(0, 10).Replace("-", "").Replace("/", "");
//开始时间
string starttimestr = st.Substring(11, 5).ToString().Replace(":", "");
//原始开始时间
//DateTime starttimeori = Convert.ToDateTime(tmpnewlinedh[2].Substring(11, 5).ToString());
//结束时间
string endtimestr = et.Substring(11, 5).ToString().Replace(":", "");
double stdaz = Math.Round((endtime - starttime).TotalHours, 1);
//原始结束时间
//DateTime endtimeori = Convert.ToDateTime(tmpnewlinedh[3].Substring(11, 5).ToString());
//oasaphead17.HEAD head = new oasap17.HEAD();
string versl = " ";
if (flow179[i].i_versl.ToString() == "加班结薪")
{
versl = "1";
}
else if (flow179[i].i_versl.ToString() == "调休")
{
versl = " ";
}
else
{
versl = " ";
}
oasaphead17.I_PERNR = flow179[i].user_id.ToString();
oasaphead17.I_BEGDA = startdatestr;
oasaphead17.I_ENDDA = enddatestr;
oasaphead17.I_BEGUZ = starttimestr;
oasaphead17.I_ENDUZ = endtimestr;
oasaphead17.I_STDAZ = stdaz.ToString();
oasaphead17.I_VERSL = versl;
WebReference.SAP_OA_JK17_REQ oasap17 = new WebReference.SAP_OA_JK17_REQ();
oasap17.HEAD = oasaphead17;
//发送ws
try
{
WebReference.OaWebService oa = new WebReference.OaWebService();
Logger.Log(flow179[i].run_id + "(" + flow179[i].run_name + "-" + flow179[i].user_id + "):" + JsonHelper.ObjectToJson(oasap17), flowid.ToString());
WebReference.DT_OA_OA17_RespITEM[] retoa = oa.SAP_OA_JK_17(oasap17);
wsretlog = JsonHelper.ObjectToJson(retoa);
Logger.Log(flow179[i].run_id + "(" + flow179[i].run_name + "-" + flow179[i].user_id + "):" + JsonHelper.ObjectToJson(retoa), flowid.ToString());
foreach (var item in retoa)
{
if (item.TYPE != "S")
{
errtype = 2;
ifsuc = false;
break;
}
}
}
catch
{
wsretlog = "ws调用不成功";
errtype = 1;
ifsuc = false;
}
MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, string.Format("UPDATE FLOW_RUN set TIMES=TIMES+1,RETRY=0 where RUN_ID={0} ", flow179[i].run_id), null);
MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, string.Format(" INSERT INTO sync_flowlog (run_id,flow_id,run_name,sendlog,receivelog,sendtime,errtype) values ({0},{1},'{2}','{3}','{4}',sysdate(),{5}) ", flow179[i].run_id, flowid, flow179[i].run_name, JsonHelper.ObjectToJson(oasap17), wsretlog, errtype), null);
if (ifsuc)
{
int ret = MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, string.Format("update flow_run set SYNC_TIME=sysdate(),RETRY=0 where RUN_ID={0} ", flow179[i].run_id), null);
//更新数据库表示他已经同步过了并且同步成功了
if (ret > 0)
{
Logger.Log(string.Format("RUN_ID={0},已经更新数据库", flow179[i].run_id), flowid.ToString());
}
else
{
Logger.Error(string.Format("RUN_ID={0},更新没有成功", flow179[i].run_id), flowid.ToString());
}
}
else
{
Logger.Log(string.Format("RUN_ID={0},SAP返回错误信息", flow179[i].run_id), flowid.ToString());
}
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
// string constr = "server=192.168.27.239;User Id=developerGroup2;password=developer_203;Database=environmental_protection";
// MySqlConnection mycon = new MySqlConnection(constr);
// mycon.Open();
// MySqlCommand mycmd = new MySqlCommand("insert into token(tokenId,tokenUserId,tokenCreateTime,tokenExpireTime) values(uuid(),'',now(),now());", mycon);
// if (mycmd.ExecuteNonQuery() > 0)
// {
// Console.WriteLine("数据插入成功！");
// }
// Console.ReadLine();zq+2oE4AYSvTs56gHJODzA==/49BA59ABBE56E057
// mycon.Close();
// var data = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, "select * from token", null).Tables[0].DefaultView;
//
// var p = Md5Utils.Md5Encrypt32("123456");
// var u = "e1adc3949ba59abbe56e057f2f883e".ToUpper();
// //e10adc3949ba59abbe56e057f20f883e
// //E1ADC3949BA59ABBE56E057F2F883E
// //E1ADC3949BA59ABBE56E057F2F883E
// //e1adc3949ba59abbe56e057f2f883e
// var x = data.Table.Rows[0]["tokenUserId"];
// MySqlCommand sqlcom = new MySqlCommand
// {
// CommandText = "INSERT INTO `token` (`tokenId`,`tokenUserId`) VALUES(uuid(),@tokenUserId);"
// };
// MySqlParameter[] commandParameters = new MySqlParameter[]{
// new MySqlParameter("@tokenUserId","赵云")
// };
// MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn,CommandType.Text,sqlcom.CommandText, commandParameters);
// TimeSpan ts = DateTime.Now - DateTime.Parse("1970-1-1");
// var milliseconds = ts.TotalMilliseconds;
// var x = "";
// DLog.w("123");
//toList接口每个用户状态返回是否开启，可选
// Console.OutputEncoding = Encoding.GetEncoding(936);
Environment.SetEnvironmentVariable("needDetails", "true");
//对单个用户的推送
//pushMessageToApp();
//PushMessage.PushMessageToSingle();
//对指定列表用户推送
//PushMessageToList();
//PushMessage.PushMessageToList();
//对指定应用群推送
//pushMessageToApp();
//PushMessage.PushMessageToApp();
//APN简化推送
//apnPush();
MySqlCommand sqlcom = new MySqlCommand
{
CommandText = "select * from ep_args_code WHERE code =@id"
};
MySqlParameter[] commandParameters = new MySqlParameter[]
{
new MySqlParameter("@id", "201")
};
var cf = MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sqlcom.CommandText, commandParameters);
Dugufeixue.Common.SystemConfig.ConnectionStringKey = ConfigurationManager.ConnectionStrings["ConnStringDes"].ToString();
tbl_ep_args_code tbl = BF <bll_ep_args_code> .Instance.GetModel("201");
Console.Write(tbl.name);
HttpContext.Current.Response.Write(tbl.name);
}
/// <summary>
/// Inserts a row in the mp_SavedQuery table. Returns rows affected count.
/// </summary>
/// <param name="id"> id </param>
/// <param name="name"> name </param>
/// <param name="statement"> statement </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="createdBy"> createdBy </param>
/// <returns>int</returns>
public static int Create(
Guid id,
string name,
string statement,
DateTime createdUtc,
Guid createdBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SavedQuery (");
sqlCommand.Append("Id, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("Statement, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("LastModUtc, ");
sqlCommand.Append("LastModBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Id, ");
sqlCommand.Append("?Name, ");
sqlCommand.Append("?Statement, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?CreatedBy, ");
sqlCommand.Append("?LastModUtc, ");
sqlCommand.Append("?LastModBy )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[7];
arParams[0] = new MySqlParameter("?Id", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id.ToString();
arParams[1] = new MySqlParameter("?Name", MySqlDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = name;
arParams[2] = new MySqlParameter("?Statement", MySqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = statement;
arParams[3] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = createdUtc;
arParams[4] = new MySqlParameter("?CreatedBy", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = createdBy.ToString();
arParams[5] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdUtc;
arParams[6] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = createdBy.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Updates a row in the mp_MediaTrack table.
/// </summary>
/// <param name="trackID">The ID of the track.</param>
/// <param name="playerID">The ID of the player instance.</param>
/// <param name="trackType">The type of the track.</param>
/// <param name="trackOrder">The order position of the Media Track.</param>
/// <param name="name">The name of the Media Track.</param>
/// <param name="artist">The artist of the Media Track.</param>
/// <param name="userGuid">The Guid of the user who added the Media Track.</param>
/// <returns>True if the row is successfully updated.</returns>
public static bool Update(
int trackId,
int playerId,
string trackType,
int trackOrder,
string name,
string artist,
Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_MediaTrack ");
sqlCommand.Append("SET ");
sqlCommand.Append("PlayerID = ?PlayerID, ");
sqlCommand.Append("TrackType = ?TrackType, ");
sqlCommand.Append("TrackOrder = ?TrackOrder, ");
sqlCommand.Append("Name = ?Name, ");
sqlCommand.Append("Artist = ?Artist, ");
sqlCommand.Append("UserGuid = ?UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("TrackID = ?TrackID ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[7];
arParams[0] = new MySqlParameter("?TrackID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
arParams[1] = new MySqlParameter("?PlayerID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = playerId;
arParams[2] = new MySqlParameter("?TrackType", MySqlDbType.VarChar, 10);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = trackType;
arParams[3] = new MySqlParameter("?TrackOrder", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = trackOrder;
arParams[4] = new MySqlParameter("?Name", MySqlDbType.VarChar, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = name;
arParams[5] = new MySqlParameter("?Artist", MySqlDbType.VarChar, 100);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = artist;
arParams[6] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userGuid.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Updates a row in the mp_ContentMetaLink table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="rel"> rel </param>
/// <param name="href"> href </param>
/// <param name="hrefLang"> hrefLang </param>
/// <param name="rev"> rev </param>
/// <param name="type"> type </param>
/// <param name="media"> media </param>
/// <param name="sortRank"> sortRank </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <param name="lastModBy"> lastModBy </param>
/// <returns>bool</returns>
public static bool Update(
Guid guid,
string rel,
string href,
string hrefLang,
string rev,
string type,
string media,
int sortRank,
DateTime lastModUtc,
Guid lastModBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_ContentMetaLink ");
sqlCommand.Append("SET ");
sqlCommand.Append("Rel = ?Rel, ");
sqlCommand.Append("Href = ?Href, ");
sqlCommand.Append("HrefLang = ?HrefLang, ");
sqlCommand.Append("Rev = ?Rev, ");
sqlCommand.Append("Type = ?Type, ");
sqlCommand.Append("Media = ?Media, ");
sqlCommand.Append("SortRank = ?SortRank, ");
sqlCommand.Append("LastModUtc = ?LastModUtc, ");
sqlCommand.Append("LastModBy = ?LastModBy ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?Guid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[10];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?Rel", MySqlDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = rel;
arParams[2] = new MySqlParameter("?Href", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = href;
arParams[3] = new MySqlParameter("?HrefLang", MySqlDbType.VarChar, 10);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = hrefLang;
arParams[4] = new MySqlParameter("?Rev", MySqlDbType.VarChar, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = rev;
arParams[5] = new MySqlParameter("?Type", MySqlDbType.VarChar, 50);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = type;
arParams[6] = new MySqlParameter("?Media", MySqlDbType.VarChar, 50);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = media;
arParams[7] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = sortRank;
arParams[8] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModUtc;
arParams[9] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = lastModBy.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static int UpdateOrSaveCount(BloodInfo info)
{
if (info.category.BLOOD == null || info.category.BLOOD.count_statistics == null)
{
return(0);
}
string sql = "INSERT INTO blood_count(device_sn,count_times_total,count_times_wb_cbc,count_times_wb_cd,count_times_wb_crp,count_times_wb_cbc_crp,count_times_wb_cd_crp,count_times_pd_cbc,count_times_pd_cd,count_times_pd_crp,count_times_pd_cbc_crp,count_times_pd_cd_crp,count_times_tipwb_cbc,count_times_tipwb_cd,count_times_qc) "
+ "VALUES(?sn,?total,?wbcbc,?wbcd,?wbcrp,?wbcbccrp,?wbcdcrp,?pdcbc,?pdcd,?pdcrp,?pdcbccrp,?pdcdcrp,?qc) "
+ "ON DUPLICATE KEY UPDATE ";
if (info.category.BLOOD.count_statistics.count_times_TOTAL != null)
{
sql += ",count_times_total = ?total";
}
if (info.category.BLOOD.count_statistics.count_times_WB_CBC != null)
{
sql += ",count_times_wb_cbc = ?wbcbc";
}
if (info.category.BLOOD.count_statistics.count_times_WB_CD != null)
{
sql += ",count_times_wb_cd = ?wbcd";
}
if (info.category.BLOOD.count_statistics.count_times_WB_CRP != null)
{
sql += ",count_times_wb_crp = ?wbcrp";
}
if (info.category.BLOOD.count_statistics.count_times_WB_CBC_CRP != null)
{
sql += ",count_times_wb_cbc_crp = ?wbcbccrp";
}
if (info.category.BLOOD.count_statistics.count_times_WB_CD_CRP != null)
{
sql += ",count_times_wb_cd_crp = ?wbcdcrp";
}
if (info.category.BLOOD.count_statistics.count_times_PD_CBC != null)
{
sql += ",count_times_pd_cbc = ?pdcbc";
}
if (info.category.BLOOD.count_statistics.count_times_PD_CD != null)
{
sql += ",count_times_pd_cd = ?pdcd";
}
if (info.category.BLOOD.count_statistics.count_times_PD_CRP != null)
{
sql += ",count_times_pd_crp = ?pdcrp";
}
if (info.category.BLOOD.count_statistics.count_times_PD_CBC_CRP != null)
{
sql += ",count_times_pd_cbc_crp = ?pdcbccrp";
}
if (info.category.BLOOD.count_statistics.count_times_PD_CD_CRP != null)
{
sql += ",count_times_pd_cd_crp = ?pdcdcrp";
}
if (info.category.BLOOD.count_statistics.count_times_tipwb_cbc != null)
{
sql += ",count_times_tipwb_cbc = ?count_times_tipwb_cbc";
}
if (info.category.BLOOD.count_statistics.count_times_tipwb_cd != null)
{
sql += ",count_times_tipwb_cd = ?count_times_tipwb_cd";
}
if (info.category.BLOOD.count_statistics.count_times_QC != null)
{
sql += ",count_times_qc = ?qc";
}
sql += ";";
sql = sql.Replace("UPDATE ,", "UPDATE ");
MySqlParameter[] parameters = { new MySqlParameter("?sn", MySqlDbType.VarChar),
new MySqlParameter("?total", MySqlDbType.VarChar),
new MySqlParameter("?wbcbc", MySqlDbType.VarChar),
new MySqlParameter("?wbcd", MySqlDbType.VarChar),
new MySqlParameter("?wbcrp", MySqlDbType.VarChar),
new MySqlParameter("?wbcbccrp", MySqlDbType.VarChar),
new MySqlParameter("?wbcdcrp", MySqlDbType.VarChar),
new MySqlParameter("?pdcbc", MySqlDbType.VarChar),
new MySqlParameter("?pdcd", MySqlDbType.VarChar),
new MySqlParameter("?pdcrp", MySqlDbType.VarChar),
new MySqlParameter("?pdcbccrp", MySqlDbType.VarChar),
new MySqlParameter("?pdcdcrp", MySqlDbType.VarChar),
new MySqlParameter("?count_times_tipwb_cbc", MySqlDbType.VarChar),
new MySqlParameter("?count_times_tipwb_cd", MySqlDbType.VarChar),
new MySqlParameter("?qc", MySqlDbType.VarChar) };
parameters[0].Value = info.sn;
parameters[1].Value = info.category.BLOOD.count_statistics.count_times_TOTAL;
parameters[2].Value = info.category.BLOOD.count_statistics.count_times_WB_CBC;
parameters[3].Value = info.category.BLOOD.count_statistics.count_times_WB_CD;
parameters[4].Value = info.category.BLOOD.count_statistics.count_times_WB_CRP;
parameters[5].Value = info.category.BLOOD.count_statistics.count_times_WB_CBC_CRP;
parameters[6].Value = info.category.BLOOD.count_statistics.count_times_WB_CD_CRP;
parameters[7].Value = info.category.BLOOD.count_statistics.count_times_PD_CBC;
parameters[8].Value = info.category.BLOOD.count_statistics.count_times_PD_CD;
parameters[9].Value = info.category.BLOOD.count_statistics.count_times_PD_CRP;
parameters[10].Value = info.category.BLOOD.count_statistics.count_times_PD_CBC_CRP;
parameters[11].Value = info.category.BLOOD.count_statistics.count_times_PD_CD_CRP;
parameters[12].Value = info.category.BLOOD.count_statistics.count_times_tipwb_cbc;
parameters[13].Value = info.category.BLOOD.count_statistics.count_times_tipwb_cd;
parameters[14].Value = info.category.BLOOD.count_statistics.count_times_QC;
int num = MySqlHelper.ExecuteNonQuery(Conn, sql, parameters);
return(num);
}
/// <summary>
/// Inserts a row in the mp_ContentMetaLink table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="rel"> rel </param>
/// <param name="href"> href </param>
/// <param name="hrefLang"> hrefLang </param>
/// <param name="rev"> rev </param>
/// <param name="type"> type </param>
/// <param name="media"> media </param>
/// <param name="sortRank"> sortRank </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="createdBy"> createdBy </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <param name="lastModBy"> lastModBy </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid moduleGuid,
Guid contentGuid,
string rel,
string href,
string hrefLang,
string rev,
string type,
string media,
int sortRank,
DateTime createdUtc,
Guid createdBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_ContentMetaLink (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("ContentGuid, ");
sqlCommand.Append("Rel, ");
sqlCommand.Append("Href, ");
sqlCommand.Append("HrefLang, ");
sqlCommand.Append("Rev, ");
sqlCommand.Append("Type, ");
sqlCommand.Append("Media, ");
sqlCommand.Append("SortRank, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("LastModUtc, ");
sqlCommand.Append("LastModBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ModuleGuid, ");
sqlCommand.Append("?ContentGuid, ");
sqlCommand.Append("?Rel, ");
sqlCommand.Append("?Href, ");
sqlCommand.Append("?HrefLang, ");
sqlCommand.Append("?Rev, ");
sqlCommand.Append("?Type, ");
sqlCommand.Append("?Media, ");
sqlCommand.Append("?SortRank, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?CreatedBy, ");
sqlCommand.Append("?LastModUtc, ");
sqlCommand.Append("?LastModBy )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[15];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = moduleGuid.ToString();
arParams[3] = new MySqlParameter("?ContentGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = contentGuid.ToString();
arParams[4] = new MySqlParameter("?Rel", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = rel;
arParams[5] = new MySqlParameter("?Href", MySqlDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = href;
arParams[6] = new MySqlParameter("?HrefLang", MySqlDbType.VarChar, 10);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = hrefLang;
arParams[7] = new MySqlParameter("?Rev", MySqlDbType.VarChar, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = rev;
arParams[8] = new MySqlParameter("?Type", MySqlDbType.VarChar, 50);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = type;
arParams[9] = new MySqlParameter("?Media", MySqlDbType.VarChar, 50);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = media;
arParams[10] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = sortRank;
arParams[11] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = createdUtc;
arParams[12] = new MySqlParameter("?CreatedBy", MySqlDbType.VarChar, 36);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = createdBy.ToString();
arParams[13] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = createdUtc;
arParams[14] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = createdBy.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_ContentMeta table. Returns rows affected count.
/// </summary>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid moduleGuid,
Guid contentGuid,
string name,
string nameProperty,
string scheme,
string langCode,
string dir,
string metaContent,
string contentProperty,
int sortRank,
DateTime createdUtc,
Guid createdBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_ContentMeta (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("ContentGuid, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("NameProperty, ");
sqlCommand.Append("Scheme, ");
sqlCommand.Append("LangCode, ");
sqlCommand.Append("Dir, ");
sqlCommand.Append("MetaContent, ");
sqlCommand.Append("ContentProperty, ");
sqlCommand.Append("SortRank, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("LastModUtc, ");
sqlCommand.Append("LastModBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ModuleGuid, ");
sqlCommand.Append("?ContentGuid, ");
sqlCommand.Append("?Name, ");
sqlCommand.Append("?NameProperty, ");
sqlCommand.Append("?Scheme, ");
sqlCommand.Append("?LangCode, ");
sqlCommand.Append("?Dir, ");
sqlCommand.Append("?MetaContent, ");
sqlCommand.Append("?ContentProperty, ");
sqlCommand.Append("?SortRank, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?CreatedBy, ");
sqlCommand.Append("?LastModUtc, ");
sqlCommand.Append("?LastModBy )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[16];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = moduleGuid.ToString();
arParams[3] = new MySqlParameter("?ContentGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = contentGuid.ToString();
arParams[4] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = name;
arParams[5] = new MySqlParameter("?Scheme", MySqlDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = scheme;
arParams[6] = new MySqlParameter("?LangCode", MySqlDbType.VarChar, 10);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = langCode;
arParams[7] = new MySqlParameter("?Dir", MySqlDbType.VarChar, 3);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = dir;
arParams[8] = new MySqlParameter("?MetaContent", MySqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = metaContent;
arParams[9] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortRank;
arParams[10] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = createdUtc;
arParams[11] = new MySqlParameter("?CreatedBy", MySqlDbType.VarChar, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = createdBy.ToString();
arParams[12] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = createdUtc;
arParams[13] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = createdBy.ToString();
arParams[14] = new MySqlParameter("?NameProperty", MySqlDbType.VarChar, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = name;
arParams[15] = new MySqlParameter("?ContentProperty", MySqlDbType.VarChar, 255);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = metaContent;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams
);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_GoogleCheckoutLog table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="notificationType"> notificationType </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="serialNumber"> serialNumber </param>
/// <param name="gTimestamp"> gTimestamp </param>
/// <param name="orderNumber"> orderNumber </param>
/// <param name="buyerId"> buyerId </param>
/// <param name="fullfillState"> fullfillState </param>
/// <param name="financeState"> financeState </param>
/// <param name="emailListOptIn"> emailListOptIn </param>
/// <param name="avsResponse"> avsResponse </param>
/// <param name="cvnResponse"> cvnResponse </param>
/// <param name="authExpDate"> authExpDate </param>
/// <param name="authAmt"> authAmt </param>
/// <param name="discountTotal"> discountTotal </param>
/// <param name="shippingTotal"> shippingTotal </param>
/// <param name="taxTotal"> taxTotal </param>
/// <param name="orderTotal"> orderTotal </param>
/// <param name="latestChgAmt"> latestChgAmt </param>
/// <param name="totalChgAmt"> totalChgAmt </param>
/// <param name="latestRefundAmt"> latestRefundAmt </param>
/// <param name="totalRefundAmt"> totalRefundAmt </param>
/// <param name="latestChargeback"> latestChargeback </param>
/// <param name="totalChargeback"> totalChargeback </param>
/// <param name="cartXml"> cartXml </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string notificationType,
string rawResponse,
string serialNumber,
DateTime gTimestamp,
string orderNumber,
string buyerId,
string fullfillState,
string financeState,
bool emailListOptIn,
string avsResponse,
string cvnResponse,
DateTime authExpDate,
decimal authAmt,
decimal discountTotal,
decimal shippingTotal,
decimal taxTotal,
decimal orderTotal,
decimal latestChgAmt,
decimal totalChgAmt,
decimal latestRefundAmt,
decimal totalRefundAmt,
decimal latestChargeback,
decimal totalChargeback,
string cartXml,
string providerName)
{
#region Bit Conversion
int intEmailListOptIn;
if (emailListOptIn)
{
intEmailListOptIn = 1;
}
else
{
intEmailListOptIn = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_GoogleCheckoutLog (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("StoreGuid, ");
sqlCommand.Append("CartGuid, ");
sqlCommand.Append("NotificationType, ");
sqlCommand.Append("RawResponse, ");
sqlCommand.Append("SerialNumber, ");
sqlCommand.Append("GTimestamp, ");
sqlCommand.Append("OrderNumber, ");
sqlCommand.Append("BuyerId, ");
sqlCommand.Append("FullfillState, ");
sqlCommand.Append("FinanceState, ");
sqlCommand.Append("EmailListOptIn, ");
sqlCommand.Append("AvsResponse, ");
sqlCommand.Append("CvnResponse, ");
sqlCommand.Append("AuthExpDate, ");
sqlCommand.Append("AuthAmt, ");
sqlCommand.Append("DiscountTotal, ");
sqlCommand.Append("ShippingTotal, ");
sqlCommand.Append("TaxTotal, ");
sqlCommand.Append("OrderTotal, ");
sqlCommand.Append("LatestChgAmt, ");
sqlCommand.Append("TotalChgAmt, ");
sqlCommand.Append("LatestRefundAmt, ");
sqlCommand.Append("TotalRefundAmt, ");
sqlCommand.Append("LatestChargeback, ");
sqlCommand.Append("TotalChargeback, ");
sqlCommand.Append("CartXml, ");
sqlCommand.Append("ProviderName )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?StoreGuid, ");
sqlCommand.Append("?CartGuid, ");
sqlCommand.Append("?NotificationType, ");
sqlCommand.Append("?RawResponse, ");
sqlCommand.Append("?SerialNumber, ");
sqlCommand.Append("?GTimestamp, ");
sqlCommand.Append("?OrderNumber, ");
sqlCommand.Append("?BuyerId, ");
sqlCommand.Append("?FullfillState, ");
sqlCommand.Append("?FinanceState, ");
sqlCommand.Append("?EmailListOptIn, ");
sqlCommand.Append("?AvsResponse, ");
sqlCommand.Append("?CvnResponse, ");
sqlCommand.Append("?AuthExpDate, ");
sqlCommand.Append("?AuthAmt, ");
sqlCommand.Append("?DiscountTotal, ");
sqlCommand.Append("?ShippingTotal, ");
sqlCommand.Append("?TaxTotal, ");
sqlCommand.Append("?OrderTotal, ");
sqlCommand.Append("?LatestChgAmt, ");
sqlCommand.Append("?TotalChgAmt, ");
sqlCommand.Append("?LatestRefundAmt, ");
sqlCommand.Append("?TotalRefundAmt, ");
sqlCommand.Append("?LatestChargeback, ");
sqlCommand.Append("?TotalChargeback, ");
sqlCommand.Append("?CartXml, ");
sqlCommand.Append("?ProviderName )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[31];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new MySqlParameter("?StoreGuid", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new MySqlParameter("?CartGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new MySqlParameter("?NotificationType", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = notificationType;
arParams[7] = new MySqlParameter("?RawResponse", MySqlDbType.Text);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = rawResponse;
arParams[8] = new MySqlParameter("?SerialNumber", MySqlDbType.VarChar, 50);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = serialNumber;
arParams[9] = new MySqlParameter("?GTimestamp", MySqlDbType.DateTime);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = gTimestamp;
arParams[10] = new MySqlParameter("?OrderNumber", MySqlDbType.VarChar, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = orderNumber;
arParams[11] = new MySqlParameter("?BuyerId", MySqlDbType.VarChar, 50);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = buyerId;
arParams[12] = new MySqlParameter("?FullfillState", MySqlDbType.VarChar, 50);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = fullfillState;
arParams[13] = new MySqlParameter("?FinanceState", MySqlDbType.VarChar, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = financeState;
arParams[14] = new MySqlParameter("?EmailListOptIn", MySqlDbType.Int32);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = intEmailListOptIn;
arParams[15] = new MySqlParameter("?AvsResponse", MySqlDbType.VarChar, 5);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = avsResponse;
arParams[16] = new MySqlParameter("?CvnResponse", MySqlDbType.VarChar, 5);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = cvnResponse;
arParams[17] = new MySqlParameter("?AuthExpDate", MySqlDbType.DateTime);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = authExpDate;
arParams[18] = new MySqlParameter("?AuthAmt", MySqlDbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = authAmt;
arParams[19] = new MySqlParameter("?DiscountTotal", MySqlDbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = discountTotal;
arParams[20] = new MySqlParameter("?ShippingTotal", MySqlDbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = shippingTotal;
arParams[21] = new MySqlParameter("?TaxTotal", MySqlDbType.Decimal);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = taxTotal;
arParams[22] = new MySqlParameter("?OrderTotal", MySqlDbType.Decimal);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = orderTotal;
arParams[23] = new MySqlParameter("?LatestChgAmt", MySqlDbType.Decimal);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = latestChgAmt;
arParams[24] = new MySqlParameter("?TotalChgAmt", MySqlDbType.Decimal);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = totalChgAmt;
arParams[25] = new MySqlParameter("?LatestRefundAmt", MySqlDbType.Decimal);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = latestRefundAmt;
arParams[26] = new MySqlParameter("?TotalRefundAmt", MySqlDbType.Decimal);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = totalRefundAmt;
arParams[27] = new MySqlParameter("?LatestChargeback", MySqlDbType.Decimal);
arParams[27].Direction = ParameterDirection.Input;
arParams[27].Value = latestChargeback;
arParams[28] = new MySqlParameter("?TotalChargeback", MySqlDbType.Decimal);
arParams[28].Direction = ParameterDirection.Input;
arParams[28].Value = totalChargeback;
arParams[29] = new MySqlParameter("?CartXml", MySqlDbType.Text);
arParams[29].Direction = ParameterDirection.Input;
arParams[29].Value = cartXml;
arParams[30] = new MySqlParameter("?ProviderName", MySqlDbType.VarChar, 255);
arParams[30].Direction = ParameterDirection.Input;
arParams[30].Value = providerName;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the sts_ga_ExternalSearchData table. Returns rows affected count.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="analyticsDate"> analyticsDate </param>
/// <param name="searchTerm"> searchTerm </param>
/// <param name="pageViews"> pageViews </param>
/// <param name="visits"> visits </param>
/// <param name="newVisits"> newVisits </param>
/// <param name="bounces"> bounces </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string searchTerm,
int pageViews,
int visits,
int newVisits,
decimal bounceRate,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_ExternalSearchData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("SearchTerm, ");
sqlCommand.Append("PageViews, ");
sqlCommand.Append("Visits, ");
sqlCommand.Append("NewVisits, ");
sqlCommand.Append("BounceRate, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?SearchTerm, ");
sqlCommand.Append("?PageViews, ");
sqlCommand.Append("?Visits, ");
sqlCommand.Append("?NewVisits, ");
sqlCommand.Append("?BounceRate, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[10];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?AnalyticsDate", MySqlDbType.DateTime);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = analyticsDate;
if (searchTerm.Length > 255)
{
searchTerm = searchTerm.Substring(0, 255);
}
arParams[3] = new MySqlParameter("?SearchTerm", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = searchTerm;
arParams[4] = new MySqlParameter("?PageViews", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pageViews;
arParams[5] = new MySqlParameter("?Visits", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = visits;
arParams[6] = new MySqlParameter("?NewVisits", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = newVisits;
arParams[7] = new MySqlParameter("?BounceRate", MySqlDbType.Decimal);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = bounceRate;
arParams[8] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = capturedUtc;
arParams[9] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = Utility.DateTonInteger(analyticsDate);
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static bool UpdateSharedFile(
int itemId,
int moduleId,
int uploadUserId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int folderId,
Guid folderGuid,
Guid userGuid,
string description)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_SharedFiles ");
sqlCommand.Append("SET ");
sqlCommand.Append("ModuleID = ?ModuleID, ");
sqlCommand.Append("UploadUserID = ?UploadUserID, ");
sqlCommand.Append("FriendlyName = ?FriendlyName, ");
sqlCommand.Append("OriginalFileName = ?OriginalFileName, ");
sqlCommand.Append("ServerFileName = ?ServerFileName, ");
sqlCommand.Append("SizeInKB = ?SizeInKB, ");
sqlCommand.Append("UploadDate = ?UploadDate, ");
sqlCommand.Append("FolderID = ?FolderID, ");
sqlCommand.Append("UserGuid = ?UserGuid, ");
sqlCommand.Append("Description = ?Description, ");
sqlCommand.Append("FolderGuid = ?FolderGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ItemID = ?ItemID ;");
MySqlParameter[] arParams = new MySqlParameter[12];
arParams[0] = new MySqlParameter("?ItemID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new MySqlParameter("?UploadUserID", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = uploadUserId;
arParams[3] = new MySqlParameter("?FriendlyName", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = friendlyName;
arParams[4] = new MySqlParameter("?OriginalFileName", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = originalFileName;
arParams[5] = new MySqlParameter("?ServerFileName", MySqlDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = serverFileName;
arParams[6] = new MySqlParameter("?SizeInKB", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = sizeInKB;
arParams[7] = new MySqlParameter("?UploadDate", MySqlDbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = uploadDate;
arParams[8] = new MySqlParameter("?FolderID", MySqlDbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = folderId;
arParams[9] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = userGuid.ToString();
arParams[10] = new MySqlParameter("?FolderGuid", MySqlDbType.VarChar, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = folderGuid.ToString();
arParams[11] = new MySqlParameter("?Description", MySqlDbType.LongText);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = description;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
private void button1_Click(object sender, EventArgs e)
{
//try
{
string roomnum, sfznum, username, date, sqltext, sqltext1;
DataSet ds = new DataSet();
roomnum = comboBox1.SelectedItem.ToString();
sfznum = comboBox2.SelectedItem.ToString();
date = dateTimePicker1.Text;
username = textBox1.Text;
sqltext = "select username from userinf where sfzid='" + sfznum + "'";
ds = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, sqltext, null);
if (ds.Tables[0].Rows.Count < 1)
{
MessageBox.Show("请先填写房客信息");
}
else
{
string trueusername = ds.Tables[0].Rows[0]["username"].ToString();
if (trueusername.CompareTo(username) == 0)
{
sqltext = "select starttime from roomrent where roomnum='" + roomnum + "'";
ds = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, sqltext, null);
if (ds.Tables[0].Rows.Count > 0)
{
string starttime = ds.Tables[0].Rows[0]["starttime"].ToString();
if (starttime.CompareTo(date) == 0)
{
MessageBox.Show("此房间已入住，请更换房间！");
}
else
{
sqltext = "insert into roomrent values('" + roomnum + "','" + username + "','" + sfznum + "','" + date + "')";
MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sqltext, null);
rentdataGridView.DataSource = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, "select * from roomrent ", null).Tables[0].DefaultView;
sqltext1 = "update roominf set status='" + "已住" + "' where num='" + roomnum + "'";
MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sqltext1, null);
}
}
else
{
sqltext = "insert into roomrent values('" + roomnum + "','" + username + "','" + sfznum + "','" + date + "')";
MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sqltext, null);
rentdataGridView.DataSource = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, "select * from roomrent ", null).Tables[0].DefaultView;
sqltext1 = "update roominf set status='" + "已住" + "' where num='" + roomnum + "'";
MySqlHelper.ExecuteNonQuery(MySqlHelper.Conn, CommandType.Text, sqltext1, null);
}
}
else
{
MessageBox.Show("身份证与姓名信息不匹配，请重新输入！");
}
}
}
/*catch (Exception)
* {
* MessageBox.Show("此房间已入住，请更换房间！");
* // throw;
* }*/
}
/// <summary>
/// Inserts a row in the mp_ContentRating table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="emailAddress"> emailAddress </param>
/// <param name="rating"> rating </param>
/// <param name="comments"> comments </param>
/// <param name="ipAddress"> ipAddress </param>
/// <param name="createdUtc"> createdUtc </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
Guid siteGuid,
Guid contentGuid,
Guid userGuid,
string emailAddress,
int rating,
string comments,
string ipAddress,
DateTime createdUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_ContentRating (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ContentGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("EmailAddress, ");
sqlCommand.Append("Rating, ");
sqlCommand.Append("Comments, ");
sqlCommand.Append("IpAddress, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("LastModUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ContentGuid, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?EmailAddress, ");
sqlCommand.Append("?Rating, ");
sqlCommand.Append("?Comments, ");
sqlCommand.Append("?IpAddress, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?CreatedUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[9];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?ContentGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = contentGuid.ToString();
arParams[3] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new MySqlParameter("?EmailAddress", MySqlDbType.VarChar, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = emailAddress;
arParams[5] = new MySqlParameter("?Rating", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = rating;
arParams[6] = new MySqlParameter("?Comments", MySqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = comments;
arParams[7] = new MySqlParameter("?IpAddress", MySqlDbType.VarChar, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = ipAddress;
arParams[8] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = createdUtc;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// 清空表中的所有数据。
/// </summary>
/// <returns>返回影响记录的行数,-1表示操作失败,大于-1表示成功</returns>
public virtual int DeleteAll()
{
string sqlCmd = string.Format("DELETE FROM {0}", this._tableName);
return(MySqlHelper.ExecuteNonQuery(this._connectionString, sqlCmd));
}
public static int InsertCountDetail(BloodInfo info)
{
if (info.category.BLOOD == null || info.category.BLOOD.count_statistics == null)
{
return(0);
}
string base_sql = "INSERT INTO blood_count_detail(device_sn,device_count,device_count_type) VALUES(\"{0}\",{1},\"{2}\"); ";
string sql = "";
if (info.category.BLOOD.count_statistics.count_times_TOTAL != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_TOTAL, "count_times_total");
}
if (info.category.BLOOD.count_statistics.count_times_WB_CBC != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_WB_CBC, "count_times_wb_cbc");
}
if (info.category.BLOOD.count_statistics.count_times_WB_CD != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_WB_CD, "count_times_wb_cd");
}
if (info.category.BLOOD.count_statistics.count_times_WB_CRP != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_WB_CRP, "count_times_wb_crp");
}
if (info.category.BLOOD.count_statistics.count_times_WB_CBC_CRP != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_WB_CBC_CRP, "count_times_wb_cbc_crp");
}
if (info.category.BLOOD.count_statistics.count_times_WB_CD_CRP != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_WB_CD_CRP, "count_times_wb_cd_crp");
}
if (info.category.BLOOD.count_statistics.count_times_PD_CBC != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_PD_CBC, "count_times_pd_cbc");
}
if (info.category.BLOOD.count_statistics.count_times_PD_CD != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_PD_CD, "count_times_pd_cd");
}
if (info.category.BLOOD.count_statistics.count_times_PD_CRP != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_PD_CRP, "count_times_pd_crp");
}
if (info.category.BLOOD.count_statistics.count_times_PD_CBC_CRP != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_PD_CBC_CRP, "count_times_pd_cbc_crp");
}
if (info.category.BLOOD.count_statistics.count_times_PD_CD_CRP != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_PD_CD_CRP, "count_times_pd_cd_crp");
}
if (info.category.BLOOD.count_statistics.count_times_tipwb_cbc != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_tipwb_cbc, "count_times_tipwb_cbc");
}
if (info.category.BLOOD.count_statistics.count_times_tipwb_cd != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_tipwb_cd, "count_times_tipwb_cd");
}
if (info.category.BLOOD.count_statistics.count_times_QC != null)
{
sql += string.Format(base_sql, info.sn, info.category.BLOOD.count_statistics.count_times_QC, "count_times_qc");
}
MySqlParameter[] parameters = {};
int num = MySqlHelper.ExecuteNonQuery(Conn, sql, parameters);
return(num);
}
/// <summary>
/// Updates a row in the mp_PayPalLog table. Returns true if row updated.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="requestType"> requestType </param>
/// <param name="apiVersion"> apiVersion </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="token"> token </param>
/// <param name="payerId"> payerId </param>
/// <param name="transactionId"> transactionId </param>
/// <param name="paymentType"> paymentType </param>
/// <param name="paymentStatus"> paymentStatus </param>
/// <param name="pendingReason"> pendingReason </param>
/// <param name="reasonCode"> reasonCode </param>
/// <param name="currencyCode"> currencyCode </param>
/// <param name="exchangeRate"> exchangeRate </param>
/// <param name="cartTotal"> cartTotal </param>
/// <param name="payPalAmt"> payPalAmt </param>
/// <param name="taxAmt"> taxAmt </param>
/// <param name="feeAmt"> feeAmt </param>
/// <param name="settleAmt"> settleAmt </param>
/// <returns>bool</returns>
public static bool Update(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string requestType,
string apiVersion,
string rawResponse,
string token,
string payerId,
string transactionId,
string paymentType,
string paymentStatus,
string pendingReason,
string reasonCode,
string currencyCode,
decimal exchangeRate,
decimal cartTotal,
decimal payPalAmt,
decimal taxAmt,
decimal feeAmt,
decimal settleAmt)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_PayPalLog ");
sqlCommand.Append("SET ");
sqlCommand.Append("CreatedUtc = ?CreatedUtc, ");
sqlCommand.Append("SiteGuid = ?SiteGuid, ");
sqlCommand.Append("UserGuid = ?UserGuid, ");
sqlCommand.Append("StoreGuid = ?StoreGuid, ");
sqlCommand.Append("CartGuid = ?CartGuid, ");
sqlCommand.Append("RequestType = ?RequestType, ");
sqlCommand.Append("ApiVersion = ?ApiVersion, ");
sqlCommand.Append("RawResponse = ?RawResponse, ");
sqlCommand.Append("Token = ?Token, ");
sqlCommand.Append("PayerId = ?PayerId, ");
sqlCommand.Append("TransactionId = ?TransactionId, ");
sqlCommand.Append("PaymentType = ?PaymentType, ");
sqlCommand.Append("PaymentStatus = ?PaymentStatus, ");
sqlCommand.Append("PendingReason = ?PendingReason, ");
sqlCommand.Append("ReasonCode = ?ReasonCode, ");
sqlCommand.Append("CurrencyCode = ?CurrencyCode, ");
sqlCommand.Append("ExchangeRate = ?ExchangeRate, ");
sqlCommand.Append("CartTotal = ?CartTotal, ");
sqlCommand.Append("PayPalAmt = ?PayPalAmt, ");
sqlCommand.Append("TaxAmt = ?TaxAmt, ");
sqlCommand.Append("FeeAmt = ?FeeAmt, ");
sqlCommand.Append("SettleAmt = ?SettleAmt ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowGuid = ?RowGuid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[23];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new MySqlParameter("?StoreGuid", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new MySqlParameter("?CartGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new MySqlParameter("?RequestType", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = requestType;
arParams[7] = new MySqlParameter("?ApiVersion", MySqlDbType.VarChar, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = apiVersion;
arParams[8] = new MySqlParameter("?RawResponse", MySqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = rawResponse;
arParams[9] = new MySqlParameter("?Token", MySqlDbType.VarChar, 50);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = token;
arParams[10] = new MySqlParameter("?PayerId", MySqlDbType.VarChar, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = payerId;
arParams[11] = new MySqlParameter("?TransactionId", MySqlDbType.VarChar, 50);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = transactionId;
arParams[12] = new MySqlParameter("?PaymentType", MySqlDbType.VarChar, 10);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = paymentType;
arParams[13] = new MySqlParameter("?PaymentStatus", MySqlDbType.VarChar, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = paymentStatus;
arParams[14] = new MySqlParameter("?PendingReason", MySqlDbType.VarChar, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = pendingReason;
arParams[15] = new MySqlParameter("?ReasonCode", MySqlDbType.VarChar, 50);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = reasonCode;
arParams[16] = new MySqlParameter("?CurrencyCode", MySqlDbType.VarChar, 50);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = currencyCode;
arParams[17] = new MySqlParameter("?ExchangeRate", MySqlDbType.Decimal);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = exchangeRate;
arParams[18] = new MySqlParameter("?CartTotal", MySqlDbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = cartTotal;
arParams[19] = new MySqlParameter("?PayPalAmt", MySqlDbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = payPalAmt;
arParams[20] = new MySqlParameter("?TaxAmt", MySqlDbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = taxAmt;
arParams[21] = new MySqlParameter("?FeeAmt", MySqlDbType.Decimal);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = feeAmt;
arParams[22] = new MySqlParameter("?SettleAmt", MySqlDbType.Decimal);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = settleAmt;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static int UpdateOrSaveReagent(BloodInfo info)
{
if (info.category.BLOOD == null || info.category.BLOOD.reagent == null)
{
return(0);
}
string sql = "INSERT INTO blood_reagent(device_sn,reagent_type,reagent_dil,reagent_lh,reagent_r1,reagent_r2,reagent_diff1,reagent_diff2,reagent_fl1,reagent_fl2,reagent_fl3,reagent_fl4,reagent_fl5,reagent_fl6) "
+ "VALUES(?sn,?reagent_type,?dil,?lh,?r1,?r2,?diff1,?diff2,?fl1,?fl2,?fl3,?fl4,?fl5,?fl6) "
+ "ON DUPLICATE KEY UPDATE ";
if (info.category.BLOOD.reagent.reagent_type != null)
{
sql += ",reagent_type = ?reagent_type";
}
if (info.category.BLOOD.reagent.reagent_DIL != null)
{
sql += ",reagent_dil = ?dil";
}
if (info.category.BLOOD.reagent.reagent_LH != null)
{
sql += ",reagent_lh = ?lh";
}
if (info.category.BLOOD.reagent.reagent_R1 != null)
{
sql += ",reagent_r1 = ?r1";
}
if (info.category.BLOOD.reagent.reagent_R2 != null)
{
sql += ",reagent_r2 = ?r2";
}
if (info.category.BLOOD.reagent.reagent_DIFF1 != null)
{
sql += ",reagent_diff1 = ?diff1";
}
if (info.category.BLOOD.reagent.reagent_DIFF2 != null)
{
sql += ",reagent_diff2 = ?diff2";
}
if (info.category.BLOOD.reagent.reagent_FL1 != null)
{
sql += ",reagent_fl1 = ?fl1";
}
if (info.category.BLOOD.reagent.reagent_FL2 != null)
{
sql += ",reagent_fl2 = ?fl2";
}
if (info.category.BLOOD.reagent.reagent_FL3 != null)
{
sql += ",reagent_fl3 = ?fl3";
}
if (info.category.BLOOD.reagent.reagent_FL4 != null)
{
sql += ",reagent_fl4 = ?fl4";
}
if (info.category.BLOOD.reagent.reagent_FL5 != null)
{
sql += ",reagent_fl5 = ?fl5";
}
if (info.category.BLOOD.reagent.reagent_FL6 != null)
{
sql += ",reagent_fl6 = ?fl6";
}
sql += ";";
sql = sql.Replace("UPDATE ,", "UPDATE ");
MySqlParameter[] parameters = { new MySqlParameter("?sn", MySqlDbType.VarChar),
new MySqlParameter("?reagent_type", MySqlDbType.VarChar),
new MySqlParameter("?dil", MySqlDbType.VarChar),
new MySqlParameter("?lh", MySqlDbType.VarChar),
new MySqlParameter("?r1", MySqlDbType.VarChar),
new MySqlParameter("?r2", MySqlDbType.VarChar),
new MySqlParameter("?diff1", MySqlDbType.VarChar),
new MySqlParameter("?diff2", MySqlDbType.VarChar),
new MySqlParameter("?fl1", MySqlDbType.VarChar),
new MySqlParameter("?fl2", MySqlDbType.VarChar),
new MySqlParameter("?fl3", MySqlDbType.VarChar),
new MySqlParameter("?fl4", MySqlDbType.VarChar),
new MySqlParameter("?fl5", MySqlDbType.VarChar),
new MySqlParameter("?fl6", MySqlDbType.VarChar) };
parameters[0].Value = info.sn;
parameters[1].Value = info.category.BLOOD.reagent.reagent_type;
parameters[2].Value = info.category.BLOOD.reagent.reagent_DIL;
parameters[3].Value = info.category.BLOOD.reagent.reagent_LH;
parameters[4].Value = info.category.BLOOD.reagent.reagent_R1;
parameters[5].Value = info.category.BLOOD.reagent.reagent_R2;
parameters[6].Value = info.category.BLOOD.reagent.reagent_DIFF1;
parameters[7].Value = info.category.BLOOD.reagent.reagent_DIFF2;
parameters[8].Value = info.category.BLOOD.reagent.reagent_FL1;
parameters[9].Value = info.category.BLOOD.reagent.reagent_FL2;
parameters[10].Value = info.category.BLOOD.reagent.reagent_FL3;
parameters[11].Value = info.category.BLOOD.reagent.reagent_FL4;
parameters[12].Value = info.category.BLOOD.reagent.reagent_FL5;
parameters[13].Value = info.category.BLOOD.reagent.reagent_FL6;
int num = MySqlHelper.ExecuteNonQuery(Conn, sql, parameters);
return(num);
}
/// <summary>
/// Inserts a row in the sts_MembershipProduct table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="memberDefGuid"> memberDefGuid </param>
/// <param name="title"> title </param>
/// <param name="description"> description </param>
/// <param name="durationInDays"> durationInDays </param>
/// <param name="price"> price </param>
/// <param name="isAvailable"> isAvailable </param>
/// <param name="isTaxable"> isTaxable </param>
/// <param name="sortRank"> sortRank </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="createdBy"> createdBy </param>
/// <param name="siteGuid"> siteGuid </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid memberDefGuid,
string title,
string description,
int durationInDays,
int gracePeriodDays,
DateTime offerBeginUtc,
DateTime offerEndUtc,
decimal price,
bool isAvailable,
bool isTaxable,
bool newMemberOnly,
int sortRank,
DateTime createdUtc,
Guid createdBy,
Guid siteGuid,
decimal renewPrice,
bool renewAnyDef,
bool renewExpired)
{
#region Bit Conversion
int intIsAvailable = 0;
if (isAvailable)
{
intIsAvailable = 1;
}
int intIsTaxable = 0;
if (isTaxable)
{
intIsTaxable = 1;
}
int intNewMemberOnly = 0;
if (newMemberOnly)
{
intNewMemberOnly = 1;
}
int intrenewAnyDef = 0;
if (renewAnyDef)
{
intrenewAnyDef = 1;
}
int intrenewExpired = 0;
if (renewExpired)
{
intrenewExpired = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_MembershipProduct (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("MemberDefGuid, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("DurationInDays, ");
sqlCommand.Append("GracePeriodDays, ");
sqlCommand.Append("OfferBeginUtc, ");
sqlCommand.Append("OfferEndUtc, ");
sqlCommand.Append("Price, ");
sqlCommand.Append("IsAvailable, ");
sqlCommand.Append("IsTaxable, ");
sqlCommand.Append("NewMemberOnly, ");
sqlCommand.Append("QtySold, ");
sqlCommand.Append("SortRank, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("ModifiedUtc, ");
sqlCommand.Append("ModifiedBy, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("RenewPrice, ");
sqlCommand.Append("RenewAnyDef, ");
sqlCommand.Append("RenewExpired ");
sqlCommand.Append(") ");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?MemberDefGuid, ");
sqlCommand.Append("?Title, ");
sqlCommand.Append("?Description, ");
sqlCommand.Append("?DurationInDays, ");
sqlCommand.Append("?GracePeriodDays, ");
sqlCommand.Append("?OfferBeginUtc, ");
sqlCommand.Append("?OfferEndUtc, ");
sqlCommand.Append("?Price, ");
sqlCommand.Append("?IsAvailable, ");
sqlCommand.Append("?IsTaxable, ");
sqlCommand.Append("?NewMemberOnly, ");
sqlCommand.Append("?QtySold, ");
sqlCommand.Append("?SortRank, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?CreatedBy, ");
sqlCommand.Append("?ModifiedUtc, ");
sqlCommand.Append("?ModifiedBy, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?RenewPrice, ");
sqlCommand.Append("?RenewAnyDef, ");
sqlCommand.Append("?RenewExpired ");
sqlCommand.Append(") ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[22];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?MemberDefGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = memberDefGuid.ToString();
arParams[2] = new MySqlParameter("?Title", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = title;
arParams[3] = new MySqlParameter("?Description", MySqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = description;
arParams[4] = new MySqlParameter("?DurationInDays", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = durationInDays;
arParams[5] = new MySqlParameter("?GracePeriodDays", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = gracePeriodDays;
arParams[6] = new MySqlParameter("?OfferBeginUtc", MySqlDbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = offerBeginUtc;
arParams[7] = new MySqlParameter("?OfferEndUtc", MySqlDbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
if (offerEndUtc > DateTime.MinValue)
{
arParams[7].Value = offerEndUtc;
}
else
{
arParams[7].Value = DBNull.Value;
}
arParams[8] = new MySqlParameter("?Price", MySqlDbType.Decimal);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = price;
arParams[9] = new MySqlParameter("?IsAvailable", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = intIsAvailable;
arParams[10] = new MySqlParameter("?IsTaxable", MySqlDbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = intIsTaxable;
arParams[11] = new MySqlParameter("?NewMemberOnly", MySqlDbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = intNewMemberOnly;
arParams[12] = new MySqlParameter("?QtySold", MySqlDbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = 0;
arParams[13] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = sortRank;
arParams[14] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = createdUtc;
arParams[15] = new MySqlParameter("?CreatedBy", MySqlDbType.VarChar, 36);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = createdBy.ToString();
arParams[16] = new MySqlParameter("?ModifiedUtc", MySqlDbType.DateTime);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = createdUtc;
arParams[17] = new MySqlParameter("?ModifiedBy", MySqlDbType.VarChar, 36);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = createdBy.ToString();
arParams[18] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = siteGuid.ToString();
arParams[19] = new MySqlParameter("?RenewPrice", MySqlDbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = renewPrice;
arParams[20] = new MySqlParameter("?RenewAnyDef", MySqlDbType.Int32);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = intrenewAnyDef;
arParams[21] = new MySqlParameter("?RenewExpired", MySqlDbType.Int32);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = intrenewExpired;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static int UpdateOrSaveModule(BloodInfo info)
{
if (info.category.BLOOD == null || info.category.BLOOD.module_statistics == null)
{
return(0);
}
string sql = "INSERT INTO blood_module(device_sn,hole_times_wbc,hole_times_rbc,needle_times_impale,sampling_times_fault,syringe_times_syringe_fault,inject_times_fault,mixing_times_fault) "
+ "VALUES(?sn,?wbc,?rbc,?needle,?sampling,?syringe,?inject,?mixing) "
+ "ON DUPLICATE KEY UPDATE ";
if (info.category.BLOOD.module_statistics.hole_times_WBC != null)
{
sql += ",hole_times_wbc = ?wbc";
}
if (info.category.BLOOD.module_statistics.hole_times_RBC != null)
{
sql += ",hole_times_rbc = ?rbc";
}
if (info.category.BLOOD.module_statistics.needle_times_impale != null)
{
sql += ",needle_times_impale = ?needle";
}
if (info.category.BLOOD.module_statistics.sampling_times_fault != null)
{
sql += ",sampling_times_fault = ?sampling";
}
if (info.category.BLOOD.module_statistics.Syringe_times_syringe_fault != null)
{
sql += ",syringe_times_syringe_fault = ?syringe";
}
if (info.category.BLOOD.module_statistics.inject_times_fault != null)
{
sql += ",inject_times_fault = ?inject";
}
if (info.category.BLOOD.module_statistics.mixing_times_fault != null)
{
sql += ",mixing_times_fault = ?mixing";
}
sql += ";";
sql = sql.Replace("UPDATE ,", "UPDATE ");
MySqlParameter[] parameters = { new MySqlParameter("?sn", MySqlDbType.VarChar),
new MySqlParameter("?wbc", MySqlDbType.VarChar),
new MySqlParameter("?rbc", MySqlDbType.VarChar),
new MySqlParameter("?needle", MySqlDbType.VarChar),
new MySqlParameter("?sampling", MySqlDbType.VarChar),
new MySqlParameter("?syringe", MySqlDbType.VarChar),
new MySqlParameter("?inject", MySqlDbType.VarChar),
new MySqlParameter("?mixing", MySqlDbType.VarChar) };
parameters[0].Value = info.sn;
parameters[1].Value = info.category.BLOOD.module_statistics.hole_times_WBC;
parameters[2].Value = info.category.BLOOD.module_statistics.hole_times_RBC;
parameters[3].Value = info.category.BLOOD.module_statistics.needle_times_impale;
parameters[4].Value = info.category.BLOOD.module_statistics.sampling_times_fault;
parameters[5].Value = info.category.BLOOD.module_statistics.Syringe_times_syringe_fault;
parameters[6].Value = info.category.BLOOD.module_statistics.inject_times_fault;
parameters[7].Value = info.category.BLOOD.module_statistics.mixing_times_fault;
int num = MySqlHelper.ExecuteNonQuery(Conn, sql, parameters);
return(num);
}
/// <summary>
/// 发送OpenShop中的手机短信
/// </summary>
private static void SendPending()
{
string strMyString = ConfigHelper.ConnectionString.MySqlString;
if (String.IsNullOrEmpty(strMyString))
{
EventLog(string.Format("MySQL 连接字符串无效"));
return;
}
try
{
MySqlHelper oMySqlHelper = new MySqlHelper(strMyString);
EucpHelper oEucpHelper = new EucpHelper();
int nCounter = 0;
// 发送手机短信
string sPending = "SELECT * FROM system_message_queue WHERE status = 1";
Dictionary<int, int> oSendList = new Dictionary<int, int>();
using (MySqlDataReader oDataReader = oMySqlHelper.ExecuteReader(sPending))
{
if (oDataReader.HasRows)
{
while (oDataReader.Read())
{
int nMsgID = (int)oDataReader["id"];
string strMsgType = oDataReader["msg_type"].ToString().Trim();
string strMobile = oDataReader["target_ids"].ToString();
string strContent = oDataReader["msg_content"].ToString();
string strMsgName = oDataReader["msg_name"].ToString();
string strOrderSn = oDataReader["order_sn"].ToString();
int nResult = -1;
if (strMsgType == "1") // 发送邮件
{
nResult = MailHelper.SendMail(ConfigHelper.SmtpConfig.Sender, ConfigHelper.SmtpConfig.From,
strMobile, "筑巢家居商城订单号：" + strOrderSn, strContent);
}
else if (strMsgType == "2") // 发送短信
{
strContent = strContent.Replace("<p>", "");
strContent = strContent.Replace("</p>", "");
nResult = oEucpHelper.SendSms(strMobile, strContent);
}
oSendList.Add(nMsgID, nResult);
nCounter++;
}
}
oDataReader.Close();
}
// 检测余额
string strBalance = oEucpHelper.GetBalance().ToString();
// 更新已发送状态
foreach (int nKey in oSendList.Keys)
{
string strUpdateStatus = "UPDATE system_message_queue SET status = 3, msg_desc = '" + oSendList[nKey].ToString() + ":" + strBalance + "', send_time = NOW() WHERE id = " + nKey.ToString();
oMySqlHelper.ExecuteNonQuery(strUpdateStatus);
}
EventLog(string.Format("已发送{0}条手机短信/邮件", nCounter));
nCounter = 0;
// 接收手机短信
List<object> oGetList = oEucpHelper.ReceiveSms();
foreach (Dictionary<string, object> oMessage in oGetList)
{
string strInsertMessage = "INSERT INTO system_message_receive (mobileNumber, smsContent, sentTime, channelNumber) " +
" VALUES ('" + oMessage["Mobile"].ToString() + "', '" + oMessage["Content"].ToString() + "', '" +
oMessage["SentTime"].ToString() + "', '" + oMessage["Channel"].ToString() + "')";
oMySqlHelper.ExecuteNonQuery(strInsertMessage);
nCounter++;
}
EventLog(string.Format("已接收{0}条手机短信", nCounter));
}
catch (Exception ex)
{
EventLog(string.Format("SendPending 出现错误 {0}", ex.Message));
}
}
/// <summary>
/// Inserts a row in the mp_Currency table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="title"> title </param>
/// <param name="code"> code </param>
/// <param name="symbolLeft"> symbolLeft </param>
/// <param name="symbolRight"> symbolRight </param>
/// <param name="decimalPointChar"> decimalPointChar </param>
/// <param name="thousandsPointChar"> thousandsPointChar </param>
/// <param name="decimalPlaces"> decimalPlaces </param>
/// <param name="value"> value </param>
/// <param name="lastModified"> lastModified </param>
/// <param name="created"> created </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
string title,
string code,
string symbolLeft,
string symbolRight,
string decimalPointChar,
string thousandsPointChar,
string decimalPlaces,
decimal value,
DateTime lastModified,
DateTime created)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_Currency (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Code, ");
sqlCommand.Append("SymbolLeft, ");
sqlCommand.Append("SymbolRight, ");
sqlCommand.Append("DecimalPointChar, ");
sqlCommand.Append("ThousandsPointChar, ");
sqlCommand.Append("DecimalPlaces, ");
sqlCommand.Append("Value, ");
sqlCommand.Append("LastModified, ");
sqlCommand.Append("Created )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?Title, ");
sqlCommand.Append("?Code, ");
sqlCommand.Append("?SymbolLeft, ");
sqlCommand.Append("?SymbolRight, ");
sqlCommand.Append("?DecimalPointChar, ");
sqlCommand.Append("?ThousandsPointChar, ");
sqlCommand.Append("?DecimalPlaces, ");
sqlCommand.Append("?Value, ");
sqlCommand.Append("?LastModified, ");
sqlCommand.Append("?Created )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[11];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?Title", MySqlDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new MySqlParameter("?Code", MySqlDbType.VarChar, 3);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = code;
arParams[3] = new MySqlParameter("?SymbolLeft", MySqlDbType.VarChar, 15);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = symbolLeft;
arParams[4] = new MySqlParameter("?SymbolRight", MySqlDbType.VarChar, 15);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = symbolRight;
arParams[5] = new MySqlParameter("?DecimalPointChar", MySqlDbType.VarChar, 1);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = decimalPointChar;
arParams[6] = new MySqlParameter("?ThousandsPointChar", MySqlDbType.VarChar, 1);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = thousandsPointChar;
arParams[7] = new MySqlParameter("?DecimalPlaces", MySqlDbType.VarChar, 1);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = decimalPlaces;
arParams[8] = new MySqlParameter("?Value", MySqlDbType.Decimal);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = value;
arParams[9] = new MySqlParameter("?LastModified", MySqlDbType.DateTime);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = lastModified;
arParams[10] = new MySqlParameter("?Created", MySqlDbType.DateTime);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = created;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}