public DataTable GetLogDaTable(string userid, string date, string enddate)
{
DataTable table = new DataTable();
string sqlStr = " select * from [dbo].[t_mes] t"
+ " left join [dbo].[t_goods] d on t.goodsid =d.goodid "
+ " left join [dbo].[t_user] u on t.userid = t.userid where convert(varchar(100),begdate,23) >='" + date + "' and convert(varchar(100),begdate,23)<='" + enddate + "'";
if (userid.Trim().Length > 0)
{
sqlStr = sqlStr + " and username ='******'";
}
try
{
table = SqlServerHelper.ExecuteDataTable(sqlStr);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
return(table);
}
public int UpDataPlcData(string plcid, string expressNo, string endprot)
{
//string sqlStr = "update t_djmx set endprot='" + endprot + "',endsts='1',endate =getdate() where "
// + " plcid=(selec top 1 plcid from t_djmx plcid='" + plcid + "' and endsts='0' order by djsj desc) ";
// string sqlStr = "update t_djmx set endprot='" + endprot + "',endsts='1',endate =getdate() " +
// "where plcid='" +plcid +"' and endsts='0' and ExpressNo = (select top 1 ExpressNo from t_djmx where plcid='" +
// plcid + "' and endsts='0' order by djsj desc)";
//select top 1 ExpressNo from t_djmx where plcid=2 and endsts=1 order by djsj desc
// update t_djmx set endprot=9,endsts=1,endate =getdate() where plcid=4 and ExpressNo = 049158651324
String sqlStr = " update t_djmx set endprot=" + endprot + ",endsts=1,endate =getdate() where plcid=" + plcid + " and ExpressNo = " + expressNo;
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public string GetWmsSeq()
{
DataTable tb = new DataTable();
try
{
string sqlStr = "select (NEXT VALUE FOR seqID )as id ";
tb = SqlServerHelper.ExecuteDataTable(sqlStr);
if (tb != null)
{
if (tb.Rows.Count > 0)
{
return(tb.Rows[0]["id"].ToString());
}
}
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
// return "0;
}
Random ran = new Random();
return((ran.Next(1, 100)).ToString());
}
/// <summary>
/// 关闭前处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Window1_OnClosing(object sender, CancelEventArgs e)
{
//保存所有便签设置
NotepadManage.SaveSetings(this);
//Application.Current.MainWindow
try
{
RichTextBoxTool.Writer(RichTextBox1, CacheFileName);
//如果ShowInTaskbar设置为true，则关闭时先关闭子窗体
//如果设置为false，则先关闭主窗体
//if (!ShowInTaskbar)
NotepadManage.RemoveNotepad(this);
NotepadManage.ShowWindowListCount--;
if (NotepadManage.ShowWindowListCount == 0)
{
//关闭一个应用程序。
SystemCommon.ExitSystem();
}
}
catch (Exception ex)
{
MessageBox.Show("保存出了点问题..." + ex.Message, "错误", MessageBoxButton.OK, MessageBoxImage.Error);
e.Cancel = true;
}
}
/// <summary>
/// 窗体关闭
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MainWindow_OnClosing(object sender, CancelEventArgs e)
{
//保存设置
_settings.Title = Title;
_settings.WinLocation = new Point(Left, Top);
_settings.Save();
//保存所有便签设置--已经在APP中设置
NotepadManage.SaveSetings();
//关闭一个应用程序。
SystemCommon.ExitSystem();
}
// 执行dml
private static int execDml(string sqlStr, SqlParameter[] commandParameters = null)
{
try
{
int i = SqlServerHelper.ExecuteNonQuery(CommandType.Text, sqlStr, commandParameters);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message);
return(0);
}
}
/// <summary>
/// 默认根据最近7天的销售情况，用标准差公式计算消化率稳定产品.
/// </summary>
/// <returns>.</returns>
private static List <ProductExpendRateStandardDeviationModel> GetStableExpendRate()
{
try
{
List <ProductExpendRateStandardDeviationModel> list = new List <ProductExpendRateStandardDeviationModel>();
using (ERPDBEntities db = new ERPDBEntities())
{
DateTime start_time = DateTime.Now; DateTime end_time = start_time.AddDays(7);
var query = (from a in db.Product
join b in db.Sell_Record on a.product_id equals b.product_id
where b.create_time >= start_time && b.create_time <= end_time
select new
{ a.product_id, a.product_name, a.stock_count, b.sell_count, b.create_time, a.product_category }).ToList();
if (query.Any())
{
var product = query.GroupBy(a => new { a.product_id, a.product_name });
if (product.Any())
{
foreach (var item in product)
{
List <double> expend_rate = new List <double>();
foreach (var info in item)
{
double rate = ((double)info.sell_count / (double)info.stock_count) * 100;
expend_rate.Add(rate);
}
ProductExpendRateStandardDeviationModel model = new ProductExpendRateStandardDeviationModel();
model.product_id = item.Key.product_id;
model.product_name = item.Key.product_name;
model.expend_rate_sd = SystemCommon.GetStdDev(expend_rate, false);
list.Add(model);
}
}
}
if (list.Any())
{
list = list.OrderBy(a => a.expend_rate_sd).Take(5).ToList();
}
}
return(list);
}
catch (Exception ex)
{
}
return(null);
}
public int UpdateProtStsSum(string prot, int sum)
{
string sqlStr = "update t_prot set count='" + sum + " where prot='" + prot + "'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public int InsertTask(string code)
{
string sqlStr = "insert into t_code (ExpressNo)values('" + code + "')";// set endprot='" + endprot + "',endsts='1',endate =getdate() where plcid='" + plcid + "' and endsts='0'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
/// <summary>
/// 更新端口的状态
/// </summary>
/// <param name="prot"></param>
/// <param name="sts"></param>
/// <returns></returns>
public int UpdateErrProtSts(string prot, string text, string sts)
{
string sqlStr = "update t_prot set sts='" + sts + "', text='" + text + "' where prot='" + prot + "'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public int UpdateProtSendSts(string prot)
{
string sqlStr = "update t_prot set datasts='0' where prot='" + prot + "'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public DataTable GetPlcSednCode()
{
lock (this)
{
string sqlStr = "Select * from t_djmx where endsts='1' ";///and ExpressNo!='noread'
try
{
return(SqlServerHelper.ExecuteDataTable(sqlStr));
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
}
}
public DataTable GetwmsSeqData(string wmsseq)
{
lock (this)
{
string sqlStr = "Select plcid from t_djmx where wmsseq='" + wmsseq + "' ";
try
{
return(SqlServerHelper.ExecuteDataTable(sqlStr));
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
}
}
public int UpdateCount(int prot)
{
//update t_prot set count=(select ifnull(count,'0') from t_prot where prot=111)+1 where prot=111
string sqlStr = "update t_prot set count=count+1 where prot=" + prot;
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public int UpdateEndprot(int endprot, string plcid)
{
//update t_djmx set endprot=4 where plcid=10417
string sqlStr = "update t_djmx set endprot=" + endprot + " where plcid=" + plcid;
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public DataTable SelectSum(string prot)
{
DataTable tb = new DataTable();
try
{
string sqlStr = "select * from t_prot where prot='" + prot + "'";
tb = SqlServerHelper.ExecuteDataTable(sqlStr);
return(tb);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
}
public IHttpActionResult UserLogin(string name, string password)
{
if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(password))
{
return(Json(new { result = "用户名或密码错误" }));
}
using (ERPDBEntities db = new ERPDBEntities())
{
var encode_pass = SystemCommon.GetMD5Str(password);
var user = db.Staff.FirstOrDefault(a => a.name == name && a.password == encode_pass);
if (user == null)
{
return(Json(new { result = "用户不存在" }));
}
return(Json(new { result = "登陆成功", data = user.id }));
}
}
public int UpDataWmsPlcData(string plcid)
{
lock (this)
{
string sqlStr = "update t_djmx set endsts='2' where plcid='" + plcid + "'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
}
public IHttpActionResult AddUser(string name, string password, int leader_id, string user_type, decimal salary, string sell_product_type)
{
try
{
if (string.IsNullOrEmpty(name))
{
return(Json(new { result = "用户名不能为空" }));
}
Enum_User_Type userType;
bool convertResult = Enum.TryParse(user_type, out userType);
if (convertResult == false)
{
return(Json(new { result = "用户类型异常" }));
}
Staff user = new Staff();
user.name = name;
user.password = string.IsNullOrEmpty(password) ? SystemCommon.GetMD5Str(SystemCommon.Default_Password) : SystemCommon.GetMD5Str(password);
user.leader_id = leader_id;
user.user_type = user_type;
user.salary = salary;
user.sell_product_type = sell_product_type;
user.create_time = DateTime.Now;
user.update_time = DateTime.Now;
using (ERPDBEntities db = new ERPDBEntities())
{
db.Entry(user).State = System.Data.Entity.EntityState.Added;
if (db.SaveChanges() < 0)
{
return(Json(new { result = "用户添加失败" }));
}
}
return(Json(new { result = "用户添加成功" }));
}
catch (Exception ex)
{
}
return(Json(new { result = "系统异常" }));
}
/// <summary>
/// 窗体加载，打开所有便签
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MainWindow_OnLoaded(object sender, RoutedEventArgs e)
{
//加载设置
_settings.Reload();
Title = _settings.Title;
Left = _settings.WinLocation.X;
Top = _settings.WinLocation.Y;
RichTextBoxTool.AddExtenstion();
NotepadManage.OpenAllNotepad();
Top = -500;
Left = -500;
//设置主窗体
SystemCommon.SetWindowMain(this);
//设置主窗体
ShowInTaskbar = !NotepadManage.SystemSetting.ShowInTaskbar;
//打开导入的文件
NotepadManage.OpenImputFileTask();
}
/// <summary>
/// 获取商品消耗率过高和过低的5个产品.
/// </summary>
/// <param name="category">.</param>
/// <param name="time">.</param>
/// <returns>.</returns>
private static ExpendModel GetProductExpendRate(int category, DateTime?time = null)
{
ExpendModel model = new ExpendModel();
try
{
DateTime start_time = time.HasValue ? time.Value.Date : DateTime.Now.Date;
DateTime end_time = start_time.AddDays(1);
using (ERPDBEntities db = new ERPDBEntities())
{
var query = (from a in db.Product
join b in db.Sell_Record on a.product_id equals b.product_id
where a.product_category == category && b.create_time >= start_time && b.create_time < end_time
select new { a.product_id, a.product_name, a.stock_count, b.sell_count, b.create_time }).AsQueryable();
if (query.Any())
{
var product = query.GroupBy(a => new { a.product_id, a.product_name });
foreach (var item in product)
{
List <double> expend_rate = new List <double>();
foreach (var info in item)
{
double rate = ((double)info.sell_count / (double)info.stock_count) * 100;
expend_rate.Add(rate);
}
double average_expend_rate = expend_rate.Average();
if (category == (int)Product_Category_Enum.RiPei)
{
//日配商品消化率过高，消化率90%，上下两个点
if (average_expend_rate >= SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.RiPei, (int)Enum_Product_Expend_Rate_Type.High_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.High_Rate.Add(high_model);
}
//日配商品消化率过低，消化率50%
if (average_expend_rate < SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.RiPei, (int)Enum_Product_Expend_Rate_Type.Low_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.Low_Rate.Add(high_model);
}
}
else if (category == (int)Product_Category_Enum.NoRiPei)
{
//非日配商品消化率过高，消化率30%，上下五个点
if (average_expend_rate >= SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.NoRiPei, (int)Enum_Product_Expend_Rate_Type.High_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.High_Rate.Add(high_model);
}
//非日配商品消化率过低，消化率10%
if (average_expend_rate < SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.NoRiPei, (int)Enum_Product_Expend_Rate_Type.Low_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.Low_Rate.Add(high_model);
}
}
}
if (model.High_Rate.Any() && model.High_Rate.Count > 5)
{
model.High_Rate = model.High_Rate.OrderByDescending(a => a.average_expend_rate).Take(5).ToList();
}
if (model.Low_Rate.Any() && model.Low_Rate.Count > 5)
{
model.Low_Rate = model.Low_Rate.OrderByDescending(a => a.average_expend_rate).Take(5).ToList();
}
}
return(model);
}
}
catch (Exception ex)
{
}
return(null);
}
public IHttpActionResult GetLowExpendRateProduct()
{
try
{
List <ProductExpendRateStandardDeviationModel> list = new List <ProductExpendRateStandardDeviationModel>();
using (ERPDBEntities db = new ERPDBEntities())
{
var query = (from a in db.Product join b in db.Sell_Record on a.product_id equals b.product_id select new { a.product_id, a.product_name, a.stock_count, b.sell_count, a.product_category }).ToList();
if (query.Any())
{
var ri_pei = query.Where(a => a.product_category == (int)Product_Category_Enum.RiPei).GroupBy(a => new { a.product_id, a.product_name });
var no_ri_pei = query.Where(a => a.product_category == (int)Product_Category_Enum.NoRiPei).GroupBy(a => new { a.product_id, a.product_name });
if (ri_pei.Any())
{
foreach (var item in ri_pei)
{
List <double> expend_rate = new List <double>();
foreach (var info in item)
{
double rate = ((double)info.sell_count / (double)info.stock_count) * 100;
expend_rate.Add(rate);
}
double expent_rate_sd = SystemCommon.GetStdDev(expend_rate, false);
if (expent_rate_sd < 50) //日配商品消化率低于50%
{
ProductExpendRateStandardDeviationModel model = new ProductExpendRateStandardDeviationModel();
model.product_id = item.Key.product_id;
model.product_name = item.Key.product_name;
model.expend_rate_sd = SystemCommon.GetStdDev(expend_rate, false);
list.Add(model);
}
}
}
if (no_ri_pei.Any())
{
foreach (var item in ri_pei)
{
List <double> expend_rate = new List <double>();
foreach (var info in item)
{
double rate = ((double)info.sell_count / (double)info.stock_count) * 100;
expend_rate.Add(rate);
}
double expent_rate_sd = SystemCommon.GetStdDev(expend_rate, false);
if (expent_rate_sd < 10) //非日配商品消化率低于10%
{
ProductExpendRateStandardDeviationModel model = new ProductExpendRateStandardDeviationModel();
model.product_id = item.Key.product_id;
model.product_name = item.Key.product_name;
model.expend_rate_sd = SystemCommon.GetStdDev(expend_rate, false);
list.Add(model);
}
}
}
}
return(Json(new { result = list }));
}
}
catch (Exception ex)
{
}
return(Json(new { result = "系统异常" }));
}
public IHttpActionResult GetUserTradeData(int user_id)
{
try
{
using (ERPDBEntities db = new ERPDBEntities())
{
Staff staff_auth = db.Staff.FirstOrDefault(a => a.id == user_id);
if (staff_auth == null)
{
return(Json(new { result = "员工不存在" }));
}
DateTime start_time = DateTime.Now.Date;
DateTime end_time = start_time.AddDays(1);
List <int> product_type = staff_auth.sell_product_type.Split(',').Select(a => int.Parse(a)).ToList();
var query = (from a in db.Sell_Record
join b in db.Product on a.product_id equals b.product_id
where a.create_time >= start_time && a.create_time < end_time && product_type.Contains(b.product_type)
select new
{
a.product_id,
a.product_name,
a.sell_count,
a.delivery_count,
a.trash_count,
b.product_type,
b.stock_count,
b.price
}).ToList();
List <UserTradeDataModel> list = new List <UserTradeDataModel>();
var group_data = query.GroupBy(a => a.product_type);
foreach (var item in group_data)
{
UserTradeDataModel model = new UserTradeDataModel();
model.product_type = SystemCommon.GetDescription((Product_Type_Enum)Enum.ToObject(typeof(Product_Type_Enum), item.Key));
foreach (var pro in item)
{
ProductSellModel sell_model = new ProductSellModel();
sell_model.product_id = pro.product_id;
sell_model.product_name = pro.product_name;
sell_model.delivery_count = pro.delivery_count; //交货
sell_model.sell_count = pro.sell_count;
sell_model.product_price = pro.price;
sell_model.sell_amount = pro.sell_count * pro.price;
model.sell_info.Add(sell_model);
}
model.total_sell_amount = model.sell_info.Sum(a => a.sell_amount);
model.total_sell_count = model.sell_info.Sum(a => a.sell_count);
model.total_delivery_count = model.sell_info.Sum(a => a.delivery_count);
list.Add(model);
}
return(Json(new { result = list }));
}
}
catch (Exception ex)
{
}
return(NotFound());
}
public App()
{
#region 序退出异常处理
var pro = Process.GetCurrentProcess();
pro.EnableRaisingEvents = true;
pro.Exited += (sender, e1) =>
{
ToolLogs.Error(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "在进程退出时发生");
};
AppDomain.CurrentDomain.ProcessExit += (sender, e1) =>
{
////晚于this.Exit
//System.Threading.Monitor.TryEnter(obj, 500);
////后来改为
//System.Threading.Monitor.Enter(obj);
ToolLogs.Error(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "当默认应用程序域的父进程存在时发生");
////不能在这保存所有便签设置，因为这时所有的程序已经全部关闭。
//NotepadManage.SaveSetings();
};
//异常处理
this.DispatcherUnhandledException += (sender, e1) =>
{
ToolLogs.Debug(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "系统异常处理", e1.Exception);
e1.Handled = true;//使用这一行代码告诉运行时，该异常被处理了，不再作为UnhandledException抛出了。
SystemCommon.IsExitSystem = true;
MessageBox.Show("我们很抱歉，当前应用程序遇到一些问题，该操作已经终止,将要重新启动。" + e1.Exception.Message, "错误", MessageBoxButton.OK, MessageBoxImage.Error);//这里通常需要给用户一些较为友好的提示，并且后续可能的操作
//保存所有便签设置
NotepadManage.SaveSetings();
//不要在这打开程序，如果你的程序是刚打开时出现错误，用户将无法关闭。这将是用户的恶梦。
SystemCommon.ExitSystem();
//System.Diagnostics.Process.Start(System.Reflection.Assembly.GetExecutingAssembly().Location);
};
this.Exit += (sender, e1) =>
{
//早于AppDomain.CurrentDomain.ProcessExit
ToolLogs.Error(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "在进程退出时发生Exit");
////不能在这保存所有便签设置，因为这时所有的程序已经全部关闭。
// NotepadManage.SaveSetings();
//e1.ApplicationExitCode
};
#endregion
bool result = ImputCacheFile();
//非调试模式
if (!System.Diagnostics.Debugger.IsAttached)
{
Client.Pub.ServiceProxyFactoryLib.ProxyBinding.ReplaceAddress = true;
Client.Pub.ServiceProxyFactoryLib.ProxyBinding.ReplacePort = true;
//检测是否已有一个程序实例运行
this.Startup += new StartupEventHandler(App_Startup);
//下载版本不同的文件
string loadPath = Path.Combine(SystemCommon.Path, "AutoUpdate");
//复制自动更新程序文件
Tools.FileManage.CopyDir(Path.Combine(loadPath, "LoadDown\\AutoUpdate\\"), loadPath);
Tools.FileManage.IsUpdateThread();
//绑定扩展名
Task task = new Task(() =>
{
try
{
BindingExtend();
}
catch (Exception e)
{
ToolLogs.Error(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "绑定扩展名", e);
}
});
task.Start();
}
}
