public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
rpt_Camera01 rpt = new rpt_Camera01();
rpt.Site = this.Site;
return(rpt);
}
private void BtnOk_OnClick(object sender, RoutedEventArgs e)
{
_cls.ClsConnect();
//ora.ClsConnect();
try
{
string nam = dtpNgay.SelectedDate.Value.ToString("yyyy");
string thang = dtpNgay.SelectedDate.Value.ToString("MM");
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
#region
/*
* string strdel= "delete from DULIEU_NT where YEAR(NGAYBC)='"+dtpNgay.SelectedDate.Value.ToString("yyyy")+"' and MONTH(NGAYBC)='"+ dtpNgay.SelectedDate.Value.ToString("MM")+"'";
* //MessageBox.Show(strdel);
* _cls.UpdateDataText(strdel);
* string strsql = "select KHOA, THUTU, TT_HIENTHI, MA, TEN,to_char(NGAYBC,'YYYY-MM-DD') NGAYBC, NAMBC, MAPGD, CO_TONGHOP, MACN, NGUOI_NHAP,to_char(NGAY_NHAP,'YYYY-MM-DD') NGAY_NHAP, NGUOI_DUYET, NGAY_DUYET, D1"
+ ", D2, D3, D4, D5, D6, D7, D8, D9, D10, D11, D12, D13, D14, D15, D16, D17, D18, D19, D20, D21, D22, D23, D24, D25, D26, D27, D28, D29"
+", D30, NHAPTAY, FONTFORMAT, KIEUIN from dulieu_nt where khoa = 'GSCMR_001' and to_char(NGAYBC, 'MM/YYYY') = '"+ dtpNgay.SelectedDate.Value.ToString("MM/yyyy") + "' ";
+ //MessageBox.Show(strsql);
+ _dt = ora.LoadDataText(strsql);
+ foreach (DataRow dr in _dt.Rows)
+ {
+ string sqladd =
+ "insert into DULIEU_NT(KHOA,THUTU,TT_HIENTHI,MA,TEN,NGAYBC,NAMBC,MAPGD,CO_TONGHOP,MACN,NGUOI_NHAP,NGAY_NHAP,NGUOI_DUYET,NGAY_DUYET "
+
+ ", D1, D2, D3, D4, D5, D6, D7, D8, D9, D10, D11, D12, D13, D14, D15, D16, D17, D18, D19, D20, D21, D22, D23, D24, D25 "
+ ", D26, D27, D28, D29, D30, NHAPTAY, FONTFORMAT, KIEUIN)" + " Values('" + dr["KHOA"]
+ "','" + _str.Right(dr["MA"].ToString(),2) + "','" + dr["TT_HIENTHI"] + "','" + dr["MA"] +
+ "',N'" + dr["TEN"] + "','" + dr["NGAYBC"] + "','" + dr["NAMBC"] + "','" + dr["MAPGD"] + "','" +
+ dr["CO_TONGHOP"] +
+ "','" + dr["MACN"] + "','" + dr["NGUOI_NHAP"] + "','" + dr["NGAY_NHAP"] + "','" +
+ dr["NGUOI_DUYET"] + "','" + dr["NGAY_DUYET"] + "',N'" + dr["D1"] + "','" + dr["D2"] + "',N'" +
+ dr["D3"] + "',N'"
+ dr["D4"] + "','" + dr["D5"] + "','" + dr["D6"] + "','" + dr["D7"] + "','" + dr["D8"] + "','" +
+ dr["D9"] + "','" + dr["D10"] + "','" + dr["D11"] + "','" + dr["D12"] + "','" + dr["D13"] + "','" +
+ dr["D14"]
+ "','" + dr["D15"] + "','" + dr["D16"] + "','" + dr["D17"] + "','" + dr["D18"] + "','" +
+ dr["D19"] + "','" + dr["D20"] + "','" + dr["D21"] + "','" + dr["D22"] + "','" + dr["D23"] +
+ "','" + dr["D24"] + "','" + dr["D25"]
+ "','" + dr["D26"] + "','" + dr["D27"] + "','" + dr["D28"] + "','" + dr["D29"] + "','" +
+ dr["D30"] + "','" + dr["NHAPTAY"] + "','" + dr["FONTFORMAT"] + "',0)";
+ _cls.UpdateDataText(sqladd);
+ }
+ string strin = "with CT2 as "
+" (select * from(select a.NGAYBC, a.D5 MAXA, b.CVI_DESC TENXA, a.THUTU, a.D3 from DULIEU_NT a, TXN_POINT_INFO b"
+ " where YEAR(a.NGAYBC) = '" + dtpNgay.SelectedDate.Value.ToString("yyyy") + "' and MONTH(a.NGAYBC) = '" + dtpNgay.SelectedDate.Value.ToString("MM") + "'"
+ " and a.D5 = b.CVI_TXN_POINT_ID"
+" ) p pivot(max(D3) for thutu in ([01], [02], [03], [04], [05], [61],[62], [07], [08], [09], [10]) ) d )"
+" , lst1 as"
+" ("
+" select SUBSTRING(a.MAXA, 5, 4) MAPOS,b.PO_TEN,a.NGAYBC,a.MAXA,a.TENXA,isnull(a.[01], '')[01]"
+" ,isnull(a.[02], '')[02],isnull(a.[03], '')[03],isnull(a.[04], '')[04]"
+" ,isnull(a.[05], '')[05],isnull(a.[61], '')[61],isnull(a.[62], '')[62]"
+" ,isnull(a.[07], '')[07],isnull(a.[08], '')[08],isnull(a.[09], '')[09]"
+" ,isnull(a.[10], '')[10],(select D4 from DULIEU_NT"
+ " where YEAR(NGAYBC) = '" + dtpNgay.SelectedDate.Value.ToString("yyyy") + "' and MONTH(NGAYBC) = '" + dtpNgay.SelectedDate.Value.ToString("MM") + "' and D5 = a.MAXA and MA = 'CMR01') LYDO"
+ " from CT2 a, DMPOS b where SUBSTRING(a.MAXA, 5, 4) = SUBSTRING(b.PO_MA, 3, 4)"
+" )"
+" select a.MAPOS,a.PO_TEN,a.NGAYBC,a.MAXA,a.TENXA,a.[01],a.[02],a.[03],a.[04],a.[05],a.[61],a.[62]"
+ " ,a.[07],a.[08],a.[09],a.[10],(case when a.LYDO = '' then N'Đạt : '+ cast((CAST([01] AS int)+CAST([02] AS int)+CAST([03] AS int)"
+ " +CAST([04] AS int)+CAST([05] AS int)+CAST([61] AS int)+CAST([62] AS int)+CAST([07] AS int)"
+ " +CAST([08] AS int)+CAST([09] AS int)++CAST([10] AS int))*100 / 11 as nvarchar(10))+'%' else a.LYDO end) LYDO from lst1 a order by a.MAXA";
*/
#endregion
// string strin = " with CT2 as (select * from(select * from LUU_CAMERA where NAM = '"+nam+"' and thang = '"+thang+"') p pivot(max(D3) for thutu in ([1], [2], [3], [4], [5], [6],[7], [8], [9], [10],[11]) ) d ) , lst1 as"
// +" (select SUBSTRING(a.MAXA, 5, 4) MAPOS,b.PO_TEN,a.MAXA,a.TPI_DESC TENXA, sum(isnull(CAST([1] AS int), 0)) [01]"
// +" ,sum(isnull(CAST([2] AS int), 0)) [02],sum(isnull(CAST([3] AS int), 0)) [03],sum(isnull(CAST([4] AS int), 0)) [04] "
// +" ,sum(isnull(CAST([5] AS int), 0)) [05],sum(isnull(CAST([6] AS int), 0)) [06],sum(isnull(CAST([7] AS int), 0)) [07] "
//+" ,sum(isnull(CAST([8] AS int), 0)) [08],sum(isnull(CAST([9] AS int), 0)) [09],sum(isnull(CAST([10] AS int), 0)) [10] "
//+" ,sum(isnull(CAST([11] AS int), 0))[11],a.D4 LYDO from CT2 a, DMPOS b where left(a.MAXA, 4) = right(b.PO_MA, 4) "
// +" group by SUBSTRING(a.MAXA, 5, 4),b.PO_TEN,a.MAXA,a.TPI_DESC,a.D4 ) select '"+ng+"' NGAYBC,* from lst1 order by MAXA";
int thamso = 1;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@Ngay";
giatri[0] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
var dtin = _cls.LoadDataProcPara("usp_Camera01", bien, giatri, thamso);
rpt_Camera01 rpt = new rpt_Camera01();
RPUtility.ShowRp(rpt, dtin, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
_cls.DongKetNoi();
ora.DongKetNoi();
}
