/// <summary>
/// 获取一个时间点之后的累计中断的时间
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int Pausemins(int id, DateTime starttime)
{
int summin = 0;
//正常中断时间
string sqlstring = "Select Sum(DATEDIFF(mi,PauseTime,StartTime)) as summin from ServicePause Where PauseTime>='" + starttime + "' And PauseTime is not null and StartTime is not null and BookID=" + id;
try
{
//DataTable Dt = SQLDbHelper.Query(sqlstring).Tables[0];
//foreach (DataRow dr in Dt.Rows)
//{
// DateTime PauseTime = DateTime.Parse(dr["PauseTime"].ToString());
// DateTime StartTime = DateTime.Parse(dr["StartTime"].ToString());
// TimeSpan ts = StartTime.Subtract(PauseTime);
// summin +=ts.Days * 24 * 60 + ts.Hours * 60 + ts.Minutes;
//}
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj.ToString() != string.Empty)
{
int.Parse(obj.ToString());
}
}
catch (Exception Err)
{
throw Err;
}
return(summin);
}
private void 完工ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (CureentCt == null)
{
return;
}
DoubleClickButton bt = (DoubleClickButton)CureentCt;
string[] strs = bt.ImageKey.Split(new Char[] { ',' });
int recordid = int.Parse(bt.Name);
string sqlstring = "Update booking set State='完工', EndServiceTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "' where ID=" + recordid;
if (SQLDbHelper.ExecuteSql(sqlstring) > 0)
{
ClsBLL.AddMsg(recordid, "车牌号码:" + strs[1] + ",完工");
if (bt.Tag.ToString() == "中断" || bt.Tag.ToString() == "过时")
{
ClsBLL.ServicePauseStart(recordid);//结束中断
}
sqlstring = @"Select Count(*) from Booking A,(Select CarNo,VIN,CreateDate From Booking Where ID=" + recordid + ")B";
sqlstring += " Where A.CarNo=B.CarNo And A.VIN=B.VIN And A.CreateDate=B.CreateDate and A.State<>'完工'";
int r = int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString());
if (r > 0)
{
MessageBox.Show("还有" + r.ToString() + "张分单未完工！");
ClsBLL.AddMsg(recordid, "车牌号码:" + bt.Tag.ToString() + "还有" + r.ToString() + "张分单未完工！" + ClsBLL.UserName);
}
bt.BackColor = Color.Blue;
bt.Tag = "完工";
}
}
private void CheckRegister()
{
//ClsBLL.IsRegist = false;
object obj = SQLDbHelper.ExecuteScalar("Select Keyword_value from Setting where keyword='IP'");
string ip = string.Empty;
string mac = string.Empty;
if (obj != null)
{
ip = obj.ToString();
}
if (ip != string.Empty)
{
mac = ClassTripleDES.GetMacAddress(ip);
}
object obj2 = SQLDbHelper.ExecuteScalar("Select Keyword_value from Setting where keyword='key'");
string key = string.Empty;
if (obj2 != null)
{
key = obj2.ToString();
}
if (key == ClassTripleDES.EncryptWithKey(mac, ClassTripleDES.passswordkey))
{
ClsBLL.IsRegist = true;
}
}
private void label1_Click(object sender, EventArgs e)
{
if (MessageBox.Show("你确定要关闭系统吗？", "关闭系统", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
if (DateTime.Now.Hour > 16)
{
object obj = SQLDbHelper.ExecuteScalar("Select Position from Worker Where WorkerName='" + ClsBLL.UserName + "'");
if (obj != null)
{
string usergroup = obj.ToString();
if (usergroup == "主管")
{
string sqlstring = "Select count(*) from Booking Where StartServiceTime between '" + DateTime.Today.ToShortDateString() + "' and '" + DateTime.Now.ToString() + "' and ServiceType='" + ClsBLL.UserGroup + "' and State in ('中断','维修进行中','过时')";
int bills = int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString());
if (bills > 0)
{
string message = "有" + bills.ToString() + "张单没有完成，请处理完之后再关闭系统！！！";
if (MessageBox.Show(message, "系统提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
ClsBLL.AddMsg(-1, message + "--" + ClsBLL.UserName);
return;
}
}
}
}
}
this.Close();
}
}
private void 洗车ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (CureentCt == null)
{
return;
}
DoubleClickButton bt = (DoubleClickButton)CureentCt;
int recordid = int.Parse(bt.Name);
string[] strs = bt.ImageKey.Split(new Char[] { ',' });
string sqlstring = @"Select Count(*) from Booking A,(Select CarNo,VIN,CreateDate From Booking Where ID=" + recordid + ")B";
sqlstring += " Where A.CarNo=B.CarNo And A.VIN=B.VIN And A.CreateDate=B.CreateDate and A.State<>'完工'";
int r = int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString());
if (r > 0)
{
MessageBox.Show("还有" + r.ToString() + "张分单未完工，不能洗车。");
return;
}
sqlstring = "Update A set A.State='洗车'";
sqlstring += " From Booking A,(Select CarNo,BookTime From Booking Where ID=" + recordid + ") B";
sqlstring += " Where A.CarNo=B.CarNo And Substring(convert(nvarchar(50),A.booktime,120),1,10)=substring(convert(nvarchar(50),B.booktime,120),1,10)";
if (SQLDbHelper.ExecuteSql(sqlstring) > 0)
{
ClsBLL.AddMsg(recordid, "车牌号码:" + strs[1] + ",洗车");
foreach (Control ct in dataGridView1.Controls)
{
if (ct.Text == bt.Text)
{
dataGridView1.Controls.Remove(ct);
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox2.Text != textBox3.Text)
{
MessageBox.Show("确认密码不对！");
return;
}
string sqlstring = "Select count(*) from SysUser where UserID='" + ClsBLL.UserID + "' and Pwd='" + textBox1.Text + "'";
try
{
if (int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString()) == 0)
{
MessageBox.Show("原密码错误！");
textBox1.Text = string.Empty;
textBox1.Focus();
return;
}
sqlstring = "Update SysUser Set Pwd='" + textBox2.Text + "' Where UserID='" + ClsBLL.UserID + "'";
if (SQLDbHelper.ExecuteSql(sqlstring) > 0)
{
MessageBox.Show("修改成功！");
this.Close();
}
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
}
}
private void 洗车与送车时间ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (CureentCt == null)
{
return;
}
DoubleClickButton bt = (DoubleClickButton)CureentCt;
int recordid = int.Parse(bt.Name);
string sqlstring = @"Select Count(*) from Booking A,(Select CarNo,VIN,CreateDate From Booking Where ID=" + recordid + ")B";
sqlstring += " Where A.CarNo=B.CarNo And A.VIN=B.VIN And A.CreateDate=B.CreateDate and A.State<>'完工'";
int r = int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString());
if (r > 0)
{
MessageBox.Show("还有" + r.ToString() + "张分单未完工，不能洗车。");
return;
}
frmParkSite fps = new frmParkSite(recordid, 1, bt.Text);
fps.ShowDialog();
bt.BackColor = Color.MediumOrchid;
bt.Tag = "洗车";
foreach (Control ct in dataGridView1.Controls)
{
if (ct.Name == bt.Name)
{
dataGridView1.Controls.Remove(ct);
}
}
}
/// <summary>
/// 获取工单类型的数量
/// </summary>
/// <returns></returns>
public static int GetSerType(string servicetype)
{
string sqlstring = "Select Count(*) from Booking Where ServiceType='" + servicetype + "' and State='正式' and ComeTime <= '" + DateTime.Today.AddDays(1).ToShortDateString() + "'";
sqlstring += " OR State='延时到明天' and StartServiceTime<'" + DateTime.Today.ToString() + "'";
return(int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString()));
}
private void frmLogin_Load(object sender, EventArgs e)
{
textBox1.Text = AppConfig.ConfigGetValue(Application.ExecutablePath, "LastUserID");
//string lastdate = AppConfig.ConfigGetValue(Application.ExecutablePath, "LastDate");
//if (lastdate == string.Empty)
//{
// AppConfig.ConfigSetValue(Application.ExecutablePath, "LastDate", DateTime.Today.AddDays(30).ToString());
//}
//else
//{
// DateTime DateLast = DateTime.Parse(lastdate);
// if (DateLast.CompareTo(DateTime.Today) < 0)
// {
// MessageBox.Show("试用期已到！");
// this.Close();
// }
//}
if (textBox1.Text == string.Empty)
{
textBox1.Focus();
}
else
{
textBox2.Focus();
}
DateTime SysDBTime = DateTime.Parse(SQLDbHelper.ExecuteScalar("Select GetDate()").ToString());
TimeSpan ts = SysDBTime.Subtract(DateTime.Now);
if (Math.Abs(ts.Minutes) > 3)
{
MessageBox.Show("你的电脑时间与服务器时间不一致，请调整时间！");
this.DialogResult = DialogResult.Cancel;
this.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cmbPreSA.Text))
{
MessageBox.Show("接车人不能为空。");
return;
}
string sqlstring = "Update A set A.CarTopNo='" + textBox1.Text + "',A.Receiver='" + cmbPreSA.Text + "',A.PlanOutTime='" + dateTimePicker1.Text + " " + numericUpDown1.Value.ToString() + ":" + numericUpDown2.Value.ToString() + "'";
sqlstring += " ,State='正式',Success=1,ComeTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'";
sqlstring += " From Booking A,(Select CarNo,BookTime From Booking Where ID=" + RecordID + ") B";
sqlstring += " Where A.CarNo=B.CarNo And substring(convert(nvarchar(50),A.booktime,120),1,10)=substring(convert(nvarchar(50),B.booktime,120),1,10)";
try
{
if (SQLDbHelper.ExecuteSql(sqlstring) > 0)
{
SQLDbHelper.ExecuteSql("Exec Sp_SetIsBook");
ClsBLL.AddSysLog(RecordID, "预约接车，车牌号码:" + SQLDbHelper.ExecuteScalar("Select CarNo from Booking Where ID=" + RecordID).ToString());
this.DialogResult = DialogResult.OK;
}
this.Close();
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
}
}
private void btnSaveUser_Click(object sender, EventArgs e)
{
if (txtUserID.Text == string.Empty || txtUserName.Text == string.Empty)
{
MessageBox.Show("用户名或用户密码不能为空！");
return;
}
sqlstring = "Select Count(*) from SysUser where UserID='" + txtUserID.Text + "'";
if (int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString()) > 0)
{
MessageBox.Show(txtUserID.Text + "已经存在！");
return;
}
sqlstring = "Insert into SysUser(UserID,UserName,Pwd)values('" + txtUserID.Text + "','" + txtUserName.Text + "','" + txtPwd.Text + "')";
if (!txtUserID.Enabled)
{
sqlstring = "Update SysUser Set UserName='******',Pwd='" + txtPwd.Text + "' Where UserID='" + txtUserID.Text + "'";
}
if (SQLDbHelper.ExecuteSql(sqlstring) > 0)
{
MessageBox.Show("新增用户成功！");
TreeNode tn = new TreeNode();
tn.Text = txtUserName.Text + "(" + txtUserID.Text + ")";
tn.Tag = txtUserID.Text;
treeView1.Nodes[0].Nodes.Add(tn);
txtPwd.Text = string.Empty;
txtUserID.Text = string.Empty;
txtUserName.Text = string.Empty;
}
}
private void frmParkSite_Load(object sender, EventArgs e)
{
if (IntTag == 1)
{
string sqlstring = "Select ParkSite from Booking Where ID=" + RecordID;
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj != null)
{
textBox1.Text = obj.ToString();
}
}
textBox1.Focus();
}
/// <summary>
/// 车牌预约记录
/// </summary>
public static bool CarBookRecord(string carno)
{
string sqlstring = "Select Count(*) from Booking Where CarNo like '%" + carno + "%'";
int records = int.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString());
if (records > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获取单实际维修TU
/// </summary>
/// <returns></returns>
public static decimal GetFactHours(int recordid)
{
string sqlstring = "Select dbo.F_FactHour(" + recordid + ")";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj.ToString() == string.Empty)
{
return(0);
}
else
{
return(decimal.Parse(obj.ToString()) / 100);
}
}
/// <summary>
/// 获取系统设置关键字
/// </summary>
/// <returns></returns>
public static string GetSet(string keyword)
{
string sqlstring = "Select KeyWord_Value From Setting Where KeyWord='" + keyword + "'";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(string.Empty);
}
else
{
return(obj.ToString());
}
}
/// <summary>
/// 维修中断原因
/// </summary>
public static string PauseReason(int id)
{
string sqlstring = "Select Reason From ServicePause Where ID in (Select Max(ID) as MaxID from ServicePause Where BookID=" + id + ")";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(string.Empty);
}
else
{
return(obj.ToString());
}
}
/// <summary>
/// 获取维修工时
/// </summary>
/// <returns></returns>
public static decimal GetServiceHour(int recordid)
{
string sqlstring = "Select ServiceHour From Booking Where ID=" + recordid;
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(0);
}
else
{
return(decimal.Parse(obj.ToString()));
}
}
/// <summary>
/// 延迟原因
/// </summary>
public static string DelayReason(int id)
{
string sqlstring = "Select DelayReason From DelayService Where BookID=" + id;
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(string.Empty);
}
else
{
return(obj.ToString());
}
}
/// <summary>
/// 获取用户组
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public static string GetUserGroup(string username)
{
string sqlstring = "Select WorkerGroup from Worker Where WorkerName='" + username + "'";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return("机电维修','车身维修");
}
else
{
return(obj.ToString());
}
}
/// <summary>
/// 车顶号
/// </summary>
public static string CarTop(int id)
{
string sqlstring = "Select CarTopNo From Booking Where ID=" + id;
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(string.Empty);
}
else
{
return(obj.ToString());
}
}
/// <summary>
/// 中断时间
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DateTime PauseTime(int id)
{
string sqlstring = "Select PauseTime From ServicePause Where ID in (Select Max(ID) as MaxID from ServicePause Where BookID=" + id + ")";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(DateTime.Now);
}
else
{
return(DateTime.Parse(obj.ToString()));
}
}
/// <summary>
/// 完工时间
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DateTime EndServiceTime(int id)
{
string sqlstring = "Select EndServiceTime From Booking Where ID=" + id;
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(DateTime.Now);
}
else
{
return(DateTime.Parse(obj.ToString()));
}
}
/// <summary>
/// 获取已用工时
/// </summary>
/// <param name="Dt"></param>
/// <returns></returns>
public static decimal UseHours(DateTime Dt)
{
string sqlstring = "Select sum(ServiceHour) as sumhours from Booking where BookTime between '" + Dt.ToShortDateString() + "' and '" + Dt.AddDays(1).ToShortDateString() + "' and State<>'取消' and State <>'失约'";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj.ToString() == string.Empty)
{
return(0);
}
else
{
return(decimal.Parse(obj.ToString()));
}
}
/// <summary>
/// 获取计划完成时间
/// </summary>
/// <returns></returns>
public static string GetPlanComplete(int recordid)
{
string sqlstring = "Select PlanCompleteTime From Booking Where ID=" + recordid;
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(string.Empty);
}
else
{
return(obj.ToString());
}
}
private void frmCarTop_Load(object sender, EventArgs e)
{
string sqlstring = "Select ServiceHour From Booking Where ID=" + RecordID;
try
{
double hours = double.Parse(SQLDbHelper.ExecuteScalar(sqlstring).ToString());
numericUpDown1.Value = DateTime.Now.AddHours(hours).Hour;
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
}
textBox1.Focus();
}
/// <summary>
/// 维修时长
/// </summary>
public static string ServiceTime(int id)
{
string sqlstring = "Select StartServiceTime from Booking Where ID=" + id;
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(string.Empty);
}
else
{
TimeSpan ts = DateTime.Now.Subtract(DateTime.Parse(obj.ToString()));
string retstr = ts.Hours.ToString() + "°" + ts.Minutes + "'";
return(retstr);
}
}
/// <summary>
/// 维修中断原因
/// </summary>
public static string PauseTimeStr(int id)
{
string sqlstring = "Select PauseTime From ServicePause Where ID in (Select Max(ID) as MaxID from ServicePause Where BookID=" + id + ")";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(string.Empty);
}
else
{
TimeSpan ts = DateTime.Now.Subtract(DateTime.Parse(obj.ToString()));
string retstr = ts.Hours.ToString() + "°" + ts.Minutes + "'";
return(retstr);
}
}
/// <summary>
/// 获取分单数量
/// </summary>
/// <returns></returns>
public static int GetFendanNum(int recordid)
{
string sqlstring = @"Select Count(*) from Booking A,(Select CarNo,VIN,CreateDate From Booking Where ID=" + recordid + ")B";
sqlstring += " Where A.CarNo=B.CarNo And A.VIN=B.VIN And A.CreateDate=B.CreateDate and A.State='延时到明天'";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(0);
}
else
{
return(int.Parse(obj.ToString()));
}
}
private void button1_Click(object sender, EventArgs e)
{
string sqlstring = "Select UserName from SysUser where UserID='" + textBox1.Text + "' and Pwd='" + textBox2.Text + "'";
try
{
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
MessageBox.Show("用户名不存在或者密码错误！");
textBox1.Focus();
return;
}
else
{
//将计算机名称保存到数据库中
if (int.Parse(SQLDbHelper.ExecuteScalar("Select count(*) from VersionUser where Computer='" + Environment.MachineName + "'").ToString()) == 0)
{
SQLDbHelper.ExecuteSql("Insert into VersionUser(Computer,Ver)values('" + Environment.MachineName + "',0)");
}
else
{
int userver = int.Parse(SQLDbHelper.ExecuteScalar("Select ver from VersionUser where Computer='" + Environment.MachineName + "'").ToString());
int sysver = int.Parse(SQLDbHelper.ExecuteScalar("Select ver from VersionSys").ToString());
if (userver < sysver)
{
if (MessageBox.Show("软件有新的版本可以升级！你需要升级吗？", "软件升级", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
SQLDbHelper.ExecuteSql("Update VersionUser Set Ver=" + sysver + " where Computer='" + Environment.MachineName + "'");
System.Diagnostics.Process.Start(Application.StartupPath + @"\Update.exe");
Application.Exit();
}
}
}
ClsBLL.UserID = textBox1.Text;
ClsBLL.UserName = obj.ToString();
ClsBLL.UserGroup = ClsBLL.GetUserGroup(ClsBLL.UserName);
AppConfig.ConfigSetValue(Application.ExecutablePath, "LastUserID", textBox1.Text);
this.DialogResult = DialogResult.OK;
this.Close();
}
}
catch (Exception Err)
{
MessageBox.Show(Err.Message);
}
}
public static int GetDoubleID(int recordid, DateTime booktime)
{
int year = booktime.Year;
int month = booktime.Month;
int day = booktime.Day;
string sqlstring = "Select A.ID from Booking A,(Select ID,CarNo,Substring(convert(nvarchar(50),Booktime,120),1,10) as BookDay From Booking Where year(BookTime)=" + year + " and month(BookTime)=" + month + " and day(BookTime)=" + day + " and ID=" + recordid + ") B Where A.CarNo=B.CarNo and substring(convert(nvarchar(50),A.booktime,120),1,10)=B.BookDay And A.ID<>B.ID";
object obj = SQLDbHelper.ExecuteScalar(sqlstring);
if (obj == null)
{
return(-1);
}
else
{
return(int.Parse(obj.ToString()));
}
}