public void GetCustomersFromDBByUserUID(string query)
{
MySqlDataReader reader;
reader = MySqlUtil.ExecuteMySQLQuery(CustomerServiceConfig.MySqlConnection, query);
if (reader.HasRows)
{
while (reader.Read())
{
CustDetails.CustomerUID = reader["CustomerUID"].ToString().ToLower();
CustDetails.Name = reader["CustomerName"].ToString();
if (reader["CustomerTypeID"].ToString() == "1")
{
CustDetails.CustomerType = "Dealer";
CustDetails.NetworkDealerCode = reader["NetworkDealerCode"].ToString();
CustDetails.DisplayName = "(" + CustDetails.NetworkDealerCode + ") " + CustDetails.Name;
}
else if (reader["CustomerTypeID"].ToString() == "2")
{
CustDetails.CustomerType = "Customer";
CustDetails.NetworkCustomerCode = reader["NetworkCustomerCode"].ToString();
CustDetails.DisplayName = "(" + CustDetails.NetworkCustomerCode + ") " + CustDetails.Name;
}
CustomersForUserAccountDBResult.Customers.Add(CustDetails);
}
}
}
void UpdateOverView()
{
MySqlUtil.GEN_OVERVIEW_INFO();
lblHPri.Text = MySqlUtil.max_price_med_name;
lblHPVal.Text = "\u20B9" + MySqlUtil.max_price;
lblLowPric.Text = MySqlUtil.min_price_med_name;
lblLPVa.Text = "\u20B9" + MySqlUtil.min_price;
lblHighStock.Text = MySqlUtil.max_stocked_med_name;
lblHSVa.Text = MySqlUtil.max_stocked;
lblLowStock.Text = MySqlUtil.min_stocked_med_name;
lblLSVa.Text = MySqlUtil.min_stocked;
lblHighBatch.Text = MySqlUtil.max_batch_med_name;
lblHBVa.Text = "#" + MySqlUtil.max_batch;
lblLowBatch.Text = MySqlUtil.min_batch_med_name;
lblLBVa.Text = "#" + MySqlUtil.min_batch;
MySqlUtil.TOTAL_MED_WORTH();
lblTStockPrice.Text = MySqlUtil.totalMedicineWorth;
var t1 = MySqlUtil.MediCineCount();
lblInStorage.Text = $"{t1}({(t1 * 100 / 15000)}%)";
lblTbatch.Text = MySqlUtil.BatchCount().ToString();
lblTBExp.Text = MySqlUtil.ExpBatchCount().ToString();
}
/// <summary>
/// 加赠元宝
/// </summary>
/// <param name="uid"></param>
/// <param name="goods"></param>
private void AddExtraYuanBao(string uid, Goods goods)
{
//更新userRecharge数据
var userRecharge = NHibernateHelper.userRechargeManager.GetUserRecharge(uid, goods.goods_id);
if (userRecharge == null)
{
userRecharge = ModelFactory.CreateUserRecharge(uid, goods.goods_id);
}
//首次充值，加送元宝
if (userRecharge.recharge_count == 0)
{
if (!string.IsNullOrWhiteSpace(goods.extra_reward))
{
if (MySqlUtil.AddProp(uid, goods.extra_reward, "元宝加赠"))
{
LogUtil.Log(uid, MyCommon.OpType.EXTRA_YUANBAO, $"购买了{goods.goods_id},{goods.goods_name},加赠了{goods.extra_reward}");
}
else
{
MySqlService.log.Warn($"{uid},{goods.goods_id} 加赠元宝失败，数据库内部错误");
}
}
}
userRecharge.recharge_count++;
NHibernateHelper.userRechargeManager.Update(userRecharge);
}
private void GetAllTuiGuangReward(string uid, JObject jObject)
{
List <UserExtend> userExtends = MySqlManager <UserExtend> .Instance.GetMyExtendDataByUid(uid).ToList();
StringBuilder sb = new StringBuilder();
foreach (var userExtend in userExtends)
{
if (userExtend.task1 == 2)
{
MySqlUtil.AddProp(uid, "121:1", $"领取{userExtend.Uid}的任务1奖励");
sb.Append("121:1");
sb.Append(";");
userExtend.task1 = 3;
}
if (userExtend.task2 == 2)
{
MySqlUtil.AddProp(uid, "111:1", $"领取{userExtend.Uid}的任务2奖励");
sb.Append("111:1");
sb.Append(";");
userExtend.task2 = 3;
}
MySqlManager <UserExtend> .Instance.Update(userExtend);
}
jObject.Add("reward", sb.ToString());
jObject.Add(MyCommon.CODE, (int)Consts.Code.Code_OK);
}
public void ThenTheUpdateCustomerEventDetailsAreStoredInVSSDB()
{
string customerUID = customerServiceSupport.UpdateCustomerModel.CustomerUID.ToString().Replace("-", "");
string customerName = String.IsNullOrEmpty(customerServiceSupport.UpdateCustomerModel.CustomerName) ? customerServiceSupport.CreateCustomerModel.CustomerName : customerServiceSupport.UpdateCustomerModel.CustomerName;
string customerType = customerServiceSupport.CreateCustomerModel.CustomerType.ToString();
string primaryContactEmail = customerServiceSupport.UpdateCustomerModel.PrimaryContactEmail == null ? customerServiceSupport.CreateCustomerModel.PrimaryContactEmail : customerServiceSupport.UpdateCustomerModel.PrimaryContactEmail;
string firstName = customerServiceSupport.UpdateCustomerModel.FirstName == null ? customerServiceSupport.CreateCustomerModel.FirstName : customerServiceSupport.UpdateCustomerModel.FirstName;
string lastName = customerServiceSupport.UpdateCustomerModel.LastName == null ? customerServiceSupport.CreateCustomerModel.LastName : customerServiceSupport.UpdateCustomerModel.LastName;
CommonUtil.WaitToProcess("2"); //Wait for the data to get persisted in DB
List <string> columnList = new List <string>()
{
"CustomerUID", "CustomerName", "CustomerType", "PrimaryContactEmail", "FirstName", "LastName"
};
List <string> updateCustomerDetails = new List <string>();
updateCustomerDetails.Add(customerUID.ToUpper());
updateCustomerDetails.Add(customerName);
updateCustomerDetails.Add(GetCustomerTypeId(customerType));
updateCustomerDetails.Add(primaryContactEmail);
updateCustomerDetails.Add(firstName);
updateCustomerDetails.Add(lastName);
string validateQuery = CustomerServiceMySqlQueries.CustomerDetailsByCustomerUID + customerUID + "')";
string validateDateQuery = CustomerServiceMySqlQueries.CustomerUpdateUTCByCustomerUID + customerUID + "')";
MySqlUtil.ValidateMySQLQuery(MySqlConnectionString, validateQuery, updateCustomerDetails);
MySqlUtil.ValidateMySQLDateValueQuery(MySqlConnectionString, validateDateQuery, DateTime.UtcNow.AddMinutes(-2), "LESS_THAN_DB");
}
//数据库操作成功
private void OperatorSuccess(User user, JObject responseData)
{
responseData.Add(MyCommon.CODE, (int)Consts.Code.Code_OK);
responseData.Add(MyCommon.UID, user.Uid);
MySqlUtil.UpdateUserTask(user.Uid);
ProgressTaskHandler.ProgressTaskSql(208, user.Uid);
}
public void ThenTheAssociateCustomerUserEventDetailsAreNOTStoredInVSSDB()
{
string userUID = customerUserServiceSupport.AssociateCustomerUserModel.UserUID.ToString();
string validateQuery = CustomerListSqlQueries.CustomerUserDetailsByCustomerUID + userUID + "'";
MySqlUtil.ValidateMySQLQueryCount(MySqlConnectionString, validateQuery, 0); // There should be no matching rows.
}
public void TestLoadMeta()
{
MySqlUtil mySqlUtil = MySqlUtil.GetOne(Server, Port, UserId, Pwd);
List <DB> dbs = mySqlUtil.LoadMeta();
Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(dbs, Newtonsoft.Json.Formatting.Indented));
Assert.IsTrue(true);
}
public void ThenTheDeleteCustomerEventDetailsAreRemovedInVSSDB()
{
CommonUtil.WaitToProcess(CustomerListConfig.KafkaTimeoutThreshold);
customerUID = customerServiceSupport.DeleteCustomerModel.CustomerUID.ToString();
string validateQuery = CustomerListSqlQueries.CustomerDetailsUpdateByCustomerUID + customerUID + "'";
MySqlUtil.ValidateMySQLQueryCount(MySqlConnectionString, validateQuery, 0); // There should be no matching rows.
}
public static MySqlUtil getInstance()
{
if (s_mySqlUtil == null)
{
s_mySqlUtil = new MySqlUtil();
}
return(s_mySqlUtil);
}
public void ThenTheAssociateCustomerUserEventDetailsAreStoredInVSSDB()
{
customerUID = customerUserServiceSupport.AssociateCustomerUserModel.CustomerUID.ToString();
userUID = customerUserServiceSupport.AssociateCustomerUserModel.UserUID.ToString();
string validateQuery = CustomerListSqlQueries.CustomerUserDetailsByCustomerUID + userUID + "'";
MySqlUtil.ValidateMySQLQuery(MySqlConnectionString, validateQuery, customerUID);
}
public void ThenTheDissociateCustomerUserEventDetailsAreRemovedInVSSDB()
{
CommonUtil.WaitToProcess("2");
string customerUID = customerAssetServiceSupport.DissociateCustomerAssetModel.CustomerUID.ToString();
string userUID = customerAssetServiceSupport.DissociateCustomerAssetModel.AssetUID.ToString();
string validateQuery = CustomerServiceMySqlQueries.CustomerUserDetails + customerUID + "' AND fk_UserUID='" + userUID + "'";
MySqlUtil.ValidateMySQLQueryCount(MySqlConnectionString, validateQuery, 0); // There should be no matching rows.
}
private String getConnStr()
{
String host = txtIP.Text;
String portStr = txtPort.Text;
String userName = txtUserName.Text;
String passWord = txtPassWord.Text;
String connStr = MySqlUtil.getConnectionStr(host, portStr, userName, passWord);
return(connStr);
}
public void TestConnectTest()
{
MySqlUtil mySqlUtil = MySqlUtil.GetOne(Server, Port, UserId, Pwd);
string errmsg = "";
Console.WriteLine(mySqlUtil.ConnectStr);
bool condition = mySqlUtil.ConnectTest(out errmsg);
Console.WriteLine(errmsg);
Assert.IsTrue(condition);
}
private void AddPropSql(string uid, int propId, int num, string reason, JObject responseData)
{
if (MySqlUtil.ChangeProp(uid, propId, num, reason))
{
OperatorSuccess(responseData);
}
else
{
OperatorFail(responseData);
}
}
public void ThenTheDeleteCustomerEventDetailsAreRemovedInVSSDB()
{
string customerUID = customerServiceSupport.DeleteCustomerModel.CustomerUID.ToString();
CommonUtil.WaitToProcess("2"); //Wait for the data to get persisted in DB
customerUID = customerServiceSupport.DeleteCustomerModel.CustomerUID.ToString();
string validateQuery = CustomerServiceMySqlQueries.CustomerDetailsUpdateByCustomerUID + customerUID + "'";
MySqlUtil.ValidateMySQLQueryCount(MySqlConnectionString, validateQuery, 0); // There should be no matching rows.
}
public void ThenTheAssociateCustomerUserEventDetailsForAllCustomersAreStoredInVSSDB()
{
userUID = customerUserServiceSupport.AssociateCustomerUserModel.UserUID.ToString();
for (int index = 0; index <= 2; index++)
{
string validateQuery = CustomerListSqlQueries.CustomerUserDetailsByUserUID +
multipleCustomerGUID[index].ToString() + "'";
MySqlUtil.ValidateMySQLQuery(MySqlConnectionString, validateQuery, userUID);
}
}
public void WhenISetAssociateCustomerAssetToAnExistingCustomer()
{
List <string> columns = new List <string> {
"fk_CustomerUID"
};
List <string> results = new List <string>();
results = MySqlUtil.ExecuteMySQLQueryResult(MySqlConnectionString, CustomerServiceMySqlQueries.CustomerAssetDetails, columns);
customerAssetServiceSupport.InvalidAssociateCustomerAssetModel.CustomerUID = results[0];
}
public void ThenTheUpdateCustomerEventDetailsAreUpdatedInVSSDB()
{
customerUID = customerServiceSupport.UpdateCustomerModel.CustomerUID.ToString();
customerName = customerServiceSupport.UpdateCustomerModel.CustomerName;
string validateQuery = CustomerListSqlQueries.CustomerNameUpdateByCustomerUID + customerUID + "'";
string validateDateQuery = CustomerListSqlQueries.CustomerUpdateUTCByCustomerUID + customerUID + "'";
MySqlUtil.ValidateMySQLQuery(MySqlConnectionString, validateQuery, customerName.ToString());
MySqlUtil.ValidateMySQLDateValueQuery(MySqlConnectionString, validateDateQuery, actionUTC, "LESS_THAN_DB");
}
public void WhenISetDuplicateAssociateUserCustomer()
{
List <string> columns = new List <string> {
"fk_CustomerUID", "fk_UserUID"
};
List <string> results = new List <string>();
results = MySqlUtil.ExecuteMySQLQueryResult(MySqlConnectionString, CustomerServiceMySqlQueries.CustomerUserDetailsLimit, columns);
customerUserServiceSupport.InvalidAssociateCustomerUserModel.CustomerUID = results[0];
customerUserServiceSupport.InvalidAssociateCustomerUserModel.UserUID = results[1];
}
private void OperatorSuccess(User user, string channelName, string ip, string versionName, JObject responseData)
{
responseData.Add(MyCommon.CODE, (int)Consts.Code.Code_OK);
responseData.Add(MyCommon.UID, user.Uid);
//更新下用户的任务
MySqlUtil.UpdateUserTask(user.Uid);
StatisticsHelper.StatisticsLogin(user.Uid);
StatictisLogUtil.Login(user.Uid, user.Username, ip, channelName, versionName, MyCommon.OpType.Login);
}
private void Get51ActivityRewardSql(string uid, int id, JObject responseData)
{
List <string> loginLists = new List <string>();
for (int i = 0; i < MySqlService.activity51Datas.Count; i++)
{
Activity51Data activity51Data = MySqlService.activity51Datas[i];
List <Log_Login> loginByDate = MySqlManager <Log_Login> .Instance.GetLoginByDate(uid, activity51Data.Time);
//当天登陆过
if (loginByDate.Count > 0)
{
loginLists.Add(activity51Data.Time);
}
}
MySqlService.log.Warn($"id:{id},count:{loginLists.Count}");
if (id > loginLists.Count)
{
OperatorFail(responseData, "未达到天数要求");
}
else
{
if (MySqlManager <UserActivity51> .Instance.Add(new UserActivity51()
{
Uid = uid,
activity_id = id
}))
{
Activity51Data activity51Data = new Activity51Data();
foreach (var item in MySqlService.activity51Datas)
{
if (item.Id == id)
{
activity51Data = item;
break;
}
}
MySqlUtil.AddProp(uid, activity51Data.Reward, "51活动");
OperatorSuccess(responseData, activity51Data.Reward);
}
else
{
OperatorFail(responseData, "奖励已领取");
}
}
}
private void work()
{
//获取源数据
String host = txtSourceIP.Text.Trim();
String portStr = txtSourcePort.Text.Trim();
String userName = txtSourceUserName.Text.Trim();
String passWord = txtSourcePassWord.Text.Trim();
String dataBase = cboSourceDataBase.Text;
String connectionString = MySqlUtil.getConnectionStr(host, portStr, userName, passWord, dataBase);
connection = MySqlUtil.getConn(connectionString);
Dictionary <String, DataTable> source = new Dictionary <string, DataTable>();
Dictionary <String, DataTable> sourceSetting = btnConnSource.Tag as Dictionary <String, DataTable>;
Dictionary <String, DataTable> targetSetting = btnConnSource.Tag as Dictionary <String, DataTable>;
DataTable sourceColumns = sourceSetting["columns"] as DataTable;
DataTable targetColumns = targetSetting["columns"] as DataTable;
lbState.Text = "开始获取源数据";
List <Dictionary <String, Dictionary <String, Object> > > sqlList = new List <Dictionary <String, Dictionary <String, Object> > >();
foreach (String tableName in clbTableInfo.CheckedItems)
{
bool createTable = radCreate.Checked && !radSkip.Checked;
bool tableIsExist = checkTableExist(tableName, sourceColumns);
if (createTable && tableIsExist)
{
String insertSql = getCreateSql(sourceColumns, tableName);
}
String sql = "select * from " + tableName;
DataTable tempDT = MySqlUtil.getDateTable(connection, sql);
DataTable tableInfo = sourceSetting[tableName];
StringBuilder fields = new StringBuilder();
var rows = sourceColumns.Select("TABLE_NAME = '" + tableName + "' and COLUMN_KEY <> 'PRI'").Distinct(row => row["COLUMN_NAME"]);
foreach (var row in rows)
{
String colName = row["COLUMN_NAME"].ToString();
fields.Append(colName).Append(",");
}
}
}
// Data From MongoDB to Mysql
public void EtlWorkerTask(string villageName)
{
// 1. Data from MongoDB
List <ShellNode> villageNodes = _mongoDbUtil.GetMongoCollectionData(villageName);
// 2. Data to Mysql
if (villageNodes.Any())
{
MySqlUtil mySqlUtil = new MySqlUtil(_mysqlConStr);
mySqlUtil.BulkInsertNodes(villageName, villageNodes);
UpdateAllLocationInfo();
mySqlUtil.Dispose();
}
Console.WriteLine($"{DateTime.Now}: <{villageName}> Finished.");
}
public string ExecuteOne(string strSql)
{
switch (_dbType)
{
case DatabaseType.Sqlserver:
return(SqlServerUtil.ExecuteOne(DbConnectionString, strSql));
//sunjian 2020/2/13 增加MySql的ExecuteOne方法
case DatabaseType.Mysql:
return(MySqlUtil.ExecuteOne(DbConnectionString, strSql));
default:
return("");
}
}
private void OneKeyReadEmailSql(string uid, JObject responseData)
{
//得到未读取的邮件
List <UserEmail> userEmails = NHibernateHelper.userEmailManager.GetListByUid(uid).OrderByDescending(i => i.CreateTime).Take(50).ToList();
if (userEmails == null)
{
MySqlService.log.Warn("没有未读取的邮件");
OperatorFail(responseData);
return;
}
int temp = 0;
temp = userEmails.Count >= 50 ? 50 : userEmails.Count;
for (int i = 0; i < temp; i++)
{
var email = userEmails[i];
//没有奖励的不能一键读取
if (string.IsNullOrWhiteSpace(email.Reward))
{
continue;
}
if (email.State == 0)
{
email.State = 1;
if (!string.IsNullOrWhiteSpace(email.Reward))
{
bool addProp = MySqlUtil.AddProp(uid, email.Reward, "读邮件");
if (!addProp)
{
MySqlService.log.Warn("读邮件加道具失败：" + uid + " " + email.Reward);
}
}
if (!NHibernateHelper.userEmailManager.Update(email))
{
MySqlService.log.Warn("读取失败");
OperatorFail(responseData);
return;
}
}
}
OperatorSuccess(responseData);
}
private bool testConnection(String host, String portStr, String userName, String passWord)
{
String sql = "SELECT 1";
String connStr = this.getConnStr(host, portStr, userName, passWord);
DataTable tempDT = MySqlUtil.getDateTable(connStr, sql);
if (tempDT.Rows.Count > 0)
{
//this.connStr = connStr;
return(true);
}
else
{
return(false);
}
}
public void UpdateAllLocationInfo()
{
MySqlUtil mySqlUtil = new MySqlUtil(_mysqlConStr);
List <string> needUpdateVillages = mySqlUtil.GetVillagesWithoutLocation();
List <LocationInfo> locationInfos = new List <LocationInfo>();
foreach (string village in needUpdateVillages)
{
LocationInfo locationInfo = GetNewVillageLocation(village);
if (locationInfo != null && locationInfo.VillageName != null)
{
locationInfos.Add(locationInfo);
}
}
mySqlUtil.InsertLocationInfos(locationInfos);
}
private void RefreshCounters()
{
if (ct.Ping())
//Refresh all the counter
{
txtLogedInUser.Text = mySqlConnection.globalUser;
Sicon.Fill = new SolidColorBrush(Color.FromRgb(20, 255, 30));
onlineStatus.Text = "Online";
UCMedi.text = MySqlMedicineEntry.MediCineCount().ToString();
UCStock.text = MySqlUtil.MediCineCount().ToString();
UCCompany.text = ManufacturerInfo.GetAllManufacturerName().Count.ToString();
}
else
{
MessageBox.Show("Not Connected!");
}
}
private bool testConnection()
{
String sql = "SELECT 1";
String connStr = this.getConnStr();
DataTable tempDT = MySqlUtil.getDateTable(connStr, sql);
if (tempDT.Rows.Count > 0)
{
this.connStr = connStr;
return(true);
}
else
{
return(false);
}
}
