Пример #1
0
        private void cboReqId_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataTable tt = null;

            if (cboReqId.SelectedIndex == 0)
                tt = Class.DBConnString.clsDB.QueryDataTable(sql_new_all());
            else
            {
                tt = Class.DBConnString.clsDB.QueryDataTable(sql_new_sub(cboReqId.Text));
            }

            Report.CtrPackingList CtrSale = new KTCERP.Report.CtrPackingList();
            CtrSale.SetDataSource(tt);

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

            CtrPackingList.ReportSource = CtrSale;
            CtrPackingList.Refresh();
            CtrPackingList.Show();
        }
Пример #2
0
        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, ");
            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,");

            //Sum Unit Price Inter
            sb.Append("(SELECT SUM(SaleDetailQuantity * SaleDetailPriceUnitInter) ");
            sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) AND (ProductId = ");
            sb.Append("PRODUCT.ProductId)) AS SaleDetailTotal,");
            //Sum Price Inter
            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,");

            //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")
            {
                sb.Append("(SELECT (SaleDetailQuantity) ");
                sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) AND (ProductId = PRODUCT.ProductId)) ");
                sb.Append("AS SaleDetailQuantityPack,");
                //Net Weight
                sb.Append("(SELECT SaleDetailQuantity - (AmountProduct * 0.2)  ");
                sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) AND (ProductId = ");
                sb.Append("PRODUCT.ProductId)) AS NetWeight,");
                //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
            {
                sb.Append("SALE_DETAIL.SaleDetailQuantityPack,");
                //Net Weight
                sb.Append("(SELECT SaleDetailQuantityPack - (AmountProduct * 0.2) ");
                sb.Append("FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) AND (ProductId = ");
                sb.Append("PRODUCT.ProductId)) AS NetWeight,");
                //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.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 ");

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

            Report.CtrPackingList CtrSale = new KTCERP.Report.CtrPackingList();
            CtrSale.SetDataSource(SaleDataset.Tables[0]);

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

            CtrPackingList.ReportSource = CtrSale;
            CtrPackingList.Refresh();
            CtrPackingList.Show();
        }