private void Header()
{
System.Text.StringBuilder x = new System.Text.StringBuilder();
Rpt.Judul(x, comp, judul);
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
if (KPAStatus == "kpa1")
{
Rpt.SubJudul(x, "Status KPR <b style='padding-left:5px'>:</b> INCLUDE TAGIHAN KPR ");
}
else
{
Rpt.SubJudul(x, "Status KPR <b style='padding-left:5px'>:</b> EXCLUDE TAGIHAN KPR");
}
Rpt.SubJudul(x
, "As of <b style='padding-left:40px'>:</b> " + Cf.Day(Dari));
Rpt.SubJudul(x, " Cara Bayar <b style='padding-left:5px'>:</b> " + CaraBayar.Replace('-', ',').TrimEnd());
Rpt.SubJudul(x, " Perusahaan : " + Perusahaan);
Rpt.SubJudul(x, " Project <b style='padding-left:28px'>:</b> " + Project);
//Rpt.Header(rpt, x);
string legend = "";
Rpt.HeaderReport(headReport, legend, x);
}
private void Header()
{
System.Text.StringBuilder x = new System.Text.StringBuilder();
Rpt.Judul(x, comp, judul);
//Rpt.SubJudul(x
// , "Tipe : " + Rpt.inSql(tipe).Replace("'", "")
// );
//Rpt.SubJudul(x
// , "Cara Bayar : " + Rpt.inSql(carabayar).Replace("'", "")
// );
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
//Cara Bayar
string akt = String.Empty;
akt = CaraBayar.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("+", " ");
akt = akt.Replace(",", ",");
Rpt.SubJudul(x
, "Tanggal" + " : " + Cf.Day(Dari) + " s/d " + Cf.Day(Sampai)
);
Rpt.SubJudul(x
, "Cara Bayar : " + akt
);
Rpt.SubJudul(x
, "Kasir : " + Kasir
);
Rpt.SubJudul(x
, "Rekening Bank : " + Rekening
);
Rpt.SubJudul(x
, "Lokasi : " + Lokasi
);
Rpt.SubJudul(x
, "Project : " + Project
);
string pers = (Perusahaan == "SEMUA") ? "SEMUA" : Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PERS WHERE Pers = '" + Perusahaan + "'");
Rpt.SubJudul(x
, "Perusahaan : " + pers
);
if (StatusP != "")
{
Rpt.SubJudul(x, "Status : " + StatusP);
}
else if (StatusV != "")
{
Rpt.SubJudul(x, "Status : " + StatusV);
}
else if (StatusB != "")
{
Rpt.SubJudul(x, "Status : " + StatusB);
}
else if (StatusS != "")
{
Rpt.SubJudul(x, "Status : " + StatusS);
}
string legend = "Cara Bayar : TN = Tunai / KD = Kartu Debit / KK = Kartu Kredit / TR = Transfer Bank / BG = Cek Giro / UJ = Uang Jaminan / DN = Diskon.";
//Rpt.Header(rpt, x);
Rpt.HeaderReport(headReport, legend, x);
}
private void Fill()
{
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string UserID = "";
if (Kasir != "SEMUA")
{
UserID = " AND UserID = '" + Kasir + "'";
}
string Status = "";
if (StatusP != "")
{
Status = " AND a.Status = 'POST'";
}
if (StatusB != "")
{
Status = " AND a.Status = 'BARU'";
}
if (StatusV != "")
{
Status = " AND a.Status = 'VOID'";
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND c.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND c.Pers = '" + Perusahaan + "'";
}
string strAcc = "";
if (Rekening != "SEMUA")
{
strAcc = " AND Acc = '" + Cf.Str(Rekening) + "'";
}
string nLokasi = "";
if (Lokasi != "SEMUA")// ;
{
nLokasi = " AND b.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string agent = "";
if (UserAgent() > 0)
{
agent = " AND (SELECT NoAgent FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = a.Ref) = " + UserAgent();
}
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
//Tipe
string type = String.Empty;
type = Tipe.Replace("-", ",").TrimEnd(',');
type = type.Replace("+", " ");
type = type.Replace(",", "','");
type = "'" + type + "'";
//Cara Bayar
string akt = String.Empty;
akt = CaraBayar.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("+", " ");
akt = akt.Replace(",", "','");
akt = "'" + akt + "'";
string tgl = "";
if (Tanggal == "tgltts")
{
tgl = "TglTTS";
}
if (Tanggal == "tglinput")
{
tgl = "a.TglInput";
}
if (Tanggal == "tglbkm")
{
tgl = "TglBKM";
}
if (Tanggal == "tglbg")
{
tgl = "TglBG";
}
string strSql = "SELECT a.*,b.Lokasi "
+ " FROM ISC064_FINANCEAR..MS_TTS a"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT b ON a.Unit = b.NoUnit "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK c ON a.Ref = c.NoKontrak"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND Tipe IN (" + type + ")"
+ " AND a.CaraBayar IN (" + akt + ")"
+ nProject
+ nPerusahaan
+ UserID
+ Status
+ strAcc
+ nLokasi
+ agent
+ " ORDER BY NoTTS";
DataTable rs = Db.Rs(strSql);
DataTable rsGiro = Db.Rs(
"SELECT a.*,b.Lokasi "
+ " FROM ISC064_FINANCEAR..MS_TTS a"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT b ON a.Unit = b.NoUnit "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK c ON a.Ref = c.NoKontrak"
+ " WHERE 1=1"
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND Tipe IN (" + type + ")"
+ " AND a.CaraBayar IN (" + akt + ")"
+ nProject
+ nPerusahaan
+ UserID
+ Status
+ strAcc
+ nLokasi
+ " AND NoBG <> ''"
);
int LembarGiro = rsGiro.Rows.Count;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditTTS('" + rs.Rows[i]["NoTTS"] + "')";
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoTTS2"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
if ((int)rs.Rows[i]["NoBKM"] != 0)
{
c.Text = rs.Rows[i]["NoBKM2"].ToString();
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Status"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglTTS"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglBKM"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["UserID"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT DISTINCT TIPE FROM ISC064_MARKETINGJUAL..MS_TAGIHAN"
+ " WHERE NOURUT in (SELECT NOTAGIHAN FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NOTTS='" + rs.Rows[i]["NoTTS"].ToString() + "')"
+ " AND NoKontrak = '" + rs.Rows[i]["Ref"] + "'"
;
DataTable rs1 = Db.Rs(strSql);
string w = "";
if (rs1.Rows.Count > 1)
{
for (int j = 0; j < rs1.Rows.Count; j++)
{
w += rs1.Rows[j]["Tipe"].ToString() + ",";
}
}
else
{
for (int j = 0; j < rs1.Rows.Count; j++)
{
w += rs1.Rows[j]["Tipe"].ToString();
}
}
c.Text = w;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Ref"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Unit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Customer"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["CaraBayar"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Ket"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoBG"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglBG"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Acc"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string Bank = Db.SingleString("SELECT Bank FROM REF_ACC WHERE Acc = '" + rs.Rows[i]["Acc"] + "' AND SubID = '" + rs.Rows[i]["SubID"] + "' ");
c = new TableCell();
c.Text = Bank;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
if (nDetil == "false")
{
c.Text = Cf.Num(rs.Rows[i]["Total"]);
}
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["LebihBayar"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
decimal t = Convert.ToDecimal(rs.Rows[i]["Total"]) + Convert.ToDecimal(rs.Rows[i]["LebihBayar"]);
c = new TableCell();
c.Text = Cf.Num(t);//Cf.Num(rs.Rows[i]["Total2"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
if (nDetil == "true")
{
Detil(
rs.Rows[i]["NoTTS"].ToString()
, rs.Rows[i]["Tipe"].ToString()
, (decimal)rs.Rows[i]["Total"]
);
}
t1 = t1 + (decimal)rs.Rows[i]["Total"];
t2 = t2 + (decimal)rs.Rows[i]["LebihBayar"];
t3 = t3 + t;
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", t1, t2, t3);
Giro(LembarGiro);
}
}
}
private void Fill()
{
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string nAgent = "";
if (Agent != "SEMUA")
{
nAgent = " AND Principal = '" + Cf.Str(Agent) + "'";
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND MS_KONTRAK.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND MS_KONTRAK.Pers = '" + Perusahaan + "'";
}
//change parameter tipe
string cb = String.Empty;
cb = CaraBayar.Replace("-", ",").TrimEnd(',');
cb = cb.Replace("+", " ");
cb = cb.Replace(",", "','");
cb = "'" + cb + "'";
string tipe = String.Empty;
tipe = Tipe.Replace("-", ",").TrimEnd(',');
tipe = tipe.Replace("%", " ");
tipe = tipe.Replace(",", "','");
tipe = "'" + tipe + "'";
string carabayar = "";
if (cb != "")
{
carabayar = " AND MS_KONTRAK.CaraBayar IN (" + cb + ")";
}
string nTipe = "";
if (tipe != "")
{
nTipe = " AND MS_KONTRAK.Jenis IN (" + tipe + ")";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND NoAgent = " + UserAgent();
}
int Tahun1 = Convert.ToInt32(Thn1);
int Tahun2 = Convert.ToInt32(Thn2);
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0, t7 = 0, t8 = 0, t9 = 0, t10 = 0, t11 = 0, t12 = 0;
for (int i = Tahun1; i <= Tahun2; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
c = new TableCell();
c.Text = i.ToString();
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
int Unit1 = Db.SingleInteger("SELECT COUNT(*) FROM MS_KONTRAK WHERE Status = 'A' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe);
decimal Net1 = Db.SingleDecimal("SELECT ISNULL(SUM(LuasNett),0) FROM MS_UNIT WHERE NoStock IN (SELECT NoStock FROM MS_KONTRAK WHERE Status = 'A' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe + ")");
decimal SGA1 = Db.SingleDecimal("SELECT ISNULL(SUM(LuasSG),0) FROM MS_UNIT WHERE NoStock IN (SELECT NoStock FROM MS_KONTRAK WHERE Status = 'A' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe + ")");
decimal Nilai1 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiKontrak),0) FROM MS_KONTRAK WHERE Status = 'A' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe);
c = new TableCell();
c.Text = Unit1.ToString();
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Net1);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(SGA1);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Nilai1);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = " ";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
int Unit2 = Db.SingleInteger("SELECT COUNT(*) FROM MS_KONTRAK WHERE Status = 'B' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe);
decimal Net2 = Db.SingleDecimal("SELECT ISNULL(SUM(LuasNett),0) FROM MS_UNIT WHERE NoStock IN (SELECT NoStock FROM MS_KONTRAK WHERE Status = 'B' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe + ")");
decimal SGA2 = Db.SingleDecimal("SELECT ISNULL(SUM(LuasSG),0) FROM MS_UNIT WHERE NoStock IN (SELECT NoStock FROM MS_KONTRAK WHERE Status = 'B' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe + ")");
decimal Nilai2 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiKontrak),0) FROM MS_KONTRAK WHERE Status = 'B' AND YEAR(TglKontrak) = " + i + nLokasi + carabayar + nProject + nPerusahaan + aa + nTipe);
c = new TableCell();
c.Text = Unit2.ToString();
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Net2);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(SGA2);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Nilai2);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = " ";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
int Unit3 = Unit1 + Unit2;
decimal Net3 = Net1 + Net2;
decimal SGA3 = SGA1 + SGA2;
decimal Nilai3 = Nilai1 + Nilai2;
c = new TableCell();
c.Text = Unit3.ToString();
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Net3);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(SGA3);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Nilai3);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 += Unit1;
t2 += Net1;
t3 += SGA1;
t4 += Nilai1;
t5 += Unit2;
t6 += Net2;
t7 += SGA2;
t8 += Nilai2;
t9 += Unit3;
t10 += Net3;
t11 += SGA3;
t12 += Nilai3;
if (i == Tahun2)
{
SubTotal("TOTAL", t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12);
}
}
}
private void Fill()
{
string Status = "";
if (StatusA != "")
{
Status = " AND a.Status = 'A'";
}
if (StatusB != "")
{
Status = " AND a.Status = 'B'";
}
string KPR = "";
if (KPAStatus == "kpa1")
{
KPR = " ";
}
else if (KPAStatus == "kpa2")
{
KPR = " AND b.KPR != '1' ";
}
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND a.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND a.Pers = '" + Perusahaan + "'";
}
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND a.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND a.NoAgent = " + UserAgent();
}
//Cara Bayar
string akt = String.Empty;
akt = CaraBayar.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("+", " ");
akt = akt.Replace(",", "','");
akt = "'" + akt + "'";
string strSql = "SELECT"
+ " a.NoKontrak"
+ ",NamaTagihan"
+ ",TglJT"
+ ",NilaiTagihan"
+ ",NoUrut"
+ ",datediff(day,convert(datetime,TglJT,112),'" + Cf.Tgl112(Dari) + "') as telat"
+ ", a.NoCustomer"
+ ", a.NoUnit"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK a INNER JOIN ISC064_MARKETINGJUAL..MS_TAGIHAN b"
+ " ON a.NoKontrak = b.NoKontrak"
+ " WHERE 1=1"
+ " AND ((SELECT ISNULL(SUM(NilaiPelunasan),0) as pelunasan FROM ISC064_MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE NoKontrak = a.NoKontrak AND NoTagihan = b.NoUrut) < NilaiTagihan)"
+ " AND TglJT < '" + Dari + "' "
+ " AND a.CaraBayar IN(" + akt + ")"
+ nProject
+ nPerusahaan
+ nLokasi
+ KPR
+ Status
+ aa
+ " ORDER BY a.NoKontrak ASC";
decimal a1 = 0;
decimal a2 = 0;
decimal a3 = 0;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString() + "." + rs.Rows[i]["NoUrut"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NoTelp FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NoHP FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaTagihan"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglJT"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["telat"].ToString();
c.HorizontalAlign = HorizontalAlign.Center;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiTagihan"].ToString());
a1 = a1 + Convert.ToDecimal(c.Text);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND NoTagihan = '" + rs.Rows[i]["NoUrut"].ToString() + "'"));
a2 = a2 + Convert.ToDecimal(c.Text);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
decimal sisa = 0;
decimal n1 = (decimal)rs.Rows[i]["NilaiTagihan"];
decimal n2 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND NoTagihan = '" + rs.Rows[i]["NoUrut"].ToString() + "'");
string txt = "";
sisa = n1 - n2;
c = new TableCell();
c.Text = Cf.Num(sisa);
a3 = a3 + Convert.ToDecimal(c.Text);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
if (i == rs.Rows.Count - 1)
{
SubTotal(txt, a1, a2, a3);
}
}
}
private void Fill()
{
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
decimal t1 = 0, t2 = 0, t3 = 0;
string KPR = "";
if (KPAStatus == "kpa1")
{
KPR = " ";
}
else if (KPAStatus == "kpa2")
{
KPR = " AND a.KPR != '1' ";
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND b.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND b.Pers = '" + Perusahaan + "'";
}
//Cara Bayar
string akt = String.Empty;
akt = CaraBayar.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("+", " ");
akt = akt.Replace(",", "','");
akt = "'" + akt + "'";
string aa = "";
if (UserAgent() > 0)
{
aa = " AND b.NoAgent = " + UserAgent();
}
string strSql = "SELECT a.*, b.NoCustomer, b.NoUnit"
+ " FROM ISC064_MARKETINGJUAL..MS_TAGIHAN a"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_KONTRAK b ON a.NoKontrak = b.NoKontrak"
+ " WHERE DATEDIFF(DAY, '" + Cf.Tgl112(Dari) + "', CONVERT(DATETIME, TglJT, 112)) BETWEEN 0 AND 14"
+ " AND b.CaraBayar IN(" + akt + ")"
+ " AND b.STATUS != 'B'"
+ nProject
+ nPerusahaan
+ KPR
+ aa
;
DataTable rs = Db.Rs(strSql);
TableRow r = new TableRow();
rpt.Rows.Add(r);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString() + "." + rs.Rows[i]["NoUrut"];
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NoTelp FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NoHP FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaTagihan"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglJt"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiTagihan"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
decimal NilaiPelunasan = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND NoTagihan = '" + rs.Rows[i]["NoUrut"] + "'");
c = new TableCell();
c.Text = Cf.Num(NilaiPelunasan);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
decimal SisaTagihan = Convert.ToDecimal(rs.Rows[i]["NilaiTagihan"]) - NilaiPelunasan;
c = new TableCell();
c.Text = Cf.Num(SisaTagihan);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 = t1 + (decimal)rs.Rows[i]["NilaiTagihan"];
t2 = t2 + NilaiPelunasan;
t3 = t3 + SisaTagihan;
if (i == (rs.Rows.Count - 1))
{
SubTotal("GRAND TOTAL", t1, t2, t3);
}
}
}
private void Header()
{
System.Text.StringBuilder x = new System.Text.StringBuilder();
Rpt.Judul(x, comp, judul);
Rpt.SubJudul(x
, "Tipe : " + Tipe.Replace("-", ",").TrimEnd(',')
);
Rpt.SubJudul(x
, "Cara Bayar : " + CaraBayar.Replace("-", ",").TrimEnd(',')
);
string tgl = "";
if (Tanggal == "tgltts")
{
tgl = "TglTTS";
}
if (Tanggal == "tglinput")
{
tgl = "a.TglInput";
}
if (Tanggal == "tglbkm")
{
tgl = "TglBKM";
}
if (Tanggal == "tglbg")
{
tgl = "TglBG";
}
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
Rpt.SubJudul(x
, tgl + " : " + Cf.Day(Dari) + " s/d " + Cf.Day(Sampai)
);
Rpt.SubJudul(x
, "Kasir : " + Kasir
);
Rpt.SubJudul(x
, "Rekening Bank : " + Rekening
);
Rpt.SubJudul(x
, "Lokasi : " + Lokasi
);
if (StatusP != "")
{
Rpt.SubJudul(x, "Status : " + StatusP);
}
else if (StatusV != "")
{
Rpt.SubJudul(x, "Status : " + StatusV);
}
else if (StatusB != "")
{
Rpt.SubJudul(x, "Status : " + StatusB);
}
else if (StatusS != "")
{
Rpt.SubJudul(x, "Status : " + StatusS);
}
//Rpt.Header(rpt, x);
string legend = "";
Rpt.HeaderReport(headReport, legend, x);
}
private void FillCsv()
{
string csv = string.Empty;
//FK KD_JENIS_TRANSAKSI FG_PENGGANTI NOMOR_FAKTUR MASA_PAJAK TAHUN_PAJAK TANGGAL_FAKTUR
//NPWP NAMA ALAMAT_LENGKAP JUMLAH_DPP JUMLAH_PPN JUMLAH_PPNBM
//ID_KETERANGAN_TAMBAHAN FG_UANG_MUKA UANG_MUKA_DPP UANG_MUKA_PPN
//UANG_MUKA_PPNBM REFERENSI
csv += "FK,";
csv += "KD_JENIS_TRANSAKSI,";
csv += "FG_PENGGANTI,";
csv += "NOMOR_FAKTUR,";
csv += "MASA_PAJAK,";
csv += "TAHUN_PAJAK,";
csv += "TANGGAL_FAKTUR,";
csv += "NPWP,";
csv += "NAMA,";
csv += "ALAMAT_LENGKAP,";
csv += "JUMLAH_DPP,";
csv += "JUMLAH_PPN,";
csv += "JUMLAH_PPNBM,";
csv += "ID_KETERANGAN_TAMBAHAN,";
csv += "FG_UANG_MUKA,";
csv += "UANG_MUKA_DPP,";
csv += "UANG_MUKA_PPN,";
csv += "UANG_MUKA_PPNBM,";
csv += "REFERENSI";
//Add new line.
csv += "\r\n";
//LT NPWP NAMA JALAN BLOK NOMOR RT RW KECAMATAN
//KELURAHAN KABUPATEN PROPINSI KODE_POS NOMOR_TELEPON
csv += "LT,";
csv += "NPWP,";
csv += "NAMA,";
csv += "JALAN,";
csv += "BLOK,";
csv += "RT,";
csv += "RW,";
csv += "KECAMATAN,";
csv += "KELURAHAN,";
csv += "KABUPATEN,";
csv += "PROPINSI,";
csv += "KODE_POS,";
csv += "NOMOR_TELEPON";
csv += "\r\n";
//OF KODE_OBJEK NAMA HARGA_SATUAN JUMLAH_BARANG HARGA_TOTAL DISKON DPP PPN TARIF_PPNBM PPNBM
csv += "OF,";
csv += "KODE_OBJEK,";
csv += "NAMA,";
csv += "HARGA_SATUAN,";
csv += "JUMLAH_BARANG,";
csv += "HARGA_TOTAL,";
csv += "DISKON,";
csv += "DPP,";
csv += "PPN,";
csv += "TARIF_PPNBM,";
csv += "PPNBM";
csv += "\r\n";
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string UserID = "";
if (Kasir != "SEMUA")
{
UserID = " AND UserID = '" + Kasir + "'";
}
string Status = "";
if (StatusP != "")
{
Status = " AND a.Status = 'POST'";
}
if (StatusB != "")
{
Status = " AND a.Status = 'BARU'";
}
if (StatusV != "")
{
Status = " AND a.Status = 'VOID'";
}
string strAcc = "";
if (Rekening != "SEMUA")
{
strAcc = " AND Acc = '" + Cf.Str(Rekening) + "'";
}
string nLokasi = "";
if (Lokasi != "SEMUA")// ;
{
nLokasi = " AND b.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string agent = "";
if (UserAgent() > 0)
{
agent = " AND (SELECT NoAgent FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = a.Ref) = " + UserAgent();
}
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
string tgl = "";
if (Tanggal == "tgltts")
{
tgl = "TglTTS";
}
if (Tanggal == "tglinput")
{
tgl = "a.TglInput";
}
if (Tanggal == "tglbkm")
{
tgl = "TglBKM";
}
if (Tanggal == "tglbg")
{
tgl = "TglBG";
}
//Tipe
string type = String.Empty;
type = Tipe.Replace("-", ",").TrimEnd(',');
type = type.Replace("+", " ");
type = type.Replace(",", "','");
type = "'" + type + "'";
//Cara Bayar
string akt = String.Empty;
akt = CaraBayar.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("+", " ");
akt = akt.Replace(",", "','");
akt = "'" + akt + "'";
string strSql = "SELECT a.*, b.Lokasi, b.Jenis "
+ " FROM ISC064_FINANCEAR..MS_TTS a"
+ " INNER JOIN [ISC064_MARKETINGJUAL].[dbo].[MS_UNIT] b ON a.Unit = b.NoUnit "
+ " WHERE 1=1 "
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND a.Tipe IN (" + type + ")"
+ " AND CaraBayar IN (" + akt + ")"
+ UserID
+ Status
+ strAcc
+ nLokasi
+ agent
+ " ORDER BY NoTTS";
DataTable rs = Db.Rs(strSql);
string NPWPPT = Db.SingleString("SELECT ISNULL(NPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string NamaNPWP = Db.SingleString("SELECT ISNULL(NPWPNama,' ') FROM ISC064_SECURITY..REF_DATA");
string AlamatNPWP = Db.SingleString("SELECT ISNULL(AlamatNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string NomorNPWP = Db.SingleString("SELECT ISNULL(NomorNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
//string strSql2 = "";
//strSql2 = "SELECT DISTINCT NamaTagihan FROM ISC064_MARKETINGJUAL..MS_TAGIHAN"
// + " WHERE NOURUT in (SELECT NOTAGIHAN FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NOTTS='" + rs.Rows[i]["NoTTS"].ToString() + "')"
// + " AND NoKontrak = '" + rs.Rows[i]["Ref"] + "'"
// ;
//DataTable rs12 = Db.Rs(strSql2);
//string w2 = "";
//if (rs12.Rows.Count > 1)
//{
// for (int j = 0; j < rs12.Rows.Count; j++)
// {
// w2 += rs12.Rows[j]["NamaTagihan"].ToString() + ";";
// }
//}
//else
//{
// for (int j = 0; j < rs12.Rows.Count; j++)
// {
// w2 += rs12.Rows[j]["NamaTagihan"].ToString();
// }
//}
string strSql2 = "";
strSql2 = "SELECT DISTINCT NoUnit FROM ISC064_MARKETINGJUAL..MS_KONTRAK"
+ " WHERE NoKontrak = '" + rs.Rows[i]["Ref"] + "'"
;
DataTable rs12 = Db.Rs(strSql2);
string w2 = "";
if (rs12.Rows.Count > 1)
{
for (int j = 0; j < rs12.Rows.Count; j++)
{
w2 += rs12.Rows[j]["NoUnit"].ToString() + ";";
}
}
else
{
for (int j = 0; j < rs12.Rows.Count; j++)
{
w2 += rs12.Rows[j]["NoUnit"].ToString();
}
}
string Kode_Objek = Db.SingleString("SELECT ISNULL(KODE,' ') FROM REF_EFAKTUR WHERE Uraian='" + w2 + "'");
//Baris 1
string NoFPS = rs.Rows[i]["NoFPS"].ToString();
string[] noFG = NoFPS.Split('.');
string printFG = "", printFP = "";
if (noFG.Length > 0)
{
//printFG = noFG.Length.ToString();
//printFP = noFG[1].ToString();
for (int count = 0; count <= noFG.Length - 1; count++)
{
if (count == 0)
{
printFG = noFG[count].ToString();
}
if (count > 0)
{
if (count == 1)
{
printFP += noFG[count].ToString();
}
else
{
printFP += "." + noFG[count].ToString();
}
}
}
}
DateTime TglKwitasi = Convert.ToDateTime(rs.Rows[i]["TglBKM"]);
int BulanKwitansi = TglKwitasi.Month;
int TahunKwitansi = TglKwitasi.Year;
string QueryNamaCS = "SELECT ISNULL(NPWP,' ') FROM ISC064_MARKETINGJUAL..MS_KONTRAK A"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER B ON A.NoCustomer = B.NoCustomer"
+ " WHERE NoKontrak = '" + rs.Rows[i]["Ref"] + "'";
string NPWPCS = Db.SingleString(QueryNamaCS);
decimal NoCustomer = Db.SingleDecimal("SELECT NoCustomer FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak='" + rs.Rows[i]["Ref"] + "'");
DataTable dtCustomer = Db.Rs("SELECT * FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + NoCustomer);
string Alm_NPWP1 = "", Alm_NPWP2 = "", Alm_NPWP3 = "";
string Alm_KTP1 = "", Alm_KTP2 = "", Alm_KTP3 = "", Alm_KTP4 = "";
string AlamatCS = " ";
if (dtCustomer.Rows.Count > 0)
{
Alm_NPWP1 = dtCustomer.Rows[0]["NPWPAlamat1"].ToString().Trim();
Alm_NPWP2 = dtCustomer.Rows[0]["NPWPAlamat2"].ToString().Trim();
Alm_NPWP3 = dtCustomer.Rows[0]["NPWPAlamat3"].ToString().Trim();
Alm_KTP1 = dtCustomer.Rows[0]["KTP1"].ToString().Trim();
Alm_KTP2 = dtCustomer.Rows[0]["KTP2"].ToString().Trim();
Alm_KTP3 = dtCustomer.Rows[0]["KTP3"].ToString().Trim();
Alm_KTP4 = dtCustomer.Rows[0]["KTP4"].ToString().Trim();
if (Alm_NPWP1 == " " || Alm_NPWP2 == " " || Alm_NPWP3 == " " || Alm_NPWP1 == "-" || Alm_NPWP2 == "-" || Alm_NPWP3 == "-")
{
if (Alm_KTP1 == " " || Alm_KTP2 == " " || Alm_KTP3 == " " || Alm_KTP4 == " " || Alm_KTP1 == "-" || Alm_KTP2 == "-" || Alm_KTP3 == "-" || Alm_KTP4 == "-")
{
AlamatCS = " ";
}
else
{
AlamatCS = Alm_KTP1 + " " + Alm_KTP2 + " " + Alm_KTP3 + " " + Alm_KTP4;
}
}
else
{
AlamatCS = Alm_NPWP1 + " " + Alm_NPWP2 + " " + Alm_NPWP3;
}
AlamatCS = AlamatCS.Trim();
if (AlamatCS == "")
{
AlamatCS = "";
}
}
decimal NilaiBayar = Convert.ToDecimal(rs.Rows[i]["Total"]);
decimal DPP = NilaiBayar / (decimal)1.1;
string sRef = w2 + " " + rs.Rows[0]["Jenis"].ToString() + " " + "SL-" + rs.Rows[0]["Unit"].ToString();
string KelurahanNPWP = Db.SingleString("SELECT ISNULL(KelurahanNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string KabupatenNPWP = Db.SingleString("SELECT ISNULL(KabupatenNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string KodePOS = Db.SingleString("SELECT ISNULL(KodePosNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string NoTelp = Db.SingleString("SELECT ISNULL(NoTelp,' ') FROM ISC064_SECURITY..REF_DATA");
string PropinsiNPWP = Db.SingleString("SELECT ISNULL(PropinsiNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string BlokNPWP = Db.SingleString("SELECT ISNULL(BlokNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string RTNPWP = Db.SingleString("SELECT ISNULL(RTNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string RWNPWP = Db.SingleString("SELECT ISNULL(RWNPWP,'') FROM ISC064_SECURITY..REF_DATA");
string KecamatanNPWP = Db.SingleString("SELECT ISNULL(KecamatanNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
csv += "FK".Replace(",", ";") + ',' + "01".Replace(",", ";") + ',' + "0".Replace(",", ";") + ',' + printFP.Replace(".", "").Replace(",", ";") + ',';
csv += BulanKwitansi.ToString().Replace(",", ";") + ',' + TahunKwitansi.ToString().Replace(",", ";") + ',' + Cf.DaySlash(TglKwitasi) + ',' + NPWPCS + ',';
csv += rs.Rows[i]["Customer"].ToString().Replace(",", ";") + ',' + AlamatCS.Replace(",", ";") + ',' + Math.Round(DPP).ToString().Replace(",", ";") + ',' + Math.Round(NilaiBayar - DPP).ToString().Replace(",", ";") + ',';
csv += "0".Replace(",", ";") + ',' + "".Replace(",", ";") + ',' + "0".Replace(",", ";") + ',' + "0".Replace(",", ";") + ',';
csv += "0".Replace(",", ";") + ',' + "0".Replace(",", ";") + ',' + sRef.Replace(",", ";");
csv += "\r\n";
//Baris 2
//csv += "FAPR".Replace(",", ";") + ',' + NPWPPT.Replace(",", ";") + ',' + NamaNPWP.Replace(",", ";") + ',' + AlamatNPWP.Replace(",", ";") + ',';
//csv += BlokNPWP.Replace(",", ";") + ',' + RTNPWP.Replace(",", ";") + ',' + RWNPWP.Replace(",", ";") + ',' + KecamatanNPWP.Replace(",", ";") + ',';
//csv += KelurahanNPWP.Replace(",", ";") + ',' + KabupatenNPWP.Replace(",", ";") + ',' + PropinsiNPWP.Replace(",", ";") + ',' + KodePOS.Replace(",", ";") + ',';
//csv += NoTelp.Replace(",", ";");
//csv += "\r\n";
//Baris 3
csv += "OF".ToString().Replace(",", ";") + ',' + Kode_Objek + ',' + w2.Replace(",", ";") + ',' + Math.Round(DPP).ToString().Replace(",", ";") + ',';
csv += "1".Replace(",", ";") + ',' + Math.Round(DPP).ToString().Replace(",", ";") + ',' + "0".ToString().Replace(",", ";") + ',' + Math.Round(DPP).ToString().Replace(",", ";") + ',' + Math.Round(NilaiBayar - DPP).ToString().Replace(",", ";") + ',' + "0".ToString().Replace(",", ";") + ',' + "0".ToString().Replace(",", ";");
csv += "\r\n";
}
string NamaFileCsv = "";
NamaFileCsv = "eFaktur" + DateTime.Now.Month + DateTime.Now.Year;
//Download the CSV file.
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + NamaFileCsv + ".csv");
Response.Charset = "";
Response.ContentType = "application/text";
Response.Output.Write(csv);
Response.Flush();
Response.End();
}
private void Fill()
{
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string UserID = "";
if (Kasir != "SEMUA")
{
UserID = " AND UserID = '" + Kasir + "'";
}
string Status = "";
if (StatusP != "")
{
Status = " AND a.Status = 'POST'";
}
if (StatusB != "")
{
Status = " AND a.Status = 'BARU'";
}
if (StatusV != "")
{
Status = " AND a.Status = 'VOID'";
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND c.Project IN('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND c.Pers = '" + Perusahaan + "'";
}
string strAcc = "";
if (Rekening != "SEMUA")
{
strAcc = " AND Acc = '" + Cf.Str(Rekening) + "'";
}
string nLokasi = "";
if (Lokasi != "SEMUA")// ;
{
nLokasi = " AND b.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string agent = "";
if (UserAgent() > 0)
{
agent = " AND (SELECT NoAgent FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = a.Ref) = " + UserAgent();
}
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
string tgl = "";
if (Tanggal == "tgltts")
{
tgl = "TglTTS";
}
if (Tanggal == "tglinput")
{
tgl = "a.TglInput";
}
if (Tanggal == "tglbkm")
{
tgl = "TglBKM";
}
if (Tanggal == "tglbg")
{
tgl = "TglBG";
}
//Tipe
string type = String.Empty;
type = Tipe.Replace("-", ",").TrimEnd(',');
type = type.Replace("+", " ");
type = type.Replace(",", "','");
type = "'" + type + "'";
//Cara Bayar
string akt = String.Empty;
akt = CaraBayar.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("+", " ");
akt = akt.Replace(",", "','");
akt = "'" + akt + "'";
string strSql = "SELECT a.*, b.Lokasi, b.Jenis "
+ " FROM ISC064_FINANCEAR..MS_TTS a"
+ " INNER JOIN [ISC064_MARKETINGJUAL].[dbo].[MS_UNIT] b ON a.Unit = b.NoUnit "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK c ON a.Ref = c.NoKontrak"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND a.Tipe IN (" + type + ")"
+ " AND a.CaraBayar IN (" + akt + ")"
+ nProject
+ nPerusahaan
+ UserID
+ Status
+ strAcc
+ nLokasi
+ agent
+ " ORDER BY NoTTS";
DataTable rs = Db.Rs(strSql);
DataTable rsGiro = Db.Rs(
"SELECT a.*,b.Lokasi "
+ " FROM ISC064_FINANCEAR..MS_TTS a"
+ " INNER JOIN [ISC064_MARKETINGJUAL].[dbo].[MS_UNIT] b ON a.Unit = b.NoUnit "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK c ON a.Ref = c.NoKontrak"
+ " WHERE 1=1"
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND a.Tipe IN (" + type + ")"
+ " AND a.CaraBayar IN (" + akt + ")"
+ nProject
+ nPerusahaan
+ UserID
+ Status
+ strAcc
+ nLokasi
+ " AND NoBG <> ''"
);
int LembarGiro = rsGiro.Rows.Count;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditTTS('" + rs.Rows[i]["NoTTS"] + "')";
c = new TableCell();
c.Text = "FK" + "<br />" + "FAPR" + "<br />" + "OF";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string strSql2 = "";
strSql2 = "SELECT DISTINCT NamaTagihan FROM ISC064_MARKETINGJUAL..MS_TAGIHAN"
+ " WHERE NOURUT in (SELECT NOTAGIHAN FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NOTTS='" + rs.Rows[i]["NoTTS"].ToString() + "')"
+ " AND NoKontrak = '" + rs.Rows[i]["Ref"] + "'"
;
DataTable rs12 = Db.Rs(strSql2);
string w2 = "";
if (rs12.Rows.Count > 1)
{
for (int j = 0; j < rs12.Rows.Count; j++)
{
w2 += rs12.Rows[j]["NamaTagihan"].ToString() + ";";
}
}
else
{
for (int j = 0; j < rs12.Rows.Count; j++)
{
w2 += rs12.Rows[j]["NamaTagihan"].ToString();
}
}
string Kode_Objek = Db.SingleString("SELECT ISNULL(KODE,' ') FROM REF_EFAKTUR WHERE Uraian='" + w2 + "'");
string NamaNPWP = Db.SingleString("SELECT ISNULL(NPWPNama,' ') FROM ISC064_SECURITY..REF_DATA");
string AlamatNPWP = Db.SingleString("SELECT ISNULL(AlamatNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
string NomorNPWP = Db.SingleString("SELECT ISNULL(NomorNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = "01" + "<br />" + BersihKoma(NamaNPWP) + "<br />" + BersihKoma(Kode_Objek);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string NoFPS = rs.Rows[i]["NoFPS"].ToString();
string[] noFG = NoFPS.Split('.');
string printFG = "", printFP = "";
if (noFG.Length > 0)
{
//printFG = noFG.Length.ToString();
//printFP = noFG[1].ToString();
for (int count = 0; count <= noFG.Length - 1; count++)
{
if (count == 0)
{
printFG = noFG[count].ToString();
}
if (count > 0)
{
if (count == 1)
{
printFP += noFG[count].ToString();
}
else
{
printFP += "." + noFG[count].ToString();
}
}
}
}
c = new TableCell();
c.Text = BersihKoma(printFG) + "<br />" + BersihKoma(AlamatNPWP) + "<br />" + w2;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
decimal NilaiBayar = Convert.ToDecimal(rs.Rows[i]["Total"]);
decimal DPP = NilaiBayar / (decimal)1.1;
c = new TableCell();
string Jalan = Db.SingleString("SELECT ISNULL(AlamatProject,' ') FROM ISC064_SECURITY..REF_DATA");
printFP = printFP.Replace(".", "");
c.Text = BersihKoma(printFP) + "<br />" + BersihKoma(Jalan) + "<br />" + Math.Round(DPP).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
DateTime TglKwitasi = Convert.ToDateTime(rs.Rows[i]["TglBKM"]);
int BulanKwitansi = TglKwitasi.Month;
int TahunKwitansi = TglKwitasi.Year;
string BlokNPWP = Db.SingleString("SELECT ISNULL(BlokNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = BulanKwitansi + "<br />" + BersihKoma(BlokNPWP) + "<br />" + "1";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = TahunKwitansi + "<br />" + " " + "<br />" + Math.Round(DPP).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string RTNPWP = Db.SingleString("SELECT ISNULL(RTNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = Cf.DaySlash(TglKwitasi) + "<br />" + BersihKoma(RTNPWP) + "<br />" + "0";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string QueryNamaCS = "SELECT ISNULL(NPWP,' ') FROM ISC064_MARKETINGJUAL..MS_KONTRAK A"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER B ON A.NoCustomer = B.NoCustomer"
+ " WHERE NoKontrak = '" + rs.Rows[i]["Ref"] + "'";
string NPWPCS = Db.SingleString(QueryNamaCS);
string RWNPWP = Db.SingleString("SELECT ISNULL(RWNPWP,'') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = BersihKoma(NPWPCS) + "<br />" + BersihKoma(RWNPWP) + "<br />" + Math.Round(DPP).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string KecamatanNPWP = Db.SingleString("SELECT ISNULL(KecamatanNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = BersihKoma(rs.Rows[i]["Customer"].ToString()) + "<br />" + BersihKoma(KecamatanNPWP) + "<br />" + Math.Round(NilaiBayar - DPP).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
// DATA CUSTOMER
decimal NoCustomer = Db.SingleDecimal("SELECT NoCustomer FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak='" + rs.Rows[i]["Ref"] + "'");
DataTable dtCustomer = Db.Rs("SELECT * FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer =" + NoCustomer);
string Alm_NPWP1 = "", Alm_NPWP2 = "", Alm_NPWP3 = "";
string Alm_KTP1 = "", Alm_KTP2 = "", Alm_KTP3 = "", Alm_KTP4 = "";
string AlamatCS = " ";
if (dtCustomer.Rows.Count > 0)
{
Alm_NPWP1 = dtCustomer.Rows[0]["NPWPAlamat1"].ToString().Trim();
Alm_NPWP2 = dtCustomer.Rows[0]["NPWPAlamat2"].ToString().Trim();
Alm_NPWP3 = dtCustomer.Rows[0]["NPWPAlamat3"].ToString().Trim();
Alm_KTP1 = dtCustomer.Rows[0]["KTP1"].ToString().Trim();
Alm_KTP2 = dtCustomer.Rows[0]["KTP2"].ToString().Trim();
Alm_KTP3 = dtCustomer.Rows[0]["KTP3"].ToString().Trim();
Alm_KTP4 = dtCustomer.Rows[0]["KTP4"].ToString().Trim();
if (Alm_NPWP1 == " " || Alm_NPWP2 == " " || Alm_NPWP3 == " " || Alm_NPWP1 == "-" || Alm_NPWP2 == "-" || Alm_NPWP3 == "-")
{
if (Alm_KTP1 == " " || Alm_KTP2 == " " || Alm_KTP3 == " " || Alm_KTP4 == " " || Alm_KTP1 == "-" || Alm_KTP2 == "-" || Alm_KTP3 == "-" || Alm_KTP4 == "-")
{
AlamatCS = " ";
}
else
{
AlamatCS = Alm_KTP1 + " " + Alm_KTP2 + " " + Alm_KTP3 + " " + Alm_KTP4;
}
}
else
{
AlamatCS = Alm_NPWP1 + " " + Alm_NPWP2 + " " + Alm_NPWP3;
}
AlamatCS = AlamatCS.Trim();
if (AlamatCS == "")
{
AlamatCS = " ";
}
}
string KelurahanNPWP = Db.SingleString("SELECT ISNULL(KelurahanNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = BersihKoma(AlamatCS) + "<br />" + BersihKoma(KelurahanNPWP) + "<br />" + "0";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string KabupatenNPWP = Db.SingleString("SELECT ISNULL(KabupatenNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = Math.Round(DPP).ToString() + "<br />" + BersihKoma(KabupatenNPWP) + "<br />" + "0";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string PropinsiNPWP = Db.SingleString("SELECT ISNULL(PropinsiNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = Math.Round(NilaiBayar - DPP).ToString() + "<br />" + BersihKoma(PropinsiNPWP) + "<br />" + "0";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//JUMLAH_PPNBM - KODE_POS
string KodePOS = Db.SingleString("SELECT ISNULL(KodePosNPWP,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = "0" + "<br />" + BersihKoma(KodePOS) + "<br />" + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//ID_KETERANGAN_TAMBAHAN - NOMOR_TELEPON - <<KOSONG>>
string NoTelp = Db.SingleString("SELECT ISNULL(NoTelp,' ') FROM ISC064_SECURITY..REF_DATA");
c = new TableCell();
c.Text = " " + "<br />" + BersihKoma(NoTelp) + "<br />" + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "0" + "<br />" + " " + "<br />" + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//UANG_MUKA_DPP
c = new TableCell();
c.Text = "0" + "<br />" + " " + "<br />" + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//UANG_MUKA_PPN
c = new TableCell();
c.Text = "0" + "<br />" + " " + "<br />" + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//UANG_MUKA_PPNBM
c = new TableCell();
c.Text = "0" + "<br />" + " " + "<br />" + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//REFERENSI
string sRef = w2 + " " + rs.Rows[0]["Jenis"].ToString() + " " + "SL-" + rs.Rows[0]["Unit"].ToString();
c = new TableCell();
c.Text = BersihKoma(sRef) + "<br />" + " " + "<br />" + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
