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