public void Setup()
{
_jobTitle = new Model.JobTitle { Id = TitleId, Name = Title };
_jobTitleProvider = MockRepository.GenerateStub<IJobTitleProvider>();
_jobTitleProvider.Stub(j => j.GetById(TitleId)).Return(_jobTitle);
_employee = new Model.Employee { Id = EmployeeId, FirstName = "Alex", Surname = "Martin", JobTitle = _jobTitle };
}
private void btnQueryEmployee_Click(object sender, EventArgs e)//查询员工
{
Employee.SearchEmployeeForm sForm = new Employee.SearchEmployeeForm();
Model.Employee loadEmp = new Model.Employee();
sForm.userName = userName;
sForm.ShowDialog(this);//打开窗体
loadEmp.EmployeeId = sForm.ID;//获取ID
grdEmployee.DataSource = dtc.LoadEmp(loadEmp);//加载数据
}
private void btnDeleteEmployee_Click(object sender, EventArgs e)
{
Model.Employee delEmp = new Model.Employee();
delEmp.EmployeeId =Convert.ToInt32( grdEmployee.CurrentRow.Cells[0].Value);//获取被删除员工的ID
dtc.DelEmp(delEmp);//删除
MessageBox.Show("删除成功！","提示：");
LoadDG();//重加载
}
public Model.Employee Get(string employeeId,string pageNumber)
{
try
{
Model.Employee emp = new Model.Employee();
List<Model.Remark> tempRemark = new List<Model.Remark>();
SqlConnection conEmp = new SqlConnection("Data Source=TRAINING12;Initial Catalog=Employee;User ID=sa;Password=test123!@#");
SqlConnection conEmpRemark = new SqlConnection("Data Source=TRAINING12;Initial Catalog=Employee;User ID=sa;Password=test123!@#");
conEmp.Open();
conEmpRemark.Open();
SqlCommand cmd = new SqlCommand("GetbyId", conEmp);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Id", employeeId));
SqlDataReader empReader = cmd.ExecuteReader();
while (empReader.Read())
{
emp.Id = empReader[0].ToString();
emp.FirstName = empReader[1].ToString();
emp.LastName = empReader[2].ToString();
emp.Title = empReader[3].ToString();
emp.Email = empReader[4].ToString();
emp.Phone = empReader[5].ToString();
emp.JoiningDate = DateTime.Parse(empReader[6].ToString());
SqlCommand cmdRemark = new SqlCommand("GetRemarksByIdPagenated", conEmpRemark);
cmdRemark.CommandType = CommandType.StoredProcedure;
cmdRemark.Parameters.Add(new SqlParameter("@Id", emp.Id));
cmdRemark.Parameters.Add(new SqlParameter("@PageNumber", pageNumber));
SqlDataReader empRemarkReader = cmdRemark.ExecuteReader();
while (empRemarkReader.Read())
{
Model.Remark remark = new Model.Remark();
remark.Text = empRemarkReader[3].ToString();
remark.CreateTimeStamp = Convert.ToDateTime(empRemarkReader[4].ToString());
tempRemark.Add(remark);
}
}
emp.Remarks = tempRemark;
conEmp.Close();
conEmpRemark.Close();
return emp;
}
catch (Exception ex)
{
var rethrow = ExceptionPolicy.HandleException("data.policy", ex);
if (rethrow) throw;
return null;
}
}
public Model.Employee GetByEmail(string email)
{
using (var connection = new SqlConnection(Configurations.EmployeeDbConnectionString))
{
var addRemarkCommand = new SqlCommand("spGetEmployeeByEmail", connection);
addRemarkCommand.CommandType = System.Data.CommandType.StoredProcedure;
addRemarkCommand.Parameters.Add(new SqlParameter("@Email", email));
var resultReader = addRemarkCommand.ExecuteReader();
var employee = new Model.Employee();
if (resultReader.HasRows)
{
while (resultReader.Read())
{
employee = ParseEmployee(resultReader);
}
}
return employee;
}
}
public Model.Employee Authenticate(string emailId, string password)
{
Model.Employee employee=new Model.Employee();
SqlConnection con = new SqlConnection("Data Source=TRAINING12;Initial Catalog=Employee;User ID=sa;Password=test123!@#");
con.Open();
SqlCommand cmd = new SqlCommand("Authenticate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@emailId",emailId));
cmd.Parameters.Add(new SqlParameter("@password",password));
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
EmployeeStorage storageObject= new EmployeeStorage();
employee = storageObject.Get(dr[0].ToString(), "1");
return employee;
}
con.Close();
return null;
}
public bool mHasRowsAfter(Model.Employee emp)
{
return(accessor.mHasRowsAfter(emp));
}
/// <summary>
/// Initiate a new todo list for new user
/// </summary>
/// <param name="userName"></param>
private static void InitiateDatabaseForNewUser(string userName)
{
var resolver = GlobalConfiguration.Configuration.DependencyResolver;
var uow = (IExpensesUow)resolver.GetService(typeof(IExpensesUow));
// Get the Euro currency
var euro = uow.Currencies.GetAll().First(c => c.Code == "EUR");
// Create the employee record
var employee = new Model.Employee { BaseCurrency = euro, UserId = userName };
uow.Employees.Add(employee);
uow.Commit();
// Create the blank report, all new expenses are in this report
var report = new Model.ExpenseReport { Employee = employee};
uow.ExpenseReports.Add(report);
uow.Commit();
byte[] image = null;
using (var ms = new MemoryStream())
{
Properties.Resources.defaultExpense.Save(ms, ImageFormat.Jpeg);
image = ms.ToArray();
}
var imageType = "data:image/jpeg;base64";
// todo: remove this in production
report.Expenses.Add(new Model.Expense
{
Image = image,
ImageType = imageType,
Currency = euro, Date = DateTime.Now.Date,
Description = "Taxi DGL => Buckingham",
Type = uow.ExpenseTypes.GetById(1),
Amount = 56
});
report.Expenses.Add(new Model.Expense
{
Image = image,
ImageType = imageType,
Currency = euro,
Date = DateTime.Now.Date,
Description = "Lunch",
Type = uow.ExpenseTypes.GetById(2),
Amount = 12.4
});
report.Expenses.Add(new Model.Expense
{
Currency = euro,
Date = DateTime.Now.Date,
Description = "NoImage",
Type = uow.ExpenseTypes.GetById(2),
Amount = 7.4
});
uow.Commit();
}
public IList<Model.InvoiceXODetail> Select(Model.Customer customer1, Model.Customer customer2, DateTime startDate, DateTime endDate, DateTime yjrq1, DateTime yjrq2, Model.Employee employee1, Model.Employee employee2, string xoid1, string xoid2, string cusxoidkey, Model.Product product, Model.Product product2, bool isclose, bool mpsIsClose, int orderColumn, int orderType, bool detailFlag)
{
return accessor.Select(customer1, customer2, startDate, endDate, yjrq1, yjrq2, employee1, employee2, xoid1, xoid2, cusxoidkey, product, product2, isclose, mpsIsClose, orderColumn, orderType, detailFlag);
}
// PUT: api/Employee/5 Update Function
public string Put(int id, HttpRequestMessage value)
{
try
{
Model.Employee emp = new Model.Employee();
emp = (from p in db.Employees
where p.Employee_ID == id
select p).First();
string message = HttpContext.Current.Server.UrlDecode(value.Content.ReadAsStringAsync().Result).Substring(5);
JObject json = JObject.Parse(message);
JObject empDetails = (JObject)json["employee"];
JArray machines = (JArray)json["machines"];
JArray labour = (JArray)json["manual_labour"];
emp.Name = (string)empDetails["name"];
emp.Surname = (string)empDetails["surname"];
emp.Employee_Type_ID = (int)empDetails["type"];
emp.Gender_ID = (string)empDetails["gender"];
emp.Email = (string)empDetails["email"];
emp.Contact_Number = (string)empDetails["contact_number"];
emp.Username = (string)empDetails["username"];
emp.Employee_Status = true;
emp.ID_Number = (string)empDetails["ID"];
if ((string)empDetails["img"] != "")
{
db.Employee_Photo.RemoveRange(db.Employee_Photo.Where(x => x.Employee_ID == id));
string img = (string)empDetails["img"];
saveImage(img, Convert.ToString(id));
}
if ((string)empDetails["password"] != "")
{
string salt = GetSalt(6);
string password = (string)empDetails["password"];
string passwordHashed = sha256(password + salt);
emp.Salt = salt;
emp.Password = passwordHashed;
}
string errorString = "false|";
bool error = false;
if ((from t in db.Employees
where t.ID_Number == emp.ID_Number && t.Employee_ID != id
select t).Count() != 0)
{
error = true;
errorString += "The Employee ID Number entered already exists on the system. ";
}
if ((from t in db.Employees
where t.Email == emp.Email && t.Employee_ID != id
select t).Count() != 0)
{
error = true;
errorString += "The Employee Email entered already exists on the system. ";
}
if ((from t in db.Employees
where t.Contact_Number == emp.Contact_Number && t.Employee_ID != id
select t).Count() != 0)
{
error = true;
errorString += "The Employee Contact Number entered already exists on the system. ";
}
if ((from t in db.Employees
where t.Username == emp.Username && t.Employee_ID != id
select t).Count() != 0)
{
error = true;
errorString += "The Employee Username entered already exists on the system. ";
}
if (error)
{
return(errorString);
}
emp.Machines.Clear();
foreach (int machine in machines)
{
Machine query = (from p in db.Machines
where p.Machine_ID == machine
select p).First();
emp.Machines.Add(query);
}
emp.Manual_Labour_Type.Clear();
foreach (int man_lab in labour)
{
Manual_Labour_Type query = (from p in db.Manual_Labour_Type
where p.Manual_Labour_Type_ID == man_lab
select p).First();
emp.Manual_Labour_Type.Add(query);
}
db.SaveChanges();
return("true|Employee successfully updated.");
}
catch (Exception e)
{
ExceptionLog.LogException(e, "EmployeeController PUT");
return("false|An error has occured updating the Employee on the system.");
}
}
//更新基础设置
public int UpMonthSalFromClockFrm(Model.Employee emp, DateTime UpdateTime)
{
return(accessor.UpMonthSalFromClockFrm(emp, UpdateTime));
}
public IList <Book.Model.RoleAuditing> GetByDate(DateTime startDate, DateTime endDate, Model.Department department, Model.Employee emp0, Model.Operators oper, bool isNoAudit, bool isHasAudit)
{
SqlParameter[] parames = { new SqlParameter("@startdate", DbType.DateTime), new SqlParameter("@enddate", DbType.DateTime), new SqlParameter("@OperatorsId", SqlDbType.VarChar, 50), new SqlParameter("@employee0id", SqlDbType.VarChar, 50) };
parames[0].Value = startDate;
parames[1].Value = endDate;
parames[2].Value = oper.OperatorsId;
if (emp0 == null)
{
parames[3].Value = DBNull.Value;
}
else
{
parames[3].Value = emp0.EmployeeId;
}
StringBuilder sql = new StringBuilder();
sql.Append("SELECT r.*,(select e.EmployeeName from Employee e where e.EmployeeId = r.Employee0Id ) as Employee0Name,(select e.EmployeeName from Employee e where e.EmployeeId = r.Employee1Id ) as Employee1Name from RoleAuditing r where inserttime between @startdate and @enddate");
if (emp0 != null)
{
sql.Append(" and employee0id=@employee0id");
}
if (oper != null)
{
sql.Append(" and (NextAuditRoleId IN(select RoleId from OperationRole where OperatorsId = @OperatorsId and IsHold=1) or NextAuditRoleId is null)");
}
if (!isNoAudit || !isHasAudit)
{
if (isNoAudit)
{
sql.Append(" and auditstate<>3 ");
}
if (isHasAudit)
{
sql.Append(" and auditstate=3 ");
}
}
sql.Append(" order by inserttime desc ");
return(this.DataReaderBind <Model.RoleAuditing>(sql.ToString(), parames, CommandType.Text));
}
public IList <Book.Model.InvoiceXSDetail> Select(DateTime startDate, DateTime endDate, Model.Employee employee, Model.Customer customer, Model.Depot depot)
{
return(accessor.Select(startDate, endDate, employee, customer, depot));
}
public DataSet SelectCuiShou(Model.Customer customer1, Model.Customer customer2, Model.Employee employee1, Model.Employee employee2, DateTime ysdate)
{
StringBuilder sql = new StringBuilder();
if (customer1 != null && customer2 != null)
{
sql.Append(" and a.CustomerId in( select CustomerId from Customer where id between '" + customer1.Id + "' and '" + customer2.Id + "') and YSDate < '" + ysdate.Date.ToString("yyyyMMdd") + "' ");
}
if (employee1 != null && employee2 != null)
{
sql.Append(" and Employee1Id in( select EmployeeId from Employee where idno between '" + employee1.IDNo + "' and '" + employee2.IDNo + "') ");
}
return(SQLDB.DbHelperSQL.Query("select YSDate ,isnull(datediff(d, YSDate,getdate()),0) as CQDays,AcInvoiceXOBillDate as InvoiceDate,AcInvoiceXOBillId as InvoiceId,'Sales invoice' as SourceName,ZongMoney as Total,mHeXiaoJingE as HasMoney,InvoiceAllowanceTotal as ZheRang,NoHeXiaoTotal NoHeXiao,(select CustomerShortName from Customer c where c.CustomerId=a.CustomerId) as CusomerName from AcInvoiceXOBill a where isnull(datediff(d, YSDate,getdate()),0)>0 " + sql.ToString()));
}
public static void EndSession()
{
SessionController.emp = null;
SessionController.usr = null;
}
public static void StartSession(Model.Employee emp, Model.User usr)
{
SessionController.emp = emp;
SessionController.usr = usr;
Permissions = usr.Permissions.ToList();
}
public DataSet SelectMayShou(Model.Supplier supplier1, Model.Supplier supplier2, Model.Employee employee1, Model.Employee employee2, DateTime startDate, DateTime endDate)
{
return(accessor.SelectMayShou(supplier1, supplier2, employee1, employee2, startDate, endDate));
}
public DataTable SelectDateRangAndWhereToTable(Model.Customer customerStart, Model.Customer customerEnd, DateTime?dateStart, DateTime?dateEnd, DateTime yjrq1, DateTime yjrq2, string cusxoid, Model.Product product1, Model.Product product2, string invoicexoid1, string invoicexoid2, string FreightedCompanyId, string ConveyanceMethodId, Model.Employee startEmp, Model.Employee endEmp, string product_Id, string productCategoryId)
{
StringBuilder sql = new StringBuilder(
@"select xs.InvoiceId,xs.InvoiceDate,xs.InvoiceNote,c.CustomerShortName as Customer,e.EmployeeName as Employee0,dp.DepotName as Depot,xo.CustomerInvoiceXOId,isnull(xsd.InvoiceXSDetailPrice*xsd.InvoiceXSDetailQuantity,0)*(case when Donatetowards=1 then 0 else 1 end) as XOMoney,ISNULL(xsd.InvoiceXSDetailPrice*xsd.InvoiceXSDetailQuantity*(case when ExchangeRate = 0 then 1 when ExchangeRate is null then 1 else ExchangeRate end),0)*(case when Donatetowards=1 then 0 else 1 end) as XSMoney from InvoiceXSDetail xsd left join InvoiceXS xs on xs.InvoiceId=xsd.InvoiceId
left join InvoiceXO xo on xo.InvoiceId=xsd.InvoiceXOId
left join Depot dp on dp.DepotId=xs.DepotId
left join Employee e on e.EmployeeId=xs.Employee0Id
left join Customer c on c.CustomerId=xs.CustomerId");
sql.Append(" where (xs.InvoiceStatus<>2 or xs.InvoiceStatus is null) and (xs.InvoiceDate between '" + dateStart.Value.ToString("yyyy-MM-dd") + "' and '" + dateEnd.Value.ToString("yyyy-MM-dd HH:mm:ss") + "')");
if (customerStart != null && customerEnd != null)
{
sql.Append(" and xs.customerid in (select CustomerId from Customer where Id between '" + customerStart.Id + "' and '" + customerEnd.Id + "')");
}
if (yjrq1 != global::Helper.DateTimeParse.NullDate && yjrq2 != global::Helper.DateTimeParse.EndDate)
{
sql.Append(" and xsd.InvoiceXOId in (select InvoiceId from InvoiceXO where InvoiceYjrq between '" + yjrq1.ToString("yyyy-MM-dd") + "' and '" + yjrq2.ToString("yyyy-MM-dd HH:mm:ss") + "')");
}
if (!string.IsNullOrEmpty(cusxoid))
{
sql.Append(" and xsd.InvoiceXOId in(select invoiceid from invoicexo where CustomerInvoiceXOId = '" + cusxoid + "' )");
}
if (!string.IsNullOrEmpty(invoicexoid1) && !string.IsNullOrEmpty(invoicexoid2))
{
sql.Append(" and xs.InvoiceId between '" + invoicexoid1 + "' and '" + invoicexoid2 + "'");
}
if (product1 != null && product2 != null)
{
sql.Append(" and xsd.productid in (select ProductId from Product where Id between '" + product1.Id + "' and '" + product2.Id + "') ");
}
if (startEmp != null && endEmp != null)
{
sql.Append(" And xs.Employee0Id in (select EmployeeId from Employee where IDNo between '" + startEmp.IDNo + "' and '" + endEmp.IDNo + "')");
}
if (!string.IsNullOrEmpty(FreightedCompanyId))
{
sql.Append(" and xs.TransportCompany='" + FreightedCompanyId + "'");
}
if (!string.IsNullOrEmpty(ConveyanceMethodId))
{
sql.Append(" and xs.ConveyanceMethodId='" + ConveyanceMethodId + "'");
}
if (!string.IsNullOrEmpty(product_Id))
{
sql.Append(" and xsd.ProductId in (select ProductId from Product where Id='" + product_Id + "')");
}
if (!string.IsNullOrEmpty(productCategoryId))
{
sql.Append(" and xsd.productId in (select ProductId from Product where ProductCategoryId='" + productCategoryId + "') ");
}
sql.Append(" order by xs.InvoiceId");
SqlDataAdapter sda = new SqlDataAdapter(sql.ToString(), sqlmapper.DataSource.ConnectionString);
DataTable dt = new DataTable();
sda.Fill(dt);
return(dt);
}
public IList <Model.InvoiceCODetail> Select(string costartid, string coendid, Model.Supplier SupplierStart, Model.Supplier SupplierEnd, DateTime?dateStart, DateTime?dateEnd, Model.Product productStart, Model.Product productEnd, string cusxoid, DateTime dateJHStart, DateTime dateJHEnd, int?invoiceFlag, Model.Employee empStart, Model.Employee empEnd)
{
return(accessor.Select(costartid, coendid, SupplierStart, SupplierEnd, dateStart, dateEnd, productStart, productEnd, cusxoid, dateJHStart, dateJHEnd, invoiceFlag, empStart, empEnd));
}
public bool mHasRowsBefore(Model.Employee emp)
{
return(accessor.mHasRowsBefore(emp));
}
public DataSet SelectMayShou(Model.Supplier supplier1, Model.Supplier supplier2, Model.Employee employee1, Model.Employee employee2, DateTime startDate, DateTime endDate)
{
StringBuilder sql = new StringBuilder();
sql.Append("select AcInvoiceCOBillDate as InvoiceDate,AcInvoiceCOBillId as InvoiceId,'Purchase invoice' as SourceName,ZongMoney as Total,mHeXiaoJingE as HasMoney,NoHeXiaoTotal NoHeXiao,(select EmployeeName from Employee e where e.Employeeid=a.Employee1Id) as Employee1Name,(select SupplierShortName from Supplier S where S.SupplierId=a.SupplierId) as SupplierName,isnull(HeJiMoney,0) AS JinE,isnull(TaxRateMoney,0) AS ShuiE,isnull(ZongMoney,0) AS Total from AcInvoiceCOBill a where 1=1 ");
sql.Append(" AND AcInvoiceCOBillDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "'");
if (supplier1 != null && supplier2 != null)
{
sql.Append(" and SupplierId in( select SupplierId from Supplier where id between '" + supplier1.Id + "' and '" + supplier2.Id + "') ");
}
if (employee1 != null && employee2 != null)
{
sql.Append(" and Employee0Id in( select EmployeeId from Employee where idno between '" + employee1.IDNo + "' and '" + employee2.IDNo + "') ");
}
sql.Append(" UNION ");
sql.Append("SELECT InvoiceDate,InvoiceId,'Purchase order' AS SourceName,InvoiceTotal AS Total,'0' AS HasMoney,'0' AS NoHeXiao,(select EmployeeName from Employee e where e.Employeeid=ic.Employee1Id) as Employee1Name,(select SupplierShortName from Supplier S where S.SupplierId=ic.SupplierId) as SupplierName,isnull(InvoiceHeji,0) AS JinE,isnull(InvoiceTax,0) AS ShuiE,isnull(InvoiceTotal,0) AS Total FROM InvoiceCO ic WHERE 1 = 1");
sql.Append(" AND InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "'");
if (supplier1 != null && supplier2 != null)
{
sql.Append(" and SupplierId in( select SupplierId from Supplier where id between '" + supplier1.Id + "' and '" + supplier2.Id + "') ");
}
if (employee1 != null && employee2 != null)
{
sql.Append(" and Employee0Id in( select EmployeeId from Employee where idno between '" + employee1.IDNo + "' and '" + employee2.IDNo + "') ");
}
//sql.Append("SELECT AcOtherShouldPaymentDate AS InvoiceDate,AcOtherShouldPaymentId AS InvoiceId,'其它應付款' as SourceName,InvoiceHeji as Total,mHeXiaoJingE as HasMoney,NoHeXiaoTotal as NoHeXiao,(SELECT e.EmployeeName FROM Employee e WHERE e.EmployeeId = asp.Employee1Id) AS Employee1Name,(SELECT s.SupplierShortName FROM Supplier s WHERE s.SupplierId = asp.SupplierId) AS SupplierName FROM AcOtherShouldPayment asp WHERE 1=1 ");
//sql.Append(" AND AcOtherShouldPaymentDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "'");
//if (supplier1 != null && supplier2 != null)
// sql.Append(" and SupplierId in (select SupplierId from Supplier where id between '" + supplier1.Id + "' and '" + supplier2.Id + "') ");
//if (employee1 != null && employee2 != null)
// sql.Append(" and Employee0Id in (select EmployeeId from Employee where idno between '" + employee1.IDNo + "' and '" + employee2.IDNo + "') ");
sql.Append(" ORDER BY InvoiceDate");
return(SQLDB.DbHelperSQL.Query("" + sql.ToString()));
}
public IList <Book.Model.InvoiceXO> SelectByYJRQCustomEmpCusXOId(Model.Customer customer1, Model.Customer customer2, DateTime startDate, DateTime endDate, DateTime yjrq1, DateTime yjrq2, Model.Employee employee1, Model.Employee employee2, string xoid1, string xoid2, string cusxoidkey, Model.Product product, Model.Product product2, bool isclose, bool mpsIsClose, bool isForeigntrade, string product_Id)
{
StringBuilder str = new StringBuilder();
//if (customer1 != null && customer2 != null)
// str.Append(" and xocustomerId in (select CustomerId from customer where id between '" + customer1.Id + "' and '" + customer2.Id + "') ");
if (customer1 != null)
{
str.Append(" and CustomerId='" + customer1.CustomerId + "'");
}
if (customer2 != null)
{
str.Append(" and xocustomerId='" + customer2.CustomerId + "'");
}
if (employee1 != null && employee2 != null)
{
str.Append(" and Employee0Id in(select EmployeeId from Employee where idno between '" + employee1.IDNo + "' and '" + employee2.IDNo + "') ");
}
if (!string.IsNullOrEmpty(xoid1) && !string.IsNullOrEmpty(xoid2))
{
str.Append(" and InvoiceId between '" + xoid1 + "' and '" + xoid2 + "' ");
}
if (!string.IsNullOrEmpty(cusxoidkey))
{
str.Append(" and CustomerInvoiceXOId like '%" + cusxoidkey + "%' ");
}
if (product != null && product2 != null)
{
str.Append(" and InvoiceId in (select invoiceid from invoicexodetail where productid in(select productid from product where productname between '" + product.ProductName + "' and '" + product2.ProductName + "')) ");
}
if (!string.IsNullOrEmpty(product_Id))
{
str.Append(" and InvoiceId in (select invoiceid from invoicexodetail where productid in(select productid from product where id= '" + product_Id + "'))");
}
if (isclose) //true 时只查询未结案
{
str.Append(" and IsClose=0");
}
if (mpsIsClose) //true 只查询未排完单
{
str.Append(" and InvoiceMPSState<>2");
}
if (isForeigntrade) //true 时只查询外销订单
{
str.Append(" and IsForeigntrade=1");
}
str.Append(" ORDER BY InvoiceId ");
Hashtable ht = new Hashtable();
ht.Add("startDate", startDate);
ht.Add("endDate", endDate);
ht.Add("yjrq1", yjrq1);
ht.Add("yjrq2", yjrq2);
ht.Add("sql", str.ToString());
return(sqlmapper.QueryForList <Book.Model.InvoiceXO>("InvoiceXO.select_byYJRQCustomEmp", ht));
}
public DataSet SelectMayShou(Model.Customer customer1, Model.Customer customer2, Model.Employee employee1, Model.Employee employee2, DateTime startDate, DateTime endDate)
{
StringBuilder sql = new StringBuilder();
sql.Append(" AcInvoiceXOBillDate>= '" + startDate.ToString("yyyy-MM-dd") + "' and AcInvoiceXOBillDate<'" + endDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "'");
if (customer1 != null && customer2 != null)
{
sql.Append(" and CustomerId in( select CustomerId from Customer where id between '" + customer1.Id + "' and '" + customer2.Id + "') ");
}
if (employee1 != null && employee2 != null)
{
sql.Append(" and Employee0Id in( select EmployeeId from Employee where idno between '" + employee1.IDNo + "' and '" + employee2.IDNo + "') ");
}
sql.Append("order by InvoiceDate");
return(SQLDB.DbHelperSQL.Query("select AcInvoiceXOBillDate as InvoiceDate,AcInvoiceXOBillId as InvoiceId,'Sales invoice' as SourceName,ZongMoney as Total,mHeXiaoJingE as HasMoney,NoHeXiaoTotal NoHeXiao,(select EmployeeName from Employee e where e.Employeeid=a.Employee1Id) as Employee1Name,(select CustomerShortName from Customer c where c.CustomerId=a.CustomerId) as CusomerName from AcInvoiceXOBill a where " + sql.ToString()));
}
public IList <Book.Model.InvoiceXO> SelectByYJRQCustomEmpCusXOId(Model.Customer customer1, Model.Customer customer2, DateTime startDate, DateTime endDate, DateTime yjrq1, DateTime yjrq2, Model.Employee employee1, Model.Employee employee2, string xoid1, string xoid2, string cusxoidkey, Model.Product product, Model.Product product2, bool isclose, bool mpsIsClose, bool isForeigntrade)
{
return(accessor.SelectByYJRQCustomEmpCusXOId(customer1, customer2, startDate, endDate, yjrq1, yjrq2, employee1, employee2, xoid1, xoid2, cusxoidkey, product, product2, isclose, mpsIsClose, isForeigntrade));
}
public DataSet SelectCuiShou(Model.Customer customer1, Model.Customer customer2, Model.Employee employee1, Model.Employee employee2, DateTime ysdate)
{
return(accessor.SelectCuiShou(customer1, customer2, employee1, employee2, ysdate));
}
public IList <Model.DepotIn> SelectByDateAndOther(DateTime startDate, DateTime endDate, Model.Product product, string depotInId, Model.Employee employee, Model.Employee employee0, string depotId, Model.Supplier supplier)
{
return(accessor.SelectByDateAndOther(startDate, endDate, product, depotInId, employee, employee0, depotId, supplier));
}
public DataSet SelectMayShou(Model.Customer customer1, Model.Customer customer2, Model.Employee employee1, Model.Employee employee2, DateTime startDate, DateTime endDate)
{
return(accessor.SelectMayShou(customer1, customer2, employee1, employee2, startDate, endDate));
}
public Model.MonthlySalary GetByeEmpIdMonth(Model.Employee employee, int year, int month)
{
return(accessor.GetByeEmpIdMonth(employee, year, month));
}
public Model.Employee mGetNext(Model.Employee emp)
{
return(accessor.mGetNext(emp));
}
public IList <Model.OverTime> SelectByEmployeeAndMonth(Model.Employee employee, int year, int month)
{
return(accessor.SelectByEmployeeAndMonth(employee, year, month));
}
public Model.Employee mGetPrev(Model.Employee emp)
{
return(accessor.mGetPrev(emp));
}
public IList <Book.Model.InvoiceXSDetail> Select(DateTime startDate, DateTime endDate, Model.Employee employee, Model.Customer customer, Model.Depot depot)
{
Hashtable pars = new Hashtable();
pars.Add("startdate", startDate);
pars.Add("enddate", endDate);
pars.Add("customerid", customer == null ? null : customer.CustomerId);
pars.Add("employeeId", employee == null ? null : employee.EmployeeId);
pars.Add("depotid", depot == null ? null : depot.DepotId);
return(sqlmapper.QueryForList <Model.InvoiceXSDetail>("InvoiceXSDetail.selectByCustomEmpDepetQuJian", pars));
}
public decimal SelectFeeSum(Model.Employee employee, int year, int month)
{
return(accessor.SelectFeeSum(employee, year, month));
}
private void btn_UpdateClockRecord_Click(object sender, EventArgs e)
{
if (this.date_Start.EditValue == null || this.date_End.EditValue == null)
{
MessageBox.Show("日期區間不完整", "提示", MessageBoxButtons.OK);
return;
}
DateTime dateStart = this.date_Start.DateTime.Date;
DateTime dateEnd = this.date_End.DateTime.Date.AddDays(1).AddSeconds(-1);
DataTable dt = new DataTable();
try
{
string fileName = System.Configuration.ConfigurationManager.AppSettings["AccessPath"];
string conn = string.Format("Provider=Microsoft.ACE.OLEDB.15.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password=!@Stan888@!", fileName);
OleDbConnection oconn = new OleDbConnection(conn);
string sql = string.Format("select c.CheckTime,u.Name,u.SSN from CHECKINOUT c left join USERINFO u on c.USERID=u.USERID where c.CheckTime between #{0}# and #{1}# order by c.CheckTime ", dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd HH:mm:ss"));
OleDbDataAdapter oda = new OleDbDataAdapter(sql, oconn);
oda.Fill(dt);
}
catch (Exception ex)
{
if (ex.Message.Contains("Microsoft.ACE.OLEDB.15.0"))
{
try
{
string fileNameNew = System.Configuration.ConfigurationManager.AppSettings["AccessPath"];
string connNew = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Jet OLEDB:Database Password=!@Stan888@!", fileNameNew);
OleDbConnection oconnNew = new OleDbConnection(connNew);
string sqlNew = string.Format("select c.CheckTime,u.Name,u.SSN from CHECKINOUT c left join USERINFO u on c.USERID=u.USERID where c.CheckTime between #{0}# and #{1}# order by c.CheckTime ", dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd HH:mm:ss"));
OleDbDataAdapter odaNew = new OleDbDataAdapter(sqlNew, oconnNew);
odaNew.Fill(dt);
}
catch (Exception ex2)
{
MessageBox.Show(ex2.Message, "提示", MessageBoxButtons.OK);
return;
}
}
else
{
MessageBox.Show("導入只支持64位操作系統配合64位ERP", "提示", MessageBoxButtons.OK);
return;
}
}
//int erpClockCount = clockDataManager.CountClockByDateRange(dateStart, dateEnd);
//if (dt.Rows.Count <= erpClockCount)
//{
// MessageBox.Show("已經導入過打卡資料", "提示", MessageBoxButtons.OK);
// return;
//}
try
{
BL.V.BeginTransaction();
clockDataManager.DeleteByDateRange(dateStart, dateEnd);
foreach (DataRow dr in dt.Rows)
{
string date = dr[0].ToString();
string name = dr[1].ToString().Trim();
string id = dr[2].ToString();
if (name.Contains("\0"))
{
name = name.Substring(0, name.IndexOf('\0'));
}
Model.Employee emp = this.employeeManager.SelectIdByNameAnId(name, id);
if (emp == null)
{
emp = this.employeeManager.GetbyIdNo(id);
}
if (emp == null)
{
continue;
}
Model.ClockData clockdata = new Book.Model.ClockData();
clockdata.ClockDataId = Guid.NewGuid().ToString();
clockdata.EmployeeId = emp.EmployeeId;
clockdata.InsertTime = DateTime.Now;
clockdata.Empclockdate = DateTime.Parse(date).Date;
clockdata.Clocktime = DateTime.Parse(date);
clockdata.CardNo = emp.CardNo;
clockDataManager.Insert(clockdata);
}
BL.V.CommitTransaction();
MessageBox.Show("導入完成", "提示", MessageBoxButtons.OK);
}
catch (Exception ex)
{
BL.V.RollbackTransaction();
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);
}
}
public void CompleteTheObject(Model.Employee theObjectToComplete)
{
_BasicPresenter.CompleteTheObject(theObjectToComplete);
}
public List<Model.Employee> GetAll()
{
List<Model.Employee> empList = new List<Model.Employee>();
SqlConnection con = new SqlConnection("Data Source=TRAINING12;Initial Catalog=Employee;User ID=sa;Password=test123!@#");
con.Open();
SqlCommand cmd = new SqlCommand("GetAll ", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader empReader = cmd.ExecuteReader();
while (empReader.Read())
{
Model.Employee emp = new Model.Employee();
emp.Id = empReader[0].ToString();
emp.FirstName = empReader[1].ToString();
emp.LastName = empReader[2].ToString();
emp.Title = empReader[3].ToString();
emp.Email = empReader[4].ToString();
emp.Phone = empReader[5].ToString();
emp.JoiningDate = DateTime.Parse(empReader[6].ToString());
empList.Add(emp);
}
con.Close();
return empList;
}
public IList <Model.InvoiceXS> Select(DateTime start, DateTime end, Model.Employee employee)
{
return(accessor.Select(start, end, employee));
}
public DataTable SelectDateRangAndWhereToTable(Model.Customer customerStart, Model.Customer customerEnd, DateTime?dateStart, DateTime?dateEnd, DateTime yjrq1, DateTime yjrq2, string cusxoid, Model.Product product1, Model.Product product2, string invoicexoid1, string invoicexoid2, string FreightedCompanyId, string ConveyanceMethodId, Model.Employee startEmp, Model.Employee endEmp, string product_Id, string productCategoryId)
{
return(accessor.SelectDateRangAndWhereToTable(customerStart, customerEnd, dateStart, dateEnd, yjrq1, yjrq2, cusxoid, product1, product2, invoicexoid1, invoicexoid2, FreightedCompanyId, ConveyanceMethodId, startEmp, endEmp, product_Id, productCategoryId));
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string userNo = txtUserName.Text.Trim();
string userPwd = txtUserPwd.Text.Trim();
string code = txtCode.Text.Trim();
if (userNo.Equals("") || userPwd.Equals(""))
{
lblTip.Visible = true;
lblTip.Text = "请输入用户名或密码";
return;
}
if (code.Equals(""))
{
lblTip.Visible = true;
lblTip.Text = "请输入验证码";
return;
}
if (Session[ConstKeys.SESSION_CODE] == null)
{
lblTip.Visible = true;
lblTip.Text = "系统找不到验证码";
return;
}
if (code.ToLower() != Session[ConstKeys.SESSION_CODE].ToString().ToLower())
{
lblTip.Visible = true;
lblTip.Text = "验证码输入不正确";
return;
}
Model.Employee modelemp = new Model.Employee();
BllEmployee bllemp=new BllEmployee ();
modelemp = bllemp.GetModel(userNo);
if (modelemp != null)
{
if (modelemp.Pass == JSOA.Common.Encrypt.MD5(userPwd, 32))
{
Session[ConstKeys.SESSION_ADMIN_INFO] = modelemp;
Session.Timeout = 45;
//登陆日志
//写入Cookies
if (cbRememberId.Checked)
{
Utils.WriteCookie("RememberName", modelemp.No, 14400);
}
else
{
Utils.WriteCookie("RememberName", modelemp.No, -14400);
}
Utils.WriteCookie("AdminName", "jsoa", modelemp.Name);
Utils.WriteCookie("AdminPwd", "jsoa", modelemp.Pass);
Utils.WriteCookie("AdminNo", "jsoa", modelemp.No);
Response.Redirect("Default.aspx");
return;
}
else
{
lblTip.Visible = true;
lblTip.Text = "密码有误";
return;
}
}
else
{
lblTip.Visible = true;
lblTip.Text = "用户名不存在";
return;
}
}
