private void SaleDataNew() { String sql = "" + " declare @SaleId varchar(20) set @SaleId = '"+Idx+"' "+ " SELECT distinct SALE_STORE_HEAD.SaleHeadId,SALE_STORE_HEAD.SaleHeadDate AS SaleHeadDateTime, PRODUCT.ProductId,SALE_STORE_HEAD.NoInv,PRODUCT.ProductParentId, " + "PRODUCT.ProductNameEng, (select sum(SaleDetailQuantity) from sale_store_detail where productid = Product.productid and SaleHeadId = SALE_STORE_HEAD.SaleHeadId) as SaleDetailQuantity, UNITSALE.UnitSaleName, SALE_STORE_Detail.SaleDetailPriceUnit as SaleDetailPriceUnitInter, "+ "(select sum(saleweight) from sale_store_weight where saledetailid in( select saledetailid from sale_store_detail where "+ "productid = PRODUCT.ProductId and saleheadid = SALE_STORE_HEAD.SaleHeadId)) as saleweight,(select sum(SaleDetailQuantityPack) from sale_store_detail where productid = Product.productid and SaleHeadId = SALE_STORE_HEAD.SaleHeadId) as AmountProduct, "+ "(select sum(salepack) from sale_store_weight where saledetailid in( select saledetailid from sale_store_detail where "+ "productid = PRODUCT.ProductId and saleheadid = SALE_STORE_HEAD.SaleHeadId)) as SalePack, SALE_STORE_HEAD.Discount, SALE_STORE_HEAD.SaleVat,SALE_STORE_HEAD.SaleShipping,MAINCATE.MainCateNameEng, "+ "CURRENCY.SaleCurrencyName,(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnit) FROM SALE_STORE_Detail as SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId) AND (ProductId = PRODUCT.ProductId)) AS SaleDetailTotal, "+ "(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnit) FROM SALE_STORE_Detail AS SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId)) AS Total, "+ "(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnit) - SALE_STORE_HEAD.Discount + SALE_STORE_HEAD.SaleVat + SALE_STORE_HEAD.SaleShipping AS Expr1 "+ "FROM SALE_STORE_Detail AS SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId)) AS TotalCash, "+ "(SELECT SUM(SaleDetailQuantityPack) FROM SALE_STORE_Detail as SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId)) AS SumAmountProduct, "+ "(SELECT sum(SaleDetailQuantityPack - 0.2) FROM SALE_STORE_Detail as SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId) AND (ProductId = PRODUCT.ProductId)) AS NetWeight, "+ "(SELECT SUM(SaleDetailQuantityPack) FROM SALE_STORE_Detail as SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId)) AS SumSaleDetailQuantityPack, "+ "(SELECT SUM(SaledetailWeight - (SaleDetailQuantityPack * 0.2) ) FROM SALE_STORE_Detail as SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId)) AS SumNetWeight, "+ "(SELECT SUM(SaledetailWeight) FROM SALE_STORE_Detail as SALE_STORE_Detail WHERE (SaleHeadId = SALE_STORE_HEAD.SaleHeadId)) AS SumGrossWeight, "+ "(SELECT CompanyName FROM Company WHERE (CompanyCatalog = 'KTC')) AS CompanyName,(SELECT CompanyNameEng FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyNameEng, "+ "(SELECT CompanyAddress FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyAddress, (SELECT CompanyPhone FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyPhone, "+ "(SELECT CompanyFax FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyFax, CUSTOMER.CustomerLable,CUSTOMER.CustomerName, CUSTOMER.CustomerAddress, CUSTOMER.CustomerPhone1, "+ "CUSTOMER.CustomerFax, DRAW.DrawName, "+ "UNITCHILD.UnitChildName, UNITPACK.UnitPackNameEng,SALE_STORE_HEAD.AbountId, SALE_STORE_HEAD.SaleHeadDrowId,UNITPACK.UnitPackName "+ "FROM SALE_STORE_HEAD INNER JOIN SALE_STORE_Detail ON SALE_STORE_HEAD.SaleHeadId = SALE_STORE_Detail.SaleHeadId LEFT JOIN PRODUCT ON SALE_STORE_Detail.ProductId = PRODUCT.ProductId LEFT JOIN UNITSALE ON "+ "PRODUCT.UnitSaleId = UNITSALE.UnitSaleId LEFT JOIN CUSTOMER ON SALE_STORE_HEAD.CustomerId = CUSTOMER.CustomerId LEFT JOIN DRAW ON SALE_STORE_HEAD.DrowId = DRAW.DrawId "+ "LEFT JOIN UNITPACK ON PRODUCT.UnitPackId = UNITPACK.UnitPackId LEFT JOIN UNITCHILD ON PRODUCT.UnitChildId = UNITCHILD.UnitChildId LEFT JOIN MAINCATE ON PRODUCT.MainCateId = MAINCATE.MainCateId "+ "LEFT JOIN CURRENCY ON SALE_STORE_HEAD.SaleCurrencyId1 = CURRENCY.SaleCurrencyId WHERE (SALE_STORE_HEAD.SaleHeadId = @SaleId) "; DataTable dt = Class.DBConnString.clsDB.QueryDataTable(sql); Report.CtrSaleInvInter CtrSale = new KTCERP.Report.CtrSaleInvInter(); CtrSale.SetDataSource(dt); CrvInvInter.ReportSource = CtrSale; string Db = Class.DBConnString.sDb; DataTable dtCompany = Class.DBConnString.clsDB.QueryDataTable("SELECT * FROM dbo.Company WHERE CompanyCatalog = '" + Db + "'"); if (dtCompany.Rows.Count > 0) { string telFax = "Tel." + dtCompany.Rows[0]["CompanyPhone"].IsNullAsEmpty() + " Fax." + dtCompany.Rows[0]["CompanyFax"].IsNullAsEmpty(); ((CrystalDecisions.CrystalReports.Engine.TextObject)CtrSale.ReportDefinition.ReportObjects["txtCompany"]).Text = dtCompany.Rows[0]["CompanyNameEng"].IsNullAsEmpty(); ((CrystalDecisions.CrystalReports.Engine.TextObject)CtrSale.ReportDefinition.ReportObjects["txtAddress"]).Text = dtCompany.Rows[0]["CompanyAddressEng"].IsNullAsEmpty(); ((CrystalDecisions.CrystalReports.Engine.TextObject)CtrSale.ReportDefinition.ReportObjects["txtTelFax"]).Text = telFax; } CrvInvInter.Refresh(); CrvInvInter.Show(); }
private void SaleData() { sb = new StringBuilder(); String sql; sb.Remove(0, sb.Length); sb.Append("SELECT SALE_HEAD.SaleHeadId, SALE_HEAD.SaleHeadDateTime, PRODUCT.ProductId,SALE_HEAD.NoInv,PRODUCT.ProductParentId, "); sb.Append("PRODUCT.ProductNameEng, SALE_DETAIL.SaleDetailQuantity, UNITSALE.UnitSaleName, "); sb.Append("SALE_DETAIL.SaleDetailPriceUnitInter, SALE_WEIGHT.SaleWeight,SALE_DETAIL.AmountProduct, "); //sb.Append("SALE_WEIGHT.SalePack, SALE_HEAD.Discount, SALE_HEAD.SaleVat,SALE_HEAD.SaleShipping,MAINCATE.MainCateNameEng,CURRENCY.SaleCurrencyName,"); sb.Append("SALE_WEIGHT.SalePack, SALE_HEAD.Discount, SALE_HEAD.SaleVat,SALE_HEAD.SaleShipping,MAINCATE.MainCateNameEng,CASE CURRENCY.SaleCurrencyName WHEN 'ไม่ระบุ' THEN 'Unknown'END AS 'SaleCurrencyName',"); //sb.Append("SALE_HEAD.Loading,SALE_HEAD.Feeder,SALE_HEAD.Destinatic,"); sb.Append("(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnitInter) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) AND (ProductId = "); sb.Append("PRODUCT.ProductId)) AS SaleDetailTotal,"); sb.Append("(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnitInter) "); sb.Append("FROM SALE_DETAIL AS SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) AS Total,"); sb.Append("(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnitInter) - SALE_HEAD.Discount + "); sb.Append("SALE_HEAD.SaleVat + SALE_HEAD.SaleShipping AS Expr1 FROM SALE_DETAIL AS SALE_DETAIL "); sb.Append("WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) AS TotalCash, "); //Sum AmountProduct sb.Append("(SELECT SUM(AmountProduct) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) "); sb.Append("AS SumAmountProduct,"); //Net Weight sb.Append("(SELECT (SaleDetailQuantityPack - 0.2) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) AND (ProductId = "); sb.Append("PRODUCT.ProductId)) AS NetWeight,"); ////Sum Net Weight if (UnitSaleId == "1") { //Sum SaleDetailQuantityPack sb.Append("(SELECT SUM(SaleDetailQuantity) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) "); sb.Append("AS SumSaleDetailQuantityPack,"); //Sum Sum NetWeight sb.Append("(SELECT SUM(SaleDetailQuantity - (AmountProduct * 0.2) ) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) "); sb.Append("AS SumNetWeight,"); } else { //Sum SaleDetailQuantityPack sb.Append("(SELECT SUM(SaleDetailQuantityPack) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) "); sb.Append("AS SumSaleDetailQuantityPack,"); //Sum Sum NetWeight sb.Append("(SELECT SUM(SaleDetailQuantityPack - (AmountProduct * 0.2) ) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) "); sb.Append("AS SumNetWeight,"); } if (strCastalog == "KTC") { sb.Append("(SELECT CompanyName FROM Company WHERE (CompanyCatalog = 'KTC')) AS CompanyName,"); sb.Append("(SELECT CompanyNameEng FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyNameEng,"); sb.Append("(SELECT CompanyAddress FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyAddress,"); sb.Append("(SELECT CompanyPhone FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyPhone,"); sb.Append("(SELECT CompanyFax FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyFax, "); } else if (strCastalog == "7CSK") { sb.Append("(SELECT CompanyName FROM Company WHERE (CompanyCatalog = '7CSK')) AS CompanyName,"); sb.Append("(SELECT CompanyNameEng FROM Company AS Company WHERE (CompanyCatalog = '7CSK')) AS CompanyNameEng,"); sb.Append("(SELECT CompanyAddress FROM Company AS Company WHERE (CompanyCatalog = '7CSK')) AS CompanyAddress,"); sb.Append("(SELECT CompanyPhone FROM Company AS Company WHERE (CompanyCatalog = '7CSK')) AS CompanyPhone,"); sb.Append("(SELECT CompanyFax FROM Company AS Company WHERE (CompanyCatalog = '7CSK')) AS CompanyFax, "); } else if (strCastalog == "7CTR") { sb.Append("(SELECT CompanyName FROM Company WHERE (CompanyCatalog = '7CTR')) AS CompanyName,"); sb.Append("(SELECT CompanyNameEng FROM Company AS Company WHERE (CompanyCatalog = '7CTR')) AS CompanyNameEng,"); sb.Append("(SELECT CompanyAddress FROM Company AS Company WHERE (CompanyCatalog = '7CTR')) AS CompanyAddress,"); sb.Append("(SELECT CompanyPhone FROM Company AS Company WHERE (CompanyCatalog = '7CTR')) AS CompanyPhone,"); sb.Append("(SELECT CompanyFax FROM Company AS Company WHERE (CompanyCatalog = '7CTR')) AS CompanyFax, "); } else if (strCastalog == "SHOP") { sb.Append("(SELECT CompanyName FROM Company WHERE (CompanyCatalog = 'SHOP')) AS CompanyName,"); sb.Append("(SELECT CompanyNameEng FROM Company AS Company WHERE (CompanyCatalog = 'SHOP')) AS CompanyNameEng,"); sb.Append("(SELECT CompanyAddress FROM Company AS Company WHERE (CompanyCatalog = 'SHOP')) AS CompanyAddress,"); sb.Append("(SELECT CompanyPhone FROM Company AS Company WHERE (CompanyCatalog = 'SHOP')) AS CompanyPhone,"); sb.Append("(SELECT CompanyFax FROM Company AS Company WHERE (CompanyCatalog = 'SHOP')) AS CompanyFax, "); } sb.Append("CUSTOMER.CustomerLable,CUSTOMER.CustomerName, CUSTOMER.CustomerAddress, CUSTOMER.CustomerPhone1, "); //sb.Append("CUSTOMER.CustomerLable,CUSTOMER.CustomerName,CUSTOMER.CustomerAddress + CASE CUSTOMER.CustomerFax WHEN '' THEN '' ELSE ' Fax :' + CUSTOMER.CustomerFax END as CustomerAddress, CUSTOMER.CustomerPhone1, "); sb.Append("CUSTOMER.CustomerFax, DRAW.DrawName, UNITCHILD.UnitChildName, UNITPACK.UnitPackNameEng,SALE_HEAD.AbountId, SALE_HEAD.SaleHeadDrawId,UNITPACK.UnitPackName "); sb.Append("FROM SALE_HEAD INNER JOIN SALE_DETAIL AS SALE_DETAIL ON SALE_HEAD.SaleHeadId = "); sb.Append("SALE_DETAIL.SaleHeadId LEFT JOIN PRODUCT ON SALE_DETAIL.ProductId = PRODUCT.ProductId "); sb.Append("LEFT JOIN UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId LEFT JOIN "); sb.Append("CUSTOMER ON SALE_HEAD.CustomerId = CUSTOMER.CustomerId LEFT JOIN "); sb.Append("DRAW ON SALE_HEAD.DrawId = DRAW.DrawId LEFT JOIN UNITPACK ON PRODUCT.UnitPackId = "); sb.Append("UNITPACK.UnitPackId LEFT JOIN UNITCHILD ON PRODUCT.UnitChildId = UNITCHILD.UnitChildId "); sb.Append("LEFT JOIN SALE_WEIGHT ON SALE_HEAD.SaleHeadId = SALE_WEIGHT.SaleHeadId AND "); sb.Append("PRODUCT.ProductId = SALE_WEIGHT.ProductId LEFT JOIN MAINCATE ON PRODUCT.MainCateId = MAINCATE.MainCateId LEFT JOIN CURRENCY ON SALE_HEAD.SaleCurrencyId = CURRENCY.SaleCurrencyId "); sb.Append("WHERE (SALE_HEAD.SaleHeadId = @SaleId) "); sb.Append(" ORDER BY SALE_DETAIL.SaleDetailId"); sql = sb.ToString(); com = new SqlCommand(sql, Conn); com.Parameters.Add("@SaleId", SqlDbType.Int).Value = SaleId; // SaleDa = new SqlDataAdapter(com); SaleDa.Fill(SaleDataset, "Sale"); Conn.Close(); Report.CtrSaleInvInter CtrSale = new KTCERP.Report.CtrSaleInvInter(); CtrSale.SetDataSource(SaleDataset.Tables[0]); CrvInvInter.ReportSource = CtrSale; string Db = Class.DBConnString.sDb; DataTable dtCompany = Class.DBConnString.clsDB.QueryDataTable("SELECT * FROM dbo.Company WHERE CompanyCatalog = '" + Db + "'"); if (dtCompany.Rows.Count > 0) { string telFax = "Tel." + dtCompany.Rows[0]["CompanyPhone"].IsNullAsEmpty() + " Fax." + dtCompany.Rows[0]["CompanyFax"].IsNullAsEmpty(); ((CrystalDecisions.CrystalReports.Engine.TextObject)CtrSale.ReportDefinition.ReportObjects["txtCompany"]).Text = dtCompany.Rows[0]["CompanyNameEng"].IsNullAsEmpty(); ((CrystalDecisions.CrystalReports.Engine.TextObject)CtrSale.ReportDefinition.ReportObjects["txtAddress"]).Text = dtCompany.Rows[0]["CompanyAddressEng"].IsNullAsEmpty(); ((CrystalDecisions.CrystalReports.Engine.TextObject)CtrSale.ReportDefinition.ReportObjects["txtTelFax"]).Text = telFax; } CrvInvInter.Refresh(); CrvInvInter.Show(); }