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)); }