public function actionCekBarcode()
{
if ($_POST['cekharga'] && isset($_POST['barcode'])) {
$barang = Barang::model()->find('barcode=:barcode', array(':barcode' => $_POST['barcode']));
$return = ['sukses' => false, 'error' => ['code' => 500, 'msg' => 'Barang tidak ditemukan']];
if (!is_null($barang)) {
$return = ['sukses' => true, 'barcode' => $barang->barcode, 'nama' => $barang->nama, 'harga' => $barang->getHargaJual()];
}
$this->renderJSON($return);
}
}
public function inputBarangKeCetak()
{
if (!empty($this->barcode)) {
$barang = Barang::model()->find('barcode=:barcode', array(':barcode' => $this->barcode));
if (!is_null($barang)) {
$label = new LabelRakCetak();
$label->barang_id = $barang->id;
try {
$label->save();
return 1;
} catch (Exception $exc) {
return 0;
}
}
}
if (!empty($this->profilId || !empty($this->rakId) || !empty($this->dari))) {
$sqlProfil = '';
$sqlRak = '';
$sqlDari = '';
if (!empty($this->profilId)) {
$sqlProfil = "JOIN supplier_barang sp ON barang.id = sp.barang_id AND sp.supplier_id = :supplierId";
}
if (!empty($this->rakId)) {
$sqlRak = "WHERE rak_id = :rakId";
}
if (!empty($this->dari)) {
$sqlDari = "JOIN\n barang_harga_jual bhj ON barang.id = bhj.barang_id\n AND bhj.updated_at >= :dari";
}
/* Menambahkan barang yang belum ada di tabel label_rak_cetak */
$tabelCetak = LabelRakCetak::model()->tableName();
$userId = Yii::app()->user->id;
$sql = "INSERT IGNORE INTO {$tabelCetak} (barang_id, updated_by)\n SELECT \n barang.id, {$userId}\n FROM\n barang\n {$sqlDari}\n {$sqlProfil}\n {$sqlRak}";
$command = Yii::app()->db->createCommand($sql);
if (!empty($this->profilId)) {
$command->bindValue(':supplierId', $this->profilId);
}
if (!empty($this->rakId)) {
$command->bindValue(':rakId', $this->rakId);
}
if (!empty($this->dari)) {
$command->bindValue(':dari', date_format(date_create_from_format('d-m-Y H:i', $this->dari), 'Y-m-d H:i:s'));
}
return $command->execute();
}
}
public function reportKartuStok()
{
if (!empty($this->barcode)) {
$barang = Barang::model()->find('barcode=:barcode', [':barcode' => $this->barcode]);
if (!is_null($barang)) {
$this->barangId = $barang->id;
}
}
$dari = date_format(date_create_from_format('d-m-Y', $this->dari), 'Y-m-d');
$sampai = date_format(date_create_from_format('d-m-Y', $this->sampai), 'Y-m-d');
$tempTableName = $this->tempTableName();
$sqlSelect = "\n SELECT \n *\n FROM\n (SELECT \n sd.id,\n :kodeSo kode,\n (sd.qty_sebenarnya - sd.qty_tercatat) qty,\n 0 harga_beli,\n so.nomor,\n so.tanggal\n FROM\n stock_opname_detail sd\n JOIN stock_opname so ON sd.stock_opname_id = so.id AND DATE_FORMAT(so.tanggal, '%Y-%m-%d') BETWEEN :dari AND :sampai\n WHERE\n sd.barang_id = :barangId\n AND sd.qty_sebenarnya != sd.qty_tercatat UNION SELECT \n pd.id,\n :kodePembelian kode,\n pd.qty,\n pd.harga_beli,\n pembelian.nomor,\n pembelian.tanggal\n FROM\n pembelian_detail pd\n JOIN pembelian ON pd.pembelian_id = pembelian.id AND DATE_FORMAT(pembelian.tanggal, '%Y-%m-%d') BETWEEN :dari AND :sampai\n WHERE\n pd.barang_id = :barangId UNION SELECT \n rd.id, :kodeReturPembelian kode, rd.qty, ib.harga_beli, retur.nomor, retur.tanggal\n FROM\n retur_pembelian_detail rd\n JOIN inventory_balance ib ON rd.inventory_balance_id = ib.id\n AND ib.barang_id = :barangId\n JOIN retur_pembelian retur ON rd.retur_pembelian_id = retur.id \n AND DATE_FORMAT(retur.tanggal, '%Y-%m-%d') BETWEEN :dari AND :sampai\n UNION SELECT \n hpp.id,\n :kodePenjualan tipe,\n hpp.qty,\n hpp.harga_beli,\n penjualan.nomor,\n penjualan.tanggal\n FROM\n harga_pokok_penjualan hpp\n JOIN penjualan_detail pd ON hpp.penjualan_detail_id = pd.id\n AND pd.barang_id = :barangId\n JOIN penjualan ON pd.penjualan_id = penjualan.id \n AND DATE_FORMAT(penjualan.tanggal, '%Y-%m-%d') BETWEEN :dari AND :sampai\n UNION SELECT \n rd.id, :kodeReturPenjualan tipe, rd.qty, 0 harga_beli, retur.nomor, retur.tanggal\n FROM\n retur_penjualan_detail rd\n JOIN penjualan_detail pd ON rd.penjualan_detail_id = pd.id\n AND pd.barang_id = :barangId\n JOIN retur_penjualan retur ON rd.retur_penjualan_id = retur.id\n AND DATE_FORMAT(retur.tanggal, '%Y-%m-%d') BETWEEN :dari AND :sampai) t1\n ORDER BY tanggal \n ";
$sql = " \n CREATE TEMPORARY TABLE IF NOT EXISTS \n {$tempTableName} (\n `local_id` int(10) UNSIGNED DEFAULT NULL,\n `tipe` varchar(45) DEFAULT NULL,\n `qty` int(11) DEFAULT NULL,\n `harga_beli` decimal(18,2) DEFAULT NULL,\n `nomor` varchar(45) DEFAULT NULL,\n `tanggal` datetime DEFAULT NULL)\n ENGINE=MEMORY \n AS (\n {$sqlSelect}\n ) ";
Yii::app()->db->createCommand("DROP TEMPORARY TABLE IF EXISTS {$tempTableName}")->execute();
$command = Yii::app()->db->createCommand($sql);
$command->execute([':barangId' => $this->barangId, ':kodeSo' => KodeDokumen::SO, ':kodePembelian' => KodeDokumen::PEMBELIAN, ':kodeReturPembelian' => KodeDokumen::RETUR_PEMBELIAN, ':kodePenjualan' => KodeDokumen::PENJUALAN, ':kodeReturPenjualan' => KodeDokumen::RETUR_PENJUALAN, ':dari' => $dari, ':sampai' => $sampai]);
$com = Yii::app()->db->createCommand()->from($tempTableName);
$comBalance = Yii::app()->db->createCommand("\n SELECT \n SUM(qty) total\n FROM\n (SELECT \n 5 tipe, SUM(sd.qty_sebenarnya - sd.qty_tercatat) qty\n FROM\n stock_opname_detail sd\n JOIN stock_opname so ON sd.stock_opname_id = so.id AND DATE_FORMAT(so.tanggal, '%Y-%m-%d') < :dari\n WHERE\n sd.barang_id = :barangId\n AND sd.qty_sebenarnya != sd.qty_tercatat UNION SELECT \n 1 tipe, SUM(pd.qty)\n FROM\n pembelian_detail pd\n JOIN pembelian ON pd.pembelian_id = pembelian.id AND DATE_FORMAT(pembelian.tanggal, '%Y-%m-%d') < :dari\n WHERE\n pd.barang_id = :barangId UNION SELECT \n 2 tipe, 0-SUM(rd.qty) qty\n FROM\n retur_pembelian_detail rd\n JOIN inventory_balance ib ON rd.inventory_balance_id = ib.id\n AND ib.barang_id = :barangId\n JOIN retur_pembelian retur ON rd.retur_pembelian_id = retur.id \n AND DATE_FORMAT(retur.tanggal, '%Y-%m-%d') < :dari\n UNION SELECT \n 3 tipe, 0-SUM(hpp.qty) qty\n FROM\n harga_pokok_penjualan hpp\n JOIN penjualan_detail pd ON hpp.penjualan_detail_id = pd.id\n AND pd.barang_id = :barangId\n JOIN penjualan ON pd.penjualan_id = penjualan.id \n AND DATE_FORMAT(penjualan.tanggal, '%Y-%m-%d') < :dari\n UNION SELECT \n 4 tipe, SUM(rd.qty) qty\n FROM\n retur_penjualan_detail rd\n JOIN penjualan_detail pd ON rd.penjualan_detail_id = pd.id\n AND pd.barang_id = :barangId\n JOIN retur_penjualan retur ON rd.retur_penjualan_id = retur.id\n AND DATE_FORMAT(retur.tanggal, '%Y-%m-%d') < :dari) AS t1\n ")->queryRow(true, [':dari' => $dari, 'barangId' => $this->barangId]);
$report = ['balance' => $comBalance['total'], 'detail' => $com->queryAll()];
return $report;
}
/**
* Returns the data model based on the primary key given in the GET variable.
* If the data model is not found, an HTTP exception will be raised.
* @param integer $id the ID of the model to be loaded
* @return Barang the loaded model
* @throws CHttpException
*/
public function loadModel($id)
{
$model = Barang::model()->findByPk($id);
if ($model === null) {
throw new CHttpException(404, 'The requested page does not exist.');
}
return $model;
}
public function gantiCustomer($customer)
{
$transaction = $this->dbConnection->beginTransaction();
try {
if (!$this->saveAttributes(array('profil_id' => $customer->id))) {
throw new Exception('Gagal ubah customer', 500);
}
$alamat1 = !empty($customer->alamat1) ? $customer->alamat1 : '';
$alamat2 = !empty($customer->alamat2) ? '<br>' . $customer->alamat2 : '';
$alamat3 = !empty($customer->alamat3) ? '<br>' . $customer->alamat3 : '';
/* Ambil data detail */
$penjualanDetails = PenjualanDetail::model()->findAll('penjualan_id=:penjualanId', array('penjualanId' => $this->id));
/* Hapus dan re-insert */
$tabelPenjualanDiskon = PenjualanDiskon::model()->tableName();
$tabelPenjualanDetail = PenjualanDetail::model()->tableName();
Yii::app()->db->createCommand("\n DELETE {$tabelPenjualanDiskon}\n FROM {$tabelPenjualanDiskon}\n INNER JOIN {$tabelPenjualanDetail} ON {$tabelPenjualanDiskon}.penjualan_detail_id = {$tabelPenjualanDetail}.id\n WHERE {$tabelPenjualanDetail}.penjualan_id=:penjualanId\n ")->bindValues(array(':penjualanId' => $this->id))->execute();
PenjualanDetail::model()->deleteAll('penjualan_id=:penjualanId', array('penjualanId' => $this->id));
foreach ($penjualanDetails as $detail) {
$barang = Barang::model()->findByPk($detail->barang_id);
$this->tambahBarangProc($barang, $detail->qty);
}
$transaction->commit();
return array('sukses' => true, 'nama' => $customer->nama, 'nomor' => $customer->nomor, 'address' => $alamat1 . $alamat2 . $alamat3);
} catch (Exception $ex) {
$transaction->rollback();
return array('sukses' => false, 'error' => array('msg' => $ex->getMessage(), 'code' => $ex->getCode()));
}
}
/**
* Cari Barang untuk autocomplete.
* @param int $profilId Profil ID
* @param text $term Text yang akan di cari
*/
public function actionCariBarang($profilId, $term)
{
$q = new CDbCriteria();
$q->join = 'JOIN supplier_barang sp ON sp.barang_id = t.id';
$q->addCondition("concat(barcode, nama) like :term");
$q->addCondition("sp.supplier_id=:profilId");
$q->order = 'nama';
$q->params = [':term' => "%{$term}%", ':profilId' => $profilId];
$barangs = Barang::model()->aktif()->findAll($q);
$r = array();
foreach ($barangs as $barang) {
$r[] = array('label' => $barang->nama, 'value' => $barang->barcode);
}
$this->renderJSON($r);
}
public function actionCariBarang($term)
{
$q = new CDbCriteria();
$q->addCondition("barcode like :term OR nama like :term");
$q->order = 'nama';
$q->params = [':term' => "%{$term}%"];
$barangs = Barang::model()->findAll($q);
$r = array();
foreach ($barangs as $barang) {
$r[] = array('label' => $barang->nama, 'value' => $barang->barcode, 'id' => $barang->id, 'stok' => is_null($barang->stok) ? 'null' : $barang->stok, 'harga' => $barang->hargaJual);
}
$this->renderJSON($r);
}
public function ambilBarangNamaPerSupplier($supplierId)
{
return Yii::app()->db->createCommand()->select('b.id, b.nama, b.barcode')->from($this->tableName() . ' sb')->join(Barang::model()->tableName() . ' b', 'b.id=sb.barang_id')->where('supplier_id=:supplierId and b.status=1', array(':supplierId' => $supplierId))->order('b.nama')->queryAll();
}
<label>Obat</label>
<table class="table table-bordered">
<thead>
<th>No.</th>
<th>Nama Obat</th>
<th>Harga</th>
<th>Diskon</th>
<th>Jumlah</th>
<th></th>
</thead>
<tbody>
<tr ng-repeat="obat in obatCollection">
<td>{{$index+1}}</td>
<td width="60%">
<?php
$obat = Barang::model()->findAll();
?>
<select ng-change="updateObat($index, obat.id_obat, obat.jumlah)" ng-model="obat.id_obat">
<option value="">--- Pilih ---</option>
<?php
for ($i = 0; $i < count($obat); $i++) {
echo '<option value="' . $obat[$i]['id_barang'] . '">' . $obat[$i]['nama_barang'] . ' - Rp. ' . number_format($obat[$i]['harga_jual'], 2, ',', '.') . '</option>';
}
?>
</select>
</td>
<td>
{{obat.harga}}
</td>
<td>
{{obat.diskon}}
public function dataForm()
{
$cr = new CDbCriteria();
$cr->condition = 'rak_id = :rakId';
$cr->params = [':rakId' => $this->rakId];
if ($this->kategoriId > 0) {
$cr->addCondition('kategori_id = :kategoriId');
$cr->params = [':kategoriId' => $this->kategoriId, ':rakId' => $this->rakId];
}
switch ($this->sortBy) {
case self::SORT_BY_NAMA_ASC:
$cr->order = 'nama';
break;
case self::SORT_BY_NAMA_DSC:
$cr->order = 'nama desc';
break;
case self::SORT_BY_BARCODE_ASC:
$cr->order = 'barcode';
break;
case self::SORT_BY_BARCODE_DSC:
$cr->order = 'barcode desc';
break;
}
return Barang::model()->findAll($cr);
}
public function actionSetInAktif($id)
{
$return = array('sukses' => false);
if (isset($_POST['barangid'])) {
$pk = $_POST['barangid'];
$barang = Barang::model()->findByPk($pk);
Barang::model()->updateByPk($pk, array('status' => Barang::STATUS_TIDAK_AKTIF));
$return = array('sukses' => true);
}
$this->renderJSON($return);
}
/**
* Proses simpan Stock Opname.
* 1. Update status dari draft menjadi so
* 2. Update inventory terkait dengan cara FIFO
* a. Pengurangan, dilakukan seperti penjualan
* b. Penambahan, dilakukan pada current inventory, terus ke inv sebelumnya
* @return boolean True jika sukses
* @throws Exception
*/
public function simpanSo()
{
$this->scenario = 'simpanSo';
$transaction = $this->dbConnection->beginTransaction();
try {
if ($this->save()) {
$details = StockOpnameDetail::model()->findAll('stock_opname_id=' . $this->id);
foreach ($details as $detail) {
InventoryBalance::model()->so($this, $detail);
if (!is_null($this->rak_id)) {
Barang::model()->updateByPk($detail->barang_id, array('rak_id' => $this->rak_id));
}
}
$transaction->commit();
return array('sukses' => true);
} else {
throw new Exception("Gagal Simpan Stock Opname");
}
} catch (Exception $ex) {
$transaction->rollback();
return array('sukses' => false, 'error' => array('msg' => $ex->getMessage(), 'code' => $ex->getCode()));
}
}
public function actionCariBarang($profilId, $term)
{
$q = new CDbCriteria();
$q->addCondition("barcode like :term OR nama like :term");
$q->order = 'nama';
$q->join = "JOIN supplier_barang ON barang_id = t.id AND supplier_id = {$profilId}";
$q->params = [':term' => "%{$term}%"];
$barangs = Barang::model()->findAll($q);
$r = array();
foreach ($barangs as $barang) {
$r[] = array('label' => $barang->nama, 'value' => $barang->barcode, 'id' => $barang->id);
}
$this->renderJSON($r);
}
public function simpanCsvKePembelian()
{
$csvFileName = $this->csvFile->name;
$namaFile = explode('-', $csvFileName);
$refNo = $namaFile[0];
$refTgl = "{$namaFile[4]}-{$namaFile[3]}-{$namaFile[2]}";
$profilId = $this->profilId;
$transaction = Yii::app()->db->beginTransaction();
$pembelian = new Pembelian();
$pembelian->profil_id = $profilId;
$pembelian->referensi = $refNo;
$pembelian->tanggal_referensi = $refTgl;
try {
if ($pembelian->save()) {
$fp = fopen($this->csvFile->tempName, 'r');
if ($fp) {
$line = fgetcsv($fp, 1000, ",");
// print_r($line); exit;
do {
if ($line[0] == 'barcode') {
continue;
}
/* field csv
* "barcode","idBarang","namaBarang","jumBarang","hargaBeli","hargaJual","RRP","SatuanBarang","KategoriBarang","Supplier","kasir"
* 0 1 2 3 4 5 6 7 8 9
*/
$barangAda = Barang::model()->find('barcode=:barcode', array(':barcode' => $line[0]));
$barangId = null;
if (is_null($barangAda)) {
/* Jika belum ada barcode nya, maka buat barang baru */
/* Jika belum ada kategori dan satuan, buat baru */
$kategoriAda = KategoriBarang::model()->find('nama=:nama', array(':nama' => $line[8]));
if (is_null($kategoriAda)) {
$kategoriBaru = new KategoriBarang();
$kategoriBaru->nama = $line[8];
if (!$kategoriBaru->save()) {
throw new Exception("Gagal simpan kategori baru", 500);
}
$kategoriId = $kategoriBaru->id;
} else {
$kategoriId = $kategoriAda->id;
}
$satuanAda = SatuanBarang::model()->find('nama=:nama', array(':nama' => $line[7]));
if (is_null($satuanAda)) {
$satuanBaru = new SatuanBarang();
$satuanBaru->nama = $line[7];
if (!$satuanBaru->save()) {
throw new Exception("Gagal simpan satuan baru", 500);
}
$satuanId = $satuanBaru->id;
} else {
$satuanId = $satuanAda->id;
}
$barangBaru = new Barang();
$barangBaru->barcode = $line[0];
$barangBaru->nama = $line[2];
$barangBaru->kategori_id = $kategoriId;
$barangBaru->satuan_id = $satuanId;
if (!$barangBaru->save()) {
throw new Exception("Gagal simpan barang baru", 500);
}
$barangId = $barangBaru->id;
/* Jadikan supplier default ke profil ini */
$supplierBarang = new SupplierBarang();
$supplierBarang->barang_id = $barangId;
$supplierBarang->supplier_id = $profilId;
$supplierBarang->default = SupplierBarang::SUPPLIER_DEFAULT;
if (!$supplierBarang->save()) {
throw new Exception("Gagal simpan Supplier Barang", 500);
}
} else {
$barangId = $barangAda->id;
}
$detail = new PembelianDetail();
$detail->pembelian_id = $pembelian->id;
$detail->barang_id = $barangId;
$detail->qty = $line[3];
$detail->harga_beli = $line[5];
$detail->harga_jual = $line[6];
if (!$detail->save()) {
throw new Exception('Gagal simpan detail pembelian', 500);
}
} while (($line = fgetcsv($fp, 2000)) != FALSE);
}
$transaction->commit();
return array('sukses' => true, 'pembelianId' => $pembelian->id);
} else {
throw new Exception("Gagal Simpan Pembelian");
}
} catch (Exception $ex) {
$transaction->rollback();
return array('sukses' => false, 'error' => array('msg' => $ex->getMessage(), 'code' => $ex->getCode()));
}
}
public function loadModelBarang($id)
{
$model = Barang::model()->findByPk($id);
return $model;
}
public function actionCariBarang($term)
{
$arrTerm = explode(' ', $term);
$wBarcode = '(';
$wNama = '(';
$pBarcode = array();
$param = array();
$firstRow = true;
$i = 1;
foreach ($arrTerm as $bTerm) {
if (!$firstRow) {
$wBarcode .= ' AND ';
$wNama .= ' AND ';
}
$wBarcode .= "barcode like :term{$i}";
$wNama .= "nama like :term{$i}";
$param[":term{$i}"] = "%{$bTerm}%";
$firstRow = FALSE;
$i++;
}
$wBarcode .= ')';
$wNama .= ')';
// echo $wBarcode.' AND '.$wNama;
// print_r($param);
$q = new CDbCriteria();
$q->addCondition("{$wBarcode} OR {$wNama}");
$q->params = $param;
$barangs = Barang::model()->findAll($q);
$r = array();
foreach ($barangs as $barang) {
$r[] = array('label' => $barang->nama, 'value' => $barang->barcode, 'stok' => is_null($barang->stok) ? 'null' : $barang->stok, 'harga' => $barang->hargaJual);
}
$this->renderJSON($r);
}
