public IList <Model.MRSdetails> SelectbyCondition(string mpsstartId, string mpsendId, string customerstartId, string customerendId, DateTime startdate, DateTime enddate, int?sourceType, string id1, string id2, string cusxoid, Book.Model.Product product, int OrderColumn, int OrderType, Model.ProductCategory productCate) { return(accessor.SelectbyCondition(mpsstartId, mpsendId, customerstartId, customerendId, startdate, enddate, sourceType, id1, id2, cusxoid, product, OrderColumn, OrderType, productCate)); }
//CdmiN--2011年9月29日16:05:38 更新product表,使其与stock表中数据对应 public void UpdateProduct_Stock(Book.Model.Product pro) { accessor.UpdateProduct_Stock(pro); }
public IList <Book.Model.ProduceMaterial> SelectBycondition(DateTime startDate, DateTime endDate, string produceMaterialId0, string produceMaterialId1, Book.Model.Product pId0, Book.Model.Product pId1, string departmentId0, string departmentId1, string PronoteHeaderId0, string PronoteHeaderId1, string CusInvoiceXOId, string handBookId) { SqlParameter[] parames = { new SqlParameter("@startDate", DbType.DateTime), new SqlParameter("@endDate", DbType.DateTime), new SqlParameter("@produceMaterialId0", DbType.String), new SqlParameter("@produceMaterialId1", DbType.String), new SqlParameter("@pId0", DbType.String), new SqlParameter("@pId1", DbType.String), new SqlParameter("@departmentId0", DbType.String), new SqlParameter("@departmentId1", DbType.String), new SqlParameter("@PronoteHeaderId0", DbType.String), new SqlParameter("@PronoteHeaderId1", DbType.String), new SqlParameter("@CusInvoiceXOId", DbType.String) }; parames[0].Value = startDate; parames[1].Value = endDate; if (!string.IsNullOrEmpty(produceMaterialId0)) { parames[2].Value = produceMaterialId0; } else { parames[2].Value = DBNull.Value; } if (!string.IsNullOrEmpty(produceMaterialId1)) { parames[3].Value = produceMaterialId1; } else { parames[3].Value = DBNull.Value; }; if (pId0 != null) { parames[4].Value = pId0.ProductName; } else { parames[4].Value = DBNull.Value; } if (pId1 != null) { parames[5].Value = pId1.ProductName; } else { parames[5].Value = DBNull.Value; } if (departmentId0 != null) { parames[6].Value = departmentId0; } else { parames[6].Value = DBNull.Value; } if (departmentId1 != null) { parames[7].Value = departmentId1; } else { parames[7].Value = DBNull.Value; } if (PronoteHeaderId0 != null) { parames[8].Value = PronoteHeaderId0; } else { parames[8].Value = DBNull.Value; } if (PronoteHeaderId1 != null) { parames[9].Value = PronoteHeaderId1; } else { parames[9].Value = DBNull.Value; } if (!string.IsNullOrEmpty(CusInvoiceXOId)) { parames[10].Value = CusInvoiceXOId; } else { parames[10].Value = DBNull.Value; } StringBuilder sql = new StringBuilder(); sql.Append("SELECT w.Workhousename as WorkhouseName,p.* "); sql.Append(", (SELECT ProductName+'{'+CustomerProductName+'}' FROM Product WHERE Product.ProductId = (SELECT PronoteHeader.ProductId FROM PronoteHeader WHERE PronoteHeader.PronoteHeaderID = p.InvoiceId)) AS ParenProductName"); sql.Append(", (SELECT EmployeeName FROM employee where employee.employeeid=p.Employee0Id) as Employee0Name, (select EmployeeName from employee where employee.employeeid=p.Employee1Id) as Employee1Name"); //,(SELECT EmployeeName FROM employee where employee.employeeid=p.Employee2Id) as Employee2Name sql.Append(", (SELECT CustomerInvoiceXOId FROM invoiceXO where invoiceXO.invoiceId=p.InvoiceXOId) as CusXOId "); //sql.Append(" ,"); sql.Append(" from ProduceMaterial p left join Workhouse w on w.WorkHouseId=p.WorkHouseId "); sql.Append(" WHERE p.ProduceMaterialDate between @startDate and @endDate "); if (!string.IsNullOrEmpty(produceMaterialId0) && !string.IsNullOrEmpty(produceMaterialId1)) { sql.Append(" AND p.ProduceMaterialID between @produceMaterialId0 and @produceMaterialId1 "); } if (pId0 != null && pId1 != null) { sql.Append(" AND p.produceMaterialID in(select ProduceMaterialID from ProduceMaterialdetails where productid IN (SELECT ProductId FROM Product WHERE ProductName between @pId0 and @pId1)) "); } //if (!string.IsNullOrEmpty(departmentId0) && !string.IsNullOrEmpty(departmentId1)) // sql.Append(" and p.Workhousename between @departmentId0 and @departmentId1"); if (!string.IsNullOrEmpty(departmentId0)) { sql.Append(" AND p.WorkHouseId = '" + departmentId0 + "'"); } if (!string.IsNullOrEmpty(PronoteHeaderId0) && !string.IsNullOrEmpty(PronoteHeaderId1)) { sql.Append(" AND p.InvoiceId between @PronoteHeaderId0 and @PronoteHeaderId1"); } if (!string.IsNullOrEmpty(CusInvoiceXOId)) { sql.Append(" AND InvoiceXOId = (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = @CusInvoiceXOId)"); } if (!string.IsNullOrEmpty(handBookId)) { sql.Append(" AND p.ProduceMaterialID in (select ProduceMaterialID from ProduceMaterialdetails where HandbookId='" + handBookId + "')"); } sql.Append(" order by p.ProduceMaterialID"); return(this.DataReaderBind <Model.ProduceMaterial>(sql.ToString(), parames, CommandType.Text)); //Hashtable ht = new Hashtable(); //ht.Add("starDate", startDate); //ht.Add("endDate", endDate); //ht.Add("produceMaterialId0", produceMaterialId0); //ht.Add("produceMaterialId1", produceMaterialId1); //ht.Add("pId0", pId0 == null ? null : pId0.ProductName); //ht.Add("pId1", pId1 == null ? null : pId1.ProductName); //ht.Add("dId0", departmentId0); //ht.Add("dId1", departmentId1); //ht.Add("pronoteId0", PronoteHeaderId0); //ht.Add("pronoteId1", PronoteHeaderId1); //return sqlmapper.QueryForList<Model.ProduceMaterial>("ProduceMaterial.selectBycondition", ht); }
public IList <Book.Model.PCFinishCheck> SelectByDateRage(DateTime startdate, DateTime enddate, Book.Model.Product product, string customerProductName, string CusXOId) { Hashtable ht = new Hashtable(); ht.Add("startdate", startdate.ToString("yyyy-MM-dd")); ht.Add("enddate", enddate.ToString("yyyy-MM-dd")); StringBuilder sql = new StringBuilder(); if (!string.IsNullOrEmpty(customerProductName)) { sql.Append(" and customerProductName='" + customerProductName + "' "); } if (!string.IsNullOrEmpty(CusXOId)) { sql.Append(" and InvoiceCusXOId like '%" + CusXOId + "%'"); } if (product != null) { sql.Append(" and ProductId = '" + product.ProductId + "'"); } ht.Add("sql", sql.ToString()); return(sqlmapper.QueryForList <Model.PCFinishCheck>("PCFinishCheck.SelectByDateRange", ht)); }
public IList <Book.Model.PCOtherCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId) { Hashtable ht = new Hashtable(); ht.Add("startdate", StartDate); ht.Add("enddate", EndDate); StringBuilder sql = new StringBuilder(); //if (customer != null) // sql.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')"); if (!string.IsNullOrEmpty(CusXOId)) { sql.Append(" and PCOtherCheckId in (select PCOtherCheckId from PCOtherCheckDetail where FromInvoiceID in (select InvoiceId from InvoiceCGDetail where InvoiceCOId in (select InvoiceId from InvoiceCO where InvoiceXOId=(select InvoiceId from InvoiceXO where CustomerInvoiceXOId='" + CusXOId + "'))) or FromInvoiceID in (select ProduceOtherInDepotId from ProduceOtherInDepotDetail where ProduceOtherCompactId in (select ProduceOtherCompactId from ProduceOtherCompact where InvoiceXOId =(select InvoiceId from InvoiceXO where CustomerInvoiceXOId='" + CusXOId + "'))))"); } //if (product != null) // sql.Append(" and ProductId = '" + product.ProductId + "'"); sql.Append(" ORDER BY PCOtherCheckId desc"); ht.Add("sql", sql.ToString()); IList <Model.PCOtherCheck> a = sqlmapper.QueryForList <Model.PCOtherCheck>("PCOtherCheck.SelectByDateRange", ht); return(a); }
public IList <Book.Model.ProduceMaterialExitDetail> SelectForListForm(DateTime startDate, DateTime endDate, string startPMEId, string endPMEId, string startPronoteHeaderId, string endPronoteHeaderId, Book.Model.Product startProduct, Book.Model.Product endProduct, string workhouseId, string invoiceXOCusId, string handBookId) { StringBuilder sb = new StringBuilder(); sb.Append(" AND pe.ProduceExitMaterialDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'"); if (!string.IsNullOrEmpty(startPMEId) && !string.IsNullOrEmpty(endPMEId)) { sb.Append(" AND ped.ProduceMaterialExitId BETWEEN '" + startPMEId + "' AND '" + endPMEId + "'"); } if (!string.IsNullOrEmpty(startPronoteHeaderId) && !string.IsNullOrEmpty(endPronoteHeaderId)) { sb.Append(" and ped.PronoteHeaderID between '" + startPronoteHeaderId + "' and '" + endPronoteHeaderId + "'"); } if (startProduct != null & endProduct != null) { sb.Append(" and ped.ProductId in (select ProductId from Product where Id between '" + startProduct.Id + "' and '" + endProduct.Id + "')"); } if (!string.IsNullOrEmpty(workhouseId)) { sb.Append(" and pe.WorkHouseId ='" + workhouseId + "'"); } if (!string.IsNullOrEmpty(invoiceXOCusId)) { sb.Append(" and ped.InvoiceXOId in (select InvoiceId from InvoiceXO where CustomerInvoiceXOId='" + invoiceXOCusId + "')"); } if (!string.IsNullOrEmpty(handBookId)) { sb.Append(" and ped.HandbookId='" + handBookId + "'"); } return(sqlmapper.QueryForList <Model.ProduceMaterialExitDetail>("ProduceMaterialExitDetail.SelectForListForm", sb.ToString())); }
public IList <Book.Model.ProduceOtherCompact> GetByDate(DateTime startDate, DateTime endDate, Book.Model.Product sendProduct, string CustomerInvoiceXOId, string customerid, string supplierid, string ProduceOtherCompactId, bool?isClose) { Hashtable ht = new Hashtable(); ht.Add("startdate", startDate); ht.Add("enddate", endDate); ht.Add("sendProductId", sendProduct == null ? null : sendProduct.ProductId); ht.Add("CustomerInvoiceXOId", string.IsNullOrEmpty(CustomerInvoiceXOId) ? null : CustomerInvoiceXOId); ht.Add("customerid", string.IsNullOrEmpty(customerid) ? null : customerid); ht.Add("supplierid", string.IsNullOrEmpty(supplierid) ? null : supplierid); ht.Add("ProduceOtherCompactId", string.IsNullOrEmpty(ProduceOtherCompactId) ? null : ProduceOtherCompactId); ht.Add("isClose", isClose); //if (!string.IsNullOrEmpty(InvoiceCusId)) // ht.Add("CustomerInvoiceXOId", InvoiceCusId); return(sqlmapper.QueryForList <Model.ProduceOtherCompact>("ProduceOtherCompact.select_GetToDate", 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(InvoiceCGDetailMoney,0)*0.05 AS ShuiE,ISNULL(InvoiceCGDetailMoney,0)*0.05+isnull(InvoiceCGDetailMoney,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_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_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_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_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_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_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_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") + "')"); } //交货日期 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_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_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_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") + "')"); } //供应商 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_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.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 + "'))"); 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 + "'))"); } //商品 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 + "')"); sb_wt.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 + "'))"); 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_cg.Append(" AND InvoiceCODetailId not in (select InvoiceCODetailId from InvoiceCODetail where IsGive=1)"); sb_ct.Append(" AND InvoiceCODetailId not in (select InvoiceCODetailId from InvoiceCODetail where IsGive=1)"); 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 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.PCFinishCheck> SelectByDateRage(DateTime startdate, DateTime enddate, Book.Model.Product product, string customerProductName, string CusXOId) { //Hashtable ht = new Hashtable(); //ht.Add("startdate", startdate.ToString("yyyy-MM-dd")); //ht.Add("enddate", enddate.ToString("yyyy-MM-dd")); //StringBuilder sql = new StringBuilder(); //if (!string.IsNullOrEmpty(customerProductName)) // sql.Append(" and customerProductName='" + customerProductName + "' "); //if (!string.IsNullOrEmpty(CusXOId)) // sql.Append(" and InvoiceCusXOId like '%" + CusXOId + "%'"); //if (product != null) // sql.Append(" and ProductId = '" + product.ProductId + "'"); //ht.Add("sql", sql.ToString()); //return sqlmapper.QueryForList<Model.PCFinishCheck>("PCFinishCheck.SelectByDateRange", ht); StringBuilder sql = new StringBuilder(" select pcf.PCFinishCheckID,pcf.PCFinishCheckDate,wh.Workhousename,xo.CustomerInvoiceXOId as InvoiceCusXOId,p.ProductName,p.CustomerProductName,pcf.PCFinishCheckCount,pcf.PCFinishCheckInCoiunt,e0.EmployeeName as Employee0Name,e1.EmployeeName as Employee1Name from PCFinishCheck pcf left join WorkHouse wh on pcf.WorkHouseId=wh.WorkHouseId left join Product p on pcf.ProductId=p.ProductId left join Employee e0 on pcf.Employee0Id=e0.EmployeeId left join Employee e1 on pcf.Employee1Id=e1.EmployeeId left join PronoteHeader ph on pcf.PronoteHeaderID=ph.PronoteHeaderID left join InvoiceXO xo on xo.InvoiceId=ph.InvoiceXOId where pcf.PCFinishCheckDate BETWEEN '" + startdate.Date.ToString("yyyy-MM-dd") + "' AND '" + enddate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'"); if (!string.IsNullOrEmpty(customerProductName)) { sql.Append(" and customerProductName='" + customerProductName + "' "); } if (!string.IsNullOrEmpty(CusXOId)) { //sql.Append(" and InvoiceCusXOId like '%" + CusXOId + "%'"); sql.Append(" and xo.CustomerInvoiceXOId like '%" + CusXOId + "%'"); } if (product != null) { sql.Append(" and p.ProductId = '" + product.ProductId + "'"); } sql.Append(" ORDER BY pcf.PCFinishCheckID desc"); return(this.DataReaderBind <Model.PCFinishCheck>(sql.ToString(), null, CommandType.Text)); }
public IList <Book.Model.ProduceOtherCompact> selectByConditionRang(DateTime startDate, DateTime endDate, Book.Model.Product sendProduct, string customerid, string supplierid, string ProduceOtherCompactId, string InvoiceCusXOId) { return(accessor.selectByConditionRang(startDate, endDate, sendProduct, customerid, supplierid, ProduceOtherCompactId, InvoiceCusXOId)); }
/// <summary> /// Insert a CustomerProducts. /// </summary> public void Insert(Model.CustomerProducts customerProducts) { this.Validate(customerProducts); //if (this.Exists(customerProducts)) //{ // throw new Helper.InvalidValueException(Model.CustomerProducts.PROPERTY_CUSTOMERPRODUCTNAME); //} try { BL.V.BeginTransaction(); //保存后返回第一个ID // string primarykeyid =null; string customProductName = null; // StringBuilder strBU = new StringBuilder(); customerProducts.CustomerId = customerProducts.Customer.CustomerId; customerProducts.InsertTime = DateTime.Now; //保存后返回第一个ID // primarykeyid = customerProducts.PrimaryKeyId; customProductName = customerProducts.CustomerProductName; // customerProducts.BuyUnitId = customerProducts.BuyUnit == null ? null : customerProducts.BuyUnit.ProductUnitId; customerProducts.DepotId = customerProducts.Depot == null ? null : customerProducts.Depot.DepotId; customerProducts.DepotPositionId = customerProducts.DepotPosition == null ? null : customerProducts.DepotPosition.DepotPositionId; //customerProducts.DepotUnitId = customerProducts.DepotUnit == null ? null : customerProducts.DepotUnit.ProductUnitId; //// customerProducts.MainUnitId = customerProducts.MainUnit == null ? null : customerProducts.MainUnit.ProductUnitId; //customerProducts.ProduceUnitId = customerProducts.ProduceUnit == null ? null : customerProducts.ProduceUnit.ProductUnitId; //customerProducts.QualityTestUnitId = customerProducts.QualityTestUnit == null ? null : customerProducts.QualityTestUnit.ProductUnitId; //customerProducts.SellUnitId = customerProducts.SellUnit == null ? null : customerProducts.SellUnit.ProductUnitId; //customerProducts.UnitGroupId = customerProducts.UnitGroup == null ? null : customerProducts.UnitGroup.UnitGroupId; Model.Product product = new Book.Model.Product();// customerProducts.Product; product.CustomerBeforeProductId = customerProducts.Product.ProductId; product.ProductId = Guid.NewGuid().ToString(); customerProducts.CustomerProductProceName = product.ProductId;//新产生的商品ID //if (IsExistsCustomerProductId(customerProducts.CustomerProductId, customerProducts.PrimaryKeyId)) // throw new Helper.InvalidValueException(Model.CustomerProducts.PRO_CustomerProductId + "_Exists"); //if (SelectByCustomerIdAndProductId(customerProducts.ProductId, null, customerProducts.CustomerId)) // throw new Helper.InvalidValueException(Model.CustomerProducts.PRO_CustomerId); if (this.Exists(customerProducts)) { throw new Helper.InvalidValueException(Model.CustomerProducts.PRO_CustomerProductId + "_Exists"); } accessor.Insert(customerProducts); //加入商品表 product.IsCustomerProduct = true; //指定客户产品对应产品编号 product.CustomerBeforeProductName = customerProducts.Product.ProductName; //添加的客户产品名稱添加至产品表CustomerProductName 字段 product.CustomerProductName = customerProducts.CustomerProductId; product.Customer = customerProducts.Customer; product.ProductDescription = customerProducts.CustomerProductDesc; if (product.Customer != null) { product.CustomerId = product.Customer.CustomerId; } //byte[] pic = new byte[] { }; //if (product.ProductImage == null) // product.ProductImage = pic; //if (product.ProductImage1 == null) // product.ProductImage1 = pic; //if (product.ProductImage2 == null) // product.ProductImage2 = pic; //if (product.ProductImage3 == null) // product.ProductImage3 = pic; product.XOPriceAndRange = customerProducts.XOPrice; product.Id = customerProducts.Product.Id + "{" + product.CustomerBeforeProductName + "}" + "-" + customerProducts.Version; product.ProductName = customerProducts.Product.ProductName; //product.ProductCategory = customerProducts.Product.ProductCategory; product.ProductCategoryId = customerProducts.Product.ProductCategoryId; product.ProductCategoryId2 = customerProducts.Product.ProductCategoryId2; product.ProductCategoryId3 = customerProducts.Product.ProductCategoryId3; // product.BasedUnitGroup = customerProducts.Product.BasedUnitGroup; product.BasedUnitGroupId = customerProducts.Product.BasedUnitGroupId; // product.BuyUnit = this.product.BuyUnit; product.BuyUnitId = customerProducts.BuyUnitId; // product.Depot = this.product.Depot; product.DepotId = customerProducts.DepotId; // product.DepotUnit = this.product.DepotUnit; product.DepotUnitId = customerProducts.DepotUnitId; // product.ProduceUnit = this.product.ProduceUnit; product.ProduceUnitId = customerProducts.ProduceUnitId; // product.SellUnit = this.product.SellUnit; product.SellUnitId = customerProducts.SellUnitId; // product.QualityTestUnit = this.product.QualityTestUnit; product.QualityTestUnitId = customerProducts.QualityTestUnitId; // product.WeightUnitGroup = this.product.WeightUnitGroup; product.WeightUnitGroupId = customerProducts.Product.WeightUnitGroupId; // product.WeightUnit = this.product.WeightUnit; product.WeightUnitId = customerProducts.Product.WeightUnitId; product.HomeMade = customerProducts.Product.HomeMade; product.OutSourcing = customerProducts.Product.OutSourcing; product.TrustOut = customerProducts.Product.TrustOut; product.Consume = customerProducts.Product.Consume; product.ProductBarCodeIsAuto = true; product.ProductBarCode = product.Id; product.StocksQuantity = 0; product.OrderOnWayQuantity = 0; product.ProductVersion = customerProducts.Version; product.ProductDeadDate = customerProducts.VersionDate; productAccessor.Insert(product); //客户产品价格 Model.CustomerProductPrice customerProducrPrice = new Book.Model.CustomerProductPrice(); customerProducrPrice.CustomerProductPriceId = Guid.NewGuid().ToString(); customerProducrPrice.CustomerId = customerProducts.CustomerId; customerProducrPrice.ProductId = product.ProductId; customerProducrPrice.InsertTime = DateTime.Now; customerProducrPrice.UpdateTime = DateTime.Now; customerProducrPrice.CustomerProductsId = customerProducts.PrimaryKeyId; customerProductPriceAccessor.Insert(customerProducrPrice); // customerProductProcessAccessor.Delete(customerProducts); //foreach (Model.CustomerProductProcess cpp in customerProducts.CustomerProductProcessList) //{ // if (cpp.ProcessCategory == null) continue; // strBU.Append(cpp.ProcessCategory.ProcessCategoryName); // cpp.CustomerProductProcessId = Guid.NewGuid().ToString(); // cpp.PrimaryKeyId = customerProducts.PrimaryKeyId; // // if (cpp.IsCheck.Value) // // { // cpp.ProcessId = cpp.Process == null ? null : cpp.Process.ProcessId; // //} // //else // //{ // // cpp.ProcessId = null; // //} // cpp.ProcessCategoryId = cpp.ProcessCategory == null ? null : cpp.ProcessCategory.ProcessCategoryId; // customerProductProcessAccessor.Insert(cpp); // } // foreach (Model.CustomerProductsBom bom in customerProducts.CustomerProductsBomInfos) // { // if (bom != null) // { // bom.PriamryKeyId = Guid.NewGuid().ToString(); // if (bom.Product != null) // bom.ProductId = bom.Product.ProductId; // bom.PrimaryKeyId = customerProducts.PrimaryKeyId; // customerProductsBomAccessor.Insert(bom); // } // } // if (customerProducts.PackageCustomerDetails!=null) // { // foreach (Model.PackageCustomerDetails pac in customerProducts.PackageCustomerDetails) // { // if (pac != null) // { // pac.PackageCustomerDetailsId = Guid.NewGuid().ToString(); // pac.PrimaryKeyId = customerProducts.CustomerProductId; // packageCustomerDetailsAccessor.Insert(pac); // } // } // } ///////////////////////////////// //加工后名称添加 //加工后名称添加 //if (strBU.Length!= 0 ) //{ // customerProducts.PrimaryKeyId = Guid.NewGuid().ToString(); // // customerProductProcessAccessor.Delete(customerProducts); // customerProducts.CustomerProductName +=strBU.ToString(); // accessor.Insert(customerProducts); // foreach (Model.CustomerProductProcess cpp in customerProducts.CustomerProductProcessList) // { // if (cpp.ProcessCategory == null) continue; // strBU.Append(customerProducts.CustomerProductName); // cpp.CustomerProductProcessId = Guid.NewGuid().ToString(); // cpp.PrimaryKeyId = customerProducts.PrimaryKeyId; // // if (cpp.IsCheck.Value) // // { // cpp.ProcessId = cpp.Process == null ? null : cpp.Process.ProcessId; // //} // //else // //{ // // cpp.ProcessId = null; // //} // cpp.ProcessCategoryId = cpp.ProcessCategory == null ? null : cpp.ProcessCategory.ProcessCategoryId; // customerProductProcessAccessor.Insert(cpp); // } //foreach (Model.CustomerProductsBom bom in customerProducts.CustomerProductsBomInfos) //{ // if (bom != null) // { // bom.PriamryKeyId = Guid.NewGuid().ToString(); // if (bom.Product != null) // bom.ProductId = bom.Product.ProductId; // bom.PrimaryKeyId = customerProducts.PrimaryKeyId; // customerProductsBomAccessor.Insert(bom); // } //} //if (customerProducts.PackageCustomerDetails.Count!= 0) //{ // foreach (Model.PackageCustomerDetails pac in customerProducts.PackageCustomerDetails) // { // if (pac != null) // { // pac.PackageCustomerDetailsId = Guid.NewGuid().ToString(); // pac.PrimaryKeyId = customerProducts.CustomerProductId; // packageCustomerDetailsAccessor.Insert(pac); // } // } //} // customerProducts.PrimaryKeyId = primarykeyid; // customerProducts.CustomerProductName =customProductName; //} BL.V.CommitTransaction(); } catch { BL.V.RollbackTransaction(); throw; } }
public IList <Model.PCPGOnlineCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId, string StartPronoteHeader, string EndPronoteHeader) { StringBuilder sb = new StringBuilder(" AND p.PCPGOnlineCheckDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'"); if (customer != null) { sb.Append(" AND p.CustomerId = '" + customer.CustomerId + "'"); } if (product != null) { sb.Append(" AND pd.ProductId='" + product.ProductId + "'"); } if (!string.IsNullOrEmpty(StartPronoteHeader) && !string.IsNullOrEmpty(EndPronoteHeader)) { sb.Append(" AND pd.FromInvoiceId BETWEEN '" + StartPronoteHeader + "' AND '" + EndPronoteHeader + "'"); } if (!string.IsNullOrEmpty(CusXOId)) { sb.Append(" AND (isnull(ph.InvoiceCusId,'')+isnull(pc.CustomerInvoiceXOId,'')) like '" + "%" + CusXOId + "%" + "'"); } sb.Append(" )"); sb.Append(" order by PCPGOnlineCheckId desc"); return(sqlmapper.QueryForList <Model.PCPGOnlineCheck>("PCPGOnlineCheck.SelectByDateRange", sb.ToString())); }
public DataTable SelectDetailByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId, string StartPronoteHeader, string EndPronoteHeader) { //SqlParameter[] parames = { // new SqlParameter("@StartDate", SqlDbType.DateTime), // new SqlParameter("@EndDate", SqlDbType.DateTime), // new SqlParameter("@CustomerId", SqlDbType.VarChar), // new SqlParameter("@InvoiceCusXOId", SqlDbType.VarChar), // new SqlParameter("@ProductId", SqlDbType.VarChar), // new SqlParameter("@StartPronoteHeader", SqlDbType.VarChar), // new SqlParameter("@EndPronoteHeader", SqlDbType.VarChar) //}; //parames[0].Value = StartDate.ToString("yyyy-MM-dd"); //parames[1].Value = EndDate.ToString("yyyy-MM-dd"); //if (customer == null) // parames[2].Value = DBNull.Value; //else // parames[2].Value = customer.CustomerId; //if (string.IsNullOrEmpty(CusXOId)) // parames[3].Value = DBNull.Value; //else // parames[3].Value = "%" + CusXOId + "%"; //if (product == null) // parames[4].Value = DBNull.Value; //else // parames[4].Value = product.ProductId; //if (string.IsNullOrEmpty(StartPronoteHeader)) // parames[5].Value = DBNull.Value; //else // parames[5].Value = StartPronoteHeader; //if (string.IsNullOrEmpty(EndPronoteHeader)) // parames[6].Value = DBNull.Value; //else // parames[6].Value = EndPronoteHeader; //StringBuilder sql = new StringBuilder("SELECT PCPGOnlineCheckId,PCPGOnlineCheckDate,InvoiceCusXOId,(SELECT EmployeeName FROM Employee WHERE Employee.EmployeeId = PCPGOnlineCheck.EmployeeId) AS EmployeeName,(SELECT ProductName FROM Product WHERE Product.ProductId = PCPGOnlineCheck.ProductId) AS ProductName,(SELECT Customer.CustomerShortName FROM Customer WHERE Customer.CustomerId = PCPGOnlineCheck.CustomerId) AS CustomerShortName,(SELECT Convert(varchar(30),PCPGOnlineCheckDetailDate,120)+'.' FROM PCPGOnlineCheckDetail WHERE PCPGOnlineCheckDetail.PCPGOnlineCheckId=PCPGOnlineCheck.PCPGOnlineCheckId FOR xml path('')) AS DescTime FROM PCPGOnlineCheck WHERE 1 = 1 "); //sql.Append(" AND PCPGOnlineCheckDate BETWEEN @StartDate AND @EndDate"); //if (customer != null) // sql.Append(" AND CustomerId = @CustomerId"); //if (!string.IsNullOrEmpty(CusXOId)) // sql.Append(" AND InvoiceCusXOId like @InvoiceCusXOId"); //if (product != null) // sql.Append(" AND PCPGOnlineCheck.ProductId=@ProductId"); //if (!string.IsNullOrEmpty(StartPronoteHeader) && !string.IsNullOrEmpty(EndPronoteHeader)) // sql.Append(" AND FromPCId BETWEEN @StartPronoteHeader AND @EndPronoteHeader"); StringBuilder sb = new StringBuilder("select * from (select pd.PCPGOnlineCheckId,p.PCPGOnlineCheckDate,(isnull(xo1.CustomerInvoiceXOId,'')+isnull(xo2.CustomerInvoiceXOId,'')+isnull(xo3.CustomerInvoiceXOId,'')) as InvoiceCusXOId2,pro.ProductName,e.EmployeeName,pd.PCPGOnlineCheckDetailDate as DescTime,pd.FromInvoiceId as FromId,b.BusinessHoursName from PCPGOnlineCheckDetail pd left join PCPGOnlineCheck p on pd.PCPGOnlineCheckId=p.PCPGOnlineCheckId left join Employee e on e.EmployeeId=p.EmployeeId left join product pro on pd.productid=pro.ProductId left join PronoteHeader ph on pd.FromInvoiceId=ph.PronoteHeaderID left join ProduceOtherCompact pc on pd.FromInvoiceId=pc.ProduceOtherCompactId left join InvoiceCO co on pd.FromInvoiceId=co.InvoiceId left join InvoiceXO xo1 on ph.InvoiceXOId=xo1.InvoiceId left join InvoiceXO xo2 on pc.InvoiceXOId=xo2.InvoiceId left join InvoiceXO xo3 on co.InvoiceXOId=xo3.InvoiceId left join BusinessHours b on p.BusinessHoursId=b.BusinessHoursId where p.PCPGOnlineCheckDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'"); if (customer != null) { sb.Append(" AND p.CustomerId = '" + customer.CustomerId + "'"); } if (product != null) { sb.Append(" AND pd.ProductId='" + product.ProductId + "'"); } if (!string.IsNullOrEmpty(StartPronoteHeader) && !string.IsNullOrEmpty(EndPronoteHeader)) { sb.Append(" AND pd.FromInvoiceId BETWEEN '" + StartPronoteHeader + "' AND '" + EndPronoteHeader + "'"); } sb.Append(" ) a"); if (!string.IsNullOrEmpty(CusXOId)) { sb.Append(" where a.InvoiceCusXOId2 = '" + CusXOId + "'"); } //if (OnlySelfMade) //JIS出货报告中查询时只查自制部分 // sb.Append(" And a.FromId like '%pnt%'"); sb.Append(" order by PCPGOnlineCheckId desc"); //return this.DataReaderBind<Model.PCPGOnlineCheck>(sb.ToString(), parames, CommandType.Text); SqlDataAdapter sda = new SqlDataAdapter(sb.ToString(), sqlmapper.DataSource.ConnectionString); DataTable dt = new DataTable(); sda.SelectCommand.CommandTimeout = 300; sda.Fill(dt); return(dt); }
public IList <Book.Model.PCImpactCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId) { Hashtable ht = new Hashtable(); ht.Add("startdate", StartDate.ToString("yyyy-MM-dd")); ht.Add("enddate", EndDate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss")); StringBuilder sql = new StringBuilder(); //if (customer != null) // sql.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')"); if (!string.IsNullOrEmpty(CusXOId)) { sql.Append(" and InvoiceCusXOId like '" + CusXOId + "'"); } if (product != null) { sql.Append(" and ProductId = '" + product.ProductId + "'"); } ht.Add("sql", sql.ToString()); IList <Model.PCImpactCheck> a = sqlmapper.QueryForList <Model.PCImpactCheck>("PCImpactCheck.SelectByDateRange", ht); return(a); }
//public void Delete(Book.Model.Product product,Model.Customer customer) //{ // Hashtable ht = new Hashtable(); // ht.Add("productid", product.ProductId); // ht.Add("customerid", customer == null ? null : customer.CustomerId); // sqlmapper.Delete("BomParentPartInfo.deleteByProductCustom", ht); //} public void Delete(Book.Model.Product product) { sqlmapper.Delete("BomParentPartInfo.deleteByProduct", product.ProductId); }
public DataTable SelectForExcel(DateTime startDate, DateTime endDate, string startPMEId, string endPMEId, string startPronoteHeaderId, string endPronoteHeaderId, Book.Model.Product startProduct, Book.Model.Product endProduct, string workhouseId, string invoiceXOCusId, string handBookId) { StringBuilder sb = new StringBuilder("select xo.CustomerInvoiceXOId,p1.CustomerProductName,ph.PronoteDate,ph.PronoteHeaderID,p1.Id,p1.ProductName,ph.InvoiceXODetailQuantity,ph.DetailsSum,p2.Id as MId,p2.ProductName as MProductName,(select sum(pmd.Materialprocesedsum) from ProduceMaterialdetails pmd where pmd.PronoteHeaderID=ph.PronoteHeaderID and pmd.ProductId=p2.ProductId) as Materialprocesedsum ,(select top 1 pm.ProduceMaterialDate from ProduceMaterialdetails pmd left join ProduceMaterial pm on pm.ProduceMaterialID=pmd.ProduceMaterialID where pmd.PronoteHeaderID=ph.PronoteHeaderID and pmd.ProductId=p2.ProductId) as ProduceMaterialDate,pe.ProduceExitMaterialDate,ped.ProduceQuantity,wh.Workhousename,ped.HandbookId,ped.HandbookProductId,ph.MRSHeaderId,p2.ProductDescription,p2.ProductId from ProduceMaterialExitDetail ped left join ProduceMaterialExit pe on pe.ProduceMaterialExitId=ped.ProduceMaterialExitId left join PronoteHeader ph on ph.PronoteHeaderID=ped.PronoteHeaderId left join InvoiceXO xo on xo.InvoiceId=ph.InvoiceXOId left join Product p1 on p1.ProductId=ph.ProductId left join Product p2 on p2.ProductId=ped.ProductId left join WorkHouse wh on wh.WorkHouseId=pe.WorkHouseId where 1=1 "); sb.Append(" AND pe.ProduceExitMaterialDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.Date.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "'"); if (!string.IsNullOrEmpty(startPMEId) && !string.IsNullOrEmpty(endPMEId)) { sb.Append(" AND ped.ProduceMaterialExitId BETWEEN '" + startPMEId + "' AND '" + endPMEId + "'"); } if (!string.IsNullOrEmpty(startPronoteHeaderId) && !string.IsNullOrEmpty(endPronoteHeaderId)) { sb.Append(" and ped.PronoteHeaderID between '" + startPronoteHeaderId + "' and '" + endPronoteHeaderId + "'"); } if (startProduct != null & endProduct != null) { sb.Append(" and p2.Id between '" + startProduct.Id + "' and '" + endProduct.Id + "'"); } if (!string.IsNullOrEmpty(workhouseId)) { sb.Append(" and pe.WorkHouseId ='" + workhouseId + "'"); } if (!string.IsNullOrEmpty(invoiceXOCusId)) { sb.Append(" and xo.CustomerInvoiceXOId='" + invoiceXOCusId + "'"); } if (!string.IsNullOrEmpty(handBookId)) { sb.Append(" and ped.HandbookId='" + handBookId + "'"); } sb.Append(" order by pe.ProduceMaterialExitId"); SqlDataAdapter sda = new SqlDataAdapter(sb.ToString(), sqlmapper.DataSource.ConnectionString); DataTable dt = new DataTable(); sda.Fill(dt); return(dt); }
public IList <Book.Model.InvoiceXSDetail> SelectbyConditionX(DateTime StartDate, DateTime EndDate, DateTime Yjri1, DateTime Yjri2, Book.Model.Customer Customer1, Book.Model.Customer Customer2, string XOId1, string XOId2, Book.Model.Product Product, Book.Model.Product Product2, string CusXOId, int OrderColumn, int OrderType, string depotId, string handBookId) { StringBuilder sb = new StringBuilder(); if (Product != null && Product2 != null) { sb.Append(" AND ProductId BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "'"); } if (!string.IsNullOrEmpty(CusXOId)) { sb.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = '" + CusXOId + "')"); } sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')"); if (Yjri1 != global::Helper.DateTimeParse.NullDate && Yjri2 != global::Helper.DateTimeParse.EndDate) { sb.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE InvoiceYjrq BETWEEN '" + Yjri1.ToString("yyyy-MM-dd") + "' AND '" + Yjri2.Date.AddDays(1).ToString("yyyy-MM-dd") + "')"); } if (Customer1 != null && Customer2 != null) { sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "'))"); } if (!string.IsNullOrEmpty(XOId1) && !string.IsNullOrEmpty(XOId2)) { sb.Append(" AND InvoiceId BETWEEN '" + XOId1 + "' AND '" + XOId2 + "'"); } if (!string.IsNullOrEmpty(depotId)) { sb.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE DepotId = '" + depotId + "')"); } if (!string.IsNullOrEmpty(handBookId)) { sb.Append(" and HandbookId in (" + handBookId + ")"); } return(sqlmapper.QueryForList <Model.InvoiceXSDetail>("InvoiceXSDetail.SelectbyConditionX", sb.ToString())); }
public IList <Book.Model.MRSdetails> SelectbyCondition(string mpsstartId, string mpsendId, string customerstartId, string customerendId, DateTime startdate, DateTime enddate, int?sourceType, string id1, string id2, string cusxoid, Book.Model.Product product, int OrderColumn, int OrderType, Model.ProductCategory productCate) { StringBuilder str = new StringBuilder(); str.Append(" select c.ProductCategoryName , a.MRSHeaderId,b.ProductName,(ISNULL(b.ProduceMaterialDistributioned,0)+isnull( b.OtherMaterialDistributioned,0)) AS ProduceDistributioned , b.OrderOnWayQuantity, b.StocksQuantity ,b.CustomerProductName,a.ArrangeDesc,a.MPSheaderId,a.ProductUnit,a.MRSdetailssum,a.MRSdetailsQuantity,a.JiaoHuoDate ,(select EmployeeName from employee where employee.employeeid=(select employee0id from mrsheader m where m.mrsheaderid=a.mrsheaderid )) as Employee0Name,(select CustomerInvoiceXOId from invoicexo where invoiceid=(select invoicexoid from mpsheader where mpsheader.mpsheaderid=(select mpsheaderid from mrsheader m where m.mrsheaderid=a.mrsheaderid )) ) as CustomerInvoiceXOId,"); str.Append(" (select SupplierShortName from Supplier s where s.SupplierId=a.SupplierId) as SupplierId,"); str.Append(" (select MRSstartdate from MRSHeader m where m.MRSHeaderId=a.MRSHeaderId) as MRSstartdate,"); str.Append(" (select case SourceType when '0' then 'Self made' when '1' then 'Purchase' when '2' then 'Consumption' when '3' then 'Outsourcing' when '4' then 'Self made(Assembly)' when '5' then 'Self made(Semi-finished products)' when '6' then 'Outsourcing(Semi-finished products)' end from MRSHeader m where m.MRSHeaderId=a.MRSHeaderId) as SourceTypeName ,"); str.Append(" (select CustomerShortName from customer where customerid=(select xocustomerId from invoicexo where invoiceid=(select invoicexoid from mpsheader where mpsheader.mpsheaderid=(select mpsheaderid from mrsheader m where m.mrsheaderid=a.mrsheaderid )))) as CustomerName"); str.Append(" from product b right join mrsDetails a on a.productid=b.productid left join ProductCategory c on c.ProductCategoryId=b.ProductCategoryId "); str.Append(" where a.MRSHeaderId IN (SELECT MRSHeader.MRSHeaderId FROM MRSHeader WHERE MRSstartdate between '" + startdate.ToString("yyyy-MM-dd") + "' and '" + enddate.Date.ToString("yyyy-MM-dd HH:mm:ss") + "' )"); if (!string.IsNullOrEmpty(customerstartId) && !string.IsNullOrEmpty(customerendId)) { str.Append(" AND a.MRSHeaderId IN (SELECT MRSHeader.MRSHeaderId FROM MRSHeader WHERE CustomerId between '" + customerstartId + "' and '" + customerendId + "' )"); } if (!string.IsNullOrEmpty(mpsstartId) && !string.IsNullOrEmpty(mpsendId)) { str.Append(" AND a.MRSHeaderId IN (SELECT MRSHeader.MRSHeaderId FROM MRSHeader WHERE MPSheaderId between '" + mpsstartId + "' and '" + mpsendId + "' )"); } if (sourceType != -1) //非全部 { str.Append(" AND a.MRSHeaderId IN (SELECT MRSHeader.MRSHeaderId FROM MRSHeader WHERE SourceType = '" + sourceType.ToString() + "')"); } if (!string.IsNullOrEmpty(id1) && !string.IsNullOrEmpty(id2)) { str.Append(" AND a.MRSHeaderId BETWEEN '" + id1 + "' AND '" + id2 + "'"); } if (!string.IsNullOrEmpty(cusxoid)) { str.Append(" AND a.MRSHeaderId in (select MRSHeaderId from MRSHeader where MPSheaderId in (select MPSheaderId from MPSheader where InvoiceXOId in(select InvoiceId from InvoiceXO where CustomerInvoiceXOId like '%" + cusxoid + "%' )))"); } if (product != null) { str.Append(" AND productid='" + product.ProductId + "'"); } if (productCate != null) { str.Append(" AND productid in (select productid from product where ProductCategoryId = '" + productCate.ProductCategoryId + "')"); } str.Append(" ORDER BY "); switch (OrderColumn) { case 0: str.Append(" a.MRSHeaderId,a.Inumber "); //头编号 break; case 1: str.Append(" (SELECT ProductName FROM Product WHERE Product.ProductId=a.ProductId)"); //商品名称 break; case 2: str.Append(" (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId =(SELECT InvoiceXOId FROM MPSheader WHERE MPSheader.MPSheaderId = MRSHeader.MPSheaderId))"); break; case 3: str.Append(" (SELECT Supplier.Id FROM Supplier WHERE Supplier.SupplierId = a.SupplierId)"); //供应商 break; case 4: str.Append(" (SELECT Id FROM ProductCategory WHERE ProductCategory.ProductCategoryId = (SELECT Product.ProductCategoryId FROM Product WHERE Product.ProductId = a.ProductId))"); break; } if (OrderType == 0) { str.Append(" ASC"); } else { str.Append(" DESC"); } return(this.DataReaderBind <Model.MRSdetails>(str.ToString(), null, CommandType.Text)); //return sqlmapper.QueryForList<Model.MRSdetails>("MRSdetails.selectByCondition", str.ToString()); }
public DataTable SelectbyConditionXBiao(DateTime StartDate, DateTime EndDate, DateTime Yjri1, DateTime Yjri2, Book.Model.Customer Customer1, Book.Model.Customer Customer2, string XOId1, string XOId2, Book.Model.Product Product, Book.Model.Product Product2, string CusXOId, int OrderColumn, int OrderType) { StringBuilder sb_xs = new StringBuilder("SELECT InvoiceId AS CHDH,(SELECT InvoiceDate FROM InvoiceXS WHERE InvoiceId = InvoiceXSDetail.InvoiceId) AS CHRQ,(SELECT ProductName FROM Product WHERE ProductId = InvoiceXSDetail.ProductId) AS ProductName,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId = InvoiceXOId ) AS KHDDBH,InvoiceXSDetailQuantity AS BCCHSL,InvoiceProductUnit AS DanWei,InvoiceXSDetailPrice AS DanJia,InvoiceAllowance AS ZheRang,InvoiceXSDetailMoney AS JinE,InvoiceXSDetailTax AS ShuiE,InvoiceXSDetailTaxMoney AS YingShou FROM InvoiceXSDetail WHERE 1 = 1 "); StringBuilder sb_xt = new StringBuilder("SELECT InvoiceId AS CHDH,(SELECT InvoiceDate FROM InvoiceXT WHERE InvoiceId = InvoiceXTDetail.InvoiceId) AS CHRQ,(SELECT ProductName FROM Product WHERE ProductId = InvoiceXTDetail.ProductId ) AS ProductName,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceId = InvoiceXOId) AS KHDDBH,InvoiceXTDetailQuantity AS BCCHSL,InvoiceProductUnit AS DanWei,InvoiceXTDetailPrice AS DanJia,InvoiceXTDetailDiscount AS ZheRang,(0-InvoiceXTDetailMoney1) AS JinE,(0-ISNULl(InvoiceXTDetailPrice,0)*isnull(InvoiceXTDetailQuantity,0)*0.05) AS ShuiE,(0-InvoiceXTDetailMoney0)+(0-ISNULl(InvoiceXTDetailPrice,0)*isnull(InvoiceXTDetailQuantity,0)*0.05) AS YingShou FROM InvoiceXTDetail WHERE 1 = 1 "); //时间日期 sb_xs.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')"); sb_xt.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE InvoiceDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.Date.AddDays(1).ToString("yyyy-MM-dd") + "')"); //预交日期 if (Yjri1 != global::Helper.DateTimeParse.NullDate && Yjri2 != global::Helper.DateTimeParse.EndDate) { sb_xs.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE InvoiceYjrq BETWEEN '" + Yjri1.ToString("yyyy-MM-dd") + "' AND '" + Yjri2.Date.AddDays(1).ToString("yyyy-MM-dd") + "')"); } //客户 if (Customer1 != null && Customer2 != null) { sb_xs.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXS WHERE CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "'))"); sb_xt.Append(" AND InvoiceId IN (SELECT InvoiceId FROM InvoiceXT WHERE CustomerId IN (SELECT CustomerId FROM Customer WHERE Id BETWEEN '" + Customer1.Id + "' AND '" + Customer2.Id + "'))"); } //头编号 if (!string.IsNullOrEmpty(XOId1) && !string.IsNullOrEmpty(XOId2)) { sb_xs.Append(" AND InvoiceId BETWEEN '" + XOId1 + "' AND '" + XOId2 + "'"); } //客户订单编号 if (!string.IsNullOrEmpty(CusXOId)) { sb_xs.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = '" + CusXOId + "')"); sb_xt.Append(" AND InvoiceXOId IN (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = '" + CusXOId + "')"); } //商品 if (Product != null && Product2 != null) { sb_xs.Append(" AND ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "')"); sb_xt.Append(" AND ProductId IN (SELECT Product.ProductId FROM Product WHERE Id BETWEEN '" + Product.Id + "' AND '" + Product2.Id + "')"); } string sql = sb_xs.ToString() + " UNION " + sb_xt.ToString() + "order by CHDH,CHRQ"; using (SqlConnection con = new SqlConnection(sqlmapper.DataSource.ConnectionString)) { SqlDataAdapter sda = new SqlDataAdapter(sql, con); DataSet ds = new DataSet(); sda.Fill(ds); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } } return(null); }
public void UpdateProduct_Stock(Book.Model.Product pro) { sqlmapper.Update("Product.update_stock", pro.ProductId); }
public IList <Book.Model.ProduceMaterialExit> SelectForListForm(DateTime startDate, DateTime endDate, string startPMEId, string endPMEId, string startPronoteHeaderId, string endPronoteHeaderId, Book.Model.Product startProduct, Book.Model.Product endProduct, string workhouseId, string invoiceXOCusId, string handBookId) { return(accessor.SelectForListForm(startDate, endDate, startPMEId, endPMEId, startPronoteHeaderId, endPronoteHeaderId, startProduct, endProduct, workhouseId, invoiceXOCusId, handBookId)); }
public IList <Book.Model.PCExportReportANSI> SelectForChooseExport(DateTime startdate, DateTime enddate, Book.Model.Product product, Book.Model.Customer customer, string CusXOId, string ExpType) { Hashtable ht = new Hashtable(); ht.Add("startdate", startdate.ToString("yyyy-MM-dd")); ht.Add("enddate", enddate.ToString("yyyy-MM-dd")); ht.Add("ExpType", ExpType); StringBuilder sb = new StringBuilder(); if (customer != null) { sb.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')"); } if (!string.IsNullOrEmpty(CusXOId)) { sb.Append(" and InvoiceCusXOId like '%" + CusXOId + "%'"); } if (product != null) { sb.Append(" and ProductId = '" + product.ProductId + "'"); } if (!string.IsNullOrEmpty(ExpType)) { sb.Append(" and ExpType ='" + ExpType + "'"); } ht.Add("sql", sb.ToString()); return(sqlmapper.QueryForList <Model.PCExportReportANSI>("PCExportReportANSI.SelectByDateRage", ht)); }
public void Decrement(Book.Model.DepotPosition depotPosition, Book.Model.Product product, double?quantity) { this.Decrement(depotPosition, product, quantity == null ? 0 : quantity.Value); }
public void Delete(Book.Model.Product product, Model.Customer customer) { accessor.Delete(product, customer); }
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)); }
public IList <Book.Model.ANSIPCImpactCheck> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, Book.Model.Customer customer, string cusXOId, string ForANSIOrJIS) { Hashtable ht = new Hashtable(); ht.Add("StartDate", StartDate); ht.Add("EndDate", EndDate); StringBuilder sql = new StringBuilder(); if (customer != null) { sql.Append(" and InvoiceCusXOId IN (SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE xocustomerId = '" + customer.CustomerId + "')"); } if (!string.IsNullOrEmpty(cusXOId)) { sql.Append(" and InvoiceCusXOId like '%" + cusXOId + "%'"); } if (product != null) { sql.Append(" and ProductId = '" + product.ProductId + "'"); } if (!String.IsNullOrEmpty(ForANSIOrJIS)) { sql.Append(" AND ForANSIOrJIS='" + ForANSIOrJIS + "'"); } ht.Add("sql", sql.ToString()); return(sqlmapper.QueryForList <Model.ANSIPCImpactCheck>("ANSIPCImpactCheck.SelectByDateRange", ht)); }
public IList <Book.Model.PCImpactCheckDetail> SelectByDateRage(DateTime StartDate, DateTime EndDate, Book.Model.Product product, string CusXOId) { return(accessor.SelectByDateRage(StartDate, EndDate, product, CusXOId)); }
public IList <Book.Model.ProduceMaterialExit> SelectForListForm(DateTime startDate, DateTime endDate, string startPMEId, string endPMEId, string startPronoteHeaderId, string endPronoteHeaderId, Book.Model.Product startProduct, Book.Model.Product endProduct, Model.Depot DepotStart, Model.Depot DepotEnd, string InvoiceCusID) { return(accessor.SelectForListForm(startDate, endDate, startPMEId, endPMEId, startPronoteHeaderId, endPronoteHeaderId, startProduct, endProduct, DepotStart, DepotEnd, InvoiceCusID)); }
public IList <Model.ProduceMaterialdetails> SelectBycondition2(DateTime startDate, DateTime endDate, string produceMaterialId0, string produceMaterialId1, Book.Model.Product pId0, Book.Model.Product pId1, string departmentId0, string departmentId1, string PronoteHeaderId0, string PronoteHeaderId1, string CusInvoiceXOId, string handBookId) { SqlParameter[] parames = { new SqlParameter("@startDate", DbType.DateTime), new SqlParameter("@endDate", DbType.DateTime), new SqlParameter("@produceMaterialId0", DbType.String), new SqlParameter("@produceMaterialId1", DbType.String), new SqlParameter("@pId0", DbType.String), new SqlParameter("@pId1", DbType.String), new SqlParameter("@departmentId0", DbType.String), new SqlParameter("@departmentId1", DbType.String), new SqlParameter("@PronoteHeaderId0", DbType.String), new SqlParameter("@PronoteHeaderId1", DbType.String), new SqlParameter("@CusInvoiceXOId", DbType.String) }; parames[0].Value = startDate; parames[1].Value = endDate; if (!string.IsNullOrEmpty(produceMaterialId0)) { parames[2].Value = produceMaterialId0; } else { parames[2].Value = DBNull.Value; } if (!string.IsNullOrEmpty(produceMaterialId1)) { parames[3].Value = produceMaterialId1; } else { parames[3].Value = DBNull.Value; }; if (pId0 != null) { parames[4].Value = pId0.Id; } else { parames[4].Value = DBNull.Value; } if (pId1 != null) { parames[5].Value = pId1.Id; } else { parames[5].Value = DBNull.Value; } if (departmentId0 != null) { parames[6].Value = departmentId0; } else { parames[6].Value = DBNull.Value; } if (departmentId1 != null) { parames[7].Value = departmentId1; } else { parames[7].Value = DBNull.Value; } if (PronoteHeaderId0 != null) { parames[8].Value = PronoteHeaderId0; } else { parames[8].Value = DBNull.Value; } if (PronoteHeaderId1 != null) { parames[9].Value = PronoteHeaderId1; } else { parames[9].Value = DBNull.Value; } if (!string.IsNullOrEmpty(CusInvoiceXOId)) { parames[10].Value = CusInvoiceXOId; } else { parames[10].Value = DBNull.Value; } // sql.Append(" from ProduceMaterial p left join Workhouse w on w.WorkHouseId=p.WorkHouseId right join ProduceMaterialdetails d on p.ProduceMaterialID = d.ProduceMaterialID left join Product pro on d.ProductId = pro.ProductId"); StringBuilder sql = new StringBuilder(); sql.Append("SELECT pd.Materialprocesedsum,pd.Distributioned,pd.HandbookId,pd.HandbookProductId,pd.PronoteHeaderID,p.ProduceMaterialID,p.ProduceMaterialDate,p.ProduceMaterialdesc, w.Workhousename as WorkhouseName,pro.Id as PID,pro.ProductName,pro.CustomerProductName,pro.StocksQuantity,(SELECT CustomerInvoiceXOId FROM InvoiceXO WHERE InvoiceXO.InvoiceId = p.InvoiceXOId) AS CusXOId,pro.ProductDescription as ProductDescription,pd.ProductStock,pd.ProductId from ProduceMaterialdetails pd left join ProduceMaterial p on pd.ProduceMaterialID=p.ProduceMaterialID left join Workhouse w on w.WorkHouseId=p.WorkHouseId left join Product pro on pd.ProductId=pro.ProductId WHERE p.ProduceMaterialDate between @startDate and @endDate "); if (!string.IsNullOrEmpty(produceMaterialId0) && !string.IsNullOrEmpty(produceMaterialId1)) { sql.Append(" AND p.ProduceMaterialID between @produceMaterialId0 and @produceMaterialId1 "); } if (pId0 != null && pId1 != null) { sql.Append(" AND pro.Id between @pId0 and @pId1"); } //if (!string.IsNullOrEmpty(departmentId0) && !string.IsNullOrEmpty(departmentId1)) // sql.Append(" and p.Workhousename between @departmentId0 and @departmentId1"); if (!string.IsNullOrEmpty(departmentId0)) { sql.Append(" AND p.WorkHouseId = '" + departmentId0 + "'"); } if (!string.IsNullOrEmpty(PronoteHeaderId0) && !string.IsNullOrEmpty(PronoteHeaderId1)) { sql.Append(" AND p.InvoiceId between @PronoteHeaderId0 and @PronoteHeaderId1"); } if (!string.IsNullOrEmpty(CusInvoiceXOId)) { sql.Append(" AND p.InvoiceXOId = (SELECT InvoiceId FROM InvoiceXO WHERE CustomerInvoiceXOId = @CusInvoiceXOId)"); } if (!string.IsNullOrEmpty(handBookId)) { sql.Append(" And pd.HandbookId='" + handBookId + "'"); } sql.Append(" order by p.ProduceMaterialID desc "); return(this.DataReaderBind <Model.ProduceMaterialdetails>(sql.ToString(), parames, CommandType.Text)); }