public static bool AddNewOrder(OrderInfo data)
{
object re = JabMySqlHelper.ExecuteScalar(Config.DBConnection,
"select count(*) from ex_orderinfo where ORDER_NUM=@OrderNumber;",
new MySqlParameter("@OrderNumber", data.OrderNumber));
if (Converter.TryToInt32(re) > 0)
{
throw new MsgException("快递单号已存在，请检查！");
}
bool insertStuatus = JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
$"INSERT INTO ex_orderinfo (ORDER_NUM,JBBW_NAME,JBBW_PHONE,JBBW_ADDRESS,SENDER_NAME,SENDER_PHONE,SENDER_ADDRESS,STATUS,REMARKS,WEIGHT,BATCH_NUMBER,CreatedBy) " +
$"VALUES (@OrderNumber,@JBBWName,@JBBWPhone,@JBBWAddress,@SenderName,@SenderPhone,@SenderAddress,@Status,@Remark,@Weight,@BatchNo,@UserName);",
new MySqlParameter("@OrderNumber", data.OrderNumber),
new MySqlParameter("@JBBWName", data.JBBWName),
new MySqlParameter("@JBBWPhone", data.JBBWPhone),
new MySqlParameter("@JBBWAddress", data.JBBWAddress),
new MySqlParameter("@SenderName", data.SenderName),
new MySqlParameter("@SenderPhone", data.SenderPhone),
new MySqlParameter("@SenderAddress", data.SenderAddress),
new MySqlParameter("@Remark", data.Remarks),
new MySqlParameter("@Weight", data.Weight),
new MySqlParameter("@BatchNo", data.BatchNo),
new MySqlParameter("@Status", OrderStatusEnum.Created),
new MySqlParameter("@UserName", data.UserName)) > 0;
if (insertStuatus)
{
AddOrderStatus(data.OrderNumber, "1001");
}
return(true);
}
public static bool UpdateOrder(OrderInfo data)
{
object re = JabMySqlHelper.ExecuteScalar(Config.DBConnection,
"select count(*) from ex_orderinfo where ID=@ID;",
new MySqlParameter("@ID", data.ID));
if (Converter.TryToInt32(re) == 0)
{
throw new MsgException("快递单号不存在，请检查！");
}
bool updateStuatus = JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
@"UPDATE ex_orderinfo
SET
SENDER_PHONE = @SenderPhone,
SENDER_NAME = @SenderName,
SENDER_ADDRESS = @SenderAddress,
JBBW_PHONE = @JBBWPhone,
JBBW_NAME = @JBBWName,
JBBW_ADDRESS = @JBBWAddress,
REMARKS = @Remark,
WEIGHT = @Weight,
STATUS = @Status,
BATCH_NUMBER = @BatchNo,
UpdateTime = now(),
UpdateBy = @UserName
WHERE ID = @ID;",
new MySqlParameter("@OrderNumber", data.OrderNumber),
new MySqlParameter("@ID", data.ID),
new MySqlParameter("@JBBWName", data.JBBWName),
new MySqlParameter("@JBBWPhone", data.JBBWPhone),
new MySqlParameter("@JBBWAddress", data.JBBWAddress),
new MySqlParameter("@SenderName", data.SenderName),
new MySqlParameter("@SenderPhone", data.SenderPhone),
new MySqlParameter("@SenderAddress", data.SenderAddress),
new MySqlParameter("@Remark", data.Remarks),
new MySqlParameter("@Weight", data.Weight),
new MySqlParameter("@BatchNo", data.BatchNo),
new MySqlParameter("@Status", data.Status),
new MySqlParameter("@UserName", data.UserName)) > 0;
if (updateStuatus)
{
AddOrderStatus(data.OrderNumber, data.Status);
}
return(true);
}
public static bool AddNewUser(string userName, string chineseName, int roleId)
{
object re = JabMySqlHelper.ExecuteScalar(Config.DBConnection,
"select count(*) from mt_employee where UserName=@UserName;",
new MySqlParameter("@UserName", userName));
if (Converter.TryToInt32(re) > 0)
{
throw new MsgException("用户已存在");
}
string password = Config.DefaultPassword.ToMD5().ToMD5();
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
$"INSERT INTO mt_employee (UserName,ChineseName,RoleID,Password) VALUES (@UserName,@ChineseName,@RoleID,'{password}');",
new MySqlParameter("@UserName", userName),
new MySqlParameter("@ChineseName", chineseName),
new MySqlParameter("@RoleID", roleId));
return(true);
}
internal static List <OrderInfo> GetOrderList(OrderInfoParam searchParam, out int total)
{
int offset = (searchParam.PageIndex - 1) * searchParam.PageSize;
int rows = searchParam.PageSize;
List <OrderInfo> recordList = new List <OrderInfo>();
string sql = @" SELECT o.ID, ORDER_NUM, BATCH_NUMBER,JBBW_PHONE, JBBW_NAME,FLIGHT_NUM, LANDING_TIME, `STATUS`, o.CreateTime, e.ChineseName
FROM ex_orderinfo o
LEFT JOIN mt_employee e ON o.CreatedBy = e.UserName
{0}
ORDER by o.CreateTime DESC
LIMIT {1},{2} ";
string where = " WHERE 1=1 ";
List <MySqlParameter> param = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(searchParam.OrderNumber))
{
where += " AND ORDER_NUM = @OrderNumber";
param.Add(new MySqlParameter("@OrderNumber", searchParam.OrderNumber));
}
if (!string.IsNullOrEmpty(searchParam.Status))
{
where += " AND `STATUS` = @Status";
param.Add(new MySqlParameter("@Status", searchParam.Status));
}
if (!string.IsNullOrEmpty(searchParam.CreateTimeStartStr))
{
where += " AND o.CreateTime > @CreateTimeStart";
param.Add(new MySqlParameter("@CreateTimeStart", Convert.ToDateTime(searchParam.CreateTimeStartStr)));
}
if (!string.IsNullOrEmpty(searchParam.CreateTimeEndStr))
{
where += " AND o.CreateTime < @CreateTimeEnd";
param.Add(new MySqlParameter("@CreateTimeEnd", Convert.ToDateTime(searchParam.CreateTimeEndStr)));
}
if (!string.IsNullOrEmpty(searchParam.BatchNo))
{
where += " AND BATCH_NUMBER = @BatchNo";
param.Add(new MySqlParameter("@BatchNo", searchParam.BatchNo));
}
if (!string.IsNullOrEmpty(searchParam.FlightNumber))
{
where += " AND FLIGHT_NUM = @FlightNumber";
param.Add(new MySqlParameter("@FlightNumber", searchParam.FlightNumber));
}
if (!string.IsNullOrEmpty(searchParam.KeyWord))
{
where += " AND JBBW_PHONE like CONCAT('%',@KeyWord,'%') OR JBBW_NAME like CONCAT('%',@KeyWord,'%')";
param.Add(new MySqlParameter("@KeyWord", searchParam.KeyWord));
}
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, string.Format(sql, where, offset, rows), param.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
recordList.Add(new OrderInfo()
{
ID = Converter.TryToInt64(row["ID"]),
OrderNumber = Converter.TryToString(row["ORDER_NUM"]),
BatchNo = Converter.TryToString(row["BATCH_NUMBER"]),
JBBWPhone = Converter.TryToString(row["JBBW_PHONE"]),
JBBWName = Converter.TryToString(row["JBBW_NAME"]),
FlightNumber = Converter.TryToString(row["FLIGHT_NUM"]),
LandingTime = string.IsNullOrEmpty(Converter.TryToString(row["LANDING_TIME"])) ? "" : Converter.TryToDateTime(row["LANDING_TIME"]).ToString("yyyy-MM-dd HH:mm:ss"),
Status = Converter.TryToString(row["STATUS"]),
StatusStr = OrderStatus.GetStatus(Converter.TryToString(row["STATUS"])),
CreateTime = Converter.TryToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
CreatedBy = Converter.TryToString(row["ChineseName"])
});
}
}
// 查询总数
string sqlCount = @"SELECT count(*) FROM ex_orderinfo o {0}";
object re = JabMySqlHelper.ExecuteScalar(Config.DBConnection, string.Format(sqlCount, where), param.ToArray());
total = Convert.ToInt32(re);
return(recordList);
}