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