private void SaleDataNew() { String sql = "" + "declare @SaleId varchar(20) set @SaleId = '"+Idx+"' " + "SELECT CUSTOMER.customername as BrokerName,CUSTOMER.customeraddress as BrokerAddress,SALE_Store_HEAD.SaleHeadId, (SALE_store_HEAD.SaleHeadDate + 2) AS Saling ,SALE_store_HEAD.SaleHeadDateTime, " + "PRODUCT.ProductId,SALE_store_HEAD.NoInv,PRODUCT.ProductParentId, PRODUCT.ProductNameEng, SALE_store_DETAIL.SaleDetailQuantity, UNITSALE.UnitSaleName," + "CURRENCY.SaleCurrencyName, SALE_store_DETAIL.SaleDetailPriceUnit as SaleDetailPriceUnitInter, SALE_store_WEIGHT.SaleWeight,SALE_store_DETAIL.SaleDetailQuantityPack, SALE_store_WEIGHT.SalePack, " + "SALE_store_HEAD.Discount, SALE_store_HEAD.SaleVat,SALE_store_HEAD.SaleShipping,LOADING.LoadingName AS Loading,FEEDER.FeederName AS Feeder,FINALDESTENATION.FinalDestenationName " + "AS Destinatic,(SELECT SUM(SaleDetailtotal) FROM SALE_store_DETAIL WHERE (SaleHeadId = SALE_Store_HEAD.SaleHeadId) AND " + "(ProductId = PRODUCT.ProductId)) AS SaleDetailTotal,(SELECT SUM(SaleDetailtotal) FROM SALE_store_DETAIL AS SALE_store_DETAIL " + "WHERE (SaleHeadId = SALE_Store_HEAD.SaleHeadId)) AS Total,(SELECT SUM(SaleDetailtotal) - " + "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 WHERE (SaleHeadId = SALE_store_HEAD.SaleHeadId)) " + "AS SumAmountProduct,(SELECT sum((SaleDetailQuantityPack - 0.2)) FROM SALE_store_DETAIL WHERE (SaleHeadId = SALE_store_HEAD.SaleHeadId) AND (ProductId = PRODUCT.ProductId)) " + "AS NetWeight,(SELECT SUM(SaleDetailQuantity) FROM SALE_store_DETAIL WHERE (SaleHeadId = SALE_store_HEAD.SaleHeadId)) AS SumSaleDetailQuantity, " + "(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnit)* 0.04 FROM SALE_store_DETAIL AS SALE_store_DETAIL WHERE (SaleHeadId = SALE_store_HEAD.SaleHeadId)) AS FREIGHT, " + "(SELECT (SUM(SaleDetailQuantity * SaleDetailPriceUnit))-(SUM(SaleDetailQuantity * SaleDetailPriceUnit)* 0.04) FROM SALE_store_DETAIL AS SALE_store_DETAIL " + "WHERE (SaleHeadId = SALE_store_HEAD.SaleHeadId)) AS FOB,(SELECT SUM(SaleDetailQuantityPack) FROM SALE_store_DETAIL WHERE (SaleHeadId = SALE_store_HEAD.SaleHeadId)) AS " + "SumSaleDetailQuantityPack,(SELECT SUM(SaledetailWeight - (SaleDetailQuantityPack * 0.2) ) FROM SALE_store_DETAIL WHERE (SaleHeadId = SALE_store_HEAD.SaleHeadId)) AS " + "SumNetWeight,(SELECT SUM(SaledetailWeight) FROM 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.UnitPackName,SALE_store_HEAD.AbountId, SALE_store_HEAD.SaleHeadDrowId,UNITPACK.UnitPackName FROM SALE_store_HEAD INNER JOIN " + "SALE_store_DETAIL AS 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 SALE_store_WEIGHT ON " + "SALE_store_WEIGHT.saledetailid = SALE_store_DETAIL.saledetailid LEFT JOIN CURRENCY ON SALE_store_HEAD.SaleCurrencyId1 = " + "CURRENCY.SaleCurrencyId LEFT JOIN LOADING ON SALE_store_HEAD.LoadingId = LOADING.LoadingId LEFT JOIN FINALDESTENATION ON SALE_store_HEAD.FinalDestinationId = " + "FINALDESTENATION.FinalDestenationId LEFT JOIN FEEDER ON SALE_store_HEAD.FeederId = FEEDER.FeederId WHERE (SALE_store_HEAD.SaleHeadId = @SaleId) " + "ORDER BY SALE_store_DETAIL.SaleDetailId "; DataTable dt = Class.DBConnString.clsDB.QueryDataTable(sql); if (this.SG == "") { Report.CtrInvOriginal CtrSale = new KTCERP.Report.CtrInvOriginal(); CtrSale.SetDataSource(dt); CtrInvOriginal.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; } } else { Report.CtrInvOriginalSingapore CtrSale = new KTCERP.Report.CtrInvOriginalSingapore(); CtrSale.SetDataSource(dt); CtrInvOriginal.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; } } CtrInvOriginal.Refresh(); CtrInvOriginal.Show(); }
private void SaleData() { sb = new StringBuilder(); String sql; sb.Remove(0, sb.Length); sb.Append("SELECT SALE_HEAD.BrokerName,SALE_HEAD.BrokerAddress,SALE_HEAD.SaleHeadId, (SALE_HEAD.SaleHeadDate + 2) AS Saling ,SALE_HEAD.SaleHeadDateTime, PRODUCT.ProductId,SALE_HEAD.NoInv,PRODUCT.ProductParentId, "); sb.Append("PRODUCT.ProductNameEng, SALE_DETAIL.SaleDetailQuantity, UNITSALE.UnitSaleName,CURRENCY.SaleCurrencyName, "); 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,"); sb.Append("LOADING.LoadingName AS Loading,FEEDER.FeederName AS Feeder,FINALDESTENATION.FinalDestenationName AS 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 SaleDetailQuantity sb.Append("(SELECT SUM(SaleDetailQuantity) "); sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) "); sb.Append("AS SumSaleDetailQuantity,"); //FREIGHT sb.Append("(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnitInter)* 0.04 "); sb.Append("FROM SALE_DETAIL AS SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) AS FREIGHT,"); //FOB sb.Append("(SELECT (SUM(SaleDetailQuantity * SaleDetailPriceUnitInter))-(SUM(SaleDetailQuantity * SaleDetailPriceUnitInter)* 0.04) "); sb.Append("FROM SALE_DETAIL AS SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId)) AS FOB,"); 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,"); 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.CustomerFax, DRAW.DrawName, UNITCHILD.UnitChildName, UNITPACK.UnitPackName,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 CURRENCY ON SALE_HEAD.SaleCurrencyId = CURRENCY.SaleCurrencyId "); sb.Append("LEFT JOIN LOADING ON SALE_HEAD.LoadingId = LOADING.LoadingId "); sb.Append("LEFT JOIN FINALDESTENATION ON SALE_HEAD.FinalDestenationId = FINALDESTENATION.FinalDestenationId "); sb.Append("LEFT JOIN FEEDER ON SALE_HEAD.FeederId = FEEDER.FeederId "); 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(); if (this.SG == "") { Report.CtrInvOriginal CtrSale = new KTCERP.Report.CtrInvOriginal(); CtrSale.SetDataSource(SaleDataset.Tables[0]); CtrInvOriginal.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; } } else { Report.CtrInvOriginalSingapore CtrSale = new KTCERP.Report.CtrInvOriginalSingapore(); CtrSale.SetDataSource(SaleDataset.Tables[0]); CtrInvOriginal.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; } } CtrInvOriginal.Refresh(); CtrInvOriginal.Show(); }