/// <summary> /// 根据短信ID查询内容 /// </summary> /// <param name="smsID"></param> /// <returns></returns> public EMSMSQueryVehicle GetSMSQuery(ulong smsID) { EMSMSQueryVehicle smsve = new EMSMSQueryVehicle (); if (!string.IsNullOrEmpty(smsID.ToString())) { string sCmdText = string.Format(@"select * from gps_smsqueryvehiclesetting where sms_vehicle_set_id={0}", smsID); using (DbDataReader sdr = MySqlDB.GetDataReader(CommandType.Text, sCmdText)) { if (sdr != null) { while (sdr.Read()) { smsve.CreateDate = sdr["CreateDate"].ToDateTime(); smsve.CustomerName = sdr["CustomerName"].ToString(); smsve.CustomerTelephone = sdr["CustomerTelephone"].ToString(); smsve.IsProfessional = sdr["IsProfessional"].ToBoolean(); smsve.sms_vehicle_set_id = sdr["sms_vehicle_set_id"].ToULong(); smsve.TenantCode = sdr["TenantCode"].ToString(); smsve.SMSVehicle = GetSMSVehicleBySmsID(sdr["sms_vehicle_set_id"].ToULong()); } } sdr.Close(); } } return smsve; }
/// <summary> /// 修改短信查车 /// </summary> /// <param name="smsQuery"></param> /// <returns></returns> public void UpdateSmsVehicle(EMSMSQueryVehicle smsQuery) { MySqlConnection con = new MySqlConnection(MySqlDB.ConnString); con.Open(); MySqlTransaction trans = con.BeginTransaction(); MySqlCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { string sCmdText = string.Format(@"update gps_smsqueryvehiclesetting set TenantCode=@TenantCode,CustomerName=@CustomerName,CustomerTelephone=@CustomerTelephone,CreateDate=@CreateDate,IsProfessional=@IsProfessional where sms_vehicle_set_id=@sms_vehicle_set_id"); MySqlParameter[] parm = new MySqlParameter[] { new MySqlParameter("sms_vehicle_set_id",smsQuery.sms_vehicle_set_id), new MySqlParameter("TenantCode",smsQuery.TenantCode), new MySqlParameter("CustomerName",smsQuery.CustomerName), new MySqlParameter("CustomerTelephone",smsQuery.CustomerTelephone), new MySqlParameter("CreateDate",smsQuery.CreateDate), new MySqlParameter("IsProfessional",smsQuery.IsProfessional), new MySqlParameter("LastSendTime",null) }; cmd.CommandText = sCmdText; cmd.Parameters.AddRange(parm); cmd.ExecuteNonQuery(); string delsql = string.Format(@"delete from gps_SMSVehicle where sms_vehicle_set_id={0}", smsQuery.sms_vehicle_set_id); cmd.CommandText = delsql; cmd.ExecuteNonQuery(); IList<EMSMSVehicle> list = smsQuery.SMSVehicle; if (list != null && list.Count > 0) { for (int i = 0; i < list.Count; i++) { ulong ismsvehicle_id = GetMaxSMSVehicleID() + ulong.Parse(i.ToString()) + 1; string sTenantCode = smsQuery.TenantCode; string sVehicleCode = list[i].VehicleCode.ToString(); string sLicenseNumber = list[i].LicenseNumber; ulong isms_vehicle_set_id = smsQuery.sms_vehicle_set_id; string sInsert = string.Format(@"insert into gps_SMSVehicle(smsvehicle_id,TenantCode,VehicleCode,LicenseNumber,sms_vehicle_set_id) values({0},'{1}','{2}','{3}',{4})", ismsvehicle_id, sTenantCode, sVehicleCode, sLicenseNumber, isms_vehicle_set_id); cmd.CommandText = sInsert; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch (Exception ex) { trans.Rollback(); } finally { con.Close(); } }
public void Add(EMSMSQueryVehicle smsQuery) { bool exist = IsExistTelephone(smsQuery.CustomerTelephone, null); if (exist) { throw new BusinessException("手机号码己存在,请输入其它号码。"); } //using (IRepository repository = SessionManager.CreateRepository(typeof(EMSMSQueryVehicle))) //{ // repository.Save(smsQuery); //} //优化代码 else { SMSVehicleDAL dal = new SMSVehicleDAL(); dal.AddSmsVehicle(smsQuery); } }
// 新增 public ResultContent AddSMSQuery(SmsQueryVehicleParam para) { ResultContent rc = new ResultContent(); try { // 要校验 IdentityNoService identity = new IdentityNoService(); EMSMSQueryVehicle entity = new EMSMSQueryVehicle(); entity.sms_vehicle_set_id = identity.GetSMSQueryVehicleSettingID(); entity.CreateDate = DateTime.Now; entity.TenantCode = para.TenantCode; entity.CustomerName = para.CustomerName; entity.CustomerTelephone = para.Telphone; entity.IsProfessional = false; if (!para.vehicleCodelist.IsNullOrEmpty()) { entity.SMSVehicle = new List<EMSMSVehicle>(); for (int i = 0; i < para.vehicleCodelist.Count; i++) { EMSMSVehicle vehicle = new EMSMSVehicle() { smsvehicle_id = identity.GetSMSVehicleID(), VehicleCode = para.vehicleCodelist[i], TenantCode = para.TenantCode, LicenseNumber = para.LicenceNumberlist[i] }; entity.SMSVehicle.Add(vehicle); } } SMSQueryVehicleService sqv = new SMSQueryVehicleService(); sqv.Add(entity); rc.Message = "新增成功!"; rc.Result = true; } catch (Exception ex) { rc.Result = false; rc.Message = ex.Message; Logger.Error(ex.Message, ex); } return rc; }
/// <summary> /// 查询短信列表 /// </summary> /// <param name="vehicleCodeList"></param> /// <param name="customerName"></param> /// <param name="telephone"></param> /// <param name="tenantCode"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <returns></returns> public List<EMSMSQueryVehicle> SMSQueryList(IList<Guid> vehicleCodeList, string customerName, string telephone, string tenantCode, int pageIndex, int pageSize, ref int rowCount) { List<EMSMSQueryVehicle> smsVeList = new List<EMSMSQueryVehicle>(); rowCount = 0; string vehicleCodeStr = ""; if (vehicleCodeList != null && vehicleCodeList.Count > 0) { for (int i = 0; i < vehicleCodeList.Count; i++) { if (i != vehicleCodeList.Count - 1) { vehicleCodeStr = vehicleCodeStr + "'" + vehicleCodeList[i] + "'" + ","; } else { vehicleCodeStr = vehicleCodeStr + "'" + vehicleCodeList[vehicleCodeList.Count - 1] + "'"; } } } else { vehicleCodeStr = "''"; } string sCmdText = string.Format(@"select * from gps_smsqueryvehiclesetting where TenantCode='{0}' and IsProfessional=false ", tenantCode); string sql = string.Format(@"select count(*) from gps_smsqueryvehiclesetting where TenantCode='{0}' and IsProfessional=false ", tenantCode); string wheresql = ""; if (vehicleCodeList != null && vehicleCodeList.Count > 0) { wheresql = wheresql + string.Format(@" and sms_vehicle_set_id in (select distinct sms_vehicle_set_id from gps_smsVehicle where vehiclecode in ({0}) ) ", vehicleCodeStr); } if (customerName != "") { wheresql = wheresql + string.Format(@" and CustomerName like '%{0}%'", customerName); } if (telephone != "") { wheresql = wheresql + string.Format(@" and CustomerTelephone like '%{0}%'", telephone); } sCmdText = sCmdText + wheresql + string.Format(@" order by CreateDate desc limit {0},{1}", pageIndex, pageSize); sql = sql + wheresql; 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()) { EMSMSQueryVehicle smsVe = new EMSMSQueryVehicle(); smsVe.CreateDate = sdr["CreateDate"].ToDateTime(); smsVe.CustomerName = sdr["CustomerName"].ToString(); smsVe.CustomerTelephone = sdr["CustomerTelephone"].ToString(); smsVe.IsProfessional = sdr["IsProfessional"].ToBoolean(); smsVe.sms_vehicle_set_id = sdr["sms_vehicle_set_id"].ToULong(); smsVe.TenantCode = sdr["TenantCode"].ToString(); smsVe.SMSVehicle = GetSMSVehicleBySmsID(sdr["sms_vehicle_set_id"].ToULong()); smsVeList.Add(smsVe); } } sdr.Close(); } return smsVeList; }
/// <summary> /// 新增短信查车(gps_smsqueryvehiclesetting,gps_SMSVehicle) /// </summary> /// <returns></returns> public void AddSmsVehicle(EMSMSQueryVehicle smsQuery) { using (MySqlConnection conn = new MySqlConnection(MySqlDB.ConnString)) { conn.Open(); MySqlTransaction trans = conn.BeginTransaction(); MySqlCommand cmd = conn.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = "INSERT INTO gps_smsqueryvehiclesetting(sms_vehicle_set_id,TenantCode,CustomerName,CustomerTelephone,CreateDate,IsProfessional,LastSendTime) values(@sms_vehicle_set_id,@TenantCode,@CustomerName,@CustomerTelephone,@CreateDate,@IsProfessional,@LastSendTime)"; cmd.Parameters.AddRange(new MySqlParameter[] { new MySqlParameter("sms_vehicle_set_id", smsQuery.sms_vehicle_set_id), new MySqlParameter("TenantCode", smsQuery.TenantCode), new MySqlParameter("CustomerName", smsQuery.CustomerName), new MySqlParameter("CustomerTelephone", smsQuery.CustomerTelephone), new MySqlParameter("CreateDate", smsQuery.CreateDate), new MySqlParameter("IsProfessional", smsQuery.IsProfessional), new MySqlParameter("LastSendTime", null) }); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); if (!smsQuery.SMSVehicle.IsEmpty()) //短信查车子表记录 { ulong _MaxSMSVehicleID = GetMaxSMSVehicleID(); cmd.CommandText = string.Format("INSERT INTO gps_SMSVehicle(smsvehicle_id,TenantCode,VehicleCode,LicenseNumber,sms_vehicle_set_id) values(@smsvehicle_id,@TenantCode,@VehicleCode,@LicenseNumber,{0})", smsQuery.sms_vehicle_set_id); cmd.Parameters.AddRange(new MySqlParameter[] { new MySqlParameter("smsvehicle_id", _MaxSMSVehicleID), new MySqlParameter("TenantCode", smsQuery.TenantCode), new MySqlParameter("VehicleCode", ""), new MySqlParameter("LicenseNumber", "") }); foreach (var item in smsQuery.SMSVehicle) { _MaxSMSVehicleID++; cmd.Parameters["smsvehicle_id"].Value = _MaxSMSVehicleID; cmd.Parameters["VehicleCode"].Value = item.VehicleCode.ToString(); cmd.Parameters["LicenseNumber"].Value = item.LicenseNumber; cmd.ExecuteNonQuery(); } cmd.Parameters.Clear(); } trans.Commit(); } catch (Exception ex) { Logger.Error("SMSVehicleDAL.AddSmsVehicle('" + smsQuery.sms_vehicle_set_id + "')错误", ex); try { trans.Rollback(); } catch (Exception exRollback) { Logger.Error("SMSVehicleDAL.AddSmsVehicle('" + smsQuery.sms_vehicle_set_id + "')事物回滚错误", exRollback); } } } }
public void Modify(EMSMSQueryVehicle smsQuery) { EMSMSQueryVehicle oldSetting = GetSMSQuery(smsQuery.sms_vehicle_set_id); bool exist = IsExistTelephone(smsQuery.CustomerTelephone, oldSetting.sms_vehicle_set_id.ToString()); if (exist) { throw new BusinessException("手机号码己存在,请输入其它号码。"); } //using (IRepository repository = SessionManager.CreateRepository(typeof(EMSMSQueryVehicle))) //{ // repository.Update(smsQuery); //} //代码优化 else { SMSVehicleDAL dal = new SMSVehicleDAL(); dal.UpdateSmsVehicle(smsQuery); } }