public function endDepBalance($dates)
{
$stuff = new Halfstaff();
//Количественный расчет по отделам
$department = Department::model()->findAll();
foreach ($department as $v) {
$depId = $v->department_id;
$depIn = array();
$depOut = array();
$inProduct = array();
$instuff = array();
$endProduct = array();
$endStuff = array();
$outProduct = array();
$outStuff = array();
$outStuffProd = array();
$prodModel = Products::model()->findAll();
foreach ($prodModel as $value) {
$outProduct[$value->product_id] = 0;
}
//расход продуктов в другой отдел
$departMoveOut = DepFaktura::model()->with('realizedProd')->findAll('date(t.real_date) = :dates AND t.department_id != :depId AND t.fromDepId = :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => $depId));
foreach ($departMoveOut as $key => $val) {
foreach ($val->getRelated('realizedProd') as $value) {
$depOut[$value->prod_id] = $depOut[$value->prod_id] + $value->count;
}
}
//приход продуктов из других отделов
$departMoveIn = DepFaktura::model()->with('realizedProd')->findAll('date(t.real_date) = :dates AND t.department_id = :depId AND t.fromDepId != :fromDepId ', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => 0));
foreach ($departMoveIn as $value) {
foreach ($value->getRelated('realizedProd') as $val) {
$depIn[$val->prod_id] = $depIn[$val->prod_id] + $val->count;
}
}
$dish = new Expense();
$outProduct = $dish->getDishProd($depId, $dates);
$outDishStuff = $dish->getDishStuff($depId, $dates);
$model = DepFaktura::model()->with('realizedProd')->findAll('date(t.real_date) = :dates AND t.department_id = :depId AND t.fromDepId = :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => 0));
foreach ($model as $key => $val) {
foreach ($val->getRelated('realizedProd') as $value) {
$inProduct[$value->prod_id] = $inProduct[$value->prod_id] + $value->count;
}
}
//Приход загатовок в отдел и расход их продуктов
$models2 = Inexpense::model()->with('inorder.stuffs.stuffStruct')->findAll('date(t.inexp_date) = :dates AND t.department_id = :depId AND stuffStruct.types = :types AND t.fromDepId = :fromDepId', array(':dates' => $dates, 'depId' => $depId, ':types' => 1, ':fromDepId' => 0));
foreach ($models2 as $val) {
foreach ($val->getRelated('inorder') as $value) {
$instuff[$value->stuff_id] = $instuff[$value->stuff_id] + $value->count;
foreach ($value->getRelated('stuffs')->getRelated('stuffStruct') as $values) {
$outStuffProd[$values->prod_id] = $outStuffProd[$values->prod_id] + $values->amount / $value->getRelated('stuffs')->count * $value->count;
}
}
}
//Приход и расход загатовок в отдел, расход их продуктов
$model3 = Inexpense::model()->with('inorder.stuffs.stuffStruct.podstuff.podstuffStruct.Struct')->findAll('date(t.inexp_date) = :dates AND t.department_id = :depId AND stuffStruct.types = :types AND t.fromDepId = :fromDepId', array(':dates' => $dates, 'depId' => $depId, ':types' => 2, ':fromDepId' => 0));
foreach ($model3 as $val) {
foreach ($val->getRelated('inorder') as $value) {
$instuff[$value->stuff_id] = $instuff[$value->stuff_id] + $value->count;
foreach ($value->getRelated('stuffs')->getRelated('stuffStruct') as $values) {
$outStuff[$values->prod_id] = $outStuff[$values->prod_id] + $values->amount / $value->getRelated('stuffs')->count * $value->count;
foreach ($values->getRelated('podstuff')->getRelated('podstuffStruct') as $vals) {
$outStuffProd[$values->prod_id] = $outStuffProd[$values->prod_id] + $vals->amount / $values->getRelated('podstuff')->count * $values->amount / $value->getRelated('stuffs')->count * $value->count;
}
}
}
}
$outStuff = $stuff->sumArray($outDishStuff, $outStuff);
$inexpense = new Inexpense();
$depStuffIn = $inexpense->getDepIn($depId, $dates);
$depStuffOut = $inexpense->getDepOut($depId, $dates);
$curProd = DepBalance::model()->with('products')->findAll('t.b_date = :dates AND t.department_id = :depId AND t.type = :type', array(':dates' => $dates, ':depId' => $depId, ':type' => 1));
foreach ($curProd as $value) {
//echo 'id -> '.$value->prod_id.' '.$value->getRelated('products')->name.'|| Начальный =>'.$value->startCount.'|| Приход =>'.$inProduct[$value->prod_id].'|| Расход =>'.$outProduct[$value->prod_id].'|| перемещ in =>'.$depIn[$value->prod_id].'|| перемещ out =>'.$depOut[$value->prod_id]."<br />";
$endProduct[$value->prod_id] = +($value->startCount + $inProduct[$value->prod_id] - $outProduct[$value->prod_id] - $outStuffProd[$value->prod_id] + $depIn[$value->prod_id] - $depOut[$value->prod_id]);
$Models = DepBalance::model()->find('prod_id = :prod_id AND department_id = :depId AND b_date = :dates AND t.type = :type', array(':prod_id' => $value->prod_id, ':depId' => $v->department_id, ':dates' => $dates, ':type' => 1));
$Models->endCount = $endProduct[$value->prod_id];
$Models->update(array('endCount'));
}
$curStuff = DepBalance::model()->findAll('t.b_date = :dates AND t.department_id = :depId AND t.type = :type', array(':dates' => $dates, ':depId' => $depId, ':type' => 2));
foreach ($curStuff as $value) {
$endStuff[$value->prod_id] = $value->startCount + $instuff[$value->prod_id] + $depStuffIn[$value->prod_id] - $outStuff[$value->prod_id] - $depStuffOut[$value->prod_id];
$Models = DepBalance::model()->find('prod_id = :prod_id AND department_id = :depId AND b_date = :dates AND t.type = :type', array(':prod_id' => $value->prod_id, ':depId' => $v->department_id, ':dates' => $dates, ':type' => 2));
$Models->endCount = $endStuff[$value->prod_id];
$Models->update(array('endCount'));
}
}
//конец
}
public function actionAjaxDetail($depId, $key, $dates)
{
$model = array();
$model2 = array();
$model3 = array();
$expense = new Expense();
$prod = new Products();
$stuff = new Halfstaff();
$measure = new Measurement();
if ($key == 'begin') {
$model = Yii::app()->db->createCommand()->select('db.startCount as count,db.prod_id,p.name as name,m.name as Mname')->from('dep_balance db')->join('products p', 'p.product_id = db.prod_id')->join('measurement m', 'm.measure_id = p.measure_id')->where('db.b_date = :dates AND db.department_id = :depId AND db.type = :types', array(':dates' => $dates, ':depId' => $depId, ':types' => 1))->queryAll();
$model2 = Yii::app()->db->createCommand()->select('db.startCount as count,db.prod_id,h.name as name, m.name as Mname')->from('dep_balance db')->join('halfstaff h', 'h.halfstuff_id = db.prod_id')->join('measurement m', 'm.measure_id = h.stuff_type')->where('db.b_date = :dates AND db.department_id = :depId AND db.type = :types', array(':dates' => $dates, ':depId' => $depId, ':types' => 2))->queryAll();
}
if ($key == 'end') {
$model = Yii::app()->db->createCommand()->select('db.endCount as count,db.prod_id,p.name as name,m.name as Mname')->from('dep_balance db')->join('products p', 'p.product_id = db.prod_id')->join('measurement m', 'm.measure_id = p.measure_id')->where('db.b_date = :dates AND db.department_id = :depId AND db.type = :types', array(':dates' => $dates, ':depId' => $depId, ':types' => 1))->queryAll();
$model2 = Yii::app()->db->createCommand()->select('db.endCount as count,db.prod_id,h.name as name, m.name as Mname')->from('dep_balance db')->join('halfstaff h', 'h.halfstuff_id = db.prod_id')->join('measurement m', 'm.measure_id = h.stuff_type')->where('db.b_date = :dates AND db.department_id = :depId AND db.type = :types', array(':dates' => $dates, ':depId' => $depId, ':types' => 2))->queryAll();
}
if ($key == 'curEnd') {
$model = Yii::app()->db->createCommand()->select('db.CurEndCount as count,db.prod_id,p.name as name,m.name as Mname')->from('dep_balance db')->join('products p', 'p.product_id = db.prod_id')->join('measurement m', 'm.measure_id = p.measure_id')->where('db.b_date = :dates AND db.department_id = :depId AND db.type = :types', array(':dates' => $dates, ':depId' => $depId, ':types' => 1))->queryAll();
$model2 = Yii::app()->db->createCommand()->select('db.CurEndCount as count,db.prod_id,h.name as name, m.name as Mname')->from('dep_balance db')->join('halfstaff h', 'h.halfstuff_id = db.prod_id')->join('measurement m', 'm.measure_id = h.stuff_type')->where('db.b_date = :dates AND db.department_id = :depId AND db.type = :types', array(':dates' => $dates, ':depId' => $depId, ':types' => 2))->queryAll();
}
if ($key == 'realize') {
$model = Yii::app()->db->createCommand()->select('dr.count as count,dr.prod_id,p.name as name,m.name as Mname')->from('dep_faktura df')->join('dep_realize dr', 'dr.dep_faktura_id = df.dep_faktura_id')->join('products p', 'p.product_id = dr.prod_id')->join('measurement m', 'm.measure_id = p.measure_id')->where('date(df.real_date) = :dates AND df.department_id = :depId AND df.fromDepId = :fromDepId ', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => 0))->queryAll();
}
if ($key == 'price') {
$model = Yii::app()->db->createCommand()->select('sum(ord.count) as count,ord.just_id as prod_id,p.name as name,ex.mType,ord.type')->from('expense ex')->join('orders ord', 'ord.expense_id = ex.expense_id')->join('products p', 'p.product_id = ord.just_id')->where('date(ex.order_date) = :dates AND p.department_id = :DepId AND ord.type = :type', array(':dates' => $dates, ':DepId' => $depId, ':type' => 3))->group('ord.just_id')->queryAll();
$model2 = Yii::app()->db->createCommand()->select('sum(ord.count) as count,ord.just_id as prod_id,h.name as name,ex.mType,ord.type')->from('expense ex')->join('orders ord', 'ord.expense_id = ex.expense_id')->join('halfstaff h', 'h.halfstuff_id = ord.just_id')->where('date(ex.order_date) = :dates AND h.department_id = :DepId AND ord.type = :type', array(':dates' => $dates, ':DepId' => $depId, ':type' => 2))->group('ord.just_id')->queryAll();
$model3 = Yii::app()->db->createCommand()->select('sum(ord.count) as count,ord.just_id as prod_id,d.name as name,ex.mType,ord.type')->from('expense ex')->join('orders ord', 'ord.expense_id = ex.expense_id')->join('dishes d', 'd.dish_id = ord.just_id')->where('date(ex.order_date) = :dates AND d.department_id = :DepId AND ord.type = :type', array(':dates' => $dates, ':DepId' => $depId, ':type' => 1))->group('ord.just_id')->queryAll();
}
if ($key == 'other') {
$model = Yii::app()->db->createCommand()->select('sum(ord.count) as count,ord.just_id as prod_id,p.name as name')->from('expense ex')->join('orders ord', 'ord.expense_id = ex.expense_id')->join('products p', 'p.product_id = ord.just_id')->where('date(ex.order_date) = :dates AND ex.kind = :kind AND ord.type = :type', array(':dates' => $dates, ':kind' => 1, ':type' => 3))->group('ord.just_id')->queryAll();
}
if ($key == 'costPrice') {
$temp = $expense->getDishProd($depId, $dates);
$count = 0;
foreach ($temp as $key => $val) {
$model[$count]['count'] = $val;
$model[$count]['prod_id'] = $key;
$model[$count]['name'] = $prod->getName($key);
$model[$count]['Mname'] = $measure->getMeasure($key, 'prod');
$count++;
}
$temp2 = $expense->getDishStuff($depId, $dates);
$count = 0;
foreach ($temp2 as $key => $val) {
$model2[$count]['count'] = $val;
$model2[$count]['prod_id'] = $key;
$model2[$count]['name'] = $stuff->getName($key);
$model2[$count]['Mname'] = $measure->getMeasure($key, 'stuff');
$count++;
}
/*$model = Yii::app()->db->createCommand()
->select('sum(ord.count) as count,ord.just_id as prod_id,p.name as name,ex.mType,ord.type')
->from('expense ex')
->join('orders ord','ord.expense_id = ex.expense_id')
->join('products p','p.product_id = ord.just_id')
->where('date(ex.order_date) = :dates AND p.department_id = :DepId AND ord.type = :type',
array(':dates'=>$dates,':DepId'=>$depId,':type'=>3))
->group('ord.just_id')
->queryAll();
$model2 = Yii::app()->db->createCommand()
->select('sum(ord.count) as count,ord.just_id as prod_id,h.name as name,ex.mType,ord.type')
->from('expense ex')
->join('orders ord','ord.expense_id = ex.expense_id')
->join('halfstaff h','h.halfstuff_id = ord.just_id')
->where('date(ex.order_date) = :dates AND h.department_id = :DepId AND ord.type = :type',
array(':dates'=>$dates,':DepId'=>$depId,':type'=>2))
->group('ord.just_id')
->queryAll();
$model3 = Yii::app()->db->createCommand()
->select('sum(ord.count) as count,ord.just_id as prod_id,d.name as name,ex.mType,ord.type')
->from('expense ex')
->join('orders ord','ord.expense_id = ex.expense_id')
->join('dishes d','d.dish_id = ord.just_id')
->where('date(ex.order_date) = :dates AND d.department_id = :DepId AND ord.type = :type',
array(':dates'=>$dates,':DepId'=>$depId,':type'=>1))
->group('ord.just_id')
->queryAll();*/
}
if ($key == 'inRealize') {
$model = Yii::app()->db->createCommand()->select('dr.count as count,dr.prod_id,p.name as name,m.name as Mname')->from('dep_faktura df')->join('dep_realize dr', 'dr.dep_faktura_id = df.dep_faktura_id')->join('products p', 'p.product_id = dr.prod_id')->join('measurement m', 'm.measure_id = p.measure_id')->where('date(df.real_date) = :dates AND df.department_id = :depId AND df.fromDepId != :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => 0))->queryAll();
$model2 = Yii::app()->db->createCommand()->select('inord.count as count,inord.stuff_id as prod_id,h.name as name,m.name as Mname')->from('inexpense inexp')->join('inorder inord', 'inord.inexpense_id = inexp.inexpense_id')->join('halfstaff h', 'h.halfstuff_id = inord.stuff_id')->join('measurement m', 'm.measure_id = h.stuff_type')->where('date(inexp.inexp_date) = :dates AND inexp.department_id = :depId AND inexp.fromDepId != :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => 0))->queryAll();
}
if ($key == 'inExp') {
$model = Yii::app()->db->createCommand()->select('dr.count as count,dr.prod_id,p.name as name,m.name as Mname')->from('dep_faktura df')->join('dep_realize dr', 'dr.dep_faktura_id = df.dep_faktura_id')->join('products p', 'p.product_id = dr.prod_id')->join('measurement m', 'm.measure_id = p.measure_id')->where('date(df.real_date) = :dates AND df.department_id != :depId AND df.fromDepId = :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => $depId))->queryAll();
$model2 = Yii::app()->db->createCommand()->select('inord.count as count,inord.stuff_id as prod_id,h.name as name,m.name as Mname')->from('inexpense inexp')->join('inorder inord', 'inord.inexpense_id = inexp.inexpense_id')->join('halfstaff h', 'h.halfstuff_id = inord.stuff_id')->join('measurement m', 'm.measure_id = h.stuff_type')->where('date(inexp.inexp_date) = :dates AND inexp.department_id != :depId AND inexp.fromDepId = :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => $depId))->queryAll();
}
$this->renderPartial('ajaxDetail', array('dates' => $dates, 'model' => $model, 'model2' => $model2, 'model3' => $model3, 'key' => $key));
}
public function actionViewStorage()
{
$dates = date("Y-m-d");
$outProduct = array();
$depOut = array();
$depIn = array();
$outStuff = array();
$prodModel = Products::model()->findAll();
foreach ($prodModel as $value) {
$outProduct[$value->product_id] = $outProduct[$value->product_id] + 0;
$depOut[$value->product_id] = $depOut[$value->product_id] + 0;
}
$depId = $_POST['department_id'];
$departMoveOut = DepFaktura::model()->with('realizedProd')->findAll('date(t.real_date) = :dates AND t.department_id = :depId AND t.fromDepId <> :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => 0));
foreach ($departMoveOut as $key => $val) {
foreach ($val->getRelated('realizedProd') as $value) {
$depIn[$value->prod_id] = $depIn[$value->prod_id] + $value->count;
}
}
$departMoveIn = DepFaktura::model()->with('realizedProd')->findAll('date(t.real_date) = :dates AND t.fromDepId = :depId', array(':dates' => $dates, ':depId' => $depId));
foreach ($departMoveIn as $value) {
foreach ($value->getRelated('realizedProd') as $val) {
$depOut[$val->prod_id] = $depOut[$val->prod_id] + $val->count;
}
}
$curProd = DepBalance::model()->with('products')->findAll('date(t.b_date) = :dates AND t.department_id = :department_id AND t.type = :type', array(':dates' => $dates, ':department_id' => $depId, ':type' => 1));
$curStuff = DepBalance::model()->with('stuff')->findAll('date(t.b_date) = :dates AND t.department_id = :department_id AND t.type = :type', array(':dates' => $dates, ':department_id' => $depId, ':type' => 2));
$dish = new Expense();
$stuff = new Halfstaff();
$outProduct = $dish->getDishProd($depId, $dates);
$outDishStuff = $dish->getDishStuff($depId, $dates);
$inProducts = array();
$model = DepFaktura::model()->with('realizedProd')->findAll('date(t.real_date) = :dates AND t.department_id = :depId AND t.fromDepId = :fromDepId', array(':dates' => $dates, ':depId' => $depId, ':fromDepId' => 0));
foreach ($model as $key => $val) {
foreach ($val->getRelated('realizedProd') as $value) {
$inProducts[$value->prod_id] = $inProducts[$value->prod_id] + $value->count;
}
}
$instuff = array();
$outStuffProd = array();
$model2 = Inexpense::model()->with('inorder.stuffs.stuffStruct')->findAll('date(t.inexp_date) = :dates AND t.department_id = :depId AND stuffStruct.types = :types AND t.fromDepId = :fromDepId', array(':dates' => $dates, 'depId' => $depId, ':types' => 1, ':fromDepId' => 0));
foreach ($model2 as $val) {
foreach ($val->getRelated('inorder') as $value) {
$instuff[$value->stuff_id] = $instuff[$value->stuff_id] + $value->count;
foreach ($value->getRelated('stuffs')->getRelated('stuffStruct') as $values) {
$outStuffProd[$values->prod_id] = $outStuffProd[$values->prod_id] + $values->amount / $value->getRelated('stuffs')->count * $value->count;
}
}
}
$model3 = Inexpense::model()->with('inorder.stuffs.stuffStruct.podstuff.podstuffStruct.Struct')->findAll('date(t.inexp_date) = :dates AND t.department_id = :depId AND stuffStruct.types = :types AND t.fromDepId = :fromDepId', array(':dates' => $dates, 'depId' => $depId, ':types' => 2, ':fromDepId' => 0));
foreach ($model3 as $val) {
foreach ($val->getRelated('inorder') as $value) {
$instuff[$value->stuff_id] = $instuff[$value->stuff_id] + $value->count;
foreach ($value->getRelated('stuffs')->getRelated('stuffStruct') as $values) {
$outStuff[$values->prod_id] = $outStuff[$values->prod_id] + $values->amount / $value->getRelated('stuffs')->count * $value->count;
foreach ($values->getRelated('podstuff')->getRelated('podstuffStruct') as $vals) {
$outStuffProd[$values->prod_id] = $outStuffProd[$values->prod_id] + $vals->amount / $values->getRelated('podstuff')->count * $values->amount / $value->getRelated('stuffs')->count * $value->count;
}
}
}
}
$outStuff = $stuff->sumArray($outDishStuff, $outStuff);
$inexpense = new Inexpense();
$depStuffIn = $inexpense->getDepIn($depId, $dates);
$depStuffOut = $inexpense->getDepOut($depId, $dates);
$this->renderPartial('viewStorage', array('depOut' => $depOut, 'depIn' => $depIn, 'depStuffOut' => $depStuffOut, 'depStuffIn' => $depStuffIn, 'prodModel' => $prodModel, 'curProd' => $curProd, 'curStuff' => $curStuff, 'inProduct' => $inProducts, 'inStuff' => $instuff, 'outProduct' => $outProduct, 'outStuff' => $outStuff, 'outStuffProd' => $outStuffProd));
}
public function actionOut()
{
$dates = $_POST['dates'];
$outProduct = array();
$dish = new Expense();
$stuff = new Halfstaff();
foreach (Department::model()->findAll() as $val) {
$outProduct = $stuff->sumArray($outProduct, $dish->getDishProd($val->department_id, $dates));
$outDishStuff = $dish->getDishStuff($val->department_id, $dates);
}
$Products = Products::model()->findAll();
$this->renderPartial('out', array('outProduct' => $outProduct, 'product' => $Products, 'dates' => $dates));
}
