/// <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);
}
}