public async Task <ServiceResponse <GetJenisDto> > AddJenis(string nama_jenis)
{
ServiceResponse <GetJenisDto> response = new ServiceResponse <GetJenisDto>();
var data = new Jenis();
var akses = new Akses();
if (nama_jenis == null || await JenisExists(nama_jenis))
{
response.Status = false;
response.Message = (nama_jenis == null ? "Inputan tidak boleh kosong" : "Nama Jenis/Akses Sudah Ada");
return(response);
}
akses.Akses_name = nama_jenis;
akses.Akses_status = "1";
akses.Sistem_created_time = DateTime.Now;
akses.Sistem_updated_time = Convert.ToDateTime("1900-01-01");
akses.Sistem_ip_create = ip;
try
{
var cek_akses = await _context.Tb_akses.FirstOrDefaultAsync(x => x.Akses_name.ToLower() == nama_jenis.ToLower());
if (cek_akses == null)
{
await _context.Tb_akses.AddAsync(akses);
await _context.SaveChangesAsync();
data.Akses = akses;
}
else
{
data.Akses = cek_akses;
}
data.nama_jenis = nama_jenis;
data.Sistem_created_time = DateTime.Now;
data.Sistem_updated_time = Convert.ToDateTime("1900-01-01");
}
catch (Exception ex)
{
response.Status = false;
response.Message = ex.Message;
return(response);
}
try
{
await _context.Tb_jenis_pekerjaan.AddAsync(data);
await _context.SaveChangesAsync();
response.Data = _mapper.Map <GetJenisDto>(data);
response.Message = "Berhasil Tambah Data Jenis";
}
catch (Exception ex)
{
response.Status = false;
response.Message = ex.Message;
}
return(response);
}
private void Header()
{
System.Text.StringBuilder x = new System.Text.StringBuilder();
Rpt.Judul(x, comp, judul);
Rpt.SubJudul(x, "Status : Terpilih Unit");
//Rpt.SubJudul(x
// , "Periode : " + periode.SelectedItem.Text
// );
Rpt.SubJudul(x
, "Jenis : " + Jenis.Replace(";", " ")
);
Rpt.SubJudul(x
, "Project : " + Project
);
Rpt.SubJudul(x
, "Lokasi : " + Lokasi
);
Rpt.Header(rpt, x);
}
private void Header()
{
System.Text.StringBuilder x = new System.Text.StringBuilder();
string[] words = Jenis.Split('-');
bool x1 = true;
string nString = "";
foreach (string akt in words)
{
nString = akt.ToString();
}
Rpt.Judul(x, comp, judul);
if (StatusA != "")
{
Rpt.SubJudul(x, "Status : " + StatusA);
}
else if (StatusB != "")
{
Rpt.SubJudul(x, "Status : " + StatusB);
}
else
{
Rpt.SubJudul(x, "Status : " + StatusS);
}
string nJenis = String.Empty;
nJenis = Jenis.Replace("-", ",");
nJenis = nJenis.Replace("%", " ").TrimEnd(',');
Rpt.SubJudul(x
, "Periode : " + Input
);
Rpt.SubJudul(x
, "Jenis : " + nJenis
);
Rpt.SubJudul(x
, "Lokasi : " + Lokasi
);
Rpt.SubJudul(x
, "Project : " + Project
);
string legend = "";
//Rpt.Header(rpt, x);
Rpt.HeaderReport(headReport, legend, x);
}
public ActionResult Edit(Jenis jenis)
{
try
{
_jenis.Edit(jenis);
return(RedirectToAction(nameof(Index)));
}
catch
{
return(View());
}
}
public ActionResult Create(Jenis jenis)
{
try
{
_jenis.Create(jenis);
return(RedirectToAction(nameof(Index)));
}
catch (Exception ex)
{
ViewBag.Error = $"Pesan kesalahan: {ex.Message}";
return(View());
}
}
public void Edit(Jenis obj)
{
using (SqlConnection conn = new SqlConnection(GetConnStr()))
{
string strSql = @"update Jenis set NamaJenis=@NamaJenis where JenisID=@JenisID";
var param = new { NamaJenis = obj.NamaJenis, JenisID = obj.JenisID };
try
{
conn.Execute(strSql, param);
}
catch (SqlException sqlEx)
{
throw new Exception($"Kesalahan: {sqlEx.Message}");
}
}
}
public void Create(Jenis obj)
{
using (SqlConnection conn = new SqlConnection(GetConnStr()))
{
string strSql = @"insert into Jenis(NamaJenis) values(@NamaJenis)";
var param = new { NamaJenis = obj.NamaJenis };
try
{
conn.Execute(strSql, param);
}
catch (SqlException sqlEx)
{
throw new Exception($"Kesalahan: {sqlEx.Message}");
}
}
}
private void Report()
{
param.Visible = false;
rpt.Visible = true;
System.Text.StringBuilder x = new System.Text.StringBuilder();
Rpt.Judul(x, comp, judul);
string nJenis = String.Empty;
nJenis = Jenis.Replace("-", ",");
nJenis = nJenis.Replace("%", " ").TrimEnd(',');
x.Append(
"<br/>Jenis : " + nJenis
);
x.Append(
"<br/>Lokasi : " + Lokasi
);
x.Append(
"<br/>Project : " + Project
);
lblHeader.Text = Mi.Pt
+ "<br />"
+ "PER " + Cf.Day(DateTime.Today)
+ x
;
string legend = "";
Rpt.HeaderReport(headReport, legend, x);
Fill();
}
private void Fill()
{
string nStatus = "";
if (StatusA != "")
{
nStatus = " AND Status = 'A'";
}
if (StatusB != "")
{
nStatus = " AND Status = 'B'";
}
if (StatusH != "")
{
nStatus = " AND Status = 'H'";
}
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND Lokasi = '" + Lokasi + "'";
}
string str = String.Empty;
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND MS_KONTRAK.Pers = '" + Cf.Str(Perusahaan) + "'";
}
string[] str3 = Jenis.Replace(".", " ").Split('-');
for (int i = 0; i < str3.Length; i++)
{
if (i == str3.Length - 1)
{
str += "'" + str3[i] + "'";
}
else
{
str += "'" + str3[i] + "',";
}
}
//string str2 = "";
//foreach (var t in characters)
//{
// str2 += "." + t + "',";
//}
//str2 = str2.TrimEnd(',');
decimal t1 = 0;
string strSql = "SELECT "
+ " NoStock"
+ ",Jenis"
+ ",Lokasi"
+ ",NoUnit"
+ ",Luas"
+ ",PriceList"
+ ",PriceListMin"
+ ",TglInput"
+ ",Status"
+ ",Panorama"
+ ",LuasSG"
+ ",LuasNett"
+ ",JenisProperti"
+ ",HadapAtrium"
+ ",HadapEntrance"
+ ",HadapEskalator"
+ ",HadapLift"
+ ",HadapParkir"
+ ",HadapAxis"
+ " FROM MS_UNIT"
+ " WHERE Jenis IN (" + str + ")"
+ nProject
+ nStatus
+ nLokasi
//+ Periode
;
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;
r.Attributes["ondblclick"] = "popEditUnit('" + rs.Rows[i]["NoStock"] + "')";
DateTime p = Convert.ToDateTime(rs.Rows[i]["TglInput"]);
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoStock"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Jenis"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM REF_LOKASI WHERE Lokasi = '" + rs.Rows[i]["Lokasi"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["JenisProperti"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
decimal luassg = Convert.ToDecimal(rs.Rows[i]["LuasSG"]);
c.Text = Math.Round(luassg, 2).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
decimal luasnet = Convert.ToDecimal(rs.Rows[i]["LuasNett"]);
c.Text = Math.Round(luasnet, 2).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
if (rs.Rows[i]["HadapAtrium"].ToString() == "1")
{
c.Text = "Hadap Atrium";
}
else if (rs.Rows[i]["HadapEntrance"].ToString() == "1")
{
c.Text = "Hadap Eskalator";
}
else if (rs.Rows[i]["HadapEskalator"].ToString() == "1")
{
c.Text = "Hadap Eskalator";
}
else if (rs.Rows[i]["HadapLift"].ToString() == "1")
{
c.Text = "Hadap Lift";
}
else if (rs.Rows[i]["HadapParkir"].ToString() == "1")
{
c.Text = "Hadap Parkir";
}
else if (rs.Rows[i]["HadapAxis"].ToString() == "1")
{
c.Text = "Hadap Axis";
}
else
{
c.Text = "";
}
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Panorama"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
if (rs.Rows[i]["Status"].ToString() == "B")
{
c.Text = "S";
}
else if (rs.Rows[i]["Status"].ToString() == "A")
{
c.Text = "A";
}
else
{
c.Text = "H";
}
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NoKontrak FROM MS_KONTRAK WHERE NoStock = '" + rs.Rows[i]["NoStock"].ToString() + "'" + nPerusahaan + " AND Status = 'A'");
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 = t1 + (decimal)rs.Rows[i]["PriceList"];
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", t1);
}
}
}
public Task Update(Jenis obj)
{
throw new NotImplementedException();
}
public Task Insert(Jenis obj)
{
throw new NotImplementedException();
}
private void Fill()
{
string Status = "";
if (StatusA != "")
{
Status = " AND MS_KONTRAK.Status = 'A'";
}
if (StatusB != "")
{
Status = " AND MS_KONTRAK.Status = 'B'";
}
string tgl = "";
string order = "";
tgl = "TglKontrak";
order = "NoKontrak";
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string nAgent = "";
if (Agent != "SEMUA")
{
nAgent = " AND MS_KONTRAK.NoAgent = " + Agent;
}
else
{
if (UserAgent() > 0)
{
nAgent = " AND MS_KONTRAK.NoAgent = " + UserAgent();
}
}
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 jenis
string akt = String.Empty;
akt = Jenis.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("%", " ");
akt = akt.Replace(",", "','");
akt = "'" + akt + "'";
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
decimal t6 = 0;
decimal t7 = 0;
decimal t8 = 0;
decimal t9 = 0;
decimal t10 = 0;
decimal t11 = 0;
decimal t12 = 0;
decimal t13 = 0;
decimal t14 = 0;
string strSql = "SELECT *"
+ " FROM MS_KONTRAK"
+ " INNER JOIN MS_CUSTOMER ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer "
+ " INNER JOIN MS_AGENT ON MS_KONTRAK.NoAgent = MS_AGENT.NoAgent "
+ " WHERE 1=1 "
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND Jenis IN (" + akt + ")"
+ nPerusahaan
+ nProject
+ nLokasi
+ Status
+ nAgent
+ " ORDER BY " + order;
DataTable rs = Db.Rs(strSql);
TableRow r = new TableRow();
TableHeaderCell hc;
// r.Attributes["ondblclick"] = "popEditKontrak('"+rs.Rows[0]["NoKontrak"]+"')";
hc = new TableHeaderCell();
hc.Text = "Kontrak";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "View";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Biaya Admin";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "KPR / Pelunasan";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Total";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Kontrak";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "View";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Biaya Admin";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "KPR / Pelunasan";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Total";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
rpt.Rows.Add(r);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "')";
c = new TableCell();
c.Text = Cf.Num(i + 1);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglKontrak"]);
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
decimal LuasSG = Db.SingleDecimal("SELECT LuasSG FROM MS_UNIT WHERE NoStock = '" + rs.Rows[i]["NoStock"] + "'");
c = new TableCell();
c.Text = Cf.Num(LuasSG);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Nama"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
decimal NilaiKontrak = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan), 0) FROM MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND TIPE != 'ADM' ");
c.Text = Cf.Num(NilaiKontrak);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal View = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan), 0) FROM MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND NamaTagihan LIKE 'FITTING OUT%' AND TIPE = 'ADM' ");
c.Text = Cf.Num(View);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal BiayaAdmin = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan), 0) FROM MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND NamaTagihan NOT LIKE 'FITTING OUT%' AND TIPE = 'ADM' ");
c.Text = Cf.Num(BiayaAdmin);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal Total = NilaiKontrak + View + BiayaAdmin;
c.Text = Cf.Num(Total);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal KontrakBayar = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " MS_PELUNASAN a INNER JOIN MS_TAGIHAN b ON "
+ " a.NoTagihan = b.NoUrut WHERE "
+ " a.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND b.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' "
+ " AND b.Tipe != 'ADM' "
+ " AND b.KPR = '0' ");
c.Text = Cf.Num(KontrakBayar);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal ViewBayar = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " MS_PELUNASAN a INNER JOIN MS_TAGIHAN b ON "
+ " a.NoTagihan = b.NoUrut WHERE "
+ " a.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND b.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' "
+ " AND b.Tipe = 'ADM' "
+ " AND b.NamaTagihan LIKE 'FITTING OUT%' ");
c.Text = Cf.Num(ViewBayar);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal AdmBayar = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " MS_PELUNASAN a INNER JOIN MS_TAGIHAN b ON "
+ " a.NoTagihan = b.NoUrut WHERE "
+ " a.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND b.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' "
+ " AND b.Tipe = 'ADM' "
+ " AND b.NamaTagihan NOT LIKE 'FITTING OUT%' ");
c.Text = Cf.Num(AdmBayar);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal KPRBayar = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " MS_PELUNASAN a INNER JOIN MS_TAGIHAN b ON "
+ " a.NoTagihan = b.NoUrut WHERE "
+ " a.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND b.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' "
+ " AND b.Tipe != 'ADM' "
+ " AND b.KPR = '1' ");
c.Text = Cf.Num(KPRBayar);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal TotalKolomBayar = KontrakBayar + ViewBayar + AdmBayar + KPRBayar;
c.Text = Cf.Num(TotalKolomBayar);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal NilaiKontrak2 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0) FROM "
+ " MS_TAGIHAN"
+ " WHERE "
+ " NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' "
+ " AND Tipe != 'ADM' "
+ " AND KPR = '0' ");
decimal KontrakOut = NilaiKontrak2 - KontrakBayar;
c.Text = Cf.Num(KontrakOut);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal ViewOut = View - ViewBayar;
c.Text = Cf.Num(ViewOut);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal AdmOut = BiayaAdmin - AdmBayar;
c.Text = Cf.Num(AdmOut);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal TagihanKPR = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0) FROM "
+ " MS_TAGIHAN "
+ " WHERE "
+ " NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' "
+ " AND Tipe != 'ADM' "
+ " AND KPR = '1' ");
decimal KPROut = TagihanKPR - KPRBayar;
c.Text = Cf.Num(KPROut);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal TotalKolomOut = KontrakOut + ViewOut + AdmOut + KPROut;
c.Text = Cf.Num(TotalKolomOut);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 += NilaiKontrak;
t2 += View;
t3 += BiayaAdmin;
t4 += Total;
t5 += KontrakBayar;
t6 += ViewBayar;
t7 += AdmBayar;
t8 += KPRBayar;
t9 += TotalKolomBayar;
t10 += KontrakOut;
t11 += ViewOut;
t12 += AdmOut;
t13 += KPROut;
t14 += TotalKolomOut;
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14);
}
}
}
private void Fill()
{
string nStatus = "";
if (StatusA != "")
{
nStatus = " AND Status = 'A'";
}
if (StatusB != "")
{
nStatus = " AND Status = 'B'";
}
string nInput = "";
if (Input != "SEMUA")
{
string[] z = Input.Split('-');
nInput = " AND YEAR(TglInput) = " + z[0]
+ " AND MONTH(TglInput) = " + z[1];
}
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND MS_UNIT.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND Project IN ('" + Project.Replace(",", "','") + "')";
}
//change parameter jenis
string akt = String.Empty;
akt = Jenis.Replace("-", ",").TrimEnd(',');
akt = akt.Replace(",", "','");
akt = akt.Replace("%", " ");
akt = "'" + akt + "'";
decimal t1 = 0, t2 = 0;
string strSql = "SELECT "
+ " NoStock"
+ ",Jenis"
+ ",Lokasi"
+ ",NoUnit"
+ ",Luas"
+ ",PriceList"
+ ",PriceListMin"
+ ",TglInput"
+ ",Status"
+ " FROM MS_UNIT"
+ " WHERE Jenis IN (" + akt + ")"
// + " WHERE"
+ nProject
+ nStatus
+ nLokasi
+ nInput
+ " ORDER BY NoStock";
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;
r.Attributes["ondblclick"] = "popEditUnit('" + rs.Rows[i]["NoStock"] + "')";
DateTime p = Convert.ToDateTime(rs.Rows[i]["TglInput"]);
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NoKontrak FROM MS_KONTRAK WHERE NoStock = '" + rs.Rows[i]["NoStock"].ToString() + "' AND Status = 'A'");
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglInput"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Lokasi"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Jenis"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["PriceListMin"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["PriceList"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
string status = rs.Rows[i]["Status"].ToString();
if (Convert.ToChar(status) == 'A')
{
c.Text = "AVAILABLE";
}
else
{
c.Text = "SOLD";
}
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 += (decimal)rs.Rows[i]["PriceList"];
t2 += (decimal)rs.Rows[i]["PriceListMin"];
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", t1, t2);
}
}
}
private void Fill()
{
string nLokasi = Lokasi != "SEMUA" ? " AND MS_UNIT.Lokasi = '" + Lokasi + "'" : "";
string nProject = (Project != "SEMUA") ? " AND Project IN ('" + Project.Replace(",", "','") + "')" : "";
string akt = String.Empty;
akt = Jenis.Replace("-", ",").TrimEnd(',');
akt = akt.Replace("+", " ");
akt = akt.Replace(",", "','");
akt = "'" + akt + "'";
decimal t1 = 0;
string strSql = "SELECT "
+ " NoStock"
+ ",Jenis"
+ ",Lokasi"
+ ",NoUnit"
+ ",Luas"
+ ",PriceList"
+ ",PriceListMin"
+ ",TglInput"
+ ",Status"
+ " FROM MS_UNIT"
+ " WHERE 1=1"
+ " AND Jenis IN (" + akt + ")"
+ nProject
+ " AND Status = 'P'"
+ nLokasi
//+ Periode
+ " AND NoStock NOT IN (SELECT NoStock FROM MS_KONTRAK)"
+ " ORDER BY NoStock";
//Response.Write(strSql);
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;
//r.Attributes["ondblclick"] = "popEditUnit('"+rs.Rows[i]["NoStock"]+"')";
DateTime p = Convert.ToDateTime(rs.Rows[i]["TglInput"]);
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NoKontrak FROM MS_KONTRAK WHERE NoStock = '" + rs.Rows[i]["NoStock"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglInput"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Lokasi"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Jenis"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Status"].ToString();
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
DataTable dtNUP = Db.Rs("SELECT B.*, C.Nama AS NamaCS, D.Nama AS NamaAG FROM MS_PRIORITY A"
+ " INNER JOIN MS_NUP B ON A.NoNUP = B.NoNUP"
+ " INNER JOIN MS_CUSTOMER C ON B.NoCustomer = C.NoCustomer"
+ " INNER JOIN MS_AGENT D ON B.NoAgent = D.NoAgent"
+ " WHERE A.NoStock = '" + rs.Rows[i]["NoStock"].ToString() + "'"
);
for (int j = 0; j < dtNUP.Rows.Count; j++)
{
if (!Response.IsClientConnected)
{
break;
}
c = new TableCell();
c.Text = dtNUP.Rows[j]["NoNUP"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
decimal bayar = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiBayar),0) FROM MS_NUP_PELUNASAN WHERE NoNUP='" + dtNUP.Rows[j]["NoNUP"].ToString() + "'");
c.Text = Cf.Num(bayar);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = dtNUP.Rows[j]["NamaCS"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = dtNUP.Rows[j]["NamaAG"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
t1 = t1 + bayar;
}
rpt.Rows.Add(r);
if (i == rs.Rows.Count - 1)
{
SubTotal("TOTAL PEMBAYARAN", t1);
}
}
}
private void Fill()
{
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND Project IN ('" + Project.Replace(",", "','") + "')";
}
//change parameter jenis
string akt = String.Empty;
akt = Jenis.Replace("-", ",").TrimEnd(',');
akt = akt.Replace(",", "','");
akt = akt.Replace("%", " ");
akt = "'" + akt + "'";
DataTable aa = Db.Rs("SELECT DISTINCT(Lokasi) FROM MS_UNIT WHERE 1=1" + nLokasi + nProject);
for (int j = 0; j < aa.Rows.Count; j++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = aa.Rows[j]["Lokasi"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string strSql = "SELECT DISTINCT(JENIS)"
+ " FROM MS_UNIT"
+ " WHERE Lokasi = '" + aa.Rows[j]["Lokasi"] + "'"
+ " AND Jenis IN (" + akt + ")"
;
DataTable rs = Db.Rs(strSql);
decimal TotalAvailable = 0, LuasAvailable = 0;
decimal TotalSold = 0, LuasSold = 0;
decimal TotalHold = 0, LuasHold = 0;
decimal Total = 0, LuasTotal = 0;
decimal TotalTitip = 0;//, LuasTitip = 0;
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0, t7 = 0, t8 = 0, t9 = 0, t10 = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
if (i > 0)
{
r = new TableRow();
c = new TableCell();
c.Text = "";
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
}
c = new TableCell();
c.Text = rs.Rows[i]["Jenis"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
TotalAvailable = Nilai(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "AVAILABLE", Project);
c.Text = Cf.Num(TotalAvailable);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
LuasAvailable = Math.Round(Persen(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "AVAILABLE", Project), 2);
c.Text = Cf.Num(Math.Round(LuasAvailable, 2)) + " %";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
TotalSold = Nilai(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "SOLD", Project);
c.Text = Cf.Num(TotalSold);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
LuasSold = Math.Round(Persen(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "SOLD", Project), 2);
c.Text = Cf.Num(LuasSold) + " %";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
TotalHold = Nilai(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "HOLD", Project);
c.Text = Cf.Num(TotalHold);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
LuasHold = Math.Round(Persen(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "HOLD", Project), 2);
c.Text = Cf.Num(LuasHold) + " %";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
Total = Nilai(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "TOTAL", Project);
c.Text = Cf.Num(Total);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
LuasTotal = Math.Round(Persen(rs.Rows[i]["Jenis"].ToString(), aa.Rows[j]["Lokasi"].ToString(), "TOTAL", Project), 2);
c.Text = Cf.Num(LuasTotal) + " %";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
t5 += Total;
t1 += TotalSold;
t2 = t1 / t5 * 100;
t3 += TotalAvailable;
t4 = t3 / t5 * 100;
t7 += TotalHold;
t8 = t7 / t5 * 100;
t9 += TotalTitip;
t10 = t9 / t5 * 100;
t6 = t2 + t4 + t8 + t10;
rpt.Rows.Add(r);
if (i == (rs.Rows.Count - 1))
{
SubTotal(t1, t2, t3, t4, t7, t8, t5, t6, t9, t10);
}
}
}
}
public void Create(Jenis obj)
{
using (SqlConnection conn = new SqlConnection(GetConnStr()))
{
}
}
public void Edit(Jenis obj)
{
throw new NotImplementedException();
}
private void Fill()
{
string nStatus = "";
if (StatusA != "")
{
nStatus = " AND Status = 'A'";
}
if (StatusB != "")
{
nStatus = " AND Status = 'B'";
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND b.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nInput = "";
if (Input != "SEMUA")
{
string[] z = Input.Split('-');
nInput = " AND YEAR(a.Periode) = '" + z[0] + "'"
+ " AND MONTH(a.Periode) = '" + z[1] + "'";
}
//string nLokasi = "";
//if(Lokasi != "SEMUA")
//{
// nLokasi = " AND MS_UNIT.Lokasi = '" + Cf.Str(Lokasi) + "'";
//}
//change parameter jenis
string akt = String.Empty;
akt = Jenis.Replace("-", ",").TrimEnd(',');
akt = akt.Replace(",", "','");
akt = akt.Replace("%", " ");
akt = "'" + akt + "'";
decimal t1 = 0, t2 = 0;
Response.Write(nInput);
string strSql = "SELECT a.NoStock"
+ " FROM MS_PRICELIST_HISTORY a"
+ " INNER JOIN MS_UNIT b ON a.NoStock = b.NoStock"
+ " WHERE b.Jenis IN (" + akt + ")"
+ nProject
+ nStatus
+ nInput
+ " GROUP BY a.NoStock"
+ " ORDER BY a.NoStock";
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string strSql2 = "SELECT a.*, b.NoUnit"
+ " FROM MS_PRICELIST_HISTORY a"
+ " INNER JOIN MS_UNIT b ON a.NoStock = b.NoStock"
+ " WHERE a.NoStock = '" + rs.Rows[i]["NoStock"] + "'"
+ " ORDER BY a.NoStock";
DataTable rs2 = Db.Rs(strSql2);
if (rs2.Rows.Count > 0)
{
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditUnit('" + rs2.Rows[0]["NoStock"] + "')";
DateTime p = Convert.ToDateTime(rs2.Rows[0]["Periode"]);
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs2.Rows[0]["NoStock"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs2.Rows[0]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
DataTable aa = Db.Rs("SELECT DISTINCT(Periode) FROM MS_PRICELIST_HISTORY ORDER BY Periode");
for (int j = 0; j < aa.Rows.Count; j++)
{
string nilai = "";
DataTable bb = Db.Rs("SELECT TOP 1 PriceList FROM MS_PRICELIST_HISTORY WHERE NoStock = '" + rs2.Rows[0]["NoStock"] + "' AND Periode = '" + aa.Rows[j]["Periode"] + "' ORDER BY No DESC");
if (bb.Rows.Count > 0)
{
nilai = Cf.Num(bb.Rows[0]["PriceList"]);
}
c = new TableCell();
c.Text = nilai != "" ? Cf.Day(aa.Rows[j]["Periode"]) : "";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = nilai;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
}
string strSql3 = "SELECT b.*, c.Nama"
+ " FROM MS_KONTRAK b"
+ " INNER JOIN MS_CUSTOMER c ON b.NoCustomer = c.NoCustomer"
+ " WHERE b.NoStock = '" + rs.Rows[i]["NoStock"] + "'"
+ " ORDER BY b.NoStock";
DataTable rs3 = Db.Rs(strSql3);
if (rs3.Rows.Count > 0)
//for (int k = 0; k < rs3.Rows.Count; k++)
{
c = new TableCell();
c.Text = rs3.Rows[0]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs3.Rows[0]["TglKontrak"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs3.Rows[0]["Nama"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs3.Rows[0]["Gross"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
}
else
{
c = new TableCell();
c.Text = "";
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
}
rpt.Rows.Add(r);
t1 += (decimal)rs2.Rows[0]["PriceList"];
}
//if(i==rs.Rows.Count-1)
// SubTotal("GRAND TOTAL", t1);
}
}
