protected override void MoveLast()
{
if (this._supplier == null)
{
this._supplier = this.supplierManager.GetLast();
}
}
public EditForm(Model.SupplierCategory SupplierCategory)
: this()
{
this._supplier = new Book.Model.Supplier();
this._supplier.SupplierCategory = SupplierCategory;
this.flag = 1;
}
public IList <Book.Model.SupplierContact> Select(Book.Model.Supplier supplier)
{
if (supplier == null)
{
return((IList <Book.Model.SupplierContact>) new List <Model.SupplierContact>());
}
return(sqlmapper.QueryForList <Model.SupplierContact>("SupplierContact.selectbysupplier", supplier.SupplierId));
}
protected override void MoveLast()
{
//if (this._supplier == null)
// this._supplier = this.supplierManager.GetLast();
if (this.flag == 1)
{
this.flag = 0; return;
}
this._supplier = this.supplierManager.GetLast();
}
protected override void MovePrev()
{
Model.Supplier _supplier = this.supplierManager.GetPrev(this._supplier);
if (_supplier == null)
{
throw new InvalidOperationException(Properties.Resources.ErrorNoMoreRows);
}
this._supplier = _supplier;
}
public EditForm(Book.Model.Supplier cmpy)
: this()
{
//if(cmpy == null)
// throw new ArithmeticException();
this._supplier = cmpy;
// oldId = cmpy.SupplierId;
this.action = "update";
if (this._supplier != null)
{
if (this._supplier.SupplierCategory != null)
{
supplierCategory = this._supplier.SupplierCategory.Id;
}
}
}
public EditForm(Book.Model.Supplier cmpy, string action)
: this()
{
this._supplier = cmpy;
this.action = action;
if (this.action == "update" || this.action == "view")
{
if (this._supplier != null)
{
if (this._supplier.SupplierCategory != null)
{
supplierCategory = this._supplier.SupplierCategory.Id;
}
}
}
}
protected override void Delete()
{
if (this._supplier == null)
{
return;
}
if (MessageBox.Show(Properties.Resources.ConfirmToDelete, this.Text, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
{
return;
}
this.supplierManager.Delete(this._supplier.SupplierId);
this._supplier = this.supplierManager.GetNext(this._supplier);
if (this._supplier == null)
{
this._supplier = this.supplierManager.GetLast();
}
}
/// <summary>
/// 用于委外加工入库——搜索
/// </summary>
/// <param name="startdate"></param>
/// <param name="enddate"></param>
/// <param name="supper1"></param>
/// <param name="supper2"></param>
/// <param name="ProduceOtherCompactId1"></param>
/// <param name="ProduceOtherCompactId2"></param>
/// <param name="startPro"></param>
/// <param name="endPro"></param>
/// <param name="invouceCusidStart"></param>
/// <param name="invouceCusidEnd"></param>
/// <returns></returns>
public IList <Model.ProduceOtherInDepotDetail> SelectByCondition(DateTime startdate, DateTime enddate, Book.Model.Supplier supper1, Book.Model.Supplier supper2, string ProduceOtherCompactId1, string ProduceOtherCompactId2, Book.Model.Product startPro, Book.Model.Product endPro, string invouceCusidStart, string invouceCusidEnd)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT po.ProduceOtherInDepotId,po.ProduceOtherInDepotDate,pc.ProduceOtherCompactId,p.ProductName,pod.ProductUnit,pod.ProduceQuantity,pod.ProduceTransferQuantity,pod.ProduceInDepotQuantity,s.SupplierShortName,pod.InvoiceCusId,pc.OtherCompactCount,pc.JiaoQi FROM ProduceOtherInDepotDetail pod LEFT JOIN ProduceOtherInDepot po ON po.ProduceOtherInDepotId = pod.ProduceOtherInDepotId LEFT JOIN product p ON p.ProductId = pod.ProductId LEFT JOIN Supplier s ON s.SupplierId = po.SupplierId LEFT JOIN ProduceOtherCompactDetail pc ON pod.ProduceOtherCompactDetailId=pc.OtherCompactDetailId WHERE po.ProduceOtherInDepotDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
if (supper1 != null || supper2 != null)
{
if (supper1 != null && supper2 != null)
{
sql.Append(" and s.Id between '" + supper1.Id + "' and '" + supper2.Id + "' ");
}
else
{
sql.Append(" and s.Id='" + (supper1 == null ? supper2.Id : supper1.Id) + "'");
}
}
if (!string.IsNullOrEmpty(ProduceOtherCompactId1) || !string.IsNullOrEmpty(ProduceOtherCompactId2))
{
if (!string.IsNullOrEmpty(ProduceOtherCompactId1) && !string.IsNullOrEmpty(ProduceOtherCompactId2))
{
sql.Append(" and pod.ProduceOtherCompactId between '" + ProduceOtherCompactId2 + "' and '" + ProduceOtherCompactId2 + "' ");
}
else
{
sql.Append(" AND pod.ProduceOtherCompactId='" + (string.IsNullOrEmpty(ProduceOtherCompactId1) ? ProduceOtherCompactId2 : ProduceOtherCompactId1) + "'");
}
}
if (startPro != null || endPro != null)
{
if (startPro != null && endPro != null)
{
sql.Append(" and pod.ProductId in(select productId from product where ProductName between '" + startPro.ProductName + "' and '" + endPro.ProductName + "') ");
}
else
{
sql.Append(" AND pod.ProductId IN (SELECT ProductId FROM Product where ProductName between '" + (startPro == null ? endPro.ProductName : startPro.ProductName) + "')");
}
}
if (!string.IsNullOrEmpty(invouceCusidStart) || !string.IsNullOrEmpty(invouceCusidEnd))
{
if (!string.IsNullOrEmpty(invouceCusidStart) && !string.IsNullOrEmpty(invouceCusidEnd))
{
sql.Append(" AND pod.InvoiceCusId BETWEEN '" + invouceCusidStart + "' and '" + invouceCusidEnd + "'");
}
else
{
sql.Append(" AND pod.InvoiceCusId='" + (string.IsNullOrEmpty(invouceCusidStart) ? invouceCusidEnd : invouceCusidStart) + "'");
}
}
//SqlDataAdapter sda = new SqlDataAdapter(sql.ToString(), sqlmapper.DataSource.ConnectionString);
//DataTable dt = new DataTable();
//sda.Fill(dt);
//return dt;
return(this.DataReaderBind <Model.ProduceOtherInDepotDetail>(sql.ToString(), null, CommandType.Text));
}
private void repositoryItemHyperLinkEdit1_Click(object sender, EventArgs e)
{
this._supplier = this.bindingSourceSupplier.Current as Model.Supplier;
this.action = "view";
Refresh();
}
public IList <Book.Model.InvoiceJR> Select(DateTime startdate, DateTime enddate, Book.Model.Supplier supplier)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", startdate);
ht.Add("enddate", enddate);
string strSupplierid = string.Empty;
if (supplier != null)
{
strSupplierid = " AND SupplierId = '" + supplier.SupplierId + "'";
}
ht.Add("sql", strSupplierid);
return(sqlmapper.QueryForList <Model.InvoiceJR>("InvoiceJR.SelectByDateRangeAndSupplier", ht));
}
public override void Refresh()
{
if (this._supplier == null)
{
this._supplier = new Book.Model.Supplier();
this.action = "insert";
}
else
{
if (this.action == "view")
{
this._supplier = this.supplierManager.Get(this._supplier.SupplierId);
}
}
this.bindingSourceEmployee.DataSource = this.employeeManager.SelectOnActive();
this.bindingSourceSupplier.DataSource = this.supplierManager.Select();
if (this._supplier.SupplierCategory != null)
{
this.buttonEditSupplierCategory.EditValue = this._supplier.SupplierCategory;
}
this.TextEditId.Text = (string.IsNullOrEmpty(this._supplier.Id) ? this._supplier.SupplierId : this._supplier.Id);
this.TextEditSupplierFullName.Text = this._supplier.SupplierFullName;
this.TextEditSupplierShortName.Text = this._supplier.SupplierShortName;
this.memoEditRemarks.Text = this._supplier.Remark;
this.MemoEditSupplierRemarks.Text = this._supplier.SupplierRemark;
this.TextEditSupplierPhone1.Text = this._supplier.SupplierPhone1;
this.TextEditSupplierPhone2.Text = this._supplier.SupplierPhone2;
this.TextEditSupplierAddress.Text = this._supplier.CompanyAddress;
this.TextEditSupplierEmail.Text = this._supplier.Email;
this.TextEditSupplierManager.Text = this._supplier.SupplierManager;
this.TextEditSupplierMobile.Text = this._supplier.SupplierMobile;
this.TextEditSupplierNumber.Text = this._supplier.SupplierNumber;
this.TextEditSupplierFax.Text = this._supplier.SupplierFax;
this.TextEditPostCode.Text = this._supplier.PostCode;
this.textEditPayAddress.Text = this._supplier.PayAddress;
this.textEditFactoryAddress.Text = this._supplier.FactoryAddress;
if (this._supplier.PayMethod != null)
{
this.comboBoxPayMethod.Text = this._supplier.PayMethod.PayMethodName;
}
this.spinEditAdvancePatment.Value = (this._supplier.AdvancePatment == null ? 0 : decimal.Parse(this._supplier.AdvancePatment.ToString()));
this.spinEditPayDay.Value = (this._supplier.PayDay == null ? 0 : decimal.Parse(this._supplier.PayDay.ToString()));
this.newChooseContorlArea.EditValue = this._supplier.AreaCategory;
this.lookUpEditBusinessId.EditValue = this._supplier.EmployeeBusinessId;
this.newChooseContorlEmployeeChangeId.Text = this._supplier.EmployeeChangeId;
this.newChooseContorlEmployeeCreatorId.Text = this._supplier.EmployeeCreatorId;
// this.newChooseContorlTrade.EditValue = this._supplier.TradeCategory;
this.dateEditInsertTime.EditValue = this._supplier.InsertTime;
this.dateEditLastPayDate.EditValue = this._supplier.LastPayDate;
this.dateEditLastTransactionDate.EditValue = this._supplier.LastTransactionDate;
this.dateEditUpdateTime.EditValue = this._supplier.UpdateTime;
this.spinEditLastPayMoney.EditValue = this._supplier.LastPayMoney;
this.spinEditLastTransactionMoney.EditValue = this._supplier.LastTransactionMoney;
//this.spinEditLineOfCredit.EditValue = this._supplier.LineOfCredit;
this.spinEditPayableOwe.EditValue = this._supplier.PayableOwe;
this.comboBoxEditAbcCategory.Text = this._supplier.AbcCategory;
this.TextEditSupplierNetAddress.Text = this._supplier.NetAddress;
this.bindingSourceSupplierContact.DataSource = this._supplier.Contacts;
this.textEditContact.Text = this._supplier.SupplierContact;
SetTextEditReadOnly(true);
switch (this.action)
{
case "insert":
this.buttonEditSupplierCategory.Properties.Buttons[0].Enabled = true;
this.buttonEditSupplierCategory.Properties.ReadOnly = false;
this.dateEditInsertTime.DateTime = DateTime.Now;
this.TextEditId.Properties.ReadOnly = false;
this.TextEditSupplierAddress.Properties.ReadOnly = false;
this.TextEditSupplierFax.Properties.ReadOnly = false;
this.TextEditSupplierFullName.Properties.ReadOnly = false;
this.TextEditSupplierShortName.Properties.ReadOnly = false;
this.TextEditSupplierPhone1.Properties.ReadOnly = false;
this.MemoEditSupplierRemarks.Properties.ReadOnly = false;
this.TextEditSupplierEmail.Properties.ReadOnly = false;
this.TextEditSupplierManager.Properties.ReadOnly = false;
this.TextEditSupplierMobile.Properties.ReadOnly = false;
this.TextEditSupplierNumber.Properties.ReadOnly = false;
this.TextEditSupplierPhone2.Properties.ReadOnly = false;
this.TextEditSupplierNetAddress.Properties.ReadOnly = false;
this.TextEditPostCode.Properties.ReadOnly = false;
this.comboBoxPayMethod.Properties.ReadOnly = false;
this.spinEditAdvancePatment.Properties.ReadOnly = false;
this.spinEditPayDay.Properties.ReadOnly = false;
this.textEditPayAddress.Properties.ReadOnly = false;
this.textEditFactoryAddress.Properties.ReadOnly = false;
this.newChooseContorlArea.ButtonReadOnly = false;
this.newChooseContorlArea.ShowButton = true;
this.lookUpEditBusinessId.Properties.ReadOnly = false;
this.lookUpEditBusinessId.Properties.Buttons[0].Visible = true;
// this.newChooseContorlTrade.ButtonReadOnly = false;
// this.newChooseContorlTrade.ShowButton = true;
this.dateEditLastPayDate.Properties.ReadOnly = false;
this.dateEditLastPayDate.Properties.Buttons[0].Visible = true;
this.dateEditLastTransactionDate.Properties.ReadOnly = false;
this.dateEditLastTransactionDate.Properties.Buttons[0].Visible = true;
this.spinEditLastPayMoney.Properties.ReadOnly = false;
this.spinEditLastPayMoney.Properties.Buttons[0].Visible = true;
this.spinEditLastTransactionMoney.Properties.ReadOnly = false;
this.spinEditLastTransactionMoney.Properties.Buttons[0].Visible = true;
this.spinEditPayableOwe.Properties.ReadOnly = false;
this.spinEditPayableOwe.Properties.Buttons[0].Visible = true;
this.comboBoxEditAbcCategory.Properties.ReadOnly = false;
this.comboBoxEditAbcCategory.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.buttonEditSupplierCategory.Properties.Buttons[0].Enabled = true;
this.buttonEditSupplierCategory.Properties.ReadOnly = false;
this.dateEditInsertTime.DateTime = DateTime.Now;
this.dateEditUpdateTime.DateTime = DateTime.Now;
this.TextEditId.Properties.ReadOnly = false;
this.TextEditSupplierAddress.Properties.ReadOnly = false;
this.TextEditSupplierFax.Properties.ReadOnly = false;
this.TextEditSupplierFullName.Properties.ReadOnly = false;
this.TextEditSupplierShortName.Properties.ReadOnly = false;
this.TextEditSupplierPhone1.Properties.ReadOnly = false;
this.MemoEditSupplierRemarks.Properties.ReadOnly = false;
this.TextEditSupplierEmail.Properties.ReadOnly = false;
this.TextEditSupplierMobile.Properties.ReadOnly = false;
this.TextEditSupplierManager.Properties.ReadOnly = false;
this.TextEditSupplierNumber.Properties.ReadOnly = false;
this.TextEditSupplierPhone2.Properties.ReadOnly = false;
this.TextEditSupplierNetAddress.Properties.ReadOnly = false;
this.TextEditPostCode.Properties.ReadOnly = false;
this.comboBoxPayMethod.Properties.ReadOnly = false;
this.spinEditAdvancePatment.Properties.ReadOnly = false;
this.spinEditPayDay.Properties.ReadOnly = false;
this.textEditPayAddress.Properties.ReadOnly = false;
this.textEditFactoryAddress.Properties.ReadOnly = false;
this.newChooseContorlArea.ButtonReadOnly = false;
this.newChooseContorlArea.ShowButton = true;
this.lookUpEditBusinessId.Properties.ReadOnly = false;
this.lookUpEditBusinessId.Properties.Buttons[0].Visible = true;
// this.newChooseContorlTrade.ShowButton = true;
this.dateEditLastPayDate.Properties.ReadOnly = false;
this.dateEditLastPayDate.Properties.Buttons[0].Visible = true;
this.dateEditLastTransactionDate.Properties.ReadOnly = false;
this.dateEditLastTransactionDate.Properties.Buttons[0].Visible = true;
this.spinEditLastPayMoney.Properties.ReadOnly = false;
this.spinEditLastPayMoney.Properties.Buttons[0].Visible = true;
this.spinEditLastTransactionMoney.Properties.ReadOnly = false;
this.spinEditLastTransactionMoney.Properties.Buttons[0].Visible = true;
this.spinEditPayableOwe.Properties.ReadOnly = false;
this.spinEditPayableOwe.Properties.Buttons[0].Visible = true;
this.comboBoxEditAbcCategory.Properties.ReadOnly = false;
this.comboBoxEditAbcCategory.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.buttonEditSupplierCategory.Properties.Buttons[0].Enabled = false;
this.buttonEditSupplierCategory.Properties.ReadOnly = true;
this.TextEditId.Properties.ReadOnly = true;
this.TextEditSupplierAddress.Properties.ReadOnly = true;
this.TextEditSupplierFax.Properties.ReadOnly = true;
this.TextEditSupplierFullName.Properties.ReadOnly = true;
this.TextEditSupplierShortName.Properties.ReadOnly = true;
this.TextEditSupplierPhone1.Properties.ReadOnly = true;
this.MemoEditSupplierRemarks.Properties.ReadOnly = true;
this.TextEditSupplierEmail.Properties.ReadOnly = true;
this.TextEditSupplierMobile.Properties.ReadOnly = true;
this.TextEditSupplierManager.Properties.ReadOnly = true;
this.TextEditSupplierNumber.Properties.ReadOnly = true;
this.TextEditSupplierPhone2.Properties.ReadOnly = true;
this.TextEditSupplierNetAddress.Properties.ReadOnly = true;
this.TextEditPostCode.Properties.ReadOnly = true;
this.comboBoxPayMethod.Properties.ReadOnly = true;
this.spinEditAdvancePatment.Properties.ReadOnly = true;
this.spinEditPayDay.Properties.ReadOnly = true;
this.textEditPayAddress.Properties.ReadOnly = true;
this.textEditFactoryAddress.Properties.ReadOnly = true;
this.newChooseContorlArea.ButtonReadOnly = true;
this.newChooseContorlArea.ShowButton = false;
this.lookUpEditBusinessId.Properties.ReadOnly = true;
this.lookUpEditBusinessId.Properties.Buttons[0].Visible = false;
// this.newChooseContorlTrade.ButtonReadOnly = true;
this.dateEditLastPayDate.Properties.ReadOnly = true;
this.dateEditLastPayDate.Properties.Buttons[0].Visible = false;
this.dateEditLastTransactionDate.Properties.ReadOnly = true;
this.dateEditLastTransactionDate.Properties.Buttons[0].Visible = false;
this.spinEditLastPayMoney.Properties.ReadOnly = true;
this.spinEditLastPayMoney.Properties.Buttons[0].Visible = false;
this.spinEditLastTransactionMoney.Properties.ReadOnly = true;
this.spinEditLastTransactionMoney.Properties.Buttons[0].Visible = false;
this.spinEditPayableOwe.Properties.ReadOnly = true;
this.spinEditPayableOwe.Properties.Buttons[0].Visible = false;
this.comboBoxEditAbcCategory.Properties.ReadOnly = true;
this.comboBoxEditAbcCategory.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();
}
public IList <Model.ProduceOtherInDepotDetail> SelectByCondition(DateTime startdate, DateTime enddate, Book.Model.Supplier supper1, Book.Model.Supplier supper2, string ProduceOtherCompactId1, string ProduceOtherCompactId2, Book.Model.Product startPro, Book.Model.Product endPro, string invouceCusidStart, string invouceCusidEnd)
{
return(accessor.SelectByCondition(startdate, enddate, supper1, supper2, ProduceOtherCompactId1, ProduceOtherCompactId2, startPro, endPro, invouceCusidStart, invouceCusidEnd));
}
protected override void MoveFirst()
{
this._supplier = this.supplierManager.GetFirst();
}
public IList <Book.Model.ProduceOtherInDepot> SelectByCondition(DateTime startdate, DateTime enddate, Book.Model.Supplier supper1, Book.Model.Supplier supper2, string ProduceOtherCompactId1, string ProduceOtherCompactId2, Book.Model.Product startPro, Book.Model.Product endPro, string invouceCusidStart, string invouceCusidEnd)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", startdate);
ht.Add("enddate", enddate);
StringBuilder sql = new StringBuilder();
if (supper1 != null && supper2 != null)
{
sql.Append(" and SupplierId IN (SELECT SupplierId FROM Supplier WHERE id between '" + supper1.Id + "' and '" + supper2.Id + "' ) ");
}
if (!string.IsNullOrEmpty(ProduceOtherCompactId1) && !string.IsNullOrEmpty(ProduceOtherCompactId2))
{
sql.Append(" and ProduceOtherCompactId between '" + ProduceOtherCompactId2 + "' and '" + ProduceOtherCompactId2 + "' ");
}
if (startPro != null && endPro != null)
{
sql.Append(" and ProduceOtherInDepotId in(select ProduceOtherInDepotId from ProduceOtherInDepotdetail where productId in(select productId from product where ProductName between '" + startPro.ProductName + "' and '" + endPro.ProductName + "')) ");
}
if (!string.IsNullOrEmpty(invouceCusidStart) || !string.IsNullOrEmpty(invouceCusidEnd))
{
if (!string.IsNullOrEmpty(invouceCusidStart) && !string.IsNullOrEmpty(invouceCusidEnd))
{
sql.Append("AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepotDetail WHERE ProduceOtherCompactDetailId IN (SELECT OtherCompactDetailId FROM ProduceOtherCompactDetail WHERE CustomInvoiceXOId BETWEEN '" + invouceCusidStart + "' and '" + invouceCusidEnd + "'))");
}
else
{
sql.Append("AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepotDetail WHERE ProduceOtherCompactDetailId IN (SELECT OtherCompactDetailId FROM ProduceOtherCompactDetail WHERE CustomInvoiceXOId = '" + (string.IsNullOrEmpty(invouceCusidStart) ? invouceCusidEnd : invouceCusidStart) + "'))");
}
}
ht.Add("sql", sql.ToString());
return(sqlmapper.QueryForList <Book.Model.ProduceOtherInDepot>("ProduceOtherInDepot.selectByDateWhere", ht));
}
//应付账款明细表
public DataTable SelectByConditionCOBiao(DateTime?startdate, DateTime?enddate, DateTime JHstartdate, DateTime JHenddate, DateTime?FKStartDate, DateTime?FKEndDate, Book.Model.Supplier startSupplier, Book.Model.Supplier endSupplier, Book.Model.Product productStart, Book.Model.Product productEnd, string coidStart, string coidEnd, string CusXOid, Book.Model.Employee empstart, Book.Model.Employee empend)
{
StringBuilder sb_cg = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceDate FROM InvoiceCG WHERE InvoiceId = InvoiceCGDetail.InvoiceId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = InvoiceCGDetail.ProductId) AS ProductName,InvoiceCGDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCGDetailPrice,0) as decimal(18,4)) AS DanJia,ISNULL(InvoiceCGDetailMoney,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,isnull(InvoiceCGDetailTax,0) AS ShuiE,ISNULL(InvoiceCGDetailMoney,0)+isnull(InvoiceCGDetailTax,0) as Total FROM InvoiceCGDetail WHERE 1=1 "); //进货
//StringBuilder sb_ct = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceDate FROM InvoiceCT WHERE InvoiceId = InvoiceCTDetail.InvoiceId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = InvoiceCTDetail.ProductId) AS ProductName,InvoiceCTDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCTDetailPrice,0) as decimal(18,4)) AS DanJia,ISNULL((0-InvoiceCTDetailMoney1) ,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 AS ShuiE,ISNULL((0-InvoiceCTDetailMoney1) ,0)+ ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 as Total FROM InvoiceCTDetail WHERE 1=1 "); //退货
StringBuilder sb_ct = new StringBuilder("SELECT ctd.InvoiceId AS JHDN,ct.InvoiceDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE ProductId = ctd.ProductId) AS ProductName,InvoiceCTDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCTDetailPrice,0) as decimal(18,4)) AS DanJia,ISNULL((0-InvoiceCTDetailMoney1) ,0) AS JinE,ctd.InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,ISNULL((0-InvoiceCTDetailMoney1) ,0)*((case ct.InvoiceKslb when '外加' then ct.InvoiceTaxRate else 0 end)/100) AS ShuiE,ISNULL((0-InvoiceCTDetailMoney1) ,0)*(1+((case ct.InvoiceKslb when '外加' then ct.InvoiceTaxRate else 0 end)/100)) as Total FROM InvoiceCTDetail ctd left join InvoiceCT ct on ctd.InvoiceId=ct.InvoiceId WHERE 1=1 ");
//StringBuilder sb_ww = new StringBuilder("SELECT ProduceOtherCompactId AS JHDN,(SELECT ProduceOtherCompactDate FROM ProduceOtherCompact WHERE ProduceOtherCompactId = ProduceOtherCompactDetail.ProduceOtherCompactId) AS JHRQ,(SELECT ProductName FROM Product WHERE ProductId = ProduceOtherCompactDetail.ProductId) AS ProudctName,OtherCompactCount AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(OtherCompactPrice,0) as decimal(10,4)) AS DanJia,ISNULL(OtherCompactMoney,0) AS JinE,'' AS CGorWWDanHao FROM ProduceOtherCompactDetail WHERE 1=1 "); //委外加工
//StringBuilder sb_sr = new StringBuilder("SELECT ProduceInDepotId AS JHDN,(SELECT ProduceInDepotDate FROM ProduceInDepot WHERE ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = ProduceInDepotDetail.ProductId) AS ProudctName,CheckOutSum AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProduceInDepotPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceMoney,0) AS JinE,PronoteHeaderId AS CGorWWDanHao,Inumber AS Inumber,isnull(ProduceMoney,0)*0.05 AS ShuiE,isnull(ProduceMoney,0)*0.05+ isnull(ProduceMoney,0) as Total FROM ProduceInDepotDetail WHERE 1 = 1 "); //生产入库
StringBuilder sb_sr = new StringBuilder("SELECT pid.ProduceInDepotId AS JHDN,pi.ProduceInDepotDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = pid.ProductId) AS ProudctName,CheckOutSum AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProduceInDepotPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceMoney,0) AS JinE,PronoteHeaderId AS CGorWWDanHao,Inumber AS Inumber,isnull(ProduceMoney,0)*(cast(ISNULL(pi.InvoiceTaxrate,0) as decimal(18,2))/100) AS ShuiE,isnull(ProduceMoney,0)*(1+(cast(ISNULL(pi.InvoiceTaxrate,0) as decimal(18,2))/100)) as Total FROM ProduceInDepotDetail pid left join ProduceInDepot pi on pid.ProduceInDepotId=pi.ProduceInDepotId WHERE 1 = 1 "); //生产入库
//StringBuilder sb_wr = new StringBuilder("SELECT ProduceOtherInDepotId AS JHDN,(SELECT ProduceOtherInDepotDate FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDetail.ProduceOtherInDepotId = ProduceOtherInDepot.ProduceOtherInDepotId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = ProduceOtherInDepotDetail.ProductId) AS ProudctName,isnull(ProduceQuantity,0) AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS JinE,ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*0.05 AS ShuiE,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))+ isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*0.05 as Total FROM ProduceOtherInDepotDetail WHERE 1 = 1");//委外入库
StringBuilder sb_wr = new StringBuilder("SELECT pod.ProduceOtherInDepotId AS JHDN,po.ProduceOtherInDepotDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId = pod.ProductId) AS ProudctName,isnull(ProduceQuantity,0) AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS DanJia,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4)) AS JinE,pod.ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*cast(ISNULL(InvoiceTaxrate,0) as decimal(18,2))/100 AS ShuiE,isnull(ProduceQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(18,4))*(1+cast(ISNULL(InvoiceTaxrate,0) as decimal(18,2))/100) as Total FROM ProduceOtherInDepotDetail pod left join ProduceOtherInDepot po on pod.ProduceOtherInDepotId=po.ProduceOtherInDepotId WHERE 1 = 1"); //2020年12月10日23:20:42：委外入库,改left join
//StringBuilder sb_wt = new StringBuilder("SELECT ProduceOtherReturnMaterialId AS JHDN,(SELECT ProduceOtherReturnMaterialDate FROM ProduceOtherReturnMaterial WHERE ProduceOtherReturnMaterial.ProduceOtherReturnMaterialId=pd.ProduceOtherReturnMaterialId) AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId=pd.ProductId) AS ProductName,Quantity AS JHSL,ProductUnit AS ProductUnit,cast(isnull(Price,0) AS decimal(18,4)) AS DanJia,isnull((0-Amount),0) AS JinE,ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull((0-Amount),0)*0.05 AS ShuiE,isnull((0-Amount),0)*0.05+isnull((0-Amount),0) AS Total FROM ProduceOtherReturnDetail pd WHERE 1=1");//委外退库
StringBuilder sb_wt = new StringBuilder("SELECT pd.ProduceOtherReturnMaterialId AS JHDN,pm.ProduceOtherReturnMaterialDate AS JHRQ,(SELECT ProductName+'{'+ISNULL(CustomerProductName,'')+'}' FROM Product WHERE Product.ProductId=pd.ProductId) AS ProductName,Quantity AS JHSL,ProductUnit AS ProductUnit,cast(isnull(Price,0) AS decimal(18,4)) AS DanJia,isnull((0-Amount),0) AS JinE,ProduceOtherCompactId AS CGorWWDanHao,'' AS Inumber,isnull((0-Amount),0)*(CAST(ISNULL(pm.InvoiceTaxrate,0) as decimal(18,2))/100) AS ShuiE,isnull((0-Amount),0)*(1+(CAST(ISNULL(pm.InvoiceTaxrate,0) as decimal(18,2))/100)) AS Total FROM ProduceOtherReturnDetail pd left join ProduceOtherReturnMaterial pm on pd.ProduceOtherReturnMaterialId=pm.ProduceOtherReturnMaterialId WHERE 1=1 "); //委外退库
StringBuilder sb_qf = new StringBuilder("SELECT AcOtherShouldPaymentId AS JHDN,(SELECT AcOtherShouldPaymentDate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId) AS JHRQ,LoanName AS ProudctName,AcQuantity AS JHSL,'' AS ProductUnit,cast(ISNULL(AcItemPrice,0) as decimal(18,4)) AS DanJia,AcMoney AS JinE,'' AS CGorWWDanHao,'' AS Inumber,CONVERT(float,(SELECT TOP 1 InvoiceTaxrate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS ShuiE,AcMoney+CONVERT(float,(SELECT TOP 1 InvoiceTaxrate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS Total FROM AcOtherShouldPaymentDetail WHERE 1 = 1 "); //其它应付款
//日期
if (startdate.HasValue && enddate.HasValue)
{
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "' )");
//sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE InvoiceDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_ct.Append(" AND ct.InvoiceDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
//sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE ProduceOtherCompactDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
//sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE ProduceInDepotDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_sr.Append(" AND pi.ProduceInDepotDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
//sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_wr.Append(" AND po.ProduceOtherInDepotDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AcOtherShouldPaymentDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
//sb_wt.Append(" AND ProduceOtherReturnMaterialId IN (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE ProduceOtherReturnMaterialDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_wt.Append(" AND pm.ProduceOtherReturnMaterialDate BETWEEN '" + startdate.Value.ToString("yyyy-MM-dd") + "' AND '" + enddate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//交货日期
if (JHstartdate != global::Helper.DateTimeParse.NullDate.Date && JHenddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddSeconds(-1))
{
//使用采购进货单-- 进货日期
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceHisDate BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
//使用采购订单---交货日期
//sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCO.InvoiceYjrq BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
}
//付款日期
if (FKStartDate.HasValue && FKEndDate.HasValue)
{
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoicePaymentDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AdvancePayableDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
//sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_ct.Append(" AND ct.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
//sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_sr.Append(" AND pi.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
//sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_wr.Append(" AND po.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
//sb_wt.Append(" AND ProduceOtherReturnMaterialId in (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "')");
sb_wt.Append(" AND pm.PayDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
//供应商
if (startSupplier != null && endSupplier != null)
{
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
//sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceCT.InvoiceId FROM InvoiceCT WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
sb_ct.Append(" AND ct.SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "')");
//sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
//sb_sr.Append(" AND (SELECT WorkHouse.Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceInDepot.WorkHouseId FROM ProduceInDepot WHERE ProduceInDepotDetail.ProduceInDepotId = ProduceInDepot.ProduceInDepotId)) IN ('" + startSupplier.SupplierShortName.Trim() + "','" + endSupplier.SupplierShortName.Trim() + "')");
//sb_sr.Append(" and EXISTS (select SupplierFullName from supplier where SupplierFullName like (SELECT '%'+WorkHouse.Workhousename+'%' FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceInDepot.WorkHouseId FROM ProduceInDepot WHERE ProduceInDepotDetail.ProduceInDepotId = ProduceInDepot.ProduceInDepotId)) AND SupplierFullName in ('" + startSupplier.SupplierFullName.Trim() + "','" + endSupplier.SupplierFullName.Trim() + "'))");
sb_sr.Append(" and (EXISTS (select SupplierFullName from supplier where SupplierFullName like (SELECT '%'+WorkHouse.Workhousename+'%' FROM WorkHouse WHERE WorkHouse.WorkHouseId = pi.WorkHouseId) AND SupplierFullName in ('" + startSupplier.SupplierFullName.Trim() + "','" + endSupplier.SupplierFullName.Trim() + "')) or EXISTS (SELECT WorkHouse.Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = pi.WorkHouseId and (Workhousename like '%" + startSupplier.SupplierFullName.Trim() + "%' or Workhousename like '%" + endSupplier.SupplierFullName.Trim() + "%')))");
//sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
sb_wr.Append(" AND po.SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "')");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "'AND '" + endSupplier.Id + "'))");
//sb_wt.Append(" AND ProduceOtherReturnMaterialId IN (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
sb_wt.Append(" AND pm.SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "')");
}
//商品
if (productStart != null && productEnd != null)
{
sb_cg.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
sb_ct.Append(" AND ctd.ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
//sb_ww.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
sb_sr.Append(" AND pid.ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
sb_wr.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
sb_wt.Append(" AND pd.ProductId IN (SELECT ProductId FROM product WHERE id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
}
//采购单号
if (!string.IsNullOrEmpty(coidStart) && !string.IsNullOrEmpty(coidEnd))
{
sb_cg.Append(" AND InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
sb_ct.Append(" AND ctd.InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
}
//客户订单编号
if (!string.IsNullOrEmpty(CusXOid))
{
sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
sb_ct.Append(" AND ctd.InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
//sb_ww.Append(" AND CustomInvoiceXOId LIKE '%" + CusXOid + "%'");
}
//员工
if (empstart != null && empend != null)
{
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE Employee0Id IN (SELECT Employee.EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
//sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE Employee0Id IN (SELECT Employee0Id FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_ct.Append(" AND ct.Employee0Id IN (SELECT Employee0Id FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
//sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
//sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_sr.Append(" AND pi.Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
//sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_wr.Append(" AND po.Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE Employee1Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
//sb_wt.Append(" AND ProduceOtherReturnMaterialId IN (SELECT ProduceOtherReturnMaterialId FROM ProduceOtherReturnMaterial WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_wt.Append(" AND pm.Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "')");
}
string sql = sb_cg.ToString() + " UNION ALL " + sb_ct.ToString() + " UNION ALL " + sb_sr.ToString() + " UNION ALL " + sb_wr.ToString() + " UNION ALL " + sb_qf.ToString() + " UNION ALL " + sb_wt.ToString();
sql = " SELECT * FROM ( " + sql + " ) a ORDER BY a.JHRQ ASC";
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 <Model.InvoiceCGDetail> SelectByConditionCO(DateTime startdate, DateTime enddate, DateTime JHstartdate, DateTime JHenddate, DateTime?FKStartDate, DateTime?FKEndDate, Book.Model.Supplier startSupplier, Book.Model.Supplier endSupplier, Book.Model.Product productStart, Book.Model.Product productEnd, string coidStart, string coidEnd, string CusXOid, Book.Model.Employee empstart, Book.Model.Employee empend)
{
StringBuilder sb = new StringBuilder();
if (startdate != global::Helper.DateTimeParse.NullDate.Date && enddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddDays(-1))
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
}
if (!string.IsNullOrEmpty(coidStart) && !string.IsNullOrEmpty(coidEnd))
{
sb.Append(" AND InvoiceCOId WHERE BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
}
if (JHstartdate != global::Helper.DateTimeParse.NullDate.Date && JHenddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddSeconds(-1))
{
//使用进货日期
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceHisDate BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
//使用采购订单--交货日期
//sb.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCO.InvoiceYjrq BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.ToString("yyyy-MM-dd") + "')");
}
if (FKStartDate.HasValue && FKEndDate.HasValue)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoicePaymentDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "')");
}
if (!string.IsNullOrEmpty(CusXOid))
{
sb.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
}
if (startSupplier != null && endSupplier != null)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
}
if (productStart != null && productEnd != null)
{
sb.Append(" AND ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
}
if (empstart != null && empend != null)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE Employee0Id IN (SELECT Employee.EmployeeId FROM Employee WHERE IDNo BETWEEN '' AND ''))");
}
return(sqlmapper.QueryForList <Model.InvoiceCGDetail>("InvoiceCGDetail.SelectByConditionCO", sb.ToString()));
}
public IList <Book.Model.InvoiceJRDetail> Select(Book.Model.Supplier supper)
{
return(sqlmapper.QueryForList <Model.InvoiceJRDetail>("InvoiceJRDetail.select_by_supperId_insert", supper.SupplierId));
}
public void Delete(Book.Model.Supplier supplier)
{
sqlmapper.Delete("SupplierContact.delete_by_supplierid", supplier.SupplierId);
}
protected override void AddNew()
{
this._supplier = new Model.Supplier();
this._supplier.TaxCaluType = 1;
}
public DataTable SelectByConditionCOBiao(DateTime startdate, DateTime enddate, DateTime JHstartdate, DateTime JHenddate, DateTime?FKStartDate, DateTime?FKEndDate, Book.Model.Supplier startSupplier, Book.Model.Supplier endSupplier, Book.Model.Product productStart, Book.Model.Product productEnd, string coidStart, string coidEnd, string CusXOid, Book.Model.Employee empstart, Book.Model.Employee empend)
{
StringBuilder sb_cg = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceHisDate FROM InvoiceCG WHERE InvoiceId = InvoiceCGDetail.InvoiceId) AS JHRQ,(SELECT ProductName FROM Product WHERE Product.ProductId = InvoiceCGDetail.ProductId) AS ProductName,InvoiceCGDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCGDetailPrice,0) as decimal(10,3)) AS DanJia,ISNULL(InvoiceCGDetailMoney,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,isnull(InvoiceCGDetailTax,0) AS ShuiE,ISNULL(InvoiceCGDetailMoney,0)+isnull(InvoiceCGDetailTax,0) as Total FROM InvoiceCGDetail WHERE 1=1 "); //进货
StringBuilder sb_ct = new StringBuilder("SELECT InvoiceId AS JHDN,(SELECT InvoiceDate FROM InvoiceCT WHERE InvoiceId = InvoiceCTDetail.InvoiceId) AS JHRQ,(SELECT ProductName FROM Product WHERE ProductId = InvoiceCTDetail.ProductId) AS ProductName,InvoiceCTDetailQuantity AS JHSL,InvoiceProductUnit AS ProductUnit,cast(ISNULL(InvoiceCTDetailPrice,0) as decimal(10,3)) AS DanJia,ISNULL((0-InvoiceCTDetailMoney1) ,0) AS JinE,InvoiceCOId AS CGorWWDanHao,Inumber AS Inumber,ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 AS ShuiE,ISNULL((0-InvoiceCTDetailMoney1) ,0)+ ISNULL((0-InvoiceCTDetailMoney1) ,0)*0.05 as Total FROM InvoiceCTDetail WHERE 1=1 "); //退货
//StringBuilder sb_ww = new StringBuilder("SELECT ProduceOtherCompactId AS JHDN,(SELECT ProduceOtherCompactDate FROM ProduceOtherCompact WHERE ProduceOtherCompactId = ProduceOtherCompactDetail.ProduceOtherCompactId) AS JHRQ,(SELECT ProductName FROM Product WHERE ProductId = ProduceOtherCompactDetail.ProductId) AS ProudctName,OtherCompactCount AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(OtherCompactPrice,0) as decimal(10,3)) AS DanJia,ISNULL(OtherCompactMoney,0) AS JinE,'' AS CGorWWDanHao FROM ProduceOtherCompactDetail WHERE 1=1 "); //委外加工
StringBuilder sb_sr = new StringBuilder("SELECT ProduceInDepotId AS JHDN,(SELECT ProduceInDepotDate FROM ProduceInDepot WHERE ProduceInDepot.ProduceInDepotId = ProduceInDepotDetail.ProduceInDepotId) AS JHRQ,(SELECT ProductName FROM Product WHERE Product.ProductId = ProduceInDepotDetail.ProductId) AS ProudctName,CheckOutSum AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProduceInDepotPrice,0) as decimal(10,3)) AS DanJia,isnull(ProduceMoney,0) AS JinE,'' AS CGorWWDanHao,Inumber AS Inumber,isnull(ProduceMoney,0)*0.05 AS ShuiE,isnull(ProduceMoney,0)*0.05+ isnull(ProduceMoney,0) as Total FROM ProduceInDepotDetail WHERE 1 = 1 "); //生产入库
StringBuilder sb_wr = new StringBuilder("SELECT ProduceOtherInDepotId AS JHDN,(SELECT ProduceOtherInDepotDate FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDetail.ProduceOtherInDepotId = ProduceOtherInDepot.ProduceOtherInDepotId) AS JHRQ,(SELECT ProductName FROM Product WHERE Product.ProductId = ProduceOtherInDepotDetail.ProductId) AS ProudctName,isnull(ProduceTransferQuantity,0) AS JHSL,ProductUnit AS ProductUnit,cast(ISNULL(ProcessPrice,0) as decimal(10,3)) AS DanJia,isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3)) AS JinE,'' AS CGorWWDanHao,'' AS Inumber,isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3))*0.05 AS ShuiE,isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3))+ isnull(ProduceTransferQuantity,0)*cast(ISNULL(ProcessPrice,0) as decimal(10,3))*0.05 as Total FROM ProduceOtherInDepotDetail WHERE 1 = 1"); //委外入库
StringBuilder sb_qf = new StringBuilder("SELECT AcOtherShouldPaymentId AS JHDN,(SELECT AcOtherShouldPaymentDate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId) AS JHRQ,LoanName AS ProudctName,AcQuantity AS JHSL,'' AS ProductUnit,cast(ISNULL(AcItemPrice,0) as decimal(10,3)) AS DanJia,AcMoney AS JinE,'' AS CGorWWDanHao,'' AS Inumber,CONVERT(float,(SELECT TOP 1 InvoiceTaxrate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS ShuiE,AcMoney+CONVERT(float,(SELECT TOP 1 InvoiceTaxrate FROM AcOtherShouldPayment WHERE AcOtherShouldPayment.AcOtherShouldPaymentId = AcOtherShouldPaymentDetail.AcOtherShouldPaymentId))*AcMoney / 100 AS Total FROM AcOtherShouldPaymentDetail WHERE 1 = 1"); //其它应付款
//日期
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "' )");
sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE InvoiceDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
//sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE ProduceOtherCompactDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE ProduceInDepotDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE ProduceOtherInDepotDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AcOtherShouldPaymentDate BETWEEN '" + startdate.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
//交货日期
if (JHstartdate != global::Helper.DateTimeParse.NullDate.Date && JHenddate != global::Helper.DateTimeParse.EndDate.Date.AddDays(1).AddSeconds(-1))
{
//使用采购进货单-- 进货日期
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoiceHisDate BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.AddDays(1).ToString("yyyy-MM-dd") + "')");
//使用采购订单---交货日期
//sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCO.InvoiceYjrq BETWEEN '" + JHstartdate.ToString("yyyy-MM-dd") + "' AND '" + JHenddate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')");
}
//付款日期
if (FKStartDate.HasValue && FKEndDate.HasValue)
{
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE InvoicePaymentDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "')");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE AdvancePayableDate BETWEEN '" + FKStartDate.Value.ToString("yyyy-MM-dd") + "' AND '" + FKEndDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "')");
}
//供应商
if (startSupplier != null && endSupplier != null)
{
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCG WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceCT.InvoiceId FROM InvoiceCT WHERE SupplierId IN (SELECT Supplier.SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
//sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
sb_sr.Append(" AND (SELECT WorkHouse.Workhousename FROM WorkHouse WHERE WorkHouse.WorkHouseId = (SELECT ProduceInDepot.WorkHouseId FROM ProduceInDepot WHERE ProduceInDepotDetail.ProduceInDepotId = ProduceInDepot.ProduceInDepotId)) IN ('" + startSupplier.SupplierShortName.Trim() + "','" + endSupplier.SupplierShortName.Trim() + "')");
sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "' AND '" + endSupplier.Id + "'))");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE SupplierId IN (SELECT SupplierId FROM Supplier WHERE Id BETWEEN '" + startSupplier.Id + "'AND '" + endSupplier.Id + "'))");
}
//商品
if (productStart != null && productEnd != null)
{
sb_cg.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
sb_ct.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
//sb_ww.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
sb_sr.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
sb_wr.Append(" AND ProductId IN (SELECT ProductId FROM Product WHERE Id BETWEEN '" + productStart.Id + "' AND '" + productEnd.Id + "')");
}
//采购单号
if (!string.IsNullOrEmpty(coidStart) && !string.IsNullOrEmpty(coidEnd))
{
sb_cg.Append(" AND InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
sb_ct.Append(" AND InvoiceCOId BETWEEN '" + coidStart + "' AND '" + coidEnd + "'");
}
//客户订单编号
if (!string.IsNullOrEmpty(CusXOid))
{
sb_cg.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
sb_ct.Append(" AND InvoiceCOId IN (SELECT InvoiceCO.InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + CusXOid + "%')");
//sb_ww.Append(" AND CustomInvoiceXOId LIKE '%" + CusXOid + "%'");
}
//员工
if (empstart != null && empend != null)
{
sb_cg.Append(" AND InvoiceId IN (SELECT InvoiceCG.InvoiceId FROM InvoiceCG WHERE Employee0Id IN (SELECT Employee.EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_ct.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCT WHERE Employee0Id IN (SELECT Employee0Id FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
//sb_ww.Append(" AND ProduceOtherCompactId IN (SELECT ProduceOtherCompactId FROM ProduceOtherCompact WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_sr.Append(" AND ProduceInDepotId IN (SELECT ProduceInDepotId FROM ProduceInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_wr.Append(" AND ProduceOtherInDepotId IN (SELECT ProduceOtherInDepotId FROM ProduceOtherInDepot WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
sb_qf.Append(" AND AcOtherShouldPaymentId IN (SELECT AcOtherShouldPaymentId FROM AcOtherShouldPayment WHERE Employee1Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empstart.IDNo + "' AND '" + empend.IDNo + "'))");
}
string sql = sb_cg.ToString() + " UNION ALL " + sb_ct.ToString() + " UNION ALL " + sb_sr.ToString() + " UNION ALL " + sb_wr.ToString() + " UNION ALL " + sb_qf.ToString();
sql = " SELECT * FROM ( " + sql + " ) a ORDER BY a.JHRQ ASC";
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);
}
protected override void AddNew()
{
this._supplier = new Model.Supplier();
}
public IList <Book.Model.InvoiceCODetail> Select(string costartid, string coendid, Book.Model.Supplier SupplierStart, Book.Model.Supplier SupplierEnd, DateTime?dateStart, DateTime?dateEnd, Book.Model.Product productStart, Book.Model.Product productEnd, string cusxoid, DateTime dateJHStart, DateTime dateJHEnd, int?invoiceFlag, Book.Model.Employee empStart, Book.Model.Employee empEnd)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(costartid) && !string.IsNullOrEmpty(coendid))
{
sb.Append(" AND InvoiceId BETWEEN '" + costartid + "' AND '" + coendid + "' ");
}
if (SupplierStart != null && SupplierEnd != null)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE SupplierId BETWEEN '" + SupplierStart.SupplierId + "' AND '" + SupplierEnd.SupplierId + "')");
}
if (dateStart.HasValue && dateEnd.HasValue)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE InvoiceDate BETWEEN '" + dateStart.Value.ToString("yyyy-MM-dd") + "' AND '" + dateEnd.Value.ToString("yyyy-MM-dd HH:mm:ss") + "')");
}
if (productStart != null && productEnd != null)
{
sb.Append(" AND ProductId BETWEEN '" + productStart.ProductId + "' AND '" + productEnd.ProductId + "'");
}
if (dateJHStart != null && dateJHEnd != null)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE InvoiceYjrq BETWEEN '" + dateJHStart.ToString("yyyy-MM-dd") + "' AND '" + dateJHEnd.ToString("yyyy-MM-dd HH:mm:ss") + "')");
}
if (invoiceFlag.HasValue && invoiceFlag.Value == 1)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE IsClose=0)");
}
if (!string.IsNullOrEmpty(cusxoid))
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE InvoiceCustomXOId LIKE '%" + cusxoid + "%')");
}
if (empStart != null && empEnd != null)
{
sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceCO WHERE Employee0Id IN (SELECT EmployeeId FROM Employee WHERE IDNo BETWEEN '" + empStart.IDNo + "' AND '" + empEnd.IDNo + "'))");
}
sb.Append(" ORDER BY InvoiceId DESC");
return(sqlmapper.QueryForList <Model.InvoiceCODetail>("InvoiceCODetail.SelectByConditionCO", sb.ToString()));
}
public IList <Book.Model.AcOtherShouldPayment> SelectByDateRangeAndSupCompany(DateTime startdate, DateTime enddate, Book.Model.Supplier supplier, Book.Model.Company company)
{
Hashtable ht = new Hashtable();
ht.Add("startdate", startdate);
ht.Add("enddate", enddate);
ht.Add("supplier", supplier == null ? null : supplier.SupplierId);
ht.Add("company", company == null ? null : company.CompanyId);
return(sqlmapper.QueryForList <Model.AcOtherShouldPayment>("AcOtherShouldPayment.SelectByDateRangeAndSupCompany", ht));
}
