Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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            = "&nbsp;";
                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            = "&nbsp;";
                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);
                }
            }
        }
Beispiel #5
0
        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);
                }
            }
        }
Beispiel #6
0
        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);
                }
            }
        }
Beispiel #7
0
        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);
        }
Beispiel #8
0
        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();
        }
Beispiel #9
0
        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 = "&nbsp;";
                    }
                }

                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            = "&nbsp;" + "<br />" + BersihKoma(NoTelp) + "<br />" + "&nbsp;";
                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);
            }
        }