public static string ConvertToConsumeType(EnumConsumeType consumeType) { switch (consumeType) { case EnumConsumeType.AccountIncome: return "账户存入"; case EnumConsumeType.BuyEquipment: return "设备购买"; case EnumConsumeType.MessageAlarm: return "短信报警"; case EnumConsumeType.MessageQueryVehicle: return "短信查车"; case EnumConsumeType.RechargeToCustomer: return "给客户充值"; case EnumConsumeType.ServiceFee: return "服务费"; default : return ""; } }
/// <summary> /// 查询租户的账户信息 /// </summary> /// <param name="strTenantCode">租户编码以逗号隔开</param> /// <param name="StartTime">开始时间</param> /// <param name="EndTime">结束时间</param> /// <param name="EnumConsumeType?"></param> /// <param name="rowIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <returns></returns> public IList<EMTenantAccountDetail> SelectTenantCountDetail(string strTenantCode, DateTime StartTime, DateTime EndTime, EnumConsumeType? consumeType, int rowIndex, int pageSize, ref int rowCount) { rowCount = 0; if (string.IsNullOrEmpty(strTenantCode)) { strTenantCode = "''"; } string consumeTypeCondition = consumeType.HasValue ? "and ConsumeType=" + Convert.ToInt32(consumeType.Value) : ""; List<EMTenantAccountDetail> list = new List<EMTenantAccountDetail>(); string sCmdText = string.Format(@"select * from movo_tenantaccount_detail where TenantCode in ({0}) and CreateTime between '{1}' and '{2}' {5} order by CreateTime desc limit {3},{4};", strTenantCode, StartTime, EndTime, rowIndex, pageSize, consumeTypeCondition); string sql = string.Format(@"select count(*) from movo_tenantaccount_detail where TenantCode in ({0}) and CreateTime between '{1}' and '{2}' {3} ", strTenantCode, StartTime, EndTime, consumeTypeCondition); using (DbDataReader sdrCount = MySqlDB.GetDataReader(CommandType.Text, sql)) { if (sdrCount != null) { while (sdrCount.Read()) { rowCount = rowCount + Convert.ToInt32(sdrCount[0]); } } sdrCount.Close(); } using (DbDataReader sdr = MySqlDB.GetDataReader(CommandType.Text, sCmdText)) { if (sdr != null) { while (sdr.Read()) { EMTenantAccountDetail acc = new EMTenantAccountDetail(); acc.ConsumeMoney = sdr["ConsumeMoney"].ToDecimal(); acc.ConsumeType = (EnumConsumeType)sdr["ConsumeType"].ToInt(); acc.CreateTime = sdr["CreateTime"].ToDateTime(); acc.OperateType = (EnumOperateType)sdr["OperateType"].ToInt(); acc.OperatorTenantCode = sdr["OperatorTenantCode"].ToString(); acc.Remark = sdr["Remark"].ToString(); acc.TargetTenantCode = sdr["TargetTenantCode"].ToString(); acc.TenantCode = sdr["TenantCode"].ToString(); list.Add(acc); } } sdr.Close(); } return list; }
public void DecuntMoney(string tenantCode, string opratorTenantCode, EnumConsumeType type, decimal money, string remark) { var detail = new ETenantAccountDetail() { TenantCode = tenantCode, ConsumeMoney = money, OperateType = EnumOperateType.Outlay, ConsumeType = type, Remark = remark, CreateTime = DateTime.Now, OperatorTenantCode = opratorTenantCode, }; using (var trans = DACFacade.Movo.TenantAccountDAC.BeginTransaction()) { try { DACFacade.Movo.TenantAccountDAC.UpdateBalance(trans, tenantCode, -money); DACFacade.Movo.TenantAccountDetailDAC.Insert(trans, detail); DACFacade.Movo.TenantAccountDetailDAC.CommitTransaction(trans); } catch { DACFacade.Movo.TenantAccountDetailDAC.RollbackTransaction(trans); throw; } } }
// 平台商操作运营商 public void FutuoOperator(string fromTenantCode, string toTenantCode, decimal money,int operatorType,EnumConsumeType consumeType,string remark) { // 0为充值,1为扣款 //decimal balance = QueryBalance(toTenantCode); //using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString)) //{ // conn.Open(); // var tran = conn.BeginTransaction(); // string sql = ""; // EMTenantAccountDetailService serv = new EMTenantAccountDetailService(); // if (operatorType == 0) // { // sql = string.Format(@"update movo_tenant_account set Balance = Balance + {0} where TenantCode = '{1}';", // money, toTenantCode); // MySqlHelper.ExecuteNonQuery(conn, sql); // EMTenantAccountDetail fromTenantDetail = new EMTenantAccountDetail() // { // ConsumeMoney = money, // ConsumeType = consumeType, // OperateType = EnumOperateType.Income, // CreateTime = DateTime.Now, // TargetTenantCode = null, // TenantCode = toTenantCode, // Remark = remark, // OperatorTenantCode = fromTenantCode, // }; // serv.Insert(conn, fromTenantDetail); // } // else // { // if (balance < money) // throw new Exception("对不起,余额不足,扣款失败!"); // sql = string.Format(@"update movo_tenant_account set Balance = Balance - {0} where TenantCode = '{1}';", // money, toTenantCode); // MySqlHelper.ExecuteNonQuery(conn, sql); // EMTenantAccountDetail toTenantDetail = new EMTenantAccountDetail() // { // ConsumeMoney = money, // ConsumeType = consumeType, // OperateType = EnumOperateType.Outlay, // TargetTenantCode = null, // CreateTime = DateTime.Now, // TenantCode = toTenantCode, // Remark = remark, // OperatorTenantCode = fromTenantCode, // }; // serv.Insert(conn, toTenantDetail); // } // tran.Commit(); //} TenantCountDetailDAL dal = new TenantCountDetailDAL(); dal.FutuoOperator(fromTenantCode, toTenantCode, money, operatorType, consumeType, remark); }
public void DecuntMoney(string tenantCode, string opratorTenantCode, EnumConsumeType type, decimal money, string remark) { using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString)) { conn.Open(); var tran = conn.BeginTransaction(); string sql = string.Format(@"update movo_tenant_account set Balance = Balance - {0} where TenantCode = '{1}';", money, tenantCode); MySqlHelper.ExecuteNonQuery(conn, sql); EMTenantAccountDetail detail = new EMTenantAccountDetail() { ConsumeMoney = money, ConsumeType = type, OperateType = EnumOperateType.Outlay, CreateTime = DateTime.Now, TenantCode = tenantCode, Remark = remark, OperatorTenantCode = opratorTenantCode, }; IEMTenantAccountDetailService serv = new EMTenantAccountDetailService(); serv.Insert(conn, detail); tran.Commit(); } }
/// <summary> /// 充值/扣款 /// </summary> /// <param name="fromTenantCode"></param> /// <param name="toTenantCode"></param> /// <param name="money"></param> /// <param name="operatorType">0为充值,1为扣款</param> /// <param name="consumeType">消费类型</param> /// <param name="remark"></param> public void FutuoOperator(string fromTenantCode, string toTenantCode, decimal money, int operatorType, EnumConsumeType consumeType, string remark) { #region 查询余额 decimal balance = 0; string sqlblance = string.Format(@"select * from movo_tenant_account where TenantCode={0};", toTenantCode); DataSet ds = MySqlDB.GetDataSet(CommandType.Text, sqlblance); if (ds == null || ds.Tables[0].Rows.Count == 0) { string sqlInsert = string.Format(@"INSERT INTO movo_tenant_account (`TenantCode`, `Balance`) VALUES ('{0}',0);", toTenantCode); MySqlDB.ExecuteNonQuery(CommandType.Text, sqlInsert); } else { balance = Convert.ToDecimal(ds.Tables[0].Rows[0]["Balance"]); } #endregion #region 充值/扣款采用事物 MySqlConnection con = new MySqlConnection(MySqlDB.ConnString); con.Open(); MySqlTransaction trans = con.BeginTransaction(); MySqlCommand cmd = con.CreateCommand(); cmd.Transaction = trans; if (operatorType == 0)//充值 { #region 充值 try { string addsql = string.Format(@"update movo_tenant_account set Balance = Balance + {0} where TenantCode = '{1}';", money, toTenantCode); cmd.CommandText = addsql; int upCount = cmd.ExecuteNonQuery(); string insertsql = string.Format(@"insert into movo_tenantaccount_detail (TenantCode, ConsumeMoney,OperateType,ConsumeType, TargetTenantCode, Remark, CreateTime,OperatorTenantCode) values(@TenantCode,@ConsumeMoney,@OperateType,@ConsumeType,@TargetTenantCode,@Remark,@CreateTime,@OperatorTenantCode)"); cmd.Parameters.AddRange(new MySqlParameter[] { new MySqlParameter("@TenantCode", toTenantCode), new MySqlParameter("@ConsumeMoney", money), new MySqlParameter("@OperateType", EnumOperateType.Income), new MySqlParameter("@ConsumeType", consumeType), new MySqlParameter("@TargetTenantCode", null), new MySqlParameter("@Remark", remark == null ? "" : remark), new MySqlParameter("@CreateTime", DateTime.Now), new MySqlParameter("@OperatorTenantCode", fromTenantCode) }); cmd.CommandText = insertsql; int inCount = cmd.ExecuteNonQuery(); if (upCount >= 1 && inCount >= 1) { trans.Commit(); } } catch (Exception ex) { trans.Rollback(); } finally { con.Close(); } #endregion } else//扣款 { #region 扣款 try { if (balance < money) throw new Exception("对不起,余额不足,扣款失败!"); string upsql = string.Format(@"update movo_tenant_account set Balance = Balance - {0} where TenantCode = '{1}'", money, toTenantCode); cmd.CommandText = upsql; int upCount = cmd.ExecuteNonQuery(); string insertsql = string.Format(@"insert into movo_tenantaccount_detail (TenantCode, ConsumeMoney,OperateType,ConsumeType, TargetTenantCode, Remark, CreateTime,OperatorTenantCode) values(@TenantCode,@ConsumeMoney,@OperateType,@ConsumeType,@TargetTenantCode,@Remark,@CreateTime,@OperatorTenantCode)"); cmd.Parameters.AddRange(new MySqlParameter[] { new MySqlParameter("@TenantCode", toTenantCode), new MySqlParameter("@ConsumeMoney", money), new MySqlParameter("@OperateType", EnumOperateType.Outlay), new MySqlParameter("@ConsumeType", consumeType), new MySqlParameter("@TargetTenantCode", null), new MySqlParameter("@Remark", remark == null ? "" : remark), new MySqlParameter("@CreateTime", DateTime.Now), new MySqlParameter("@OperatorTenantCode", fromTenantCode) }); cmd.CommandText = insertsql; int inCount = cmd.ExecuteNonQuery(); if (upCount >= 1 && inCount >= 1) { trans.Commit(); } } catch (Exception ex) { trans.Rollback(); } #endregion } #endregion }
/// <summary> /// 获取消费类型 /// </summary> /// <param name="cs"></param> /// <returns></returns> private string GetConsumeType(EnumConsumeType cs) { switch (cs) { case EnumConsumeType.AccountIncome: return "账户存入"; case EnumConsumeType.BuyEquipment: return "设备购买"; case EnumConsumeType.MessageAlarm: return "短信报警"; case EnumConsumeType.MessageQueryVehicle: return "短信查车"; case EnumConsumeType.RechargeToCustomer: return "给客户充值"; case EnumConsumeType.ServiceFee: return "服务费"; case EnumConsumeType.OnlinePayment: return "在线支付"; case EnumConsumeType.OnlinePaymentFixed: return "在线支付补款"; } return ""; }
/// <summary> /// 获取消费类型 /// </summary> /// <param name="cs"></param> /// <returns></returns> private string GetConsumeType(EnumConsumeType consumeType) { string returnString = string.Empty; switch (consumeType) { case EnumConsumeType.AccountIncome: { returnString = "账户存入"; } break; case EnumConsumeType.BuyEquipment: { returnString = "设备购买"; } break; case EnumConsumeType.MessageAlarm: { returnString = "短信报警"; } break; case EnumConsumeType.MessageQueryVehicle: { returnString = "短信查车"; } break; case EnumConsumeType.RechargeToCustomer: { returnString = "给客户充值"; } break; case EnumConsumeType.ServiceFee: { returnString = "服务费"; } break; } return returnString; }
public bool DecuntMoney(string tenantCode, string opratorTenantCode, EnumConsumeType type, decimal money, string remark) { try { EMTenantAccountService service = new EMTenantAccountService(); service.DecuntMoney(tenantCode, opratorTenantCode, type, money, remark); return true; } catch (Exception ex) { Logger.Error(ex.Message, ex); return false; } }
public IList<EMTenantAccountDetail> Select(List<string> tenantCodeList, DateTime StartTime, DateTime EndTime, EnumConsumeType? consumeType, int rowIndex, int pageSize, ref int rowCount, ref int IncomeRowCount, ref decimal IncomeAccount, ref int OutlayRowCount, ref decimal OutlayAccount) { IList<EMTenantAccountDetail> list = new List<EMTenantAccountDetail>(); if (tenantCodeList == null || tenantCodeList.Count == 0) return list; StringBuilder strTenantCode = new StringBuilder(); for (int i = 1, i_count = tenantCodeList.Count; i <= i_count; i++) { if (i == i_count) strTenantCode.Append(string.Format("'{0}'", tenantCodeList[i - 1].ToString())); else strTenantCode.Append(string.Format("'{0}',", tenantCodeList[i - 1].ToString())); } TenantCountDetailDAL dal = new TenantCountDetailDAL(); #region 查询所有存入金额 // string sqlIncome = string.Format(@"select ConsumeMoney from movo_tenantaccount_detail where TenantCode in ({0}) // and CreateTime between '{1}' and '{2}' and OperateType=0;", strTenantCode, StartTime, EndTime); // DataSet ds = MySqlHelper.ExecuteDataSet(ConnectionString, sqlIncome, null); // if (ds == null || ds.Tables[0].Rows.Count == 0) // { // IncomeRowCount = 0; // IncomeAccount = 0; // } // else // { // IncomeAccount = 0; // IncomeRowCount = ds.Tables[0].Rows.Count; // for (int i = 0, i_Count = ds.Tables[0].Rows.Count; i < i_Count; i++) // { // IncomeAccount += Convert.ToDecimal(ds.Tables[0].Rows[i]["ConsumeMoney"]); // } // } dal.SelectIncomeAccount(strTenantCode.ToString(), StartTime, EndTime, ref IncomeRowCount, ref IncomeAccount); #endregion #region 查询所有扣款金额 // string sqlOutlay = string.Format(@"select ConsumeMoney from movo_tenantaccount_detail where TenantCode in ({0}) // and CreateTime between '{1}' and '{2}' and OperateType=1;", strTenantCode, StartTime, EndTime); // ds = MySqlHelper.ExecuteDataSet(ConnectionString, sqlOutlay, null); // if (ds == null || ds.Tables[0].Rows.Count == 0) // { // OutlayRowCount = 0; // OutlayAccount = 0; // } // else // { // OutlayAccount = 0; // OutlayRowCount = ds.Tables[0].Rows.Count; // for (int i = 0, i_Count = ds.Tables[0].Rows.Count; i < i_Count; i++) // { // OutlayAccount += Convert.ToDecimal(ds.Tables[0].Rows[i]["ConsumeMoney"]); // } // } dal.SelectOutlayAccount(strTenantCode.ToString(), StartTime, EndTime, ref OutlayRowCount, ref OutlayAccount); #endregion //string consumeTypeCondition = consumeType.HasValue ? "and ConsumeType=" + Convert.ToInt32(consumeType.Value) : ""; //查询总数据笔数 // string sqlAll = string.Format(@"select count(*) from movo_tenantaccount_detail where TenantCode in ({0}) // and CreateTime between '{1}' and '{2}' {3};", strTenantCode, StartTime, EndTime, consumeTypeCondition); // ds = MySqlHelper.ExecuteDataSet(ConnectionString, sqlAll, null); // if (ds == null || ds.Tables[0].Rows.Count == 0) // { // rowCount = 0; // } // else // { // rowCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]); // } // string sql = string.Format(@"select * from movo_tenantaccount_detail where TenantCode in ({0}) // and CreateTime between '{1}' and '{2}' {5} order by CreateTime desc limit {3},{4};", strTenantCode, StartTime, EndTime, rowIndex, pageSize, consumeTypeCondition); // ds = MySqlHelper.ExecuteDataSet(ConnectionString, sql, null); // if (ds == null || ds.Tables[0].Rows.Count == 0) // return list; // for (int i = 0, i_Count = ds.Tables[0].Rows.Count; i < i_Count; i++) // { // list.Add(ConvertInfo(ds.Tables[0].Rows[i])); // } //代码优化 list = dal.SelectTenantCountDetail(strTenantCode.ToString(), StartTime, EndTime, consumeType, rowIndex, pageSize, ref rowCount); return list; }