/// <summary>
/// 获得数据列表
/// </summary>
public List<Model.Customer> DataTableToList(DataTable dt)
{
List<Model.Customer> modelList = new List<Model.Customer>();
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
Model.Customer model;
for (int n = 0; n < rowsCount; n++)
{
model = new Model.Customer();
if (dt.Rows[n]["Id"].ToString() != "")
{
model.Id = int.Parse(dt.Rows[n]["Id"].ToString());
}
model.ShortName = dt.Rows[n]["ShortName"].ToString();
model.FullName = dt.Rows[n]["FullName"].ToString();
model.TypeName = dt.Rows[n]["TypeName"].ToString();
model.Category = dt.Rows[n]["Category"].ToString();
model.Code = dt.Rows[n]["Code"].ToString();
model.LinkMan = dt.Rows[n]["LinkMan"].ToString();
model.LinkTel = dt.Rows[n]["LinkTel"].ToString();
model.MobileNumber = dt.Rows[n]["MobileNumber"].ToString();
model.LinkAddress = dt.Rows[n]["LinkAddress"].ToString();
model.TaxRegistrationNumber = dt.Rows[n]["TaxRegistrationNumber"].ToString();
model.Remarks = dt.Rows[n]["Remarks"].ToString();
modelList.Add(model);
}
}
return modelList;
}
protected void btnRegister_Click(object sender, EventArgs e)
{
CustomerRepo customerrepo = new CustomerRepo();
lbMessage.Text = "";
if (customerrepo.DoesUsernameExist(txtUsername.Text))
{
lbMessage.Text = "Username already exists!";
lbMessage.ForeColor = System.Drawing.Color.Red;
txtUsername.Text = "";
return;
}
if (customerrepo.DoesEmailExist(txtEmail.Text))
{
lbMessage.Text = "Email already exists!";
lbMessage.ForeColor = System.Drawing.Color.Red;
txtEmail.Text = "";
return;
}
Model.Customer customer = new Model.Customer();
customer.FirstName = txtFirstName.Text;
customer.LastName = txtLastName.Text;
customer.Username = txtUsername.Text;
customer.Password = Security.Encrypt(ConfigurationManager.AppSettings["KeyCustomer"], txtPassword.Text);
customer.Key = ConfigurationManager.AppSettings["KeyCustomer"];
customer.Email = txtEmail.Text;
customer.Phone = txtPhone.Text;
customer.DateOfBirth = ToSQL.SQLToDateTimeNull(txtDateOfBirth.Text);
customer.Gender = rdbtnGender.SelectedIndex == 0 ? true : false;
customer.DateCreated = DateTime.Now;
int i = customerrepo.CreateCustomer(customer);
Session["Customer"] = customer;
Response.Redirect("CustomerInfo.aspx");
}
public async Task GetInfo(string login, string password)
{
AutoMapper.Mapper.CreateMap<Customer, Model.Customer>();
AutoMapper.Mapper.CreateMap<Model.Customer, Customer>();
var converted = await _customerAuthenticationService.GetInfoAboutCustomerAsync(login, password);
ActualLoggedUser = AutoMapper.Mapper.Map<Model.Customer>(converted);
Messenger.Default.Send(ActualLoggedUser);
}
public CustomerViewModel(Model.Customer customer)
{
_customer = customer;
var bitmapImage = new BitmapImage();
if (_customer.Image != null)
{
var stream = new MemoryStream(_customer.Image);
bitmapImage.SetSource(stream);
}
BitmapImage = bitmapImage;
}
public void RaisePostBackEvent(string eventArgument)
{
if (eventArgument == "登录")
{
CustomerEmail = this.Customertx.Text;
Password = this.Passwordtx.Text;
BLL.Customer bllcustomer = new BLL.Customer();
List<Model.Customer> customerlist = bllcustomer.GetModelList("1=1");
Model.Customer customer = new Model.Customer();
customer.CustomerName = "123";
}
}
/// <summary>
/// 用户注册
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnRegister_Click(object sender, EventArgs e)
{
string phone = this.phone.Text.Trim();
string email = this.email.Text.Trim();
string name = this.name.Text.Trim();
string cardno = this.cardno.Text.Trim();
string password = this.password.Text.Trim();
string repassword = this.repassword.Text.Trim();
string checkcode = this.checkcode.Text.Trim();
string ischeck = Session["UserCode"].ToString();
if (password.Equals(repassword) == false)
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('两次密码输入不一致');</script>");
return;
}
if (ischeck.Equals(checkcode.ToUpper()) == false)
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('验证码输入错误');</script>");
return;
}
try
{
using (ZLzuche.Model.ZLzucheEntities entity = new Model.ZLzucheEntities())
{
ZLzuche.Model.Customer customer = new Model.Customer();
customer.LoginID = name;
customer.Phone = phone;
customer.Email = email;
customer.CustomerName = name;
customer.PapersNumber = cardno;
customer.Password = Common.Md5.GetMd5Hash(password);
customer.RegisterTime = DateTime.Now;
entity.AddToCustomer(customer);
entity.SaveChanges();
}
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('用户注册失败');</script>");
return;
}
}
private bool DoAdd()
{
bool result = false;
Model.Customer model = new Model.Customer();
BLL.Customer bll = new BLL.Customer();
model.Code = txtCode.Text;
model.Name = txtName.Text;
model.LinkMan = txtLinkMan.Text;
model.LinkTel = txtLinkTel.Text;
model.LinkAddress = txtLinkAddress.Text;
model.Email = txtEmail.Text;
model.Fax = txtFax.Text;
model.Status = Convert.ToInt32(rblStatus.SelectedValue);
model.Remark = txtRemark.Text;
if (bll.Add(model))
{
AddAdminLog(DTEnums.ActionEnum.Add.ToString(), "添加客户:" + model.Name); //记录日志
result = true;
}
return result;
}
public IEither <IEnumerable <ICommandError>, IEnumerable <IEvent> > Handle(
Model.Customer aggregateRoot,
Commands.SurveyUpdateCommand command)
{
return(CommandResult.Handled(new Events.SurveyUpdatedEvent(command.Data)));
}
public IList <Model.PCImpactCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Model.Product product, Model.Customer customer, string CusXOId)
{
return(accessor.SelectByDateRage(StartDate, EndDate, product, customer, CusXOId));
}
public ChooseProductForm(Model.Customer customer)
: this()
{
this._customer = customer;
}
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 IList <Model.Product> Select(Model.Customer customer)
{
return(sqlmapper.QueryForList <Model.Product>("Product.SelectByCustomer", customer == null ? null : customer.CustomerId));
}
/// <summary>
/// 生产入库选择加工单
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="customer"></param>
/// <param name="cusxoid"></param>
/// <param name="product"></param>
/// <param name="PronoteHeaderIdStart"></param>
/// <param name="PronoteHeaderIdEnd"></param>
/// <param name="sourcetype"></param>
/// <param name="workhouseIndepot"></param>
/// <param name="jiean"></param>
/// <returns></returns>
public IList <Book.Model.PronoteHeader> GetByDate(DateTime startDate, DateTime endDate, Model.Customer customer, string cusxoid, Model.Product product, string PronoteHeaderIdStart, string PronoteHeaderIdEnd, string workhouseIndepot, bool jiean, string proNameKey, string proCusNameKey, string pronoteHeaderIdKey, bool sourcetype0, bool sourcetype4, bool sourcetype5, bool sourcetype7, DateTime JiaohuoDateStart, DateTime JiaohuoDateEnd, string MachineName)
{
SqlParameter[] parames = { new SqlParameter("@startdate", DbType.DateTime), new SqlParameter("@enddate", DbType.DateTime), new SqlParameter("@xocustomerId", DbType.String), new SqlParameter("@CustomerInvoiceXOId", DbType.String), new SqlParameter("@productid", DbType.String) };
parames[0].Value = startDate;
parames[1].Value = endDate;
if (customer != null)
{
parames[2].Value = customer.CustomerId;
}
else
{
parames[2].Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(cusxoid))
{
parames[3].Value = cusxoid;
}
else
{
parames[3].Value = DBNull.Value;
}
if (product != null)
{
parames[4].Value = product.ProductId;
}
else
{
parames[4].Value = DBNull.Value;
}
StringBuilder sql = new StringBuilder();
sql.Append("SELECT w.Workhousename as WorkHouseNextName,a.Checkeds,a.IsClose,a.InvoiceXOId,a.PronoteHeaderID,a.InvoiceCusId,a.InvoiceXODetailQuantity,a.PronoteDate,a.Pronotedesc,a.MRSHeaderId,a.MRSdetailsId, a.DetailsSum,a.ProductId,a.ProductUnit,a.InvoiceXODetailQuantity");
sql.Append(", (SELECT EmployeeName FROM employee where employee.employeeid=a.Employee0Id) as Employee0Name, (select EmployeeName from employee where employee.employeeid=a.Employee1Id) as Employee1Name");
//sql.Append(", (SELECT EmployeeName FROM employee where employee.employeeid=a.Employee2Id) as Employee2Name ");
//sql.Append(" , (SELECT Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId =(SELECT TOP 1 WorkHouseId FROM ProduceInDepotDetail pr WHERE pr.PronoteHeaderId= a.PronoteHeaderID ORDER BY ProduceInDepotId DESC ) ) AS WorkHouseNextName ");
// sql.Append(" , (SELECT TOP 1 ProduceTransferQuantity FROM ProduceInDepotDetail pr WHERE pr.PronoteHeaderId= a.PronoteHeaderID ORDER BY ProduceInDepotId DESC ) AS ProduceTransferQuantity");
//本车间合格数量
sql.Append(" , (SELECT sum(CheckOutSum) FROM ProduceInDepotDetail pr WHERE pr.PronoteHeaderId= a.PronoteHeaderID and pr.ProduceInDepotId in (select ProduceInDepotid from ProduceInDepot where WorkHouseId='" + workhouseIndepot + "')) AS HeJiCheckOutSum");
//前车间合格数量
sql.Append(" , (SELECT sum(CheckOutSum) FROM ProduceInDepotDetail pr WHERE pr.PronoteHeaderId= a.PronoteHeaderID and WorkHouseId='" + workhouseIndepot + "') AS ProduceTransferQuantity");
//当前部门合计生产数量,出自<生产入库详细>
sql.Append(", (SELECT sum(isnull(p.ProceduresSum,0)) FROM ProduceInDepotDetail p WHERE p.PronoteHeaderId = a.PronoteHeaderID AND p.ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE WorkHouseId = '" + workhouseIndepot + "')) AS HeJiProceduresSum");
//当前部门合计转生产数量
sql.Append(", (SELECT SUM(HeJiProduceTransferQuantity) FROM ProduceInDepotDetail p WHERE p.PronoteHeaderId = a.PronoteHeaderId AND p.ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE WorkHouseId = '" + workhouseIndepot + "')) AS HeJiProduceTransferQuantity");
////当前部门合计入库数量
//sql.Append(", (SELECT SUM(HeJiProduceQuantity) FROM ProduceInDepotDetail p WHERE p.PronoteHeaderId = a.PronoteHeaderId AND p.ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE WorkHouseId = '" + workhouseIndepot + "')) AS HeJiProduceQuantity");
//PronoteProceduresDate 订单交期
sql.Append(", i.CustomerInvoiceXOId,i.InvoiceYjrq as PronoteProceduresDate, (SELECT CheckedStandard FROM Customer c WHERE c.CustomerId = i.xocustomerId) as CustomerCheckStandard");
sql.Append(", (SELECT CustomerShortName FROM Customer c WHERE c.CustomerId = i.xocustomerId) as CustomerShortName");
//if (!string.IsNullOrEmpty(workhouseIndepot))
//{
// sql.Append(", (select top 1 PronoteProceduresDate from PronoteProceduresDetail u where u.PronoteHeaderID=a.PronoteHeaderID and u.WorkHouseId='" + workhouseIndepot + "' order by PronoteProceduresDate ) as PronoteProceduresDate");
//}
sql.Append(", (SELECT TOP 1 PronoteMachineId FROM PronoteProceduresDetail WHERE PronoteHeaderID=a.PronoteHeaderId ORDER BY ProceduresNo) as PronoteMachineId");
sql.Append(", (SELECT TOP 1 PronoteProceduresDate FROM PronoteProceduresDetail WHERE PronoteHeaderID=a.PronoteHeaderId ORDER BY ProceduresNo) as Shechudata");
sql.Append(",b.ProductName,b.id, b.CustomerProductName FROM PronoteHeader a left join Product b on a.productid=b.productid left join invoicexo i on a.invoicexoid=i.invoiceid left join WorkHouse w on a.WorkHouseId=w.WorkHouseId");
sql.Append(" where PronoteDate between @startdate and @enddate ");
if (!string.IsNullOrEmpty(cusxoid))
{
sql.Append(" and i.CustomerInvoiceXOId like '%'+@CustomerInvoiceXOId+'%'");
}
if (customer != null)
{
sql.Append(" and i.xocustomerId=@xocustomerId");
}
if (product != null)
{
sql.Append(" and a.productid=@productid");
}
if (!string.IsNullOrEmpty(PronoteHeaderIdStart) && !string.IsNullOrEmpty(PronoteHeaderIdEnd))
{
sql.Append(" and a.PronoteHeaderID between '" + PronoteHeaderIdStart + "' and '" + PronoteHeaderIdEnd + "'");
}
//if (sourcetype != -1) //全部时为-1
// sql.Append(" and a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType=" + sourcetype + ")");
if (jiean) // 只显示未结案
{
sql.Append(" and a.IsClose=0");
}
if (!string.IsNullOrEmpty(proNameKey)) // 商品名称关键字
{
sql.Append(" and b.ProductName like '%" + proNameKey + "%'");
}
if (!string.IsNullOrEmpty(proCusNameKey)) //客户型号名称关键字
{
sql.Append(" and b.CustomerProductName like '%" + proCusNameKey + "%'");
}
if (!string.IsNullOrEmpty(pronoteHeaderIdKey)) // 加工单号关键字
{
sql.Append(" and a.PronoteHeaderID like '%" + pronoteHeaderIdKey + "%'");
}
sql.Append(" And i.InvoiceYjrq between '" + JiaohuoDateStart.ToString("yyyy-MM-dd") + "' and '" + JiaohuoDateEnd.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'"); //交货日期
if (!string.IsNullOrEmpty(MachineName)) //机台号
{
sql.Append(" and a.PronoteHeaderID in (SELECT PronoteHeaderID FROM PronoteProceduresDetail WHERE PronoteMachineId='" + MachineName + "')");
}
//三种自制条件
if (sourcetype0 && sourcetype4 && !sourcetype5)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0','4')) and 2=2)");
}
else if (sourcetype0 && sourcetype5 && !sourcetype4)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0','5')) and 2=2)");
}
else if (sourcetype4 && sourcetype5 && !sourcetype0)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('4','5')) and 2=2)");
}
else if (sourcetype0 && !sourcetype5 && !sourcetype4)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0')) and 2=2)");
}
else if (sourcetype4 && !sourcetype0 && !sourcetype5)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('4')) and 2=2)");
}
else if (sourcetype5 && !sourcetype0 && !sourcetype4)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('5')) and 2=2)");
}
else if (sourcetype0 && sourcetype4 && sourcetype5)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0','4','5')) and 2=2)");
}
//在加一种自制条件--仓库(半成品加工)
if (sourcetype7)
{
if (sql.ToString().Contains("and 2=2"))
{
sql.Replace("and 2=2", "or a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('7'))");
}
else
{
sql.Append(" and a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('7'))");
}
}
sql.Append(" order by a.PronoteHeaderID desc ");
return(this.DataReaderBind <Model.PronoteHeader>(sql.ToString(), parames, CommandType.Text));
}
public IList <Book.Model.InvoiceXO> SelectByCustomers(Model.Customer customer)
{
return(sqlmapper.QueryForList <Model.InvoiceXO>("InvoiceXO.select_byCustomers", customer.CustomerId));
}
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 async Task <int> Add(Model.Customer model)
{
_context.Customer.Add(model);
return(_context.SaveChanges());
}
public IList <Model.InvoiceXS> SelectInvoice(Model.Customer customer)
{
return(sqlmapper.QueryForList <Model.InvoiceXS>("InvoiceXS.selectByCustomerId", customer.CustomerId));
}
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 <Book.Model.InvoiceXS> Select(Model.Customer customer)
{
return(sqlmapper.QueryForList <Book.Model.InvoiceXS>("InvoiceXS.select_bycustomerId", customer.CustomerId));
}
//public void DeleteByInProductCustomer(Book.Model.Product product,Model.Customer customer)
//{
// Hashtable ht = new Hashtable();
// ht.Add("productid", product.ProductId);
// ht.Add("customerid", customer == null ? null : customer.CustomerId);
// sqlmapper.Delete("BomParentPartInfo.deleteByBOMInProductCustom", ht);
//}
public IList <Model.BomParentPartInfo> SelectNotContentByCustomer(Model.Customer customer)
{
return(sqlmapper.QueryForList <Model.BomParentPartInfo>("BomParentPartInfo.selectnotcontentByCustomer", customer.CustomerId));
}
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, string depotId, string handBookId)
{
return(accessor.Select(customer1, customer2, startDate, endDate, yjrq1, yjrq2, employee1, employee2, xoid1, xoid2, cusxoidkey, product, product2, isclose, mpsIsClose, orderColumn, orderType, detailFlag, depotId, handBookId));
}
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));
}
public IList <Book.Model.InvoiceXS> SelectDateRangAndWhere(Model.Customer customer, DateTime?dateStart, DateTime?dateEnd, string cusxoid, Model.Product product, string invoicexoid, string FreightedCompanyId, string ConveyanceMethodId)
{
return(accessor.SelectDateRangAndWhere(customer, dateStart, dateEnd, cusxoid, product, invoicexoid, FreightedCompanyId, ConveyanceMethodId));
}
public CustomerIndexViewItem(Model.Customer customer, Model.Country country)
: base(customer)
{
CountryName = country.CountryName;
}
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 <Book.Model.ProduceInDepotDetail> SelectList(string startPronoteHeader, string endPronoteHeader, DateTime startDate, DateTime endDate, Model.Product product, Model.WorkHouse work, Model.Depot mDepot, Model.DepotPosition mDepotPosition, string id1, string id2, string cusxoid, Model.Customer customer1, Model.Customer customer2, int ProductState)
{
return(accessor.SelectList(startPronoteHeader, endPronoteHeader, startDate, endDate, product, work, mDepot, mDepotPosition, id1, id2, cusxoid, customer1, customer2, ProductState));
}
//数据输入页 选择加工单
public IList <Book.Model.PronoteHeader> GetByDateDI(DateTime startDate, DateTime endDate, Model.Customer customer, string cusxoid, Model.Product product, string PronoteHeaderIdStart, string PronoteHeaderIdEnd, string workhouseIndepot, bool jiean, string proNameKey, string proCusNameKey, string pronoteHeaderIdKey, bool sourcetype0, bool sourcetype4, bool sourcetype5, bool sourcetype7)
{
SqlParameter[] parames = { new SqlParameter("@startdate", DbType.DateTime), new SqlParameter("@enddate", DbType.DateTime), new SqlParameter("@xocustomerId", DbType.String), new SqlParameter("@CustomerInvoiceXOId", DbType.String), new SqlParameter("@productid", DbType.String) };
parames[0].Value = startDate;
parames[1].Value = endDate;
if (customer != null)
{
parames[2].Value = customer.CustomerId;
}
else
{
parames[2].Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(cusxoid))
{
parames[3].Value = cusxoid;
}
else
{
parames[3].Value = DBNull.Value;
};
if (product != null)
{
parames[4].Value = product.ProductId;
}
else
{
parames[4].Value = DBNull.Value;
}
StringBuilder sql = new StringBuilder();
sql.Append("SELECT w.Workhousename as WorkHouseNextName,a.Checkeds,a.IsClose,a.InvoiceXOId,a.PronoteHeaderID,a.InvoiceCusId,a.InvoiceXODetailQuantity,a.PronoteDate,a.Pronotedesc,a.MRSHeaderId,a.MRSdetailsId, a.DetailsSum,a.ProductId,a.ProductUnit,a.InvoiceXODetailQuantity");
sql.Append(", (SELECT EmployeeName FROM employee where employee.employeeid=a.Employee0Id) as Employee0Name, (select EmployeeName from employee where employee.employeeid=a.Employee1Id) as Employee1Name");
sql.Append(", (SELECT EmployeeName FROM employee where employee.employeeid=a.Employee2Id) as Employee2Name ");
sql.Append(" , (SELECT sum(CheckOutSum) FROM ProduceInDepotDetail pr WHERE pr.PronoteHeaderId= a.PronoteHeaderID and WorkHouseId = (select WorkHouseId from WorkHouse where WorkHousename = '射出' )) AS ProduceTransferQuantity");
sql.Append(", i.CustomerInvoiceXOId,i.InvoiceYjrq as PronoteProceduresDate, (SELECT CheckedStandard FROM Customer c WHERE c.CustomerId = i.xocustomerId) as CustomerCheckStandard");
sql.Append(", (SELECT CustomerShortName FROM Customer c WHERE c.CustomerId = i.xocustomerId) as CustomerShortName");
sql.Append(", (SELECT TOP 1 PronoteMachineId FROM PronoteProceduresDetail WHERE PronoteHeaderID=a.PronoteHeaderId ORDER BY ProceduresNo) as PronoteMachineId");
sql.Append(", (SELECT TOP 1 PronoteProceduresDate FROM PronoteProceduresDetail WHERE PronoteHeaderID=a.PronoteHeaderId ORDER BY ProceduresNo) as Shechudata");
sql.Append(",b.ProductName,b.id, b.CustomerProductName FROM PronoteHeader a left join Product b on a.productid=b.productid left join invoicexo i on a.invoicexoid=i.invoiceid left join WorkHouse w on a.WorkHouseId=w.WorkHouseId");
sql.Append(" where PronoteDate between @startdate and @enddate ");
if (!string.IsNullOrEmpty(cusxoid))
{
sql.Append(" and i.CustomerInvoiceXOId like '%'+@CustomerInvoiceXOId+'%'");
}
if (customer != null)
{
sql.Append(" and i.xocustomerId=@xocustomerId");
}
if (product != null)
{
sql.Append(" and a.productid=@productid");
}
if (!string.IsNullOrEmpty(PronoteHeaderIdStart) && !string.IsNullOrEmpty(PronoteHeaderIdEnd))
{
sql.Append(" and a.PronoteHeaderID between '" + PronoteHeaderIdStart + "' and '" + PronoteHeaderIdEnd + "'");
}
if (jiean) // 只显示未结案
{
sql.Append(" and a.IsClose=0");
}
if (!string.IsNullOrEmpty(proNameKey)) // 商品名称关键字
{
sql.Append(" and b.ProductName like '%" + proNameKey + "%'");
}
if (!string.IsNullOrEmpty(proCusNameKey)) //客户型号名称关键字
{
sql.Append(" and b.CustomerProductName like '%" + proCusNameKey + "%'");
}
if (!string.IsNullOrEmpty(pronoteHeaderIdKey)) // 加工单号关键字
{
sql.Append(" and a.PronoteHeaderID like '%" + pronoteHeaderIdKey + "%'");
}
//2017年9月11日00:43:22
sql.Append(" and a.PronoteHeaderID not in (select PronoteHeaderId from PCDataInput)"); //数据输入也 只能拉一次 PNT
//三种自制条件
if (sourcetype0 && sourcetype4 && !sourcetype5)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0','4')) and 2=2)");
}
else if (sourcetype0 && sourcetype5 && !sourcetype4)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0','5')) and 2=2)");
}
else if (sourcetype4 && sourcetype5 && !sourcetype0)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('4','5')) and 2=2)");
}
else if (sourcetype0 && !sourcetype5 && !sourcetype4)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0')) and 2=2)");
}
else if (sourcetype4 && !sourcetype0 && !sourcetype5)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('4')) and 2=2)");
}
else if (sourcetype5 && !sourcetype0 && !sourcetype4)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('5')) and 2=2)");
}
else if (sourcetype0 && sourcetype4 && sourcetype5)
{
sql.Append(" and (a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('0','4','5')) and 2=2)");
}
//在加一种自制条件--仓库(半成品加工)
if (sourcetype7)
{
if (sql.ToString().Contains("and 2=2"))
{
sql.Replace("and 2=2", "or a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('7'))");
}
else
{
sql.Append(" and a.MRSHeaderId IN(SELECT MRSHeaderId FROM MRSHeader WHERE SourceType in ('7'))");
}
}
sql.Append(" order by a.PronoteHeaderID desc ");
return(this.DataReaderBind <Model.PronoteHeader>(sql.ToString(), parames, CommandType.Text));
}
public IList <Model.InvoiceXO> SelectDateRangCusXOCustomer(DateTime startdate, DateTime enddate, string cusxoid, Model.Customer customer)
{
Hashtable ta = new Hashtable();
ta.Add("startdate", startdate == null || startdate == new DateTime() ? global::Helper.DateTimeParse.NullDate : startdate);
ta.Add("enddate", enddate == null || enddate == new DateTime() ? global::Helper.DateTimeParse.EndDate : enddate);
ta.Add("cusxoid", string.IsNullOrEmpty(cusxoid) ? null : cusxoid);
ta.Add("customerid", customer == null ? null : customer.CustomerId);
return(sqlmapper.QueryForList <Model.InvoiceXO>("InvoiceXO.selectDateRangCusXOCustomer", ta));
}
public bool IsExistShortName(Model.Customer customer)
{
return(accessor.IsExistShortName(customer));
}
private void ShowInfo(int _id)
{
BLL.Customer bll = new BLL.Customer();
model = bll.GetModel(_id);
txtName.Text = model.c_name;
ddltype.SelectedValue = model.c_type.ToString();
if (model.c_type == 3)
{
ddltype.Visible = false;
labtype.Text = Common.BusinessDict.customerType()[model.c_type.Value];
btnDelete.Visible = false;
btnDelBank.Visible = false;
liAdd.Visible = false;
}
txtNum.Text = model.c_num;
txtBusinessScope.Text = model.c_business;
txtRemark.Text = model.c_remarks;
if (model.c_isUse.Value)
{
cbIsUse.Checked = true;
}
else
{
cbIsUse.Checked = false;
}
btnSubmit.Visible = false;
BtnContact.Visible = false;
BtnBank.Visible = false;
//已审核通过的不能再修改
if (model.c_flag == 2)
{
btnSubmit.Visible = false;
BtnContact.Visible = false;
BtnBank.Visible = false;
}
else
{
if (manager.user_name == model.c_owner)
{
btnSubmit.Visible = true;
BtnContact.Visible = true;
BtnBank.Visible = true;
}
else
{
if (new MettingSys.BLL.permission().checkHasPermission(manager, "0301"))
{
btnSubmit.Visible = true;
BtnContact.Visible = true;
BtnBank.Visible = true;
}
}
}
Mdl1.Visible = false;
Mdl2.Visible = false;
//绑定联系人
this.rptList.DataSource = new BLL.Contacts().GetList(0, "co_cid=" + _id + "", "co_flag desc,co_id asc");
this.rptList.DataBind();
//绑定银行账号
this.bankrptList.DataSource = new BLL.customerBank().GetList(0, "cb_cid=" + _id + "", "cb_id asc");
this.bankrptList.DataBind();
}
/// <summary>
/// Insert a Customer.
/// </summary>
public void Insert(Model.Customer customer)
{
//
// todo:add other logic here
//
Validate(customer);
if (this.Exists(customer.Id))
{
throw new Helper.InvalidValueException(Model.Customer.PRO_Id);
}
try
{
V.BeginTransaction();
//设置KEY值
SequenceManager.Increment("customer");
if (customer.AreaCategory != null)
{
customer.AreaCategoryId = customer.AreaCategory.AreaCategoryId;
}
if (customer.TradeCategory != null)
{
customer.TradeCategoryId = customer.TradeCategory.TradeCategoryId;
}
if (customer.CustomerCategory != null)
{
customer.CustomerCategoryId = customer.CustomerCategory.CustomerCategoryId;
}
customer.InsertTime = DateTime.Now;
customer.CustomerId = Guid.NewGuid().ToString();
accessor.Insert(customer);
foreach (Model.CustomerContact contact in customer.Contacts)
{
contact.Customer = customer;
contact.CustomerId = customer.CustomerId;
if (string.IsNullOrEmpty(contact.CustomerContactId))
{
contact.CustomerContactId = Guid.NewGuid().ToString();
}
customerContactAccessor.Insert(contact);
}
//foreach (Model.CustomerMarks mark in customer.Marks)
//{
// mark.CustomerId = customer.CustomerId;
// (new BL.CustomerMarksManager()).Insert(mark);
//}
V.CommitTransaction();
}
catch (Exception ex)
{
V.RollbackTransaction();
throw ex;
}
}
public IList <Book.Model.MPSdetails> Select(Model.Customer customer)
{
return(sqlmapper.QueryForList <Model.MPSdetails>("MPSdetails.select_byCustomerId", customer.CustomerId));
}
public IList <Book.Model.InvoiceXS> Select(Model.Customer customer)
{
return(accessor.Select(customer));
}
//商品不良率统计(2012年12月10日16:06:48没有调用过)
public DataTable PTSelect_ChooseDefectRateCls(DateTime StartDate, DateTime EndDate, string StartProduceInDepotId, string EndProduceInDepotId, Model.Product StartProduct, Model.Product EndProduct, string StartPronoteHeaderId, string EndPronoteHeaderId, Model.WorkHouse StartWorkHouse, Model.WorkHouse EndWorkHouse, Model.Customer StartCustomer, Model.Customer EndCustomer, bool attrJiLuFangShi, bool attrQiangHua, bool attrWuDu, bool attrWuQiangHuaWuDu, int attrProductStates, double RejectionRate, string RejectionRateCompare, bool EnableBLV, int attrOrderColumn, int attrOrderType)
{
return(accessor.PTSelect_ChooseDefectRateCls(StartDate, EndDate, StartProduceInDepotId, EndProduceInDepotId, StartProduct, EndProduct, StartPronoteHeaderId, EndPronoteHeaderId, StartWorkHouse, EndWorkHouse, StartCustomer, EndCustomer, attrJiLuFangShi, attrQiangHua, attrWuDu, attrWuQiangHuaWuDu, attrProductStates, RejectionRate, RejectionRateCompare, EnableBLV, attrOrderColumn, attrOrderType));
}
public IList <Model.InvoiceXS> SelectInvoice(Model.Customer customer)
{
return(accessor.SelectInvoice(customer));
}
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 IList <Model.AcOtherShouldCollection> SelectByDateRangeAndCustomerCompany(DateTime startdate, DateTime enddate, Model.Customer customer, Model.Company company)
{
return(accessor.SelectByDateRangeAndCustomerCompany(startdate, enddate, customer, company));
}
