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