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