private void simpleButton1_Click(object sender, EventArgs e)
{
Model.Customer customer = newChooseCustom.EditValue as Model.Customer;
if (customer == null)
{
customer = new Book.Model.Customer();
}
Model.Employee emp = this.comboBoxRole.EditValue as Model.Employee;
if (emp == null)
{
emp = new Book.Model.Employee();
}
IList <Model.InvoiceXO> invoicesXOs = invoiceXOManager.SelectByYJRQCustomEmpCusXOId(customer, this.dateEditStartDate.DateTime, this.dateEditEndDate.DateTime, emp, null);
if (invoicesXOs.Count <= 0)
{
MessageBox.Show("尚沒有應該處理的訂單。", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
XSForm f = new XSForm(invoicesXOs);
if (f.ShowDialog() == DialogResult.OK)
{
this.DialogResult = DialogResult.OK;
}
}
protected override void AddNew()
{
this._customer = new Model.Customer();
this._customer.LastTransactionDate = DateTime.Now;
this._customer.Id = this.customerManager.GetNewId();
this._customer.TaxCaluType = 1;
}
protected override void Delete()
{
if (this._customer == null)
{
return;
}
if (MessageBox.Show(Properties.Resources.ConfirmToDelete, this.Text, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
{
return;
}
try
{
this.customerManager.Delete(this._customer.CustomerId);
this._customer = this.customerManager.GetNext(this._customer);
if (this._customer == null)
{
this._customer = this.customerManager.GetLast();
}
}
catch
{
throw;
}
}
public IList <Book.Model.CustomerProducts> Select(Book.Model.Customer customer, Book.Model.Customer customer2)
{
Hashtable ht = new Hashtable();
ht.Add("startid", customer == null ? "" : customer.Id);
ht.Add("endid", customer2 == null ? "" : customer2.Id);
return(sqlmapper.QueryForList <Model.CustomerProducts>("CustomerProducts.selectbyCustomerIdRange", ht));
}
protected override void MovePrev()
{
Model.Customer company = this.customerManager.GetPrev(this._customer);
if (company == null)
{
throw new InvalidOperationException(Properties.Resources.ErrorNoMoreRows);
}
this._customer = company;
}
private void btn_Search_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (customerManager.HasRows())
{
ListForm f = new ListForm();
if (f.ShowDialog(this) == DialogResult.OK)
{
this._customer = cstomer;
this.action = "view";
this.Refresh();
}
}
}
protected override void AddNew()
{
this._customer = new Model.Customer();
//this._customer.LastTransactionDate = DateTime.Now;
this._customer.Id = this.customerManager.GetNewId();
//联系人
ClearText();
SetTextEditReadOnly(false);
_ac = "insert";
cc = new Book.Model.CustomerContact();
this.simpleButtonAdd.Enabled = false;
this.simpleButtonSave.Enabled = true;
this.simpleButtonModify.Enabled = false;
this.simpleButtonDel.Enabled = false;
this.simpleButtonUndo.Enabled = true;
}
public IList <Book.Model.AcInvoiceXOBillDetail> selectByConditionInvoiceXODetail(DateTime?startdate, DateTime?enddate, string IdStart, string IdEnd, Book.Model.Customer startCustomer, Book.Model.Customer endCustomer)
{
StringBuilder sb = new StringBuilder();
if (startdate.HasValue && enddate.HasValue)
{
sb.Append(" AND AcInvoiceXOBillDetail.AcInvoiceXOBillId IN (SELECT AcInvoiceXOBill.AcInvoiceXOBillId FROM AcInvoiceXOBill WHERE AcInvoiceXOBill.AcInvoiceXOBillDate BETWEEN '" + startdate.Value.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
}
if (!string.IsNullOrEmpty(IdStart) && !string.IsNullOrEmpty(IdEnd))
{
sb.Append("AND AcInvoiceXOBillDetail.AcInvoiceXOBillId IN (SELECT AcInvoiceXOBill.AcInvoiceXOBillId FROM AcInvoiceXOBill WHERE AcInvoiceXOBill.Id BETWEEN '" + IdStart + "' AND '" + IdEnd + "')");
}
if (startCustomer != null && endCustomer != null)
{
sb.Append("AND AcInvoiceXOBillDetail.AcInvoiceXOBillId IN (SELECT AcInvoiceXOBill.AcInvoiceXOBillId FROM AcInvoiceXOBill WHERE AcInvoiceXOBill.CustomerId BETWEEN '" + startCustomer.CustomerId + "' AND '" + endCustomer.CustomerId + "')");
}
return(sqlmapper.QueryForList <Model.AcInvoiceXOBillDetail>("AcInvoiceXOBillDetail.selectByConditionInvoiceXODetail", sb.ToString()));
}
public DataTable SelectbyConditionXBiao(DateTime StartDate, DateTime EndDate, DateTime Yjri1, DateTime Yjri2, Book.Model.Customer Customer1, Book.Model.Customer Customer2, string XOId1, string XOId2, Book.Model.Product Product, Book.Model.Product Product2, string CusXOId, int OrderColumn, int OrderType, bool?isSpecial)
{
StringBuilder sb_xs = new StringBuilder("SELECT InvoiceId AS CHDH,(SELECT InvoiceDate FROM InvoiceXS WHERE InvoiceId = InvoiceXSDetail.InvoiceId) AS CHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = InvoiceXSDetail.ProductId) AS ProductName,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId = InvoiceXOId ) AS KHDDBH,InvoiceXSDetailQuantity AS BCCHSL,InvoiceProductUnit AS DanWei,InvoiceXSDetailPrice AS DanJia,InvoiceAllowance AS ZheRang,ROUND(InvoiceXSDetailMoney,0) AS JinE,ROUND(InvoiceXSDetailTaxMoney,0)-ROUND(InvoiceXSDetailMoney,0) AS ShuiE,ROUND(InvoiceXSDetailTaxMoney,0) AS YingShou FROM InvoiceXSDetail WHERE 1 = 1 ");
StringBuilder sb_xt = new StringBuilder("SELECT InvoiceId AS CHDH,(SELECT InvoiceDate FROM InvoiceXT WHERE InvoiceId = InvoiceXTDetail.InvoiceId) AS CHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = InvoiceXTDetail.ProductId ) AS ProductName,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId = InvoiceXOId) AS KHDDBH,InvoiceXTDetailQuantity AS BCCHSL,InvoiceProductUnit AS DanWei,InvoiceXTDetailPrice AS DanJia,InvoiceXTDetailDiscount AS ZheRang,ROUND((0-InvoiceXTDetailMoney1),0) AS JinE,ROUND((0-InvoiceXTDetailMoney0)+(0-ISNULl(InvoiceXTDetailPrice,0)*isnull(InvoiceXTDetailQuantity,0)*0.05),0)-ROUND((0-InvoiceXTDetailMoney1),0) AS ShuiE,ROUND((0-InvoiceXTDetailMoney0)+(0-ISNULl(InvoiceXTDetailPrice,0)*isnull(InvoiceXTDetailQuantity,0)*0.05),0) AS YingShou FROM InvoiceXTDetail WHERE 1 = 1 ");
//时间日期
sb_xs.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
sb_xt.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
//预交日期
if (Yjri1 != global::Helper.DateTimeParse.NullDate && Yjri2 != global::Helper.DateTimeParse.EndDate)
{
sb_xs.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE InvoiceYjrq BETWEEN '" + Yjri1.ToString("yyyy-MM-dd") + "' AND '" + Yjri2.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
}
//客户
if (Customer1 != null && Customer2 != null)
{
sb_xs.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "'))");
sb_xt.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "'))");
}
//头编号
if (!string.IsNullOrEmpty(XOId1) && !string.IsNullOrEmpty(XOId2))
{
sb_xs.Append(" AND InvoiceId BETWEEN '" + XOId1 + "' AND '" + XOId2 + "'");
}
//客户订单编号
if (!string.IsNullOrEmpty(CusXOId))
{
sb_xs.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = '" + CusXOId + "')");
sb_xt.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = '" + CusXOId + "')");
}
//商品
if (Product != null && Product2 != null)
{
sb_xs.Append(" AND ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "')");
sb_xt.Append(" AND ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "')");
}
//特殊 只在应收账款明细表使用，其他引用 传递 null
if (isSpecial != null)
{
if ((bool)isSpecial)
{
sb_xs.Append(" AND InvoiceId in (select InvoiceId from InvoiceXS where Special='" + 1 + "')");
}
else
{
sb_xs.Append(" AND InvoiceId in (select InvoiceId from InvoiceXS where Special='" + 0 + "' or Special is null)");
}
}
//赠送的不算做应收
sb_xs.Append(" AND InvoiceXODetailId not in (select InvoiceXODetailId from InvoiceXODetail where Islargess=1)");
sb_xt.Append(" AND InvoiceXODetailId not in (select InvoiceXODetailId from InvoiceXODetail where Islargess=1)");
string sql = sb_xs.ToString() + " UNION ALL " + sb_xt.ToString() + " order by CHDH,CHRQ";
using (SqlConnection con = new SqlConnection(sqlmapper.DataSource.ConnectionString))
{
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
sda.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
return(null);
}
public IList <Book.Model.PCPGOnlineCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId, string StartPronoteHeader, string EndPronoteHeader)
{
SqlParameter[] parames =
{
new SqlParameter("@StartDate", SqlDbType.DateTime),
new SqlParameter("@EndDate", SqlDbType.DateTime),
new SqlParameter("@CustomerId", SqlDbType.VarChar),
new SqlParameter("@InvoiceCusXOId", SqlDbType.VarChar),
new SqlParameter("@ProductId", SqlDbType.VarChar),
new SqlParameter("@StartPronoteHeader", SqlDbType.VarChar),
new SqlParameter("@EndPronoteHeader", SqlDbType.VarChar)
};
parames[0].Value = StartDate.ToString("yyyy-MM-dd");
parames[1].Value = EndDate.ToString("yyyy-MM-dd");
if (customer == null)
{
parames[2].Value = DBNull.Value;
}
else
{
parames[2].Value = customer.CustomerId;
}
if (string.IsNullOrEmpty(CusXOId))
{
parames[3].Value = DBNull.Value;
}
else
{
parames[3].Value = "%" + CusXOId + "%";
}
if (product == null)
{
parames[4].Value = DBNull.Value;
}
else
{
parames[4].Value = product.ProductId;
}
if (string.IsNullOrEmpty(StartPronoteHeader))
{
parames[5].Value = DBNull.Value;
}
else
{
parames[5].Value = StartPronoteHeader;
}
if (string.IsNullOrEmpty(EndPronoteHeader))
{
parames[6].Value = DBNull.Value;
}
else
{
parames[6].Value = EndPronoteHeader;
}
StringBuilder sql = new StringBuilder("SELECT PCPGOnlineCheckId,PCPGOnlineCheckDate,xo.CustomerInvoiceXOId as InvoiceCusXOId,(SELECT EmployeeName FROM Employee WHERE Employee.EmployeeId = PCPGOnlineCheck.EmployeeId) AS EmployeeName,(SELECT ProductName FROM Product WHERE Product.ProductId = PCPGOnlineCheck.ProductId) AS ProductName,(SELECT Customer.CustomerShortName FROM Customer WHERE Customer.CustomerId = PCPGOnlineCheck.CustomerId) AS CustomerShortName,(SELECT Convert(varchar(30),PCPGOnlineCheckDetailDate,120)+'.' FROM PCPGOnlineCheckDetail WHERE PCPGOnlineCheckDetail.PCPGOnlineCheckId=PCPGOnlineCheck.PCPGOnlineCheckId FOR xml path('')) AS DescTime FROM PCPGOnlineCheck left join InvoiceXO xo on PCPGOnlineCheck.InvoiceXOId=xo.InvoiceId WHERE 1 = 1 ");
sql.Append(" AND PCPGOnlineCheckDate BETWEEN @StartDate AND @EndDate");
if (customer != null)
{
sql.Append(" AND CustomerId = @CustomerId");
}
if (!string.IsNullOrEmpty(CusXOId))
{
sql.Append(" AND xo.CustomerInvoiceXOId like @InvoiceCusXOId");
}
if (product != null)
{
sql.Append(" AND PCPGOnlineCheck.ProductId=@ProductId");
}
if (!string.IsNullOrEmpty(StartPronoteHeader) && !string.IsNullOrEmpty(EndPronoteHeader))
{
sql.Append(" AND FromPCId BETWEEN @StartPronoteHeader AND @EndPronoteHeader");
}
return(this.DataReaderBind <Model.PCPGOnlineCheck>(sql.ToString(), parames, CommandType.Text));
}
public IList <Model.PCPGOnlineCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId, string StartPronoteHeader, string EndPronoteHeader)
{
StringBuilder sb = new StringBuilder(" AND p.PCPGOnlineCheckDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
if (customer != null)
{
sb.Append(" AND p.CustomerId = '" + customer.CustomerId + "'");
}
if (product != null)
{
sb.Append(" AND pd.ProductId='" + product.ProductId + "'");
}
if (!string.IsNullOrEmpty(StartPronoteHeader) && !string.IsNullOrEmpty(EndPronoteHeader))
{
sb.Append(" AND pd.FromInvoiceId BETWEEN '" + StartPronoteHeader + "' AND '" + EndPronoteHeader + "'");
}
if (!string.IsNullOrEmpty(CusXOId))
{
sb.Append(" AND (isnull(ph.InvoiceCusId,'')+isnull(pc.CustomerInvoiceXOId,'')) like '" + "%" + CusXOId + "%" + "'");
}
sb.Append(" )");
sb.Append(" order by PCPGOnlineCheckId desc");
return(sqlmapper.QueryForList <Model.PCPGOnlineCheck>("PCPGOnlineCheck.SelectByDateRange", sb.ToString()));
}
/// <param name="cmpy">model 對象</param>
/// <param name="action">當前動作</param>
public EditForm(Book.Model.Customer cmpy, string action)
: this()
{
this._customer = cmpy;
this.action = action;
}
public override void Refresh()
{
if (this._customer == null)
{
this.AddNew();
this.action = "insert";
}
else
{
if (this.action == "view")
{
if (this._customer.CustomerId != null)
{
this._customer = this.customerManager.Get(this._customer.CustomerId);
}
}
}
this.bindingSourceBusiness.DataSource = this.employeeManager.SelectOnActive();
this.bindingSourceCustom.DataSource = this.customerManager.Select();
this.bindingSourceCustomsContact.DataSource = this._customer.Contacts;
this.CompanyIdTextEdit.Text = string.IsNullOrEmpty(this._customer.Id) ? this._customer.CustomerId : this._customer.Id;
this.CustomerFullNameTextEdit.Text = this._customer.CustomerFullName;
this.CustomerShortNameTextEdit.Text = this._customer.CustomerShortName;
this.CompanyDescriptionMemoEdit.Rtf = this._customer.CustomerDescription;
this.textEditCompanyPhone.Text = this._customer.CustomerPhone;
this.CompanyFaxTextEdit.Text = this._customer.CustomerFax;
this.CompanyAddressTextEdit.Text = this._customer.CustomerAddress;
this.textEditCompanyEMail.Text = this._customer.CustomerEMail;
this.textEditCompanyJinChuAddress.Text = this._customer.CustomerJinChuAddress;
//this.textEditCompanyManager.Text = this._customer.CustomerManager;
this.textEditCompanyMobile.Text = this._customer.CustomerMobile;
this.textEditCompanyNumber.Text = this._customer.CustomerNumber;
this.textEditCompanyPhone1.Text = this._customer.CustomerPhone1;
this.textEditCompanyWebSiteAddress.Text = this._customer.CustomerWebSiteAddress;
this.spinEditCompanyPayDate.EditValue = this._customer.CustomerPayDate;
this.lookUpEditBusiness.EditValue = this._customer.EmployeesBusinessId;
this.cobCheckedStandard.Text = this._customer.CheckedStandard;
this.textEditContact.Text = this._customer.CustomerContact;
if (global::Helper.DateTimeParse.DateTimeEquls(this._customer.LastTransactionDate, global::Helper.DateTimeParse.NullDate))
{
this.dateEditCompanyExchangeDay.EditValue = null;
}
else
{
this.dateEditCompanyExchangeDay.EditValue = this._customer.LastTransactionDate;
}
Model.CompanyLevel level = this.companyLevelManager.Get(this._customer.CompanyLevelId);
if (level != null)
{
this.comboBoxEditCompanyLevel.Text = level.CompanyLevelId + @"-" + level.CompanyLevelName;
}
else
{
this.comboBoxEditCompanyLevel.Text = "";
}
SetTextEditReadOnly(true);
this.richTextBoxMarks1.Rtf = this._customer.Marks1;
this.richTextBoxMarks2.Rtf = this._customer.Marks2;
this.richTextBoxMarks3.Rtf = this._customer.Marks3;
this.comboBoxEditFP.EditValue = this._customer.CustomerFP;
//联系人
this.textEditEmail.Text = "";
this.textEditMobile.Text = "";
this.textEditName.Text = "";
this.textEditPhone.Text = "";
this.memoEditRemarks.Text = "";
//this._customer.Marks = this.customerMarksManager.SelectByCustomerId(this._customer.CustomerId);
//this.bindingSourceCustomerMarks.DataSource = this._customer.Marks;
//switch (this.action)
//{
// case "insert":
// this.CompanyIdTextEdit.Properties.ReadOnly = false;
// this.CompanyAddressTextEdit.Properties.ReadOnly = false;
// this.CompanyFaxTextEdit.Properties.ReadOnly = false;
// this.CustomerShortNameTextEdit.Properties.ReadOnly = false;
// this.CustomerFullNameTextEdit.Properties.ReadOnly = false;
// //this.CompanyPayDayTextEdit.Properties.ReadOnly = false;
// this.textEditCompanyPhone.Properties.ReadOnly = false;
// this.CompanyDescriptionMemoEdit.Rtf.ReadOnly = false;
// this.textEditCompanyEMail.Properties.ReadOnly = false;
// this.dateEditCompanyExchangeDay.Properties.ReadOnly = false;
// this.textEditCompanyJinChuAddress.Properties.ReadOnly = false;
// this.textEditCompanyManager.Properties.ReadOnly = false;
// this.textEditCompanyMobile.Properties.ReadOnly = false;
// this.textEditCompanyNumber.Properties.ReadOnly = false;
// this.textEditCompanyPhone1.Properties.ReadOnly = false;
// this.textEditCompanyWebSiteAddress.Properties.ReadOnly = false;
// this.spinEditCompanyPayDate.Properties.ReadOnly = false;
// this.dateEditCompanyExchangeDay.Properties.Buttons[0].Visible = true;
// this.lookUpEditBusiness.Properties.ReadOnly = false;
// this.lookUpEditBusiness.Properties.Buttons[0].Visible = true;
// this.simpleButtonAdd.Enabled = true;
// this.simpleButtonDel.Enabled = false;
// this.simpleButtonModify.Enabled = false;
// this.simpleButtonSave.Enabled = false;
// this.simpleButtonUndo.Enabled = false;
// break;
// case "update":
// this.CompanyIdTextEdit.Properties.ReadOnly = false;
// this.CompanyAddressTextEdit.Properties.ReadOnly = false;
// this.CompanyFaxTextEdit.Properties.ReadOnly = false;
// this.CustomerShortNameTextEdit.Properties.ReadOnly = false;
// this.CustomerFullNameTextEdit.Properties.ReadOnly = false;
// //this.CompanyPayDayTextEdit.Properties.ReadOnly = false;
// this.textEditCompanyPhone.Properties.ReadOnly = false;
// this.CompanyDescriptionMemoEdit.Rtf.ReadOnly = false;
// this.textEditCompanyEMail.Properties.ReadOnly = false;
// this.dateEditCompanyExchangeDay.Properties.ReadOnly = false;
// this.textEditCompanyJinChuAddress.Properties.ReadOnly = false;
// this.textEditCompanyManager.Properties.ReadOnly = false;
// this.textEditCompanyMobile.Properties.ReadOnly = false;
// this.textEditCompanyNumber.Properties.ReadOnly = false;
// this.textEditCompanyPhone1.Properties.ReadOnly = false;
// this.textEditCompanyWebSiteAddress.Properties.ReadOnly = false;
// this.spinEditCompanyPayDate.Properties.ReadOnly = false;
// this.dateEditCompanyExchangeDay.Properties.Buttons[0].Visible = true;
// this.lookUpEditBusiness.Properties.ReadOnly = false;
// this.lookUpEditBusiness.Properties.Buttons[0].Visible = true;
// this.simpleButtonAdd.Enabled = true;
// this.simpleButtonDel.Enabled = true;
// this.simpleButtonModify.Enabled = true;
// this.simpleButtonSave.Enabled = false;
// this.simpleButtonUndo.Enabled = false;
// break;
// case "view":
// this.CompanyIdTextEdit.Properties.ReadOnly = true;
// this.CompanyAddressTextEdit.Properties.ReadOnly = true;
// this.CompanyFaxTextEdit.Properties.ReadOnly = true;
// this.CustomerShortNameTextEdit.Properties.ReadOnly = true;
// this.CustomerFullNameTextEdit.Properties.ReadOnly = true;
// //this.CompanyPayDayTextEdit.Properties.ReadOnly = true;
// this.textEditCompanyPhone.Properties.ReadOnly = true;
// this.CompanyDescriptionMemoEdit.Rtf.ReadOnly = true;
// this.comboBoxEditCompanyLevel.Properties.ReadOnly = true;
// this.textEditCompanyEMail.Properties.ReadOnly = true;
// this.dateEditCompanyExchangeDay.Properties.ReadOnly = true;
// this.textEditCompanyJinChuAddress.Properties.ReadOnly = true;
// this.textEditCompanyManager.Properties.ReadOnly = true;
// this.textEditCompanyMobile.Properties.ReadOnly = true;
// this.textEditCompanyNumber.Properties.ReadOnly = true;
// this.textEditCompanyPhone1.Properties.ReadOnly = true;
// this.textEditCompanyWebSiteAddress.Properties.ReadOnly = true;
// this.spinEditCompanyPayDate.Properties.ReadOnly = true;
// this.dateEditCompanyExchangeDay.Properties.Buttons[0].Visible = false;
// this.lookUpEditBusiness.Properties.ReadOnly = true;
// this.lookUpEditBusiness.Properties.Buttons[0].Visible = false;
// this.simpleButtonAdd.Enabled = false;
// this.simpleButtonDel.Enabled = false;
// this.simpleButtonModify.Enabled = false;
// this.simpleButtonSave.Enabled = false;
// this.simpleButtonUndo.Enabled = false;
// break;
// default:
// break;
//}
base.Refresh();
//联系人 增删改 按钮状态
//this.simpleButtonAdd.Enabled = true;
//this.simpleButtonDel.Enabled = true;
//this.simpleButtonModify.Enabled = true;
this.simpleButtonSave.Enabled = false;
this.simpleButtonUndo.Enabled = false;
this.textEditEmail.Properties.ReadOnly = true;
this.textEditMobile.Properties.ReadOnly = true;
this.textEditName.Properties.ReadOnly = true;
this.textEditPhone.Properties.ReadOnly = true;
this.memoEditRemarks.Properties.ReadOnly = true;
}
protected override void MoveFirst()
{
this._customer = this.customerManager.GetFirst();
}
public IList <Book.Model.PCDoubleImpactCheck> SelectByDateRage(DateTime startdate, DateTime enddate, int pcFlag, Book.Model.Product product, Book.Model.Customer customer, string CusXOId)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", startdate);
ht.Add("enddate", enddate);
ht.Add("pcFlag", pcFlag);
StringBuilder sql = new StringBuilder();
////if (customer != null)
//// sql.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId like '" + customer.CustomerId + "')");
//if (!string.IsNullOrEmpty(CusXOId))
// sql.Append(" and InvoiceCusXOId = '" + CusXOId + "'");
//if (product != null)
// sql.Append(" and ProductId = '" + product.ProductId + "'");
//ht.Add("sql", sql.ToString());
//IList<Model.PCDoubleImpactCheck> a = sqlmapper.QueryForList<Model.PCDoubleImpactCheck>("PCDoubleImpactCheck.SelectByDateRage", ht);
//return a;
if (product != null)
{
sql.Append(" and pc.ProductId = '" + product.ProductId + "'");
}
if (customer != null)
{
sql.Append(" and xo.xocustomerId = '" + customer.CustomerId + "'");
}
if (!string.IsNullOrEmpty(CusXOId))
{
sql.Append(" and xo.CustomerInvoiceXOId='" + CusXOId + "'");
}
ht.Add("sql", sql.ToString());
return(sqlmapper.QueryForList <Model.PCDoubleImpactCheck>("PCDoubleImpactCheck.SelectByDateRage", ht));
}
public IList <Book.Model.PCOtherCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", StartDate);
ht.Add("enddate", EndDate);
StringBuilder sql = new StringBuilder();
//if (customer != null)
// sql.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')");
if (!string.IsNullOrEmpty(CusXOId))
{
sql.Append(" and PCOtherCheckId in (select PCOtherCheckId from PCOtherCheckDetail where FromInvoiceID in (select InvoiceId from InvoiceCGDetail where InvoiceCOId in (select InvoiceId from InvoiceCO where InvoiceXOId=(select InvoiceId from InvoiceXO where CustomerInvoiceXOId='" + CusXOId + "'))) or FromInvoiceID in (select ProduceOtherInDepotId from ProduceOtherInDepotDetail where ProduceOtherCompactId in (select ProduceOtherCompactId from ProduceOtherCompact where InvoiceXOId =(select InvoiceId from InvoiceXO where CustomerInvoiceXOId='" + CusXOId + "'))))");
}
//if (product != null)
// sql.Append(" and ProductId = '" + product.ProductId + "'");
sql.Append(" ORDER BY PCOtherCheckId desc");
ht.Add("sql", sql.ToString());
IList <Model.PCOtherCheck> a = sqlmapper.QueryForList <Model.PCOtherCheck>("PCOtherCheck.SelectByDateRange", ht);
return(a);
}
public IList <Book.Model.AcOtherShouldCollection> SelectByDateRangeAndCustomerCompany(DateTime startdate, DateTime enddate, Book.Model.Customer customer, Book.Model.Company company)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", startdate.ToString("yyyy-MM-dd HH:mm:ss"));
ht.Add("enddate", enddate.ToString("yyyy-MM-dd HH:mm:ss"));
ht.Add("customer", customer == null ? null : customer.CustomerId);
ht.Add("company", company == null ? null : company.CompanyId);
return(sqlmapper.QueryForList <Model.AcOtherShouldCollection>("AcOtherShouldCollection.SelectByDateRangeAndCustomerCompany", ht));
}
public IList <Book.Model.ANSIPCImpactCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string cusXOId, string ForANSIOrJIS)
{
Hashtable ht = new Hashtable();
ht.Add("StartDate", StartDate);
ht.Add("EndDate", EndDate);
StringBuilder sql = new StringBuilder();
//if (customer != null)
// sql.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')");
//if (!string.IsNullOrEmpty(cusXOId))
// //sql.Append(" and InvoiceCusXOId like '%" + cusXOId + "%'");
// sql.Append(" and PronoteHeaderId in (select PronoteHeaderID from PronoteHeader where InvoiceXOId =(select InvoiceId from InvoiceXO where CustomerInvoiceXOId='" + cusXOId + "'))");
//if (product != null)
// sql.Append(" and ProductId = '" + product.ProductId + "'");
//if (!String.IsNullOrEmpty(ForANSIOrJIS))
// sql.Append(" AND ForANSIOrJIS='" + ForANSIOrJIS + "'");
//ht.Add("sql", sql.ToString());
//return sqlmapper.QueryForList<Model.ANSIPCImpactCheck>("ANSIPCImpactCheck.SelectByDateRange", ht);
if (product != null)
{
sql.Append(" and ac.ProductId = '" + product.ProductId + "'");
}
if (customer != null)
{
sql.Append(" and xo.xocustomerId = '" + customer.CustomerId + "'");
}
if (!string.IsNullOrEmpty(cusXOId))
{
sql.Append(" and xo.CustomerInvoiceXOId='" + cusXOId + "'");
}
if (!String.IsNullOrEmpty(ForANSIOrJIS))
{
sql.Append(" AND ac.ForANSIOrJIS='" + ForANSIOrJIS + "'");
}
ht.Add("sql", sql.ToString());
return(sqlmapper.QueryForList <Model.ANSIPCImpactCheck>("ANSIPCImpactCheck.SelectByDateRange", ht));
}
public IList <Book.Model.PCExportReportANSI> SelectForChooseExport(DateTime startdate, DateTime enddate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId, string ExpType)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", startdate.ToString("yyyy-MM-dd"));
ht.Add("enddate", enddate.ToString("yyyy-MM-dd"));
ht.Add("ExpType", ExpType);
StringBuilder sb = new StringBuilder();
if (customer != null)
{
sb.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')");
}
if (!string.IsNullOrEmpty(CusXOId))
{
sb.Append(" and InvoiceCusXOId like '%" + CusXOId + "%'");
}
if (product != null)
{
sb.Append(" and ProductId = '" + product.ProductId + "'");
}
if (!string.IsNullOrEmpty(ExpType))
{
sb.Append(" and ExpType ='" + ExpType + "'");
}
ht.Add("sql", sb.ToString());
return(sqlmapper.QueryForList <Model.PCExportReportANSI>("PCExportReportANSI.SelectByDateRage", ht));
}
public IList <Book.Model.PCImpactCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", StartDate);
ht.Add("enddate", EndDate);
StringBuilder sql = new StringBuilder();
if (customer != null)
{
sql.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')");
}
if (!string.IsNullOrEmpty(CusXOId))
{
sql.Append(" and InvoiceCusXOId like '%" + CusXOId + "%'");
}
if (product != null)
{
sql.Append(" and ProductId = '" + product.ProductId + "'");
}
ht.Add("sql", sql.ToString());
IList <Model.PCImpactCheck> a = sqlmapper.QueryForList <Model.PCImpactCheck>("PCImpactCheck.SelectByDateRange", ht);
return(a);
}
protected override void MoveLast()
{
//if (this._customer == null)
this._customer = this.customerManager.GetLast();
}
public IList <Book.Model.CustomerProducts> Select(Book.Model.Customer customer)
{
return(sqlmapper.QueryForList <Model.CustomerProducts>("CustomerProducts.select_by_customerId", customer == null ? "" : customer.CustomerId));
}
/// <param name="cmpy">model 對象</param>
public EditForm(Book.Model.Customer cmpy)
: this()
{
this._customer = cmpy;
this.action = "update";
}
public void Delete(Book.Model.Customer customer)
{
sqlmapper.Delete("CustomerContact.delete_by_customer", customer.CustomerId);
}
private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e)
{
_customer = this.bindingSourceCustom.Current as Model.Customer;
Refresh();
}
public IList <Book.Model.PackageType> Select(Book.Model.Customer customer)
{
return(accessor.Select(customer));
}
public DataTable SelectDetailByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId, string StartPronoteHeader, string EndPronoteHeader)
{
//SqlParameter[] parames = {
// new SqlParameter("@StartDate", SqlDbType.DateTime),
// new SqlParameter("@EndDate", SqlDbType.DateTime),
// new SqlParameter("@CustomerId", SqlDbType.VarChar),
// new SqlParameter("@InvoiceCusXOId", SqlDbType.VarChar),
// new SqlParameter("@ProductId", SqlDbType.VarChar),
// new SqlParameter("@StartPronoteHeader", SqlDbType.VarChar),
// new SqlParameter("@EndPronoteHeader", SqlDbType.VarChar)
//};
//parames[0].Value = StartDate.ToString("yyyy-MM-dd");
//parames[1].Value = EndDate.ToString("yyyy-MM-dd");
//if (customer == null)
// parames[2].Value = DBNull.Value;
//else
// parames[2].Value = customer.CustomerId;
//if (string.IsNullOrEmpty(CusXOId))
// parames[3].Value = DBNull.Value;
//else
// parames[3].Value = "%" + CusXOId + "%";
//if (product == null)
// parames[4].Value = DBNull.Value;
//else
// parames[4].Value = product.ProductId;
//if (string.IsNullOrEmpty(StartPronoteHeader))
// parames[5].Value = DBNull.Value;
//else
// parames[5].Value = StartPronoteHeader;
//if (string.IsNullOrEmpty(EndPronoteHeader))
// parames[6].Value = DBNull.Value;
//else
// parames[6].Value = EndPronoteHeader;
//StringBuilder sql = new StringBuilder("SELECT PCPGOnlineCheckId,PCPGOnlineCheckDate,InvoiceCusXOId,(SELECT EmployeeName FROM Employee WHERE Employee.EmployeeId = PCPGOnlineCheck.EmployeeId) AS EmployeeName,(SELECT ProductName FROM Product WHERE Product.ProductId = PCPGOnlineCheck.ProductId) AS ProductName,(SELECT Customer.CustomerShortName FROM Customer WHERE Customer.CustomerId = PCPGOnlineCheck.CustomerId) AS CustomerShortName,(SELECT Convert(varchar(30),PCPGOnlineCheckDetailDate,120)+'.' FROM PCPGOnlineCheckDetail WHERE PCPGOnlineCheckDetail.PCPGOnlineCheckId=PCPGOnlineCheck.PCPGOnlineCheckId FOR xml path('')) AS DescTime FROM PCPGOnlineCheck WHERE 1 = 1 ");
//sql.Append(" AND PCPGOnlineCheckDate BETWEEN @StartDate AND @EndDate");
//if (customer != null)
// sql.Append(" AND CustomerId = @CustomerId");
//if (!string.IsNullOrEmpty(CusXOId))
// sql.Append(" AND InvoiceCusXOId like @InvoiceCusXOId");
//if (product != null)
// sql.Append(" AND PCPGOnlineCheck.ProductId=@ProductId");
//if (!string.IsNullOrEmpty(StartPronoteHeader) && !string.IsNullOrEmpty(EndPronoteHeader))
// sql.Append(" AND FromPCId BETWEEN @StartPronoteHeader AND @EndPronoteHeader");
StringBuilder sb = new StringBuilder("select * from (select pd.PCPGOnlineCheckId,p.PCPGOnlineCheckDate,(isnull(xo1.CustomerInvoiceXOId,'')+isnull(xo2.CustomerInvoiceXOId,'')+isnull(xo3.CustomerInvoiceXOId,'')) as InvoiceCusXOId2,pro.ProductName,e.EmployeeName,pd.PCPGOnlineCheckDetailDate as DescTime,pd.FromInvoiceId as FromId from PCPGOnlineCheckDetail pd left join PCPGOnlineCheck p on pd.PCPGOnlineCheckId=p.PCPGOnlineCheckId left join Employee e on e.EmployeeId=p.EmployeeId left join product pro on pd.productid=pro.ProductId left join PronoteHeader ph on pd.FromInvoiceId=ph.PronoteHeaderID left join ProduceOtherCompact pc on pd.FromInvoiceId=pc.ProduceOtherCompactId left join InvoiceCO co on pd.FromInvoiceId=co.InvoiceId left join InvoiceXO xo1 on ph.InvoiceXOId=xo1.InvoiceId left join InvoiceXO xo2 on pc.InvoiceXOId=xo2.InvoiceId left join InvoiceXO xo3 on co.InvoiceXOId=xo3.InvoiceId where p.PCPGOnlineCheckDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
if (customer != null)
{
sb.Append(" AND p.CustomerId = '" + customer.CustomerId + "'");
}
if (product != null)
{
sb.Append(" AND pd.ProductId='" + product.ProductId + "'");
}
if (!string.IsNullOrEmpty(StartPronoteHeader) && !string.IsNullOrEmpty(EndPronoteHeader))
{
sb.Append(" AND pd.FromInvoiceId BETWEEN '" + StartPronoteHeader + "' AND '" + EndPronoteHeader + "'");
}
sb.Append(" ) a");
if (!string.IsNullOrEmpty(CusXOId))
{
sb.Append(" where a.InvoiceCusXOId2 = '" + CusXOId + "'");
}
//if (OnlySelfMade) //JIS出货报告中查询时只查自制部分
// sb.Append(" And a.FromId like '%pnt%'");
sb.Append(" order by PCPGOnlineCheckId desc");
//return this.DataReaderBind<Model.PCPGOnlineCheck>(sb.ToString(), parames, CommandType.Text);
SqlDataAdapter sda = new SqlDataAdapter(sb.ToString(), sqlmapper.DataSource.ConnectionString);
DataTable dt = new DataTable();
sda.SelectCommand.CommandTimeout = 300;
sda.Fill(dt);
return(dt);
}
public IList <Book.Model.InvoiceXSDetail> SelectbyConditionX(DateTime StartDate, DateTime EndDate, DateTime Yjri1, DateTime Yjri2, Book.Model.Customer Customer1, Book.Model.Customer Customer2, string XOId1, string XOId2, Book.Model.Product Product, Book.Model.Product Product2, string CusXOId, int OrderColumn, int OrderType)
{
StringBuilder sb = new StringBuilder();
if (Product != null && Product2 != null)
{
sb.Append(" AND ProductId BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "'");
}
if (!string.IsNullOrEmpty(CusXOId))
{
sb.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = '" + CusXOId + "')");
}
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
if (Yjri1 != global::Helper.DateTimeParse.NullDate && Yjri2 != global::Helper.DateTimeParse.EndDate)
{
sb.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE InvoiceYjrq BETWEEN '" + Yjri1.ToString("yyyy-MM-dd") + "' AND '" + Yjri2.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
}
if (Customer1 != null && Customer2 != null)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "'))");
}
if (!string.IsNullOrEmpty(XOId1) && !string.IsNullOrEmpty(XOId2))
{
sb.Append(" AND InvoiceId BETWEEN '" + XOId1 + "' AND '" + XOId2 + "'");
}
return(sqlmapper.QueryForList <Model.InvoiceXSDetail>("InvoiceXSDetail.SelectbyConditionX", sb.ToString()));
}
public IList <Model.CustomerProducts> Select(Book.Model.Customer customer)
{
return(accessor.Select(customer));
}
//应收账款明细表
public DataTable SelectbyConditionXBiao(DateTime StartDate, DateTime EndDate, DateTime Yjri1, DateTime Yjri2, Book.Model.Customer Customer1, Book.Model.Customer Customer2, Model.Employee startEmp, Model.Employee endEmp, string XOId1, string XOId2, Book.Model.Product Product, Book.Model.Product Product2, string CusXOId, int OrderColumn, int OrderType, bool?isSpecial, string product_Id, string productCategoryId, string currency)
{
//StringBuilder sb_xs = new StringBuilder("SELECT InvoiceId AS CHDH,(SELECT InvoiceDate FROM InvoiceXS WHERE InvoiceId = InvoiceXSDetail.InvoiceId) AS CHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = InvoiceXSDetail.ProductId) AS ProductName,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId = InvoiceXOId ) AS KHDDBH,InvoiceXSDetailQuantity AS BCCHSL,InvoiceProductUnit AS DanWei,InvoiceXSDetailPrice AS DanJia,InvoiceAllowance AS ZheRang,ROUND(InvoiceXSDetailMoney,0) AS JinE,ROUND(InvoiceXSDetailTaxMoney,0)-ROUND(InvoiceXSDetailMoney,0) AS ShuiE,ROUND(InvoiceXSDetailTaxMoney,0) AS YingShou FROM InvoiceXSDetail WHERE 1 = 1 ");
StringBuilder sb_xs = new StringBuilder("SELECT xs.InvoiceId AS CHDH,xs.InvoiceDate AS CHRQ,xs.CustomerId,(SELECT ProductName FROM Product WHERE ProductId = xsd.ProductId) AS ProductName,xo.CustomerInvoiceXOId AS KHDDBH,xsd.InvoiceXSDetailQuantity AS BCCHSL,xsd.InvoiceProductUnit AS DanWei,xsd.InvoiceXSDetailPrice AS DanJia,xsd.InvoiceAllowance AS ZheRang,(case xs.TaxCaluType when 2 then ROUND(xsd.InvoiceXSDetailMoney/(1+xs.InvoiceTaxrate/100),0) else ROUND(xsd.InvoiceXSDetailMoney,0) end) AS JinE,(case xs.TaxCaluType when 2 then ROUND(xsd.InvoiceXSDetailMoney-xsd.InvoiceXSDetailMoney/(1+xs.InvoiceTaxrate/100),0) else ROUND(xsd.InvoiceXSDetailTaxMoney,0)-ROUND(xsd.InvoiceXSDetailMoney,0) end) as ShuiE,(case xs.TaxCaluType when 2 then ROUND(xsd.InvoiceXSDetailMoney,0) else ROUND(xsd.InvoiceXSDetailTaxMoney,0) end) AS YingShou ,ROUND((case when xsd.ExchangeRate=0 then 1 else xsd.ExchangeRate end)*xsd.InvoiceXSDetailTaxMoney,0) as TaibiTotal ,(case when xs.Currency='人民幣' then 'RMB' when xs.Currency='新台幣' then 'NTD' when xs.Currency='美金' then 'USD' when xs.Currency='歐元' then 'EURO' when xs.Currency='日圓' then 'JYP' end) as Currency,c.CustomerFullName FROM InvoiceXSDetail xsd left join InvoiceXS xs on xs.InvoiceId=xsd.InvoiceId left join InvoiceXO xo on xo.InvoiceId=xsd.InvoiceXOId left join Customer c on c.CustomerId=xs.CustomerId WHERE 1 = 1 ");
//StringBuilder sb_xt = new StringBuilder("SELECT xd.InvoiceId AS CHDH,x.InvoiceDate AS CHRQ,(select CustomerId from InvoiceXT where InvoiceId =xd.InvoiceId) as CustomerId,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = xd.ProductId ) AS ProductName,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId = InvoiceXOId) AS KHDDBH,InvoiceXTDetailQuantity AS BCCHSL,InvoiceProductUnit AS DanWei,InvoiceXTDetailPrice AS DanJia,InvoiceXTDetailDiscount AS ZheRang,ROUND((0-InvoiceXTDetailMoney1),0) AS JinE,ROUND((0-InvoiceXTDetailMoney1)*x.InvoiceTaxRate/100,0) AS ShuiE,ROUND((0-InvoiceXTDetailMoney1)*(1+x.InvoiceTaxRate/100),0) AS YingShou FROM InvoiceXTDetail xd left join InvoiceXT x on xd.InvoiceId=x.InvoiceId WHERE 1 = 1 ");
//时间日期
sb_xs.Append(" AND xs.InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "'");
//sb_xt.Append(" AND xd.InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
//预交日期
if (Yjri1 != global::Helper.DateTimeParse.NullDate && Yjri2 != global::Helper.DateTimeParse.EndDate)
{
sb_xs.Append(" AND xo.InvoiceYjrq BETWEEN '" + Yjri1.ToString("yyyy-MM-dd") + "' AND '" + Yjri2.Date.AddDays(1).ToString("yyyy-MM-dd") + "'");
}
//客户
if (Customer1 != null && Customer2 != null)
{
sb_xs.Append(" AND xs.CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "')");
//sb_xt.Append(" AND xd.InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "'))");
}
if (startEmp != null && endEmp != null)
{
sb_xs.Append(" and xs.Employee0Id in (select EmployeeId from Employee where IDNo between '" + startEmp.IDNo + "' and '" + endEmp.IDNo + "')");
}
//头编号
if (!string.IsNullOrEmpty(XOId1) && !string.IsNullOrEmpty(XOId2))
{
sb_xs.Append(" AND xs.InvoiceId BETWEEN '" + XOId1 + "' AND '" + XOId2 + "'");
}
//客户订单编号
if (!string.IsNullOrEmpty(CusXOId))
{
sb_xs.Append(" AND xo.CustomerInvoiceXOId = '" + CusXOId + "'");
//sb_xt.Append(" AND xd.InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = '" + CusXOId + "')");
}
//商品
if (Product != null && Product2 != null)
{
sb_xs.Append(" AND xsd.ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "')");
//sb_xt.Append(" AND xd.ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "')");
}
//商品編號 2018年12月28日17:39:40
if (!string.IsNullOrEmpty(product_Id))
{
sb_xs.Append(" AND xsd.ProductId IN (SELECT Product.ProductId FROM Product WHERE Id ='" + product_Id + "')");
//sb_xt.Append(" AND xd.ProductId IN (SELECT Product.ProductId FROM Product WHERE Id ='" + product_Id + "')");
}
//特殊 只在应收账款明细表使用，其他引用 传递 null
if (isSpecial != null)
{
if ((bool)isSpecial)
{
sb_xs.Append(" AND xs.Special='" + 1 + "'");
}
else
{
sb_xs.Append(" AND xs.Special='" + 0 + "' or xs.Special is null");
}
}
if (!string.IsNullOrEmpty(productCategoryId))
{
sb_xs.Append(" AND xsd.ProductId IN (SELECT Product.ProductId FROM Product WHERE ProductCategoryId ='" + productCategoryId + "')");
//sb_xt.Append(" AND xd.ProductId IN (SELECT Product.ProductId FROM Product WHERE ProductCategoryId ='" + productCategoryId + "')");
}
//赠送的不算做应收
//sb_xs.Append(" AND InvoiceXODetailId not in (select InvoiceXODetailId from InvoiceXODetail where Islargess=1)");
//sb_xt.Append(" AND xd.InvoiceXODetailId not in (select InvoiceXODetailId from InvoiceXODetail where Islargess=1)");
//幣別
if (!string.IsNullOrEmpty(currency) && currency != "全部")
{
sb_xs.Append(" and xs.Currency = '" + currency + "' ");
}
//string sql = sb_xs.ToString() + " UNION ALL " + sb_xt.ToString() + " order by CHDH,CHRQ";
string sql = sb_xs.ToString() + " order by CustomerId,CHDH,CHRQ";
using (SqlConnection con = new SqlConnection(sqlmapper.DataSource.ConnectionString))
{
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
sda.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
}
return(null);
}
