/*แสดงรายงานทะเบียนคุมใบเบิก*/
private void button1_Click(object sender, EventArgs e)
{
Class.clsDatabase cls = null;
String local = "select GrStockDetailId,ip,db,dbuser,dbpass FROM GrStockDetail WHERE GrStockDetailId = " + Id;
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (d["GrStockDetailId"].ToString() == "") return;
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString()
, d["dbuser"].ToString(), d["dbpass"].ToString(), "");
String sql = "";
if (Class.DBConnString.sDb == "7CSK")
{
sql = "select h.SdateId,h.TranferReqHeadDateSave as saleappheaddatesave,h.TranferReqHeadDate as saleappheaddate,c.GrStockDetailLname as customername,s.tranferstatusname as statusname " +
",CASE WHEN h.sdateid IN (SELECT tbb.SdateId FROM [7csk].dbo.TranferBetweenBrnDt tbb) THEN (SELECT tranferid FROM [7csk].dbo.TranferBetweenBrnDt tbb WHERE tbb.SdateId = h.SdateId ) ELSE '-' END as saleheadid,(select sh.tranferdate from [7csk].dbo.TranferBetweenBrn sh where h.Sdateid in(SELECT tbbd.SdateId FROM [7csk].dbo.TranferBetweenBrnDt tbbd WHERE sh.TranferId = tbbd.TranferId)) as saledate ";
sql += "," +
"(SELECT grstockdetailLname from grstockdetail where grstockdetailid = " + Id + ") AS CompanyName ";
sql += "from tranferreqhead h inner join grstockdetail c on h.grstockdetailid = c.grstockdetailid inner join tranferstatus s on h.statusid = s.tranferstatusid " +
"where h.tranferreqheaddatesave between '" + Class.Function.GetDate(dateTimePicker1.Value) + " 00:00:00' and '" + Class.Function.GetDate(dateTimePicker2.Value) + " 23:59:59' ";
}
else {
sql = "select h.SdateId,h.TranferReqHeadDateSave as saleappheaddatesave,h.TranferReqHeadDate as saleappheaddate,c.GrStockDetailLname as customername,s.tranferstatusname as statusname " +
",CASE WHEN h.sdateid IN (SELECT tbb.SdateId FROM TranferBetweenBrnDt tbb) THEN (SELECT tranferid FROM TranferBetweenBrnDt tbb WHERE tbb.SdateId = h.SdateId ) ELSE '-' END as saleheadid,(select sh.tranferdate from TranferBetweenBrn sh where h.Sdateid in(SELECT tbbd.SdateId FROM TranferBetweenBrnDt tbbd WHERE sh.TranferId = tbbd.TranferId)) as saledate ";
sql += "," +
"(SELECT grstockdetailLname from grstockdetail where grstockdetailid = " + Id + ") AS CompanyName ";
sql += "from tranferreqhead h inner join grstockdetail c on h.grstockdetailid = c.grstockdetailid inner join tranferstatus s on h.statusid = s.tranferstatusid " +
"where h.tranferreqheaddatesave between '" + Class.Function.GetDate(dateTimePicker1.Value) + " 00:00:00' and '" + Class.Function.GetDate(dateTimePicker2.Value) + " 23:59:59' ";
}
Report.CtrRequisRegis Crv = new Report.CtrRequisRegis();
Crv.SetDataSource(cls.QueryDataTable(sql));
Crv.SetParameterValue("sdate", dateTimePicker1.Value);
Crv.SetParameterValue("edate", dateTimePicker2.Value);
((TextObject)Crv.Section2.ReportObjects["Text9"]).Text = "สาขาผู้รับโอน";
((TextObject)Crv.Section2.ReportObjects["Text11"]).Text = "หมายเลขใบโอน";
((TextObject)Crv.Section2.ReportObjects["Text13"]).Text = "วันที่โอน";
crystalReportViewer1.ReportSource = Crv;
crystalReportViewer1.Refresh();
crystalReportViewer1.Show();
}
private void button1_Click(object sender, EventArgs e)
{
String sql = "";
#region
#endregion
if (ol == Overload.Sale)
{
#region
sql = "SELECT sh.SaleHeadId,sh.SaleHeadDate " +
",c.CustomerName " +
",t.total " +
",sah.SaleAppHeadId,sah.SaleAppHeadDate " +
",ss.SaleHeadStatusName" +
",c2.ConfirmName " +
",s.SaleHeadPayTypeName " +
"FROM ( " +
" SELECT sd.SaleHeadId,SUM(sd.SaleDetailTotal) AS total " +
" FROM dbo.SALE_DETAIL_view sd " +
" GROUP BY sd.SaleHeadId " +
")t " +
"INNER JOIN dbo.SALE_HEAD_view sh ON sh.SaleHeadId = t.SaleHeadId " +
"LEFT JOIN dbo.CUSTOMER c ON c.CustomerId = sh.CustomerId " +
"LEFT JOIN dbo.SALE_APP_HEAD sah ON sah.SaleHeadId = cast(sh.SaleHeadId AS varchar(15)) " +
"LEFT JOIN dbo.CONFIRM c2 ON c2.ConfirmId = sh.ConfirmId " +
"LEFT JOIN dbo.SALEHEADPAYTYPE s ON s.SaleHeadPayTypeId = sh.SaleHeadPayTypeId " +
"LEFT JOIN dbo.SALEHEADSTATUS ss ON ss.SaleHeadStatusId = sh.SaleHeadStatus " +
"WHERE sh.SaleHeadDate BETWEEN '2015-08-01-' AND '2015-10-31+' " +
"ORDER BY sh.SaleHeadDate";
sql = sql.Replace("2015-08-01-", Class.Function.GetDate(dateTimePicker1.Value));
sql = sql.Replace("2015-10-31+", Class.Function.GetDate(dateTimePicker2.Value));
DataTable data = Class.DBConnString.clsDB.QueryDataTable(sql);
data.TableName = "DT_SaleListApp";
string CompanyName = QueryCompanyName();
Report.CtrSaleApp.CtrSaleListApp r = new Report.CtrSaleApp.CtrSaleListApp();
r.SetDataSource(data);
r.SetParameterValue("Company", CompanyName);
r.SetParameterValue("sdate", dateTimePicker1.Value);
r.SetParameterValue("edate", dateTimePicker2.Value);
crystalReportViewer1.ReportSource = r;
crystalReportViewer1.Refresh();
crystalReportViewer1.Show();
#endregion
}
else if (ol == Overload.App)
{
#region
sql = "SELECT sah.SaleAppHeadId,sah.SaleAppHeadDate " +
",c.CustomerName " +
",p.ProductId,p.ProductName " +
",sad.SaleAppDetailQuantity " +
",sas.statusName " +
",sh.SaleHeadId,sh.SaleHeadDate " +
",c2.CustomerName CustomerName1 " +
",p2.ProductId ProductId1,p2.ProductName ProductName1 " +
",sd.SaleDetailQuantity " +
",sd.SaleDetailPriceUnit " +
",sd.SaleDetailTotal " +
",s.SaleHeadStatusName " +
",(SELECT SUM(sd2.SaleDetailTotal) FROM dbo.SALE_DETAIL_view sd2 WHERE sd2.SaleHeadId = sh.SaleHeadId) total " +
"FROM dbo.SALE_APP_HEAD sah " +
"INNER JOIN dbo.SALE_APP_DETAIL sad ON sad.SaleAppHeadId = sah.SaleAppHeadId " +
"INNER JOIN dbo.CUSTOMER c ON c.CustomerId = sah.CustomerId " +
"INNER JOIN dbo.PRODUCT p ON p.ProductId = sad.ProductId " +
"INNER JOIN dbo.Sale_App_Status sas ON sas.statusId = sah.statusId " +
"LEFT JOIN dbo.SALE_HEAD_view sh ON sh.SaleHeadId = sah.SaleHeadId " +
"LEFT JOIN dbo.SALE_DETAIL_view sd ON sd.SaleHeadId = sh.SaleHeadId AND sd.ProductId = sad.ProductId " +
"LEFT JOIN dbo.CUSTOMER c2 ON c2.CustomerId = sh.CustomerId " +
"LEFT JOIN dbo.PRODUCT p2 ON p2.ProductId = sd.ProductId " +
"LEFT JOIN dbo.SALEHEADSTATUS s ON s.SaleHeadStatusId = sh.SaleHeadStatus " +
"WHERE sah.SaleAppHeadDate BETWEEN '2015-01-01-' AND '2015-12-31+' " +
(txtFilter.Text == "" ? "" : " AND sah.SaleAppHeadId IN (" + txtFilter.Text + ") ") +
"ORDER BY sah.SaleAppHeadId ";
sql = sql.Replace("2015-01-01-", Class.Function.GetDate(dateTimePicker1.Value));
sql = sql.Replace("2015-12-31+", Class.Function.GetDate(dateTimePicker2.Value));
try
{
DataTable data = Class.DBConnString.clsDB.QueryDataTable(sql);
data.TableName = "DT_AppListSale";
string CompanyName = QueryCompanyName();
Report.CtrSaleApp.CtrAppListSale r = new Report.CtrSaleApp.CtrAppListSale();
r.SetDataSource(data);
r.SetParameterValue("Company", CompanyName);
r.SetParameterValue("sdate", dateTimePicker1.Value);
r.SetParameterValue("edate", dateTimePicker2.Value);
crystalReportViewer1.ReportSource = r;
crystalReportViewer1.Refresh();
crystalReportViewer1.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
#endregion
}
else if(ol == Overload.SaleApp){
#region
sql = "SELECT sah.SaleAppHeadId,sah.SaleAppHeadDate " +
",c.CustomerName " +
",p.ProductId,p.ProductName " +
",sad.SaleAppDetailQuantity " +
",sas.statusName " +
",sh.SaleHeadId,sh.SaleHeadDate " +
",c2.CustomerName CustomerName1 " +
",p2.ProductId ProductId1,p2.ProductName ProductName1 " +
",sd.SaleDetailQuantity " +
",sd.SaleDetailPriceUnit " +
",sd.SaleDetailTotal " +
",s.SaleHeadStatusName " +
",(SELECT SUM(sd2.SaleDetailTotal) FROM dbo.SALE_DETAIL_view sd2 WHERE sd2.SaleHeadId = sh.SaleHeadId) total " +
"FROM dbo.SALE_HEAD_view sh " +
"INNER JOIN dbo.SALE_DETAIL_view sd ON sd.SaleHeadId = sh.SaleHeadId " +
"INNER JOIN dbo.CUSTOMER c2 ON c2.CustomerId = sh.CustomerId " +
"INNER JOIN dbo.PRODUCT p2 ON p2.ProductId = sd.ProductId " +
"INNER JOIN dbo.SALEHEADSTATUS s ON s.SaleHeadStatusId = sh.SaleHeadStatus " +
"INNER JOIN dbo.SALE_APP_HEAD sah ON sh.SaleHeadId = sah.SaleHeadId " +
"LEFT JOIN dbo.SALE_APP_DETAIL sad ON sad.SaleAppHeadId = sah.SaleAppHeadId AND sd.ProductId = sad.ProductId " +
"LEFT JOIN dbo.CUSTOMER c ON c.CustomerId = sah.CustomerId " +
"LEFT JOIN dbo.PRODUCT p ON p.ProductId = sad.ProductId " +
"LEFT JOIN dbo.Sale_App_Status sas ON sas.statusId = sah.statusId " +
"WHERE sah.SaleAppHeadDate BETWEEN '2015-01-01-' AND '2015-12-31+' " +
(txtFilter.Text.Trim() == "" ? "" : " AND sh.SaleHeadId IN ("+txtFilter.Text+") ") +
"ORDER BY sah.SaleAppHeadId ";
sql = sql.Replace("2015-01-01-", Class.Function.GetDate(dateTimePicker1.Value));
sql = sql.Replace("2015-12-31+", Class.Function.GetDate(dateTimePicker2.Value));
try
{
DataTable data = Class.DBConnString.clsDB.QueryDataTable(sql);
data.TableName = "DT_AppListSale";
string CompanyName = QueryCompanyName();
Report.CtrSaleApp.CtrSaleListAppDetail r = new Report.CtrSaleApp.CtrSaleListAppDetail();
r.SetDataSource(data);
r.SetParameterValue("Company", CompanyName);
r.SetParameterValue("sdate", dateTimePicker1.Value);
r.SetParameterValue("edate", dateTimePicker2.Value);
crystalReportViewer1.ReportSource = r;
crystalReportViewer1.Refresh();
crystalReportViewer1.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
#endregion
}
else
{
#region
sql = "select h.SdateId,h.SaleAppHeadDateSave,h.SaleAppHeadDate,c.CustomerName,s.statusname " +
",Case when h.saleheadid = null then '-' else cast(h.saleheadid as varchar(20)) end as saleheadid,(select sh.saleheaddatetime from sale_head_view sh where sh.saleheadid = h.saleheadid) as saledate ";
if (Class.DBConnString.sDb == "KTC")
{
sql += "," +
"(SELECT CompanyName FROM Company WHERE (CompanyCatalog = 'KTC')) AS CompanyName ";
}
else if (Class.DBConnString.sDb == "7CSK")
{
sql += "," +
"(SELECT CompanyName FROM Company WHERE (CompanyCatalog = '7SEA')) AS CompanyName ";
}
else if (Class.DBConnString.sDb == "SHOP")
{
sql += "," +
"(SELECT CompanyName FROM Company WHERE (CompanyCatalog = 'SHOP')) AS CompanyName ";
}
sql += "from sale_app_head h inner join customer c on h.customerId = c.customerId inner join sale_app_status s on h.statusid = s.statusid " +
"where h.saleappheaddatesave between '" + Class.Function.GetDate(dateTimePicker1.Value) + " 00:00:00' and '" + Class.Function.GetDate(dateTimePicker2.Value) + " 23:59:59' ";
Report.CtrRequisRegis Crv = new Report.CtrRequisRegis();
Crv.SetDataSource(Class.DBConnString.clsDB.QueryDataTable(sql));
Crv.SetParameterValue("sdate", dateTimePicker1.Value);
Crv.SetParameterValue("edate", dateTimePicker2.Value);
crystalReportViewer1.ReportSource = Crv;
crystalReportViewer1.Refresh();
crystalReportViewer1.Show();
#endregion
}
}
