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