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