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