private static string kondisi(string kolom, string satuan, int waktu)
{
string s = "";
switch (satuan)
{
case ("detik"):
s = " AND CONVERT(varchar," + kolom + ",112) = '" + Cf.Tgl112(DateTime.Today.AddSeconds(waktu)) + "'";
break;
case ("menit"):
s = " AND CONVERT(varchar," + kolom + ",112) = '" + Cf.Tgl112(DateTime.Today.AddMinutes(waktu)) + "'";
break;
case ("jam"):
s = " AND CONVERT(varchar," + kolom + ",112) = '" + Cf.Tgl112(DateTime.Today.AddHours(waktu)) + "'";
break;
case ("hari"):
s = " AND CONVERT(varchar," + kolom + ",112) = '" + Cf.Tgl112(DateTime.Today.AddDays(waktu)) + "'";
break;
case ("bulan"):
s = " AND CONVERT(varchar," + kolom + ",112) = '" + Cf.Tgl112(DateTime.Today.AddMonths(waktu)) + "'";
break;
case ("tahun"):
s = " AND CONVERT(varchar," + kolom + ",112) = '" + Cf.Tgl112(DateTime.Today.AddYears(waktu)) + "'";
break;
}
return(s);
}
private void AktivitasCustomer(ref string ContentMail)
{
ContentMail += "<b>Aktivitas Customer :</b> <br />";
//Aktivitas Batal
ContentMail += "<table>";
DataTable rsBatal = Db.Rs("SELECT * FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE TglBatal = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND Status = 'B'");
ContentMail += "<tr><td>Batal : " + Cf.Num(rsBatal.Rows.Count) + "</td></tr>";
ContentMail += "</table>";
//Aktivitas Pengalihan Hak
ContentMail += "<table>";
DataTable rsGantiNama = Db.Rs("SELECT * FROM ISC064_MARKETINGJUAL..MS_KONTRAK_LOG WHERE Tgl = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND Aktivitas = 'GN'");
ContentMail += "<tr><td>Pengalihan Hak : " + Cf.Num(rsGantiNama.Rows.Count) + "</td></tr>";
ContentMail += "</table>";
//Aktivitas Pindah Unit
ContentMail += "<table>";
DataTable rsGantiUnit = Db.Rs("SELECT * FROM ISC064_MARKETINGJUAL..MS_KONTRAK_LOG WHERE Tgl = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND Aktivitas = 'GU'");
ContentMail += "<tr><td>Pindah Unit : " + Cf.Num(rsGantiUnit.Rows.Count) + "</td></tr>";
ContentMail += "</table><br />";
}
private void Finance(ref string ContentMail)
{
ContentMail += "<b>Finance :</b> <br />";
decimal total = Db.SingleDecimal("SELECT ISNULL(SUM(Total),0) FROM ISC064_FINANCEAR..MS_TTS WHERE TglTTS = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "'");
ContentMail += "<table>";
ContentMail += "<tr><td></td><td colspan='2'>Payment Received (Rp) : " + Cf.Num(total) + "</td></tr>";
decimal cash = Db.SingleDecimal("SELECT ISNULL(SUM(Total),0) FROM ISC064_FINANCEAR..MS_TTS WHERE TglTTS = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND CaraBayar = 'TN'");
ContentMail += "<tr><td colspan='2'></td><td style='text-align:right;'>Cash :</td><td>" + Cf.Num(cash) + "</td></tr>";
decimal creditcard = Db.SingleDecimal("SELECT ISNULL(SUM(Total),0) FROM ISC064_FINANCEAR..MS_TTS WHERE TglTTS = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND CaraBayar = 'KK'");
ContentMail += "<tr><td colspan='2'></td><td style='text-align:right;'>Credit Card :</td><td>" + Cf.Num(creditcard) + "</td></tr>";
decimal debitcard = Db.SingleDecimal("SELECT ISNULL(SUM(Total),0) FROM ISC064_FINANCEAR..MS_TTS WHERE TglTTS = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND CaraBayar = 'KD'");
ContentMail += "<tr><td colspan='2'></td><td style='text-align:right;'>Debit Card :</td><td>" + Cf.Num(debitcard) + "</td></tr>";
decimal giro = Db.SingleDecimal("SELECT ISNULL(SUM(Total),0) FROM ISC064_FINANCEAR..MS_TTS WHERE TglTTS = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND CaraBayar = 'BG'");
ContentMail += "<tr><td colspan='2'></td><td style='text-align:right;'>Giro :</td><td>" + Cf.Num(giro) + "</td></tr>";
decimal transfer = Db.SingleDecimal("SELECT ISNULL(SUM(Total),0) FROM ISC064_FINANCEAR..MS_TTS WHERE TglTTS = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND CaraBayar = 'TR'");
ContentMail += "<tr><td colspan='2'></td><td style='text-align:right;'>Transfer :</td><td>" + Cf.Num(transfer) + "</td></tr>";
ContentMail += "</table><br /><br />";
}
private void Summary(ref string ContentMail)
{
decimal totalJenis = 0, totalUnit = 0;
ContentMail += "<b>Sales :</b> <br />";
DataTable rsJenis = Db.Rs("SELECT DISTINCT Jenis FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE TglKontrak = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND Status = 'A'");
ContentMail += "<table>";
ContentMail += "<tr><td style='width:100px;'>Tipe Unit</td><td style='width:100px;'>Jumlah Unit</td><td style='width:100px;'>Nilai Kontrak (Rp.)</td></tr>";
for (int i = 0; i < rsJenis.Rows.Count; i++)
{
ContentMail += "<tr>";
ContentMail += "<td>" + rsJenis.Rows[i]["Jenis"].ToString() + "</td>";
int unitJenis = Db.SingleInteger("SELECT COUNT(*) FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE TglKontrak = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND Status = 'A' AND Jenis = '" + rsJenis.Rows[i]["Jenis"] + "'");
totalUnit += unitJenis;
ContentMail += "<td>" + Cf.Num(unitJenis) + "</td>";
decimal nilaiJenis = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiKontrak), 0) FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE TglKontrak = '" + Cf.Tgl112(DateTime.Today.AddDays(-1)) + "' AND Status = 'A' AND Jenis = '" + rsJenis.Rows[i]["Jenis"] + "'");
totalJenis += nilaiJenis;
ContentMail += "<td>" + Cf.Num(nilaiJenis) + "</td>";
ContentMail += "</tr>";
}
ContentMail += "<tr><td>Total</td><td style='border-top:double black 1px;'>" + Cf.Num(totalUnit) + "</td><td style='border-top:double black 1px;'>" + Cf.Num(totalJenis) + "</td></tr>";
ContentMail += "</table><br />";
}
