private Lokasi GetData()
{
Lokasi oData = new Lokasi();
oData.NamaTempatLokasi = txtLocationName.Text;
oData.NoTelpLokasi = txtPhone.Text;
oData.EmailLokasi = txtEmail.Text;
if (this.NegaraSelected != null)
{
oData.IdNegara = this.NegaraSelected.Id;
oData.NamaNegara = this.NegaraSelected.Nama;
}
oData.AlamatLokasi = txtAddress.Text;
oData.KotaLokasi = txtCity.Text;
if (this.PropinsiSelected != null)
{
oData.IdPropinsi = this.PropinsiSelected.Id;
oData.PropinsiLokasi = this.PropinsiSelected.Nama;
}
oData.KodePosLokasi = int.Parse(txtZipcode.Text);
oData.MapLocationLokasi = txtMap.Text;
oData.CheckboxDefault = chkDefault.IsChecked;
oData.CheckboxNotActive = chkNotActive.IsChecked;
if (this.LocationForm.Lokasi != null)
{
oData.Id = this.LocationForm.Lokasi.Id;
}
return(oData);
}
private void lokasib_selectionchange(object sender, SelectionChangedEventArgs e)
{
this.lokasiSelected = null;
if (cbLocationB.SelectedItem != null)
{
lokasiSelected = (Lokasi)cbLocationB.SelectedItem;
}
}
private void cblokasi_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
this.lokasiSelected = null;
if (cbLocation.SelectedItem != null)
{
this.lokasiSelected = (Lokasi)cbLocation.SelectedItem;
}
}
public bool EditLokasi(Lokasi oData)
{
methodName = "EditLokasi";
traceID = 1;
using (var uow = new UnitOfWork(AppConfig.Current.ContextName))
{
traceID = 2;
var oDBData = uow.Lokasi.Get(oData.Id);
if (oDBData != null)
{
using (var trans = uow.BeginTransaction())
{
try
{
traceID = 3;
oDBData.MapFrom(oData);
uow.Lokasi.Update(oDBData);
traceID = 4;
var oDBListData = uow.ListLokasi.SingleOrDefault(m => m.IdLokasi == oData.Id);
if (oDBListData != null)
{
traceID = 5;
int idListLokasi = oDBListData.Id;
oDBListData.MapFrom(oData);
oDBListData.Id = idListLokasi;
oDBListData.IdLokasi = oData.Id;
oDBListData.NegaraLokasi = oData.NamaNegara;
traceID = 6;
uow.ListLokasi.Update(oDBListData);
}
else
{
ListLokasi oNewListLokasi = new ListLokasi();
oNewListLokasi.MapFrom(oData);
oNewListLokasi.IdLokasi = oData.Id;
oNewListLokasi.NegaraLokasi = oData.NamaNegara;
uow.ListLokasi.Add(oNewListLokasi);
}
traceID = 7;
uow.Save();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw new AppException(500, methodName, traceID, ex);
}
}
}
}
return(true);
}
public void AddLokasi(LokasiCreateEditViewModel model)
{
var lokasi = new Lokasi()
{
NomorRak = model.NomorRak,
NomorBay = model.NomorBay
};
context.Add(lokasi);
context.SaveChanges();
}
private void LiLocation_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
this.ClearForm();
if (LiLocation.SelectedItem != null)
{
this.ListLokasiSelected = (ListLokasi)LiLocation.SelectedItem;
if (this.ListLokasiSelected != null)
{
using (var uow = new UnitOfWork(AppConfig.Current.ContextName))
{
this.Lokasi = uow.Lokasi.Get(this.ListLokasiSelected.IdLokasi);
}
}
this.LoadLokasiSKU();
}
}
public int AddLokasi(Lokasi oData)
{
methodName = "AddLokasi";
traceID = 1;
using (var uow = new UnitOfWork(AppConfig.Current.ContextName))
{
using (var trans = uow.BeginTransaction())
{
try
{
traceID = 2;
Lokasi oNewLokasi = new Lokasi();
oNewLokasi.MapFrom(oData);
oNewLokasi = uow.Lokasi.Add(oNewLokasi);
uow.Save();
traceID = 3;
oData.Id = oNewLokasi.Id;
if (oData.Id > 0)
{
traceID = 4;
ListLokasi oNewListLokasi = new ListLokasi();
oNewListLokasi.MapFrom(oData);
oNewListLokasi.IdLokasi = oData.Id;
oNewListLokasi.NegaraLokasi = oData.NamaNegara;
uow.ListLokasi.Add(oNewListLokasi);
}
traceID = 5;
uow.Save();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw new AppException(500, methodName, traceID, ex);
}
}
}
return(oData.Id);
}
public ActionResult ImportKota(FormCollection formCollection)
{
var dataList = new List <Lokasi>();
if (Request != null)
{
HttpPostedFileBase file = Request.Files["UploadKota"];
if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
{
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
var listData = new Lokasi();
listData.ID = Convert.ToInt32(workSheet.Cells[rowIterator, 1].Value);
listData.IDProvinsi = workSheet.Cells[rowIterator, 2].Value.ToString();
listData.Provinsi = workSheet.Cells[rowIterator, 3].Value.ToString();
listData.Kota = workSheet.Cells[rowIterator, 4].Value.ToString();
dataList.Add(listData);
}
}
}
}
using (BuildingContext importDB = new BuildingContext())
{
foreach (var item in dataList)
{
importDB.Lokasi.Add(item);
}
importDB.SaveChanges();
}
return(View("Index"));
}
public bool RemoveLokasi(int id)
{
methodName = "RemoveLokasi";
traceID = 1;
using (var uow = new UnitOfWork(AppConfig.Current.ContextName))
{
using (var trans = uow.BeginTransaction())
{
try
{
traceID = 2;
Lokasi oDBLokasi = uow.Lokasi.SingleOrDefault(m => m.Id == id);
if (oDBLokasi != null)
{
traceID = 3;
uow.Lokasi.Remove(id);
var oDBListLokasi = uow.ListLokasi.SingleOrDefault(m => m.IdLokasi == id);
if (oDBListLokasi != null)
{
uow.ListLokasi.Remove(oDBListLokasi.Id);
}
uow.Save();
}
traceID = 5;
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw new AppException(500, methodName, traceID, ex);
}
}
}
return(true);
}
private void Fill()
{
string Status = "";
if (StatusA != "")
{
Status = " AND " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK.Status = 'A'";
}
if (StatusB != "")
{
Status = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'B'";
}
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Pers = '" + Perusahaan + "'";
}
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND Lokasi = '" + Cf.Str(Lokasi.Replace("%", " ")) + "'";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK.NoAgent = " + UserAgent();
}
string strSql = "SELECT ISC064_MARKETINGJUAL..MS_KONTRAK.*"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama AS Cs"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.NoTelp AS NoTelp"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.NoHp AS NoHp"
+ ",ISC064_MARKETINGJUAL..MS_AGENT.Nama AS Ag"
+ ",ISC064_MARKETINGJUAL..MS_AGENT.Principal"
+ ",(SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak) AS NilaiTTS"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER ON ISC064_MARKETINGJUAL..MS_KONTRAK.NoCustomer = ISC064_MARKETINGJUAL..MS_CUSTOMER.NoCustomer"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_AGENT ON ISC064_MARKETINGJUAL..MS_KONTRAK.NoAgent = ISC064_MARKETINGJUAL..MS_AGENT.NoAgent "
+ " WHERE 1=1 "
+ nProject
+ nPerusahaan
+ nLokasi
+ Status
+ aa
;
DataTable rs = Db.Rs(strSql);
TableRow r = new TableRow();
TableHeaderCell hc;
hc = new TableHeaderCell();
hc.Text = "Overdue";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Actual";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Early";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "A";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "B";
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;
}
DataTable rs2 = Db.Rs("SELECT *"
+ " ,(SELECT COUNT(*) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoKontrak AND NoTagihan = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoUrut) AS CountLunas"
+ " ,(SELECT COUNT(*) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoKontrak AND NoTagihan = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoUrut AND (CONVERT(VARCHAR, TglPelunasan, 112) >= " + Cf.Tgl112(Dari) + " AND CONVERT(VARCHAR, TglPelunasan, 112) <= " + Cf.Tgl112(Sampai) + ")) AS CountLunas2"
+ " FROM ISC064_MARKETINGJUAL..MS_TAGIHAN"
+ " WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "'");
string temp = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "NAMA", Dari, Sampai);
if (temp != "")
{
r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "')";
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]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglKontrak"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["Cs"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["NoTelp"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["NoHp"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["Ag"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = temp;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "TGL", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "OVERDUE", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "ACTUAL", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "EARLY", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "TGLLUNAS", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "A", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "B", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
if (i == (rs.Rows.Count - 1))
{
SubTotal();
}
}
}
private void Fill()
{
string strAdd = "";
if (Lokasi != "SEMUA")
{
strAdd += " AND a.Lokasi = '" + Lokasi.Replace("%", " ") + "'";
}
if (Sales != "SEMUA")
{
strAdd += " AND a.NoAgent = " + Cf.Pk(Sales);
}
else
{
if (UserAgent() > 0)
{
strAdd += " AND a.NoAgent = " + UserAgent();
}
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND a.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND a.Pers = '" + Perusahaan + "'";
}
string strSql = "SELECT *, b.Nama AS NamaCustomer, c.Nama AS NamaAgent, c.Principal"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK a "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER b "
+ " ON a.NoCustomer = b.NoCustomer "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_AGENT c"
+ " ON a.NoAgent = c.NoAgent "
+ " WHERE 1=1 "
+ " AND a.Status = 'A' "
+ nProject
+ nPerusahaan
+ strAdd
+ " ORDER BY a.NoUnit"
;
DataTable rs = Db.Rs(strSql);
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0;
int index = 1;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
decimal st1 = 0, st2 = 0, st3 = 0, st4 = 0, st5 = 0;
TableRow tr = new TableRow();
TableCell c;
c = new TableCell();
c.Text = index.ToString();
c.HorizontalAlign = HorizontalAlign.Center;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaCustomer"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaAgent"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
decimal Total = TotalOutstanding(rs.Rows[i]["NoKontrak"].ToString());
c.Text = Cf.Num(Total);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
tr.Cells.Add(c);
FillOutstanding(rs.Rows[i]["NoKontrak"].ToString(), ref t2, ref t3, ref t4, ref t5, ref t6,
ref st1, ref st2, ref st3, ref st4, ref st5, ref index, tr);
t1 += Total;
}
GrandTotal(t1, t2, t3, t4, t5, t6);
}
