Renumbering Functionality for Bill Management
Renumbering functionality is crucial for maintaining the integrity of bill numbering systems in various scenarios. It serves two primary purposes:
Rectifying Consecutive Number Breaks: If a user deletes a bill, it can disrupt the consecutive numbering sequence, necessitating renumbering to fill the gap.
Starting Fresh for a Financial Year: At the beginning of a new financial year, it’s often necessary to reset bill numbering, typically starting from 1.
While the renumbering process generally works well, there is a specific case where it fails: renumbering bills based on the order date. This scenario presents challenges that need to be addressed in the renumbering logic.
Below is a sample code snippet demonstrating the renumbering process, with emphasis on the S_SalesBill case. Discussion and refinement of this code can provide insights into resolving the issue encountered during renumbering based on the bill order date.
public function actionRenumber()
{
$prefix = Prefixes::find()->one(); // prefix for bill_number
$toRenumber = $_POST['slug']; // model name for renumber
$postDate = $_POST['date']; // date for filterout the records
$date = DateTime::createFromFormat('d-m-Y', $postDate)->format('Y-m-d');
$dateObj = DateTime::createFromFormat('d-m-Y', $postDate);
$year = $dateObj->format('Y');
$smallY = $dateObj->format('y');
$month = $dateObj->format('m');
if ($month >= 4) {
// If the month is April or later, financial year end is March 31st of the next year
$financialYearEnd = date_create(($year + 1) . '-03-31')->format('Y-m-d');
$renum_fin_year = $smallY.'-'.( $smallY+1);
} else {
// If the month is before April, financial year end is March 31st of the current year
$financialYearEnd = date_create($year . '-03-31')->format('Y-m-d');
$renum_fin_year = ($smallY-1).'-'.( $smallY);
}
$current_month = date('n');
$current_year = date('y');
// Calculate the financial year based on the current date
if ($current_month >= 4) {
// If the current month is April or later, financial year starts from the current year
$current_fin_year = $current_year . '-' . ($current_year + 1);
} else {
// If the current month is before April, financial year starts from the previous year
$current_fin_year = ($current_year - 1) . '-' . $current_year;
}
$outPut = array(); // log / response purpoose
$outPut['key'] = $toRenumber;
$outPut['date'] = $date;
$lastNum = $_POST['lastNum']; // lastNum passed as array of counterkey=>lastnumber
$inspect = array(); // inspect array is used for inspecting the flow of working.every step inserted to this array so this can see in response tab
// getting the source counter data to renumber
switch($toRenumber){
case "S_SalesBill":
$source = SalesBill::find()->select('s_gold_counter,s_silver_counter')
->andFilterWhere(['>=', 'created_date', $date])
->andFilterWhere(['<=', 'created_date', $financialYearEnd])
->orderBy('created_date')
->asArray()->all();
break;
case "R_SalesBill":
$source = SalesBill::find()->select('r_gold_counter,r_silver_counter')->andFilterWhere(['>=', 'created_date', $date])->orderBy('id')->asArray()->all();
break;
case"P_Purchase":
$source = PurchaseInvoice::find()->select('p_gold_counter,p_silver_counter')->andFilterWhere(['>=','inv_date',$date])->asArray()->all();
break;
case"P_OldGold":
$source = PurchaseInvoice::find()->select('p_oldGold_counter,p_oldSilver_counter')->andFilterWhere(['>=','inv_date',$date])->asArray()->all();
break;
case "Despatch":
$source = Despatch::find()->select('d_gold_counter')->andFilterWhere(['>=','created_date',$date])->orderBy('id')->asArray()->all();
break;
case"HallMarking":
$source = Despatch::find()->select('d_hallmarking_counter,r_hallmarking_counter')->andFilterWhere(['>=','created_date',$date])->orderBy('id')->asArray()->all();
break;
case "DespatchReturn":
$source = Despatch::find()->select('r_gold_counter')->andFilterWhere(['>=','created_date',$date])->orderBy('id')->asArray()->all();
break;
}
$inspect['LASTNUM'] = $_POST['lastNum'];
$inspect['ToRenumber'] = $toRenumber;
$inspect['sources'] = $source;
// arranging the source data in to a sing array $temp
$temp = [];
foreach ($source as $record) {
foreach ($record as $counterKey => $counterValue) {
if (!isset($temp[$counterKey])) {
$temp[$counterKey] = [];
}
if (!empty($counterValue)) {
$temp[$counterKey][] = $counterValue;
}
}
}
// echo "<pre>"; print_r($temp); echo "</pre>"; exit;
// so the temp array look like example
/**
* [s_gold_counter] => Array
* (
* [0] => 1
* [1] => 2
* [2] => 3
* [3] => 649
* )
*/
// here counterKey is s_gold_counter
// check for firstValue
$inspect['loopStart'] = "fromeHere";
$renumbered = array();
$queries = array();
$itration = 1;
foreach ($temp as $counterKey => $counterValues) { // itrating temp array with counter key
$counterValues = array_unique($counterValues);
sort($counterValues); // getting array of value like Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 649 )
$firstVal = ($lastNum[$counterKey] != "") ? $lastNum[$counterKey]+1 : 1; // determine lastnumber
$inspect[] = array('itration'=>$itration);
$inspect[] = array('key'=>$counterKey);
$inspect[] = array('value'=>$counterValues);
$inspect[] = array('firstvalue'=>$firstVal);
foreach ($counterValues as $counterValue) { // itrating each values inside counter key
echo "<b>First condition</b> counterValue- ";
var_dump($counterValue);echo "<br>";
echo "firstVal ";
var_dump($firstVal);
// echo "counterValue- "."<pre>".var_dump($counterValue)."</pre>"."<br>"."firstVal "."<pre>".var_dump($firstVal)."</pre>"."<br>" ;
if ((int)$counterValue !== $firstVal) { // skip in the case of starting are same . checking the lastNumber passed is same as in the values array
echo"true"."<br>";
$inspect[] = array($counterValue.'NotEqualTo'.$firstVal=>true);
echo "<b> second condition</b> counterValue- ";
var_dump($counterValue);echo "<br>";
echo "firstVal ";
var_dump($firstVal);
if ((int)$counterValue !== $firstVal + 1) { // skip in the case no deleted bills next to current bill number
echo"true"."<br>";
$inspect[] = array($counterValue.'NotEqualTo'.($firstVal+1)=>"true");
// echo "counterKey-".$counterKey."<br>"."counterValue".$counterValue ; exit;
switch($toRenumber){
case "S_SalesBill":
$bill = SalesBill::find()
->select(['id','s_gold_counter','bill_no'])
->where([$counterKey => $counterValue])
->andFilterWhere(['>=', 'created_date', $date])
->andFilterWhere(['<=', 'created_date', $financialYearEnd])
->asArray()->one();
echo "<pre>";print_r($bill); echo "</pre>";
// $bill_no = preg_replace('//(d+)/', '/'.$firstVal, $bill['bill_no']);
// $bill_no = preg_replace('/(d+)(?=(?:[^/]*/){0,2}[^/]*$)/', $firstVal, $bill['bill_no']);
$parts = explode('/', $bill['bill_no']);
array_pop($parts);
$parts[] = $firstVal;
$bill_no = implode('/',$parts);
// echo "from-".$bill['bill_no']." -to - " .$bill_no ; exit;
$query = Yii::$app->db->createCommand()->update('sales_bill', [$counterKey => $firstVal, 'bill_no' => $bill_no], ['id' => $bill['id']])->getRawSql();
$queries[] = array('queries' => $query);
// update query
Yii::$app->db->createCommand()->update('sales_bill', [$counterKey => $firstVal, 'bill_no' => $bill_no], ['id' => $bill['id']])->execute();
break;
case "R_SalesBill":
$bill = SalesBill::find()->where([$counterKey => $counterValue])->asArray()->one();
$parts = explode('/', $bill['bill_no']);
array_pop($parts);
$parts[] = $firstVal;
$bill_no = implode('/',$parts);
// $bill_no = preg_replace('//(d+)/', '/'.$firstVal, $bill['bill_no']);
$query = Yii::$app->db->createCommand()->update('sales_bill', [$counterKey => $firstVal, 'bill_no' => $bill_no], [$counterKey => $counterValue])->getRawSql();
$queries[] = array('queries' => $query);
// update query
Yii::$app->db->createCommand()->update('sales_bill', [$counterKey => $firstVal, 'bill_no' => $bill_no], [$counterKey => $counterValue])->execute();
break;
case "P_Purchase":
$bill = PurchaseInvoice::find()->where([$counterKey=>$counterValue])->asArray()->one();
$parts = explode('/', $bill['invoice_no']);
array_pop($parts);
$parts[] = $firstVal;
$bill_no = implode('/',$parts);
// $bill_no = preg_replace('//(d+)/', '/'.$firstVal, $bill['invoice_no']);
$query = Yii::$app->db->createCommand()->update('purchase_invoice',[$counterKey=>$firstVal,'invoice_no'=>$bill_no],[$counterKey =>$counterValue])->getRawSql();
$queries[]= array('queries'=>$query);
// update query
Yii::$app->db->createCommand()->update('purchase_invoice',[$counterKey=>$firstVal,'invoice_no'=>$bill_no],[$counterKey =>$counterValue])->execute();
break;
case "P_OldGold":
$bill = PurchaseInvoice::find()->where([$counterKey=>$counterValue])->asArray()->one();
$parts = explode('/', $bill['invoice_no']);
array_pop($parts);
$parts[] = $firstVal;
$bill_no = implode('/',$parts);
// $bill_no = preg_replace('//(d+)/', '/'.$firstVal, $bill['invoice_no']);
$query = Yii::$app->db->createCommand()->update('purchase_invoice',[$counterKey=>$firstVal,'invoice_no'=>$bill_no],[$counterKey =>$counterValue])->getRawSql();
$queries[]= array('queries'=>$query);
// update query
Yii::$app->db->createCommand()->update('purchase_invoice',[$counterKey=>$firstVal,'invoice_no'=>$bill_no],[$counterKey =>$counterValue])->execute();
break;
case "Despatch":
$bill = Despatch::find()->where([$counterKey=>$counterValue])->asArray()->one();
$parts = explode('/', $bill['despatch_no']);
array_pop($parts);
$parts[] = $firstVal;
$bill_no = implode('/',$parts);
// $bill_no = preg_replace('//(d+)/', '/'.$firstVal, $bill['despatch_no']);
$query = Yii::$app->db->createCommand()->update('despatch',[$counterKey=>$firstVal,'despatch_no'=>$bill_no],[$counterKey =>$counterValue])->getRawSql();
$queries[]= array('queries'=>$query);
// update query
Yii::$app->db->createCommand()->update('despatch',[$counterKey=>$firstVal,'despatch_no'=>$bill_no],[$counterKey =>$counterValue])->execute();
break;
case "DespatchReturn":
$bill = Despatch::find()->where([$counterKey=>$counterValue])->asArray()->one();
$parts = explode('/', $bill['despatch_no']);
array_pop($parts);
$parts[] = $firstVal;
$bill_no = implode('/',$parts);
// $bill_no = preg_replace('//(d+)/', '/'.$firstVal, $bill['despatch_no']);
$query = Yii::$app->db->createCommand()->update('despatch',[$counterKey=>$firstVal,'despatch_no'=>$bill_no],[$counterKey =>$counterValue])->getRawSql();
$queries[]= array('queries'=>$query);
// update query
Yii::$app->db->createCommand()->update('despatch',[$counterKey=>$firstVal,'despatch_no'=>$bill_no],[$counterKey =>$counterValue])->execute();
break;
case"HallMarking":
$bill = Despatch::find()->where([$counterKey=>$counterValue])->asArray()->one();
$parts = explode('/', $bill['despatch_no']);
array_pop($parts);
$parts[] = $firstVal;
$bill_no = implode('/',$parts);
// $bill_no = preg_replace('//(d+)/', '/'.$firstVal, $bill['despatch_no']);
$query = Yii::$app->db->createCommand()->update('despatch',[$counterKey=>$firstVal,'despatch_no'=>$bill_no],[$counterKey =>$counterValue])->getRawSql();
$queries[]= array('queries'=>$query);
// update query
Yii::$app->db->createCommand()->update('despatch',[$counterKey=>$firstVal,'despatch_no'=>$bill_no],[$counterKey =>$counterValue])->execute();
break;
}
}
}
$firstVal += 1;
$renumbered[$counterKey][$counterValue] = $firstVal;
$renumbered[] = array('values' => $counterValue,'firstVal'=>$firstVal);
}
$itration++;
}
switch($toRenumber){
case "S_SalesBill":
if($renum_fin_year == $current_fin_year){
echo "<br> same financial year";
$salesbill = SalesBill::find()
->select([
'COALESCE(MAX(s_gold_counter), 1) as s_gold_counter',
'COALESCE(MAX(s_silver_counter), 1) as s_silver_counter',
])
->andFilterWhere(['>=', 'created_date', $date])
->andFilterWhere(['<=', 'created_date', $financialYearEnd])
->asArray()->one();
echo "<pre>"; print_r($salesbill); echo "</pre>";
$seq_s_gold_counter = SequenceInfo::find()->where(['key'=>"SalesBill_s_gold_counter"])->one();
$seq_s_gold_counter->number = $salesbill['s_gold_counter'];
$seq_s_gold_counter->financial_year = $current_fin_year;
$seq_s_gold_counter->last_updated = date('Y-m-d H:i:s');
$seq_s_gold_counter->detail ="renumbered";
$seq_s_gold_counter->validate();
echo "<pre>"; print_r($seq_s_gold_counter); echo "</pre>";
$seq_s_gold_counter->save();
$seq_s_silver_counter = SequenceInfo::find()->where(['key'=>"SalesBill_s_silver_counter"])->one();
$seq_s_silver_counter->number = $salesbill['s_silver_counter'];
$seq_s_silver_counter->financial_year = $current_fin_year;
$seq_s_silver_counter->last_updated = date('Y-m-d H:i:s');
$seq_s_silver_counter->detail ="renumbered";
$seq_s_silver_counter->save();
}
break;
}
return json_encode(["renumbered" => $renumbered, 'queries' => $queries,'startingfrom'=>$firstVal,'inspect'=>$inspect]);
die;
}
Additional Notes:
In the provided code, $_POST[‘lastNum’] represents a number specified by the user to initiate renumbering, starting from $_POST[‘lastNum’]+1.
For further context:
The renumbering process relies on a SequenceInfo model, which stores the updated last number. This model is utilized in the creation function of every single inventory controller, determining the bill number based on the sequence info key number.
An exceptional case arises when a bill’s creation date is modified after renumbering. This scenario aims to eliminate any potential gaps in bill numbering caused by the initial renumbering. However, it results in the bill numbers not aligning chronologically by date. Consequently, performing renumbering again will not rectify the issue of bill numbering order based on date.
Your assistance in resolving this issue is greatly appreciated.