private void Print(int NoTunggakan)
{
//increment
Db.Execute("UPDATE MS_TUNGGAKAN SET PrintST = PrintST + 1 "
+ " WHERE NoTunggakan = " + NoTunggakan);
//Logfile
DataTable rs = Db.Rs("SELECT "
+ " CONVERT(varchar, TglTunggakan, 106) AS [Tanggal]"
+ ",Tipe"
+ ",Ref AS [Ref.]"
+ ",Unit"
+ ",Customer"
+ ",Total"
+ ",LevelTunggakan AS [Level]"
+ " FROM MS_TUNGGAKAN WHERE NoTunggakan = " + NoTunggakan);
Db.Execute("EXEC spLogTunggakan"
+ " 'P-ST'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Cf.LogCapture(rs) + "'"
+ ",'" + NoTunggakan.ToString().PadLeft(7, '0') + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_TUNGGAKAN_LOG ORDER BY LogID DESC");
string Project = Db.SingleString("SELECT Project FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = (SELECT Ref FROM MS_TUNGGAKAN WHERE NoTunggakan = '" + NoTunggakan + "') ");
Db.Execute("UPDATE MS_TUNGGAKAN_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
//ConvertPdf();
//Response.Redirect(Param.PathLinkFilePDFCollection + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "_STBatch.pdf");
string file = Param.PathFilePDFCollection + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + project.SelectedValue + "_STBatch.pdf";
bool exist = System.IO.File.Exists(file);
if (exist)
{
System.IO.File.Delete(file);
}
ConvertPdf();
Response.Redirect(Param.PathLinkFilePDFCollection + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + project.SelectedValue + "_STBatch.pdf");
//Template
PrintSTTemplate uc = (PrintSTTemplate)Page.LoadControl("PrintSTTemplate.ascx");
uc.NoTunggakan = NoTunggakan.ToString();
uc.Project = project.SelectedValue;
list.Controls.Add(uc);
}
private void Fill()
{
int index = 0, i = 0;
string NoTTS = "";
foreach (Control tr in list2.Controls)
{
HtmlTableCell c = (HtmlTableCell)list2.FindControl("pk_" + index);
CheckBox cb = (CheckBox)list2.FindControl("notts_" + index);
if (c != null)
{
if (cb.Checked)
{
Print(Convert.ToInt32(c.Attributes["title"]));
NoTTS += c.Attributes["title"] + ";";
Label pb = new Label();
if (i % 2 != 0)
{
pb.Text = "<div style='page-break-after:always'> </div>";
list.Controls.Add(pb);
}
else
{
pb.Text = "<div style='margin-top:15px;'> </div>";
list.Controls.Add(pb);
}
i++;
}
}
index++;
}
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
string Project = project.SelectedValue;
string file = Param.PathFilePDFFinanceAR + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + Project + "_BKMBatch.pdf";
bool exist = System.IO.File.Exists(file);
if (exist)
{
System.IO.File.Delete(file);
}
ConvertPdf(NoTTS);
Response.Redirect(Param.PathLinkFilePDFFinanceAR + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + Project + "_BKMBatch.pdf");
}
private void ConvertPdf(string NoTTS)
{
Process p = new System.Diagnostics.Process();
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
string Project = project.SelectedValue;
string myHtml = "http://" + HttpContext.Current.Request.Url.Host + ":" + HttpContext.Current.Request.Url.Port + "/financear/PrintBKMBatch1.aspx?id=" + NoTTS + "&project=" + Project;
string save = Param.PathFilePDFFinanceAR + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + Project + "_BKMBatch.pdf";
string link = Param.PathLinkFilePDFFinanceAR + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + Project + ".pdf";
p.StartInfo.Arguments = "--orientation portrait --page-width 8.5in --page-height 11in --margin-left 2cm --margin-right 2cm --margin-top 1.25cm --margin-bottom 0 " + myHtml + " " + save;
p.StartInfo.FileName = Mi.PathWkhtmlPDFReport;
p.Start();
p.WaitForExit(60000);
}
private void ConvertPdf()
{
Process p = new System.Diagnostics.Process();
//DateTime Dari = Convert.ToDateTime(Request.QueryString["dari"]);
//DateTime Sampai = Convert.ToDateTime(Request.QueryString["sampai"]);
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(dari.Text);
string myHtml = "http://localhost:8034/collection/PrintPJTBatch1.aspx?Dari=" + Cf.Tgl(Dari) + "&Sampai=" + Cf.Tgl(Sampai) + "&project=" + project.SelectedValue;
string save = Param.PathFilePDFCollection + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + project.SelectedValue + "_PJTBatch.pdf";
string link = Param.PathLinkFilePDFCollection + Cf.Tgl(Dari) + "&" + Cf.Tgl(Sampai) + "&" + project.SelectedValue + ".pdf";
p.StartInfo.Arguments = "--orientation portrait --page-width 8.5in --page-height 11in --margin-left 2cm --margin-right 2cm --margin-top 1.25cm --margin-bottom 0 " + myHtml + " " + save;
p.StartInfo.FileName = Mi.PathWkhtmlPDFReport;
p.Start();
p.WaitForExit(60000);
}
protected void FillCust()
{
DateTime Dari = Convert.ToDateTime(Request.QueryString["dari"]);
DateTime Sampai = Convert.ToDateTime(Request.QueryString["sampai"]);
dari.Text = Cf.Tgl(Dari);
sampai.Text = Cf.Tgl(Sampai);
DataTable rs = Db.Rs("SELECT "
+ " a.* "
+ ", c.NoVA "
+ ", c.NoUnit "
+ ", c.NoCustomer "
+ ", c.NoStock "
+ ", (a.NilaiTagihan - (Select ISNULL(SUM(d.NilaiPelunasan), 0) from " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN d where a.NoKontrak = d.NoKontrak and a.NoUrut = d.NoTagihan and a.Tipe != 'BF')) AS SisaTagihan"
+ ", (select SUBSTRING(NoVA, 5, 15) from " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK where NoKontrak = a.NoKontrak) AS NoVAstr"
+ ", (select Lokasi from " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT where NoStock = c.NoStock) AS Lokasi"
+ ", (select Lantai from " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT where NoStock = c.NoStock) AS Blok"
+ ", (select Nomor from " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT where NoStock = c.NoStock) AS Nomor"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN a"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK C ON a.NoKontrak = c.NoKontrak"
+ " WHERE c.Status = 'A' AND c.NoVa != '' AND a.Tipe != 'BF'"
+ " AND a.NilaiTagihan - (Select ISNULL(SUM(d.NilaiPelunasan), 0) from " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN d where a.NoKontrak = d.NoKontrak and a.NoUrut = d.NoTagihan and a.Tipe != 'BF') != 0"
+ " AND CONVERT(varchar,a.TglJT,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,a.TglJT,112) <= '" + Cf.Tgl112(Sampai) + "'"
);
if (rs.Rows.Count == 0)
{
save.Enabled = false;
}
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
HtmlTableRow tr;
HtmlTableCell c;
RadioButtonList rb;
tr = new HtmlTableRow();
list.Controls.Add(tr);
string NamaCs = Db.SingleString("select ISNULL(Nama, '') from " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER where NoCustomer = '" + rs.Rows[i]["NoCustomer"] + "'");
string UnitVA = rs.Rows[i]["Lokasi"].ToString() + rs.Rows[i]["Blok"].ToString() + rs.Rows[i]["Nomor"].ToString();
//string pattern = @"^[.,:;!?€¥£¢$-]{0,2048}$";
string[] input = { NamaCs };
string pattern = "(\\.|,|>|=)";
string NamaBuatVA = "";
foreach (string name in input)
{
NamaBuatVA = Regex.Replace(name, pattern, String.Empty);
}
if (NamaBuatVA.Length > 30)
{
NamaBuatVA = NamaBuatVA.Substring(0, 30);
}
c = new HtmlTableCell();
c.InnerHtml = (i + 1).ToString();
c.ID = "pk_" + i;
c.Attributes["title"] = rs.Rows[i]["NoVAstr"] + ";" + Math.Round(Convert.ToDecimal(rs.Rows[i]["SisaTagihan"])) + ";" + NamaBuatVA + ";" + rs.Rows[i]["NamaTagihan"].ToString() + " " + UnitVA;
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = NamaBuatVA;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NoVA"].ToString();
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = "PT PANAHOME DELTAMAS INDONESIA";
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = "SAVASA";
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NamaTagihan"].ToString() + " " + rs.Rows[i]["NoUnit"].ToString();
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(rs.Rows[i]["SisaTagihan"]);
tr.Cells.Add(c);
}
}
protected void Fill()
{
list.Controls.Clear();
if (skema.Items.Count > 0)
{
DataTable sk = Db.Rs("SELECT * FROM REF_SKOM_REWARD WHERE NoSkema = " + Convert.ToInt32(skema.SelectedValue));
if (sk != null)
{
DateTime Dari = Convert.ToDateTime(sk.Rows[0]["Dari"]);
DateTime Sampai = Convert.ToDateTime(sk.Rows[0]["Sampai"]);
string w = " AND CONVERT(VARCHAR, a.TglKontrak,112) >= '" + Cf.Tgl112(Dari) + "' AND CONVERT(VARCHAR, a.TglKontrak,112) <= '" + Cf.Tgl112(Sampai) + "'";
periode.InnerText = "Periode " + Cf.Tgl(Dari) + " s/d " + Cf.Tgl(Sampai);
string strSql = "";
//Satuan per unit
if (sk.Rows[0]["Rumus"].ToString() == "UNIT")
{
strSql = "SELECT a.NoAgent, a.NoKontrak, b.Nama, b.SalesLevel, 1 AS JumUnit, a.NilaiKontrak AS Total FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b ON a.NoAgent = b.NoAgent"
+ " WHERE a.Status = 'A'"
+ " AND b.SalesTipe = " + Convert.ToInt32(sk.Rows[0]["SalesTipe"])
+ " AND RewardID = ''"
+ " AND a.Project = '" + project.SelectedValue + "'"
+ w;
}
//Kumulatif
else
{
strSql = "SELECT a.NoAgent, b.Nama, b.SalesLevel, COUNT(a.NoKontrak) AS JumUnit, SUM(a.NilaiKontrak) AS Total FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b ON a.NoAgent = b.NoAgent"
+ " WHERE a.Status = 'A'"
+ " AND b.SalesTipe = " + Convert.ToInt32(sk.Rows[0]["SalesTipe"])
+ " AND RewardID = ''"
+ " AND a.Project = '" + project.SelectedValue + "'"
+ w
+ " GROUP BY a.NoAgent, b.Nama, b.SalesLevel";
}
DataTable rs = Db.Rs(strSql);
int index = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
int Unit = Convert.ToInt32(rs.Rows[i]["JumUnit"]);
decimal TotalKontrak = Convert.ToDecimal(rs.Rows[i]["Total"]);
string reward = "";
if (sk.Rows[0]["Rumus"].ToString() == "UNIT")
{
DataTable d2 = Db.Rs("SELECT * FROM REF_SKOM_REWARD_DETAIL WHERE NoSkema = " + Convert.ToInt32(sk.Rows[0]["NoSkema"]) + " AND SalesLevel = " + Convert.ToInt32(rs.Rows[i]["SalesLevel"]) + " AND " + TotalKontrak + " >= Penjualan");
if (d2.Rows.Count > 0)
{
reward = d2.Rows[0]["Reward"].ToString();
}
}
else
{
//Kumulatif
//unit
decimal a = Db.SingleDecimal("SELECT TargetAtas FROM REF_SKOM_REWARD_DETAIL2 WHERE NoSkema = '" + Convert.ToInt32(sk.Rows[0]["NoSkema"]) + "' AND SalesLevel = " + Convert.ToInt32(rs.Rows[i]["SalesLevel"]) + " AND TipeTarget = 'UNIT' ORDER BY SN DESC");
string b = (a > 0) ? " AND " + Unit + " <= TargetAtas" : "";
DataTable d2 = Db.Rs("SELECT * FROM REF_SKOM_REWARD_DETAIL2 WHERE NoSkema = " + Convert.ToInt32(sk.Rows[0]["NoSkema"]) + " AND SalesLevel = " + Convert.ToInt32(rs.Rows[i]["SalesLevel"]) + " AND " + Unit + " >= TargetBawah " + b + " AND TipeTarget = 'UNIT' ORDER BY SN DESC");
if (d2.Rows.Count > 0)
{
reward = d2.Rows[0]["Reward"].ToString();
}
decimal d = Db.SingleDecimal("SELECT TargetAtas FROM REF_SKOM_REWARD_DETAIL2 WHERE NoSkema = '" + Convert.ToInt32(sk.Rows[0]["NoSkema"]) + "' AND SalesLevel = " + Convert.ToInt32(rs.Rows[i]["SalesLevel"]) + " AND TipeTarget = 'NILAI' ORDER BY SN DESC");
string e = (d > 0) ? " AND " + TotalKontrak + " <= TargetAtas" : "";
//nilai kontrak
DataTable d3 = Db.Rs("SELECT * FROM REF_SKOM_REWARD_DETAIL2 WHERE NoSkema = " + Convert.ToInt32(sk.Rows[0]["NoSkema"]) + " AND SalesLevel = " + Convert.ToInt32(rs.Rows[i]["SalesLevel"]) + " AND " + TotalKontrak + " >= TargetBawah " + e + " AND TipeTarget = 'NILAI' ORDER BY SN DESC");
if (d3.Rows.Count > 0)
{
reward = d3.Rows[0]["Reward"].ToString();
}
}
bool tampil = reward != "" ? true : false;
//cek existing periode reward (tidak bisa generate reward lagi jika kumulatif)
int cek = Db.SingleInteger("SELECT COUNT(*) FROM MS_KOMISI_REWARD"
+ " WHERE NoAgent = " + Convert.ToInt32(rs.Rows[i]["NoAgent"])
+ " AND ("
+ " (CONVERT(VARCHAR,PeriodeDari,112) >= '" + Cf.Tgl112(Dari) + "' AND CONVERT(VARCHAR,PeriodeSampai,112) <= '" + Cf.Tgl112(Sampai) + "') OR"
+ " (CONVERT(VARCHAR,PeriodeDari,112) >= '" + Cf.Tgl112(Dari) + "' AND CONVERT(VARCHAR,PeriodeDari,112) <= '" + Cf.Tgl112(Sampai) + "') OR"
+ " (CONVERT(VARCHAR,PeriodeSampai,112) >= '" + Cf.Tgl112(Dari) + "' AND CONVERT(VARCHAR,PeriodeSampai,112) <= '" + Cf.Tgl112(Sampai) + "') OR"
+ " ('" + Cf.Tgl112(Dari) + "' >= CONVERT(VARCHAR,PeriodeDari,112) AND '" + Cf.Tgl112(Dari) + "' <= CONVERT(VARCHAR,PeriodeSampai,112)) OR"
+ " ('" + Cf.Tgl112(Sampai) + "' >= CONVERT(VARCHAR,PeriodeDari,112) AND '" + Cf.Tgl112(Sampai) + "' <= CONVERT(VARCHAR,PeriodeSampai,112))"
+ " )"
+ " AND Project = '" + project.SelectedValue + "'"
);
bool gen = cek > 0 && sk.Rows[0]["Rumus"].ToString() != "UNIT" ? false : true;
if (tampil)
{
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
if (cek > 0 && sk.Rows[0]["Rumus"].ToString() != "UNIT")
{
r.ForeColor = Color.Red;
}
c = new TableCell();
c.Text = rs.Rows[i]["Nama"].ToString();
c.Attributes["title"] = rs.Rows[i]["NoAgent"] + ";" + gen;
c.ID = "pk_" + index;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Unit);
if (sk.Rows[0]["Rumus"].ToString() == "UNIT")
{
c.Attributes["title"] = rs.Rows[i]["NoKontrak"].ToString();
}
c.ID = "nokontrak_" + index;
c.CssClass = "num";
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(TotalKontrak);
c.CssClass = "num";
r.Cells.Add(c);
c = new TableCell();
c.Text = reward;
c.ID = "reward_" + index;
r.Cells.Add(c);
Rpt.Border(r);
list.Controls.Add(r);
index++;
}
}
}
}
}
protected void Fill()
{
list.Controls.Clear();
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string w = "";
if (skema.SelectedIndex != 0)
{
w = " AND NoSkema = '" + skema.SelectedValue + "'";
}
string strSql = "SELECT *"
+ " FROM MS_KOMISI_REWARD"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar,Tgl,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,Tgl,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ w
+ " AND Project = '" + project.SelectedValue + "'"
+ " ORDER BY NoReward";
DataTable rs = Db.Rs(strSql);
Rpt.NoData(list, rs, "Tidak terdapat data dengan kriteria seperti tersebut diatas.");
del.Enabled = false;
int index = 0;
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 = rs.Rows[i]["NoReward"].ToString();
c.ID = "pk_" + index;
c.Attributes["title"] = rs.Rows[i]["NoReward"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaAgent"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaSkema"].ToString() + " (" + rs.Rows[i]["NoSkema"].ToString() + ")";
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Tgl(Convert.ToDateTime(rs.Rows[i]["PeriodeDari"])) + " s/d " + Cf.Tgl(Convert.ToDateTime(rs.Rows[i]["PeriodeSampai"]));
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Reward"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
list.Controls.Add(r);
index++;
del.Enabled = true;
}
}
