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);
}
internal static bool DeleteUser(string userName)
{
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
"Delete from mt_employee where UserName=@UserName;",
new MySqlParameter("@UserName", userName));
return(true);
}
internal static bool SaveUser(string userName, string chineseName, int roleId)
{
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
"Update mt_employee set ChineseName=@ChineseName,RoleID=@RoleID where UserName=@UserName;",
new MySqlParameter("@UserName", userName),
new MySqlParameter("@ChineseName", chineseName),
new MySqlParameter("@RoleID", roleId));
return(true);
}
internal static bool SetPassword(string userName, string password)
{
password = password.ToUpper().ToMD5();
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
$"Update mt_employee set Password=@Password, WaitSet=0 where UserName=@UserName;",
new MySqlParameter("@UserName", userName),
new MySqlParameter("@Password", password));
return(true);
}
internal static bool ResetPassword(string userName)
{
// 两次MD5加密
string password = Config.DefaultPassword.ToMD5().ToMD5();
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
$"Update mt_employee set Password='******', WaitSet = 1 where UserName=@UserName;",
new MySqlParameter("@UserName", userName));
return(true);
}
internal static bool AddOrderStatus(string orderNum, string status)
{
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
$@"INSERT INTO `ex_statusinfo`(`ORDER_NUM`, `UPDATE_TIME`, `UPDATE_STATUS`, `REMARKS`)
VALUES (@ORDER_NUM, now(), @UPDATE_STATUS, @REMARKS);",
new MySqlParameter("@ORDER_NUM", orderNum),
new MySqlParameter("@UPDATE_STATUS", OrderStatus.GetStatus(status)),
new MySqlParameter("@REMARKS", OrderStatusDetaill.GetStatus(status)));
return(true);
}
public static bool SetPermission(int roleId, List <int> menuIds)
{
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
"Delete from cf_rolepermission where RoleID=@RoleID",
new MySqlParameter("@RoleID", roleId));
foreach (int menuId in menuIds)
{
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
"INSERT INTO cf_rolepermission (RoleID, MenuID) VALUES (@RoleID, @MenuID);",
new MySqlParameter("@RoleID", roleId),
new MySqlParameter("@MenuID", menuId));
}
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);
}
/// <summary>
/// 修改订单状态
/// </summary>
/// <param name="dicOrders">订单id,订单号</param>
/// <param name="status">状态</param>
/// <param name="userName">用户名</param>
/// <returns></returns>
public static bool BatchUpdateStatus(OrderStatusParam param)
{
string ids = string.Join(",", param.dicOrders.Select(t => t.Id).ToList());
string updateInfo = param.Status == "1011" ? "BATCH_NUMBER = '" + DateTime.Now.ToString("第yyyyMMdd批") + "'," : "";
bool upsucess = JabMySqlHelper.ExecuteNonQuery(Config.DBConnection,
$@"UPDATE ex_orderinfo
SET
STATUS = @Status,
UpdateBy = @UserName,
{updateInfo}
UpdateTime = now()
WHERE ID IN ({ids});",
new MySqlParameter("@Status", param.Status),
new MySqlParameter("@UserName", param.UserName)) > 0;
if (upsucess)
{
foreach (var item in param.dicOrders)
{
AddOrderStatus(item.Order_Num, param.Status);
}
}
return(true);
}
public IActionResult importEmployee(IFormFile file)
{
try
{
DataTable dt = ExcelHelper.ReadExcelToDataTable(file.OpenReadStream(), "Sheet1");
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection, "TRUNCATE TABLE tmp_importemployee");
string sql = @"INSERT INTO tmp_importemployee
(`EmployeeID`,
`ChineseName`,
`EmploymentStatus`,
`EmailAddress`,
`SupervisorID`,
`Department`,
`Costcenter`,
`BadgeID`,
`AreaCode`) VALUES ";
List <string> values = new List <string>();
foreach (DataRow row in dt.Rows)
{
string chineseName = Convert.ToString(row[0]);
long employeeID = Convert.ToInt64(row[1]);
string badgeID = Convert.ToString(row[2]);
string costcenter = Convert.ToString(row[4]);
string employmentStatus = "是";
try
{
employmentStatus = Convert.ToString(row["是否在职"]);
}
catch (Exception) { }
string emailAddress = "";
try
{
emailAddress = Convert.ToString(row["邮箱"]);
}
catch (Exception) { }
long supervisorID = -1;
try
{
supervisorID = Converter.TryToInt64(row["主管工号"], -1);
}
catch (Exception) { }
string department = "";
try
{
department = Convert.ToString(row["部门"]);
}
catch (Exception) { }
string areaCode = "CN51";
try
{
areaCode = Convert.ToString(row["AreaCode"]);
}
catch (Exception) { }
employmentStatus = employmentStatus == "是" ? "1" : "0";
values.Add($"({employeeID},'{chineseName}',{employmentStatus},'{emailAddress}','{supervisorID}','{department}','{costcenter}','{badgeID}','{areaCode}')");
}
if (values.Count == 0)
{
throw new Exception("导入人员信息为空！");
}
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection, sql + string.Join(",", values));
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection, "call Sp_confirmEmployee()");
return(Ok(new
{
status = 200,
msg = "导入成功"
}));
}
catch (Exception e)
{
return(Ok(new
{
status = 500,
msg = "导入失败:" + e.Message,
error = "导入失败:" + e.Message + Environment.NewLine + e.StackTrace
}));
}
}
public IActionResult importOrder(IFormFile file, [FromQuery] string userName)
{
try
{
DataTable dt = ExcelHelper.ReadExcelToDataTable(file.OpenReadStream(), "Sheet1");
string sql = @"INSERT INTO tmp_importorder
(`BatchID`,
`ORDER_NUM`,
`SENDER_PHONE`,
`SENDER_NAME`,
`SENDER_ADDRESS`,
`JBBW_PHONE`,
`JBBW_NAME`,
`JBBW_ADDRESS`,
`BATCH_NUMBER`,
`CreatedBy`) VALUES ";
List <string> values = new List <string>();
string batchId = Guid.NewGuid().ToString("N");
string batchNumber = DateTime.Now.ToString("第yyyyMMdd批");
foreach (DataRow row in dt.Rows)
{
string orderNum = Convert.ToString(row["快递单号"]);
string jbbwPhone = Convert.ToString(row["津巴布韦电话"]);
string jbbwAddress = Convert.ToString(row["津巴布韦地址"]);
string jbbwName = Convert.ToString(row["津巴布韦姓名"]);
string senderName = Convert.ToString(row["寄件人姓名"]);
string senderPhone = Convert.ToString(row["寄件人电话"]);
string senderAddress = Convert.ToString(row["寄件人地址"]);
values.Add($"('{batchId}','{orderNum}','{senderPhone}','{senderName}','{senderAddress}','{jbbwPhone}','{jbbwName}','{jbbwAddress}','{batchNumber}','{userName}')");
}
if (values.Count == 0)
{
throw new MsgException("导入人员信息为空！");
}
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection, sql + string.Join(",", values));
DataTable resultdt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, $"call Sp_confirmImportOrder('{batchId}')");
List <OrderInfo> recordList = new List <OrderInfo>();
if (resultdt != null && resultdt.Rows.Count > 0)
{
foreach (DataRow row in resultdt.Rows)
{
recordList.Add(new OrderInfo()
{
OrderNumber = Converter.TryToString(row["ORDER_NUM"]),
JBBWPhone = Converter.TryToString(row["JBBW_PHONE"]),
JBBWName = Converter.TryToString(row["JBBW_NAME"]),
JBBWAddress = Converter.TryToString(row["JBBW_ADDRESS"]),
SenderName = Converter.TryToString(row["SENDER_NAME"]),
SenderPhone = Converter.TryToString(row["SENDER_PHONE"]),
SenderAddress = Converter.TryToString(row["SENDER_ADDRESS"]),
Status = Converter.TryToString(row["Status"]),
BatchNo = Converter.TryToString(row["BATCH_NUMBER"]),
});
}
}
JabMySqlHelper.ExecuteNonQuery(Config.DBConnection, $"delete from tmp_importorder where BatchID = '{batchId}'");
return(Ok(new
{
status = 200,
msg = $"导入完成，今日包裹批次号：{batchNumber}",
data = recordList
}));
}
catch (Exception e)
{
return(Ok(new
{
status = 500,
msg = "导入失败:" + e.Message,
error = "导入失败:" + e.Message + Environment.NewLine + e.StackTrace
}));
}
}
