/// <summary>
/// 用於返回事物所用到的sql語句
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string SaveSms(Sms model)
{
model.Replace4MySQL();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" insert into sms (type,order_id,mobile,subject,content,estimated_send_time,send,created,modified) ");
sb.AppendFormat(" values({0},{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}');", model.type, model.order_id, model.mobile, model.subject, model.content, CommonFunction.DateTimeToString(model.estimated_send_time), model.send, CommonFunction.DateTimeToString(model.created), CommonFunction.DateTimeToString(model.modified));
return sb.ToString();
}
public string GetSmsId(Sms sms)
{
try
{
return _IDeliverDetailDao.GetSmsId(sms);
}
catch (Exception ex)
{
throw new Exception("DeliverDetailMgr-->GetSmsId-->" + ex.Message, ex);
}
}
public string Send(int type, string order_id, string mobile, string subject, string content, string estimated_send_time, string memo)
{
StringBuilder sql = new StringBuilder();
Sms sms = new Sms();
sms.type = type;
sms.order_id = int.Parse(order_id);
sms.mobile = mobile;
sms.subject = subject;
sms.content = content;
sms.memo = memo;
SmsLog smslog = new SmsLog();
if (!string.IsNullOrEmpty(estimated_send_time))
{
sms.estimated_send_time = DateTime.Parse(estimated_send_time);
}
else
{
smslog.provider = 0;
smslog.success = 0;
smslog.code = "0";
smslog.free_sms_id = "";
if (smslog.free_sms_id.Length >= 12)
{
sms.sms_number = smslog.free_sms_id;
}
sms.send = smslog.success;
}
sql.AppendFormat(@" select max(id) from sms;");
DataTable dt = _access.getDataTable(sql.ToString());
if (dt.Rows.Count > 0)
{
smslog.sms_id = int.Parse(dt.Rows[0][0].ToString()) + 1;
}
sql.Clear();
sql.AppendFormat(@" insert into sms (type,order_id,mobile,subject,");
sql.AppendFormat(@"content,estimated_send_time,send,sms_number,");
sql.AppendFormat(@"trust_send,memo,created,modified) values(");
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", sms.type, sms.order_id, sms.mobile, sms.subject);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", sms.content, sms.estimated_send_time.ToString("yyyy-MM-dd HH:mm:ss"), sms.send, sms.sms_number);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}');", sms.trust_send, sms.memo, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sql.AppendFormat(@" insert into sms_log (sms_id,provider,success,code,");
sql.AppendFormat(@"free_sms_id,created,modified ) values(");
sql.AppendFormat(@"'{0}','{1}','{2}','{3}',", smslog.sms_id, smslog.provider, smslog.success, smslog.code);
sql.AppendFormat(@"'{0}','{1}','{2}');", smslog.free_sms_id, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), smslog.modified.ToString("yyyy-MM-dd HH:mm:ss"));
return sql.ToString();
}
/// <summary>
/// 查詢smsid
/// </summary>
/// <param name="sms"></param>
/// <returns></returns>
public string GetSmsId(Sms sms)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"SELECT id from sms where memo='{0}' and send=0;", sms.memo);
try
{
DataTable sm = _access.getDataTable(sql.ToString());
if (sm.Rows.Count > 0)
{
return sm.Rows[0]["id"].ToString();
}
else
{
return "";
}
}
catch (Exception ex)
{
throw new Exception(" DeliverMasterDao-->GetSmsId-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 修改物流單號 出貨日期 出貨信息
/// </summary>
/// <returns></returns>
public HttpResponseBase DeliverDetailEdit()
{
string json = string.Empty;
try
{
_DeliverDetailMgr = new DeliverDetailMgr(mySqlConnectionString);
string deliver_id = Request.Params["deliver_id"];
string delivery_store = string.Empty;
string delivery_code = string.Empty;
string delivery_date = string.Empty;
string sms_date = string.Empty;
bool success = false;
if (!string.IsNullOrEmpty(Request.Params["delivery_store"]) && !string.IsNullOrEmpty(Request.Params["delivery_code"]))
{
delivery_store = Request.Params["delivery_store"];
delivery_code = Request.Params["delivery_code"];
delivery_date = Request.Params["delivery_date"];
success = _DeliverDetailMgr.DeliveryCode(deliver_id, delivery_store, delivery_code, delivery_date, "0");
if (success)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
else if (!string.IsNullOrEmpty(Request.Params["sms_date"]))
{
Sms sms = new Sms();
sms.memo = deliver_id;
string smsid = _DeliverDetailMgr.GetSmsId(sms);
if (!string.IsNullOrEmpty(smsid))
{
int i = _DeliverDetailMgr.UpSmsTime(deliver_id, sms_date, smsid);
if (i > 0)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
else
{
json = "{success:false}";
}
}
else
{
DeliverMaster dm = new DeliverMaster();
int type = int.Parse(Request.Params["type"]);
dm.deliver_id = uint.Parse(deliver_id);
if (type == 1)
{
if (!string.IsNullOrEmpty(Request.Params["estimated_arrival_date"]))
{
dm.estimated_arrival_date = DateTime.Parse(Request.Params["estimated_arrival_date"]);
}
if (!string.IsNullOrEmpty(Request.Params["estimated_delivery_date"]))
{
dm.estimated_delivery_date = DateTime.Parse(Request.Params["estimated_arrival_date"]);
}
dm.estimated_arrival_period = int.Parse(Request.Params["estimated_arrival_period"]);
//dm.estimated_delivery_date = DateTime.Parse(Request.Params["estimated_delivery_date"]);
}
if (type == 2)
{
dm.delivery_name = Request.Params["delivery_name"];
dm.delivery_mobile = Request.Params["delivery_mobile"];
dm.delivery_phone = Request.Params["delivery_phone"];
dm.delivery_zip = uint.Parse(Request.Params["delivery_zip"]);
dm.delivery_address = Request.Params["delivery_address"];
}
int j = _DeliverDetailMgr.DeliverMasterEdit(dm, type);
if (j == 1)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public int SaveUserPhone(Model.Query.UserQuery uQuery)
{
_userhistoryDao = new UserHistoryDao(connStr);
_smsdao = new SmsDao(connStr);
_serialDao = new SerialDao(connStr);
Serial serial = new Serial();
uQuery.Replace4MySQL();
int i = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
#region 處理serial表數據
mySqlCmd.CommandText = _serialDao.Update(22);//22電話會員
serial = _serialDao.GetSerialById(22);
uQuery.user_id = Convert.ToUInt32(serial.Serial_Value) + 1;
#endregion
#region 處理user 和user_history數據
mySqlCmd.CommandText = Save(uQuery);
mySqlCmd.CommandText += _userhistoryDao.Save(uQuery);
#endregion
#region 處理sms表
Sms smsModel = new Sms();
smsModel.type = 9;
smsModel.mobile = uQuery.user_mobile;
smsModel.subject = "電話會員";
smsModel.content = "感謝您選擇成為吉甲地市集電話會員，未來我們將透過簡訊提供商品優惠。日後若有電話訂購服務之需求，可撥打專線(02)2783-4995，謝謝。";
smsModel.send = uQuery.send_sms_ad == true ? 1 : 0;
smsModel.created = uQuery.created;
smsModel.modified = uQuery.created;
smsModel.estimated_send_time = uQuery.created;
mySqlCmd.CommandText += _smsdao.SaveSms(smsModel);
#endregion
i += mySqlCmd.ExecuteNonQuery();
//全部执行成功以后，对serial表的serial_value的值進行變更
if (i == 3)
{
serial.Serial_Value = serial.Serial_Value + 1;
int j = _serialDao.Update(serial);
}
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("UsersDao-->Save-->" + ex.Message + mySqlCmd.CommandText.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
