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 List <object> GetEmployeeSummary(int siteId)
{
List <Object> list = new List <Object>();
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection,
@"
select sum(isAdd) as addSum,sum(isLeave) AS leaveSum,sum(t) as total from(
SELECT
IF(TO_DAYS(createdate) = TO_DAYS(NOW()) AND EmploymentStatus = 1,
1,
0) AS isAdd,
IF(TO_DAYS(LastUpdate) = TO_DAYS(NOW()) AND EmploymentStatus = 0,
1,
0) AS isLeave,
IF(EmploymentStatus = 1,
1,
0) AS t
FROM
mt_employee) as temp");
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
list.Add(new
{
AddSum = Converter.TryToInt32(row["addSum"]),
LeaveSum = Converter.TryToInt32(row["leaveSum"]),
Total = Converter.TryToInt32(row["total"])
});
}
}
return(list);
}
public static UserInfo GetUserDetail(string userName, string password)
{
password = password.ToUpper().ToMD5();
List <Object> userList = new List <Object>();
DataTable dt = JabMySqlHelper.ExecuteDataTable(
Config.DBConnection,
@"SELECT
e.UserName,e.ChineseName,e.WaitSet,
r.RoleName,
r.RoleId
FROM mt_employee e
INNER JOIN cf_role r ON e.RoleID = r.roleid
WHERE e.UserName = @UserName and e.Password = @Password LIMIT 1",
new MySqlParameter("@UserName", userName),
new MySqlParameter("@Password", password));
if (dt != null && dt.Rows.Count > 0)
{
var row = dt.Rows[0];
return(new UserInfo
{
UserName = Converter.TryToInt64(row["UserName"]),
ChineseName = Converter.TryToString(row["ChineseName"]),
RoleName = Converter.TryToString(row["RoleName"]),
RoleId = Converter.TryToInt32(row["RoleId"]),
WaitSet = Converter.TryToInt16(row["WaitSet"]),
});
}
else
{
return(null);
}
}
public static List <Object> GetUserList()
{
List <Object> userList = new List <Object>();
DataTable dt = JabMySqlHelper.ExecuteDataTable(
Config.DBConnection,
@"SELECT
e.UserName,e.ChineseName,e.RoleId,
r.RoleName
FROM mt_employee e
INNER JOIN cf_role r ON e.RoleID = r.RoleID order by LastUpdate DESC");
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
userList.Add(new
{
UserName = Converter.TryToString(row["UserName"]),
ChineseName = Converter.TryToString(row["ChineseName"]),
RoleName = Converter.TryToString(row["RoleName"]),
RoleId = Converter.TryToInt32(row["RoleId"]),
});
}
}
return(userList);
}
internal static List <object> GetBatchNos()
{
string sql = @" SELECT DISTINCT BATCH_NUMBER FROM ex_orderinfo
ORDER by CreateTime DESC ";
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, sql);
List <object> result = new List <object>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
string batchNo = Converter.TryToString(row[0]);
if (!string.IsNullOrEmpty(batchNo))
{
result.Add(new
{
label = batchNo,
value = batchNo
});
}
}
}
return(result);
}
public static List <MenuEntity> GetMenuList()
{
List <MenuEntity> menuList = new List <MenuEntity>();
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, "select * from cf_menus where ShowFlag=1 order by DisplayOrder ", null);
if (dt != null && dt.Rows.Count > 0)
{
List <MenuEntity> tempList = new List <MenuEntity>();
foreach (DataRow row in dt.Rows)
{
tempList.Add(new MenuEntity()
{
MenuID = Converter.TryToInt32(row["MenuID"]),
MenuText = Converter.TryToString(row["MenuText"]),
Icon = Converter.TryToString(row["Icon"]),
RouterLink = Converter.TryToString(row["RouterLink"]),
ParentID = Converter.TryToInt32(row["ParentID"]),
});;
}
menuList = tempList.Where(m => m.ParentID == -1).ToList();
foreach (MenuEntity item in menuList)
{
item.Children = tempList.Where(m => m.ParentID == item.MenuID).ToList();
}
}
return(menuList);
}
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 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 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);
}
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 UpdateStatusByBatchNumber(BatchUpdateParam batchParam)
{
// 根据action 获取 fromStatus、toStatus
string fromStatus; string toStatus;
switch (batchParam.Action)
{
case "airportconfirm":
fromStatus = ((int)OrderStatusEnum.HasSend).ToString();
toStatus = ((int)OrderStatusEnum.InFlight).ToString(); break;
case "jbbwconfirm":
fromStatus = ((int)OrderStatusEnum.InFlight).ToString();
toStatus = ((int)OrderStatusEnum.ToJBBW).ToString(); break;
case "qingguanconfirm":
fromStatus = ((int)OrderStatusEnum.ToJBBW).ToString();
toStatus = ((int)OrderStatusEnum.QingGuan).ToString(); break;
default:
throw new MsgException("参数错误！");
}
// 根据batchNumber 查询订单信息
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, @"
SELECT ORDER_NUM,ID FROM ex_orderinfo
WHERE BATCH_NUMBER=@BatchNumber and `STATUS`=@Status",
new MySqlParameter("@BatchNumber", batchParam.BatchNumber),
new MySqlParameter("@Status", fromStatus));
if (dt != null && dt.Rows.Count > 0)
{
OrderStatusParam param = new OrderStatusParam();
param.Status = toStatus;
param.UserName = batchParam.UserName;
param.dicOrders = new List <OrderStatusParam.OrderInfo_F>();
foreach (DataRow row in dt.Rows)
{
param.dicOrders.Add(new OrderStatusParam.OrderInfo_F()
{
Id = Converter.TryToString(row["ID"]),
Order_Num = Converter.TryToString(row["ORDER_NUM"])
});
}
// 更新订单信息
return(BatchUpdateStatus(param));
}
else
{
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);
}
internal static List <object> GetUniformSummary(int siteId, DateTime startTime, DateTime endTime)
{
List <Object> list = new List <Object>();
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection,
@"
SELECT u.Style,OutSummary,InSummary
FROM ( SELECT DISTINCT style FROM cf_uniformtype WHERE SiteID = @SiteID) as u
LEFT JOIN(
SELECT uniformstyle,SUM(NUMBER) AS outSummary
FROM cf_giveoutrecord
WHERE TYPE = 1 AND siteID = @SiteID AND CreateTime > @StartTime AND CreateTime < @EndTime
GROUP BY uniformstyle
) AS a ON u.style = a.uniformstyle
LEFT JOIN(
SELECT uniformstyle, SUM(backnumber) AS inSummary
FROM view_applyrecord
WHERE siteId = @SiteID AND backNumber > 0 AND backTime > @StartTime AND backTime < @EndTime
GROUP BY uniformstyle
) AS b ON u.style = b.uniformstyle",
new MySqlParameter("@SiteID", siteId),
new MySqlParameter("@StartTime", startTime),
new MySqlParameter("@EndTime", endTime));
if (dt != null && dt.Rows.Count > 0)
{
int sumOut = 0;
int sumIn = 0;
foreach (DataRow row in dt.Rows)
{
list.Add(new
{
UniformStyle = Converter.TryToString(row["Style"]),
OutSummary = Converter.TryToInt32(row["OutSummary"]),
InSummary = Converter.TryToInt32(row["InSummary"])
});
sumOut += Converter.TryToInt32(row["OutSummary"]);
sumIn += Converter.TryToInt32(row["InSummary"]);
}
list.Add(new
{
UniformStyle = "Total",
OutSummary = sumOut,
InSummary = sumIn
});
}
return(list);
}
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);
}
internal static List <int> GetRoleMenus(int roleId)
{
List <int> menuList = new List <int>();
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection,
"select menuId from cf_rolepermission where RoleID=@RoleID",
new MySqlParameter("@RoleID", roleId));
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
menuList.Add(Converter.TryToInt32(row["menuId"]));
}
}
return(menuList);
}
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);
}
public static List <Object> GetRoleList()
{
List <Object> roleList = new List <Object>();
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection,
"select * from cf_role");
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
roleList.Add(new
{
RoleID = Converter.TryToInt32(row["RoleID"]),
RoleName = Converter.TryToString(row["RoleName"])
});
}
}
return(roleList);
}
internal static List <StatusInfo> GetOrderInfo(string order_num)
{
List <StatusInfo> statusInfos = new List <StatusInfo>();
string sql = @"SELECT
ORDER_NUM,
UPDATE_TIME,
UPDATE_STATUS,
REMARKS
FROM
ex_statusinfo
WHERE
ORDER_NUM = @OrderNumber
ORDER BY
UPDATE_TIME";
List <MySqlParameter> param = new List <MySqlParameter>();
param.Add(new MySqlParameter("@OrderNumber", order_num));
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, sql, param.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
StatusInfo statusInfo = new StatusInfo()
{
Order_Num = Converter.TryToString(row["ORDER_NUM"]),
Update_Status = Converter.TryToString(row["UPDATE_STATUS"]),
Update_Date = Converter.TryToDateTime(row["UPDATE_TIME"]).ToShortDateString(),
Update_Time = Converter.TryToDateTime(row["UPDATE_TIME"]).ToString("HH:mm:ss"),
Remarks = Converter.TryToString(row["REMARKS"]),
};
statusInfos.Add(statusInfo);
}
statusInfos.Last().LastFlag = true;
}
return(statusInfos);
}
/// <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);
}
internal static OrderInfo GetOrderDetail(OrderInfoParam searchParam)
{
OrderInfo orderInfo = new OrderInfo();
string sql = @"SELECT `ID`,
`ORDER_NUM`,
`USER_ID`,
`SENDER_PHONE`,
`SENDER_NAME`,
`SENDER_ADDRESS`,
`JBBW_PHONE`,
`JBBW_NAME`,
`JBBW_ADDRESS`,
`REMARKS`,
`WEIGHT`,
`FLIGHT_NUM`,
`LANDING_TIME`,
`STATUS`,
`BATCH_NUMBER`,
`CreateTime`,
`CreatedBy`,
`UpdateTime`,
`UpdateBy`
FROM `ex_orderinfo`
{0};";
List <MySqlParameter> param = new List <MySqlParameter>();
string where;
if (searchParam.ID != null)
{
where = " WHERE `ID` = @ID";
param.Add(new MySqlParameter("@ID", searchParam.ID));
}
else if (!string.IsNullOrEmpty(searchParam.OrderNumber))
{
where = " WHERE ORDER_NUM = @OrderNumber";
param.Add(new MySqlParameter("@OrderNumber", searchParam.OrderNumber));
}
else
{
throw new MsgException("参数错误！");
}
DataTable dt = JabMySqlHelper.ExecuteDataTable(Config.DBConnection, string.Format(sql, where), param.ToArray());
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
OrderInfo orderinfo = new OrderInfo()
{
ID = Converter.TryToInt64(row["ID"]),
OrderNumber = Converter.TryToString(row["ORDER_NUM"]),
SenderPhone = Converter.TryToString(row["SENDER_PHONE"]),
SenderName = Converter.TryToString(row["SENDER_NAME"]),
SenderAddress = Converter.TryToString(row["SENDER_ADDRESS"]),
JBBWPhone = Converter.TryToString(row["JBBW_PHONE"]),
JBBWName = Converter.TryToString(row["JBBW_NAME"]),
JBBWAddress = Converter.TryToString(row["JBBW_ADDRESS"]),
Remarks = Converter.TryToString(row["REMARKS"]),
Weight = Converter.TryToString(row["WEIGHT"]),
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"])),
BatchNo = Converter.TryToString(row["BATCH_NUMBER"]),
CreateTime = Converter.TryToDateTime(row["CreateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
CreatedBy = Converter.TryToString(row["CreatedBy"]),
UpdateTime = Converter.TryToDateTime(row["UpdateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
UpdatedBy = Converter.TryToString(row["UpdateBy"]),
};
orderinfo.BatchNo = string.IsNullOrEmpty(orderinfo.BatchNo) ? null : orderinfo.BatchNo;
return(orderinfo);
}
else
{
return(null);
}
}
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
}));
}
}
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
}));
}
}
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);
}
