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