private void rptRIIcome_Load(object sender, EventArgs e)
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
objConn.ConnectionString = strConn;
objConn.Open();
DataTable dtMain = null;
DataTable dtAudit = null;
DataSet ds = new DataSet();
string strSQL = null;
//*** for Main Report (Start) ***'
sb.Remove(0, sb.Length);
sb.Append("SELECT SALE_HEAD.SaleHeadId,");//เลขที่บิลขาย
sb.Append("CUSTOMER.CustomerName,");//ชื่อลูกค้า
sb.Append("SALE_HEAD.SaleHeadDateTime, ");//วันที่ขาย
sb.Append(" (");
sb.Append("ISNULL ((SELECT SUM(cast(SaleDetailQuantity * SaleDetailPriceUnit as decimal(18,2))) FROM SALE_DETAIL WHERE SaleHeadId = SALE_HEAD.SaleHeadId),0) ");
sb.Append(" )");
sb.Append(" As SaleDetailTotal,");//ราคาขาย
sb.Append(" SALE_HEAD.SaleShipping,");//ค่าขนส่ง
sb.Append(" SALE_HEAD.SaleVat,");//Vat
sb.Append(" SALE_HEAD.Discount,");//ส่วนลด
sb.Append("(");
sb.Append(" ISNULL((SELECT (SUM(cast(SaleDetailQuantity * SaleDetailPriceUnit as decimal(18,2))))- (Discount)+ (SaleVat + SaleShipping) FROM SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) and (SALE_HEAD.SaleHeadPayTypeId = 1)),0) ");
sb.Append(")");
sb.Append(" As Cash,");//ขายสด
sb.Append("(");
sb.Append(" ISNULL ((SELECT (SUM(cast(SaleDetailQuantity * SaleDetailPriceUnit as decimal(18,2)))) - (Discount)+ (SaleVat + SaleShipping) AS Expr1 FROM SALE_DETAIL AS SALE_DETAIL WHERE (SaleHeadId = SALE_HEAD.SaleHeadId) and ((SALE_HEAD.SaleHeadPayTypeId = 2) OR (SALE_HEAD.SaleHeadPayTypeId = 4) OR (SALE_HEAD.SaleHeadPayTypeId = 5))),0) ");
sb.Append(")");
sb.Append(" As Credit, ");//ขายเชื่อ
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 CompanyAddressEng FROM Company AS Company WHERE (CompanyCatalog = 'KTC')) AS CompanyAddressEng,");
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 CompanyAddressEng FROM Company AS Company WHERE (CompanyCatalog = '7CSK')) AS CompanyAddressEng,");
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 CompanyAddressEng FROM Company AS Company WHERE (CompanyCatalog = '7CTR')) AS CompanyAddressEng,");
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 ");
}
else if (strCastalog == "7CTP")
{
sb.Append("(SELECT CompanyName FROM Company WHERE (CompanyCatalog = '7CTP')) AS CompanyName,");
sb.Append("(SELECT CompanyNameEng FROM Company AS Company WHERE (CompanyCatalog = '7CTP')) AS CompanyNameEng,");
sb.Append("(SELECT CompanyAddress FROM Company AS Company WHERE (CompanyCatalog = '7CTP')) AS CompanyAddress,");
sb.Append("(SELECT CompanyPhone FROM Company AS Company WHERE (CompanyCatalog = '7CTP')) AS CompanyPhone,");
sb.Append("(SELECT CompanyFax FROM Company AS Company WHERE (CompanyCatalog = '7CTP')) AS CompanyFax ");
}
sb.Append("FROM SALE_HEAD INNER JOIN CUSTOMER ON SALE_HEAD.CustomerId = ");
sb.Append("CUSTOMER.CustomerId LEFT JOIN SALEHEADSTATUS ON SALE_HEAD.SaleHeadStatusId = ");
sb.Append("SALEHEADSTATUS.SaleHeadStatusId LEFT JOIN SALEHEADPAYTYPE ON ");
sb.Append("SALE_HEAD.SaleHeadPayTypeId = SALEHEADPAYTYPE.SaleHeadPayTypeId LEFT JOIN ");
sb.Append("DRAW ON SALE_HEAD.DrawId = DRAW.DrawId LEFT JOIN SALE_DETAIL ON ");
sb.Append("SALE_HEAD.SaleHeadId = SALE_DETAIL.SaleHeadId LEFT JOIN ");
sb.Append("PRODUCT ON SALE_DETAIL.ProductId = PRODUCT.ProductId CROSS JOIN Company ");
sb.Append("WHERE SALE_HEAD.SaleHeadDateTime BETWEEN @saleDate and @saleDate1 ");
sb.Append("AND SALE_HEAD.SaleHeadStatusId = '1' ");
if (CutValue == "0")
{
sb.Append("AND (SALE_HEAD.CustomerId != '2133') AND (SALE_HEAD.CustomerId != '1749') AND (SALE_HEAD.CustomerId !='2148') AND (SALE_HEAD.CustomerId != '2200') ");
}
if (CutValue == "1")
{
sb.Append("AND (SALE_HEAD.CustomerId != '2133') AND (SALE_HEAD.CustomerId != '1749') AND (SALE_HEAD.CustomerId !='2148') AND (SALE_HEAD.CustomerId !='1757') AND (SALE_HEAD.CustomerId !='1221') AND (SALE_HEAD.CustomerId != '2200')AND (SALE_HEAD.CustomerId != '1754') ");
}
sb.Append(" GROUP BY SALE_HEAD.SaleHeadId, CUSTOMER.CustomerName,SALE_HEAD.Discount,SALE_HEAD.SaleHeadPayTypeId,SALE_HEAD.SaleHeadDateTime,SALE_HEAD.SaleVat,SALE_HEAD.SaleShipping ");
sb.Append(" ORDER BY SALE_HEAD.SaleHeadId");
strSQL = sb.ToString();
objCmd = new SqlCommand(strSQL, objConn);
objCmd.Parameters.Add("@saleDate", SqlDbType.DateTime).Value = Convert.ToDateTime(saleDate).ToString("dd/MM/yyyy 00:00");
objCmd.Parameters.Add("@saleDate1", SqlDbType.DateTime).Value = Convert.ToDateTime(saleDate).ToString("dd/MM/yyyy 23:59");
var _with1 = objCmd;
_with1.Connection = objConn;
_with1.CommandText = strSQL;
_with1.CommandType = CommandType.Text;
dtAdapter.SelectCommand = objCmd;
dtAdapter.Fill(ds);
dtMain = ds.Tables[0];
//*** for Main Report (End) ***'
////*** for Audit Sub Report (Start) ***'
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("SELECT IncomeId, IncomeType, IncomeDate, IncomeDateTime, ");
sb.Append("IncomeSaleHeadId, IncomeSaleHeadDrawId, IncomeList, IncomeNote, IncomeTotalCash, IncomeTotalCredit, IncomeTotalOther ");
sb.Append("FROM Income ");
sb.Append("WHERE (IncomeDate BETWEEN @saleDate and @saleDate1) ");
strSQL = sb.ToString();
objCmd = new SqlCommand(strSQL, objConn);
objCmd.Parameters.Add("@saleDate", SqlDbType.DateTime).Value = Convert.ToDateTime(saleDate).ToString("dd/MM/yyyy 00:00");
objCmd.Parameters.Add("@saleDate1", SqlDbType.DateTime).Value = Convert.ToDateTime(saleDate).ToString("dd/MM/yyyy 23:59");
var _with2 = objCmd;
_with2.Connection = objConn;
_with2.CommandText = strSQL;
_with2.CommandType = CommandType.Text;
dtAdapter.SelectCommand = objCmd;
dtAdapter.Fill(ds);
dtAudit = ds.Tables[0];
////*** for Audit Sub Report (End) ***'
ReportDocument Ctr = new CryRIIncome();
//** for Main Report **'
//** for Audit Sub Report **'
//Ctr.Subreports["SubReportIncome.rpt"].Database.Tables[0].SetDataSource(dtAudit);
Ctr.Subreports["Income"].Database.Tables[0].SetDataSource(dtAudit);
Ctr.SetDataSource(dtMain);
this.CtrRIIncome.ReportSource = Ctr;
dtAdapter = null;
objConn.Close();
objConn = null;
}
public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
CryRIIncome rpt = new CryRIIncome();
rpt.Site = this.Site;
return rpt;
}