Creating a CS DOC for a existing program

Reference: https://linear.app/citydataservices/issue/CIT-2525/%5Bmil%5D-milpitas-action-items-public-services-fy24-set-up-monitoring

FY 24-25 Billing Summary Sheet (1).xlsx

Looking at the program lists above, if the programs already exists, all we have to do is to create a new CS DOC (Contract Source Document) for the next FY.

Source code needed for this process: gencsnoapp.pl

For this example, we will be looking at City of Milpitas.

---

1. Verify Program in phpMyAdmin

• Open phpMyAdmin and navigate to the database Milpa, then go to the table progs.

  

• Locate the URL for the program to find the progs number (referred to as prop).
  • Example: If the URL shows the program number as 9, this is the progs number.

• Search the progs table by program and enter the number 9 to verify the program exists.

• Confirm the program is already created before proceeding to create the report for CS Doc.

2. Modify Code in gencsnoapp.pl

• Open the file located at cds-app/milpa/gencsnoapp.pl.

• Update the following lines in the file:

$fyr=2024;
 $pfy=$fyr-1;
# $execute = $DB->query($qryrdrp);
# $qry = "SELECT rpttext FROM reports WHERE rptty = 'IA8';";
# $ex6 = $DB->query($qry);
# $ex2 = $DB->query($qryun);

$execute = $DB->query($qryrdpr);
$qry = "SELECT rpttext FROM progs WHERE fyr='$pfy' AND program='35';";
$ex6 = $DB->query($qry);
$ex2 = $DB->query($qryun);
while ($dat = $ex6->fetchrow()) {
@x=split(/\|/,$dat);
# print "$qry,$w[0]<br>";
# if (($w[4]=~/^C/ || $w[4]=~/^A/)) {
    $prop=$x[1];
    if ($x[0] ne "") {
        $x[497]=$x[2];
        $x[495]=$x[0];
        $x[496]=$x[1];
        $x[498]=$x[3];
        $x[0]=$x[1];
        $x[1]="CS";
        $x[2]="06302024";
        $x[3]="$fyr";

        $ex1 = $DB->query($qryrdrp);
        $qry = "SELECT rptnum FROM reports WHERE program='$prop' AND rptty='CS' AND rptper='$fyr';";
        $ex1 = $DB->query($qry);
        $ex2 = $DB->query($qryun);
        $rptx = $ex1->fetchrow();
#print "**$x[10],$x[12]<br>";
#print "$qry,$rptx<br>";

• Ensure the following updates:
  • Set $fyr to the correct fiscal year.
  • Adjust the $qry to match the correct program number.
  • Set $x[2] for the submission date.

• Push the changes to the server.

3. Run the Script

• Run the gencsnoapp.pl script by accessing the URL in your browser:

  

4. Verify the New FY CS Doc

• Go back to the program and refresh to see the newly created FY CS Doc.

5. Update Funded Amount

• Open the newly created CS Doc and set the funded amount to the requested amount.

6. Adjust Contract Info

• Go to the Contract Info section.

• In the Reports section, uncheck “Quarterly Progress Report” to prevent further confusion.