ModuleMaster/logistics/month_report.aspx.vb - main - ivan/ModMas

ModuleMaster/logistics/month_report.aspx.vb
186 строк8 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports sharedClasses.dbFunctions
Partial Class GA_month_report
    Inherits System.Web.UI.Page

    Private sqlf As New SqlFunctions
    Protected Sub report_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles report.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            'Dim rowView As Data.DataRowView = CType(e.Row.DataItem, Data.DataRowView)

            For Each c As TableCell In e.Row.Cells
                c.Text = c.Text.Replace(",", " ")
            Next
            Try
                e.Row.Cells(1).Controls.Add(New Image With {.ImageUrl = CType(e.Row.DataItem, Data.DataRowView)("photo"), .CssClass = "thumbnail"})
            Catch ex As Exception

            End Try

            If CType(e.Row.DataItem, Data.DataRowView)("MONTH_IS_FIXED") = 1 Then
                e.Row.Style.Add("background", "LightGreen")
            End If
        End If
    End Sub

    Protected Sub Page_PreRenderComplete(sender As Object, e As EventArgs) Handles Me.PreRenderComplete
        Using sqlf
            Dim periodStart, periodEnd As String
            periodStart = Date.Parse(month.SelectedValue & "-01")
            periodEnd = Date.Parse(month.SelectedValue & "-01").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")
            If sqlf.GetScalar("SELECT count(MONTH_IS_FIXED) FROM hr.dbo.[TB_DRIVER_MONTHLY_PAYMENTS_2] WHERE [MONTH]='" & month.SelectedValue & "'") = 0 Then
                'Month not fixed yet, get data from view
                sqlf.NonQuery("UPDATE UserModules.[user].[DRIVER_DAILY_2] " &
                    "SET PERIOD = '" & month.SelectedValue & "'" &
                    "WHERE [DATE] BETWEEN '" & periodStart & "' AND '" & periodEnd & "'")
                sdsReport.SelectCommand = sdsReport.SelectCommand.Replace("TB_DRIVER_MONTHLY_PAYMENTS_2", "VI_DRIVER_MONTHLY_PAYMENTS_LOGISTICS")
            Else ' month is fixed , taking data from table 
            End If
            lblPeriod.Text = periodStart & " / " & periodEnd
            report.DataSource = sdsReport
            'lblDebug.Text = sdsReport.SelectCommand
            'Try
            report.DataBind()
            'Catch ex As Exception

            'End Try

            ' Label1.Text = sdsReport.SelectCommand
        End Using
    End Sub

    Protected Sub Page_SaveStateComplete(sender As Object, e As EventArgs) Handles Me.SaveStateComplete
        Try
            Dim ttlROw As GridViewRow = report.Rows(report.Rows.Count - 1)
            For Each c As TableCell In ttlROw.Cells
                c.Style.Add("background-color", "SteelBlue")
                c.Style.Add("color", "White")
                c.Style.Add("font-weight", "bold")
            Next
            ttlROw.Cells(0).ColumnSpan = 3
            ttlROw.Cells(0).Text = "Total"
            ttlROw.Cells(0).HorizontalAlign = HorizontalAlign.Center
            ttlROw.Cells.RemoveAt(2)
            ttlROw.Cells.RemoveAt(1)
        Catch ex As Exception

        End Try
    End Sub

    Protected Sub refresh_Click(sender As Object, e As ImageClickEventArgs) Handles refresh.Click
        month.DataBind()
    End Sub

    Protected Sub confirm_Click(sender As Object, e As ImageClickEventArgs) Handles confirm.Click
        Using sqlf
            Dim sql As New StringBuilder
            sql.AppendLine("WITH SRC as ( ")
            sql.AppendLine("SELECT * FROM hr.dbo.VI_DRIVER_MONTHLY_PAYMENTS_LOGISTICS ")
            sql.AppendLine("WHERE MONTH = '" & month.SelectedValue & "' ")
            sql.AppendLine(") ")
            sql.AppendLine("MERGE hr.dbo.TB_DRIVER_MONTHLY_PAYMENTS_2 as trg ")
            sql.AppendLine("using src ")
            sql.AppendLine("on trg.[month] Collate Cyrillic_General_CI_AI  = src.[month] Collate Cyrillic_General_CI_AI ")
            sql.AppendLine(" and trg.[driver] Collate Cyrillic_General_CI_AI = src.[driver] Collate Cyrillic_General_CI_AI ")
            sql.AppendLine("when not matched then ")
            sql.AppendLine("insert ")
            sql.AppendLine("([MONTH] ")
            sql.AppendLine(",[DRIVER] ")
            sql.AppendLine(",[THIS_MONTH_WORK_DAYS] ")
            sql.AppendLine(",[THIS_MONTH_WORK_HOURS] ")
            sql.AppendLine(",[WORKED_DAYS] ")
            sql.AppendLine(",[MONTH_RATE] ")
            sql.AppendLine(",[T_04] ")
            sql.AppendLine(",[T_1] ")
            sql.AppendLine(",[T_15] ")
            sql.AppendLine(",[T_2] ")
            sql.AppendLine(",[SALARY] ")
            sql.AppendLine(",[OVERTIME] ")
            sql.AppendLine(",[GROSS_SALARY] ")
            sql.AppendLine(",[SALARY_WO_EXPENSE] ")
            sql.AppendLine(",[EXPENSE] ")
            sql.AppendLine(",[PREPAYMENT] ")
            sql.AppendLine(",[driver_type] ")
            sql.AppendLine(",[PER_HOUR] ")
            sql.AppendLine(",[SECOND_PAYMENT] ")
            sql.AppendLine(",[TAX_13] ")
            sql.AppendLine(",[TAX_305] ")
            sql.AppendLine(",[TC_PROFIT] ")
            sql.AppendLine(",[TOTAL] ")
            sql.AppendLine(",[MONTH_IS_FIXED] ")
            sql.AppendLine(",[updated]  ")
            sql.AppendLine(" ) ")
            sql.AppendLine("VALUES ")
            sql.AppendLine("([MONTH] ")
            sql.AppendLine(",[DRIVER] ")
            sql.AppendLine(",[THIS_MONTH_WORK_DAYS] ")
            sql.AppendLine(",[THIS_MONTH_WORK_HOURS] ")
            sql.AppendLine(",[WORKED_DAYS] ")
            sql.AppendLine(",[MONTH_RATE] ")
            sql.AppendLine(",[T_04] ")
            sql.AppendLine(",[T_1] ")
            sql.AppendLine(",[T_15] ")
            sql.AppendLine(",[T_2] ")
            sql.AppendLine(",[SALARY] ")
            sql.AppendLine(",[OVERTIME] ")
            sql.AppendLine(",[GROSS_SALARY] ")
            sql.AppendLine(",[SALARY_WO_EXPENSE] ")
            sql.AppendLine(",[EXPENSE] ")
            sql.AppendLine(",[PREPAYMENT] ")
            sql.AppendLine(",[driver_type] ")
            sql.AppendLine(",[PER_HOUR] ")
            sql.AppendLine(",[SECOND_PAYMENT] ")
            sql.AppendLine(",[TAX_13] ")
            sql.AppendLine(",[TAX_305] ")
            sql.AppendLine(",[TC_PROFIT] ")
            sql.AppendLine(",[TOTAL] ")
            sql.AppendLine(", 1 ")
            sql.AppendLine(", getdate()); ")

            sqlf.NonQuery(sql.ToString)
            sql.Clear()

            sql.AppendLine("DELETE From usermodules.[user].DRIVERS_MONTHLY_PAYMENT_2 ")
            sql.AppendLine("Where [month] = '" & month.SelectedValue & "' ")

            sqlf.NonQuery(sql.ToString)
            sql.Clear()

            sql.AppendLine("INSERT INTO usermodules.[user].DRIVERS_MONTHLY_PAYMENT_2 ")
            sql.AppendLine("([DRIVER] ")
            sql.AppendLine(",[MONTH] ")
            sql.AppendLine(", VACATION ")
            sql.AppendLine(", SICK_LIST ")
            sql.AppendLine(", BONUS ")
            sql.AppendLine(", PROSTOY ")
            sql.AppendLine(", TOTAL ")
            sql.AppendLine(", [PAYMENT] ")
            sql.AppendLine(", WORK_HOURS ")
            sql.AppendLine(", COST_OF_1_HOUR ")
            sql.AppendLine(") ")
            sql.AppendLine("select ")
            sql.AppendLine(" driver ")
            sql.AppendLine(", [month] ")
            sql.AppendLine(", '' as vacation ")
            sql.AppendLine(", '' as sick_list ")
            sql.AppendLine(", '' as bonus ")
            sql.AppendLine(", GROSS_SALARY - salary - overtime - expense as prostoy ")
            sql.AppendLine(", GROSS_SALARY as TOTAL ")
            sql.AppendLine(", salary + overtime + expense as PAYMENT ")
            sql.AppendLine(", T_04 + T_1 + T_15 + T_2 as WORK_HOURS ")
            sql.AppendLine(", CAST((salary + overtime + expense) / (T_04 + T_1 + T_15 + T_2) AS MONEY) as COST_OF_1_HR ")
            sql.AppendLine(" from hr.dbo.VI_DRIVER_MONTHLY_PAYMENTS_LOGISTICS ")
            sql.AppendLine(" where month = '" & month.SelectedValue & "' ")

            sqlf.NonQuery(sql.ToString)

            'lblDebug.Text = sql.ToString
        End Using
    End Sub

    Protected Sub ImageButton1_Click(sender As Object, e As ImageClickEventArgs) Handles ImageButton1.Click
        Using sqlf
            sqlf.NonQuery("DELETE FROM hr.dbo.TB_DRIVER_MONTHLY_PAYMENTS_2 WHERE [MONTH] = '" & month.SelectedValue & "'")
            sqlf.NonQuery("DELETE From usermodules.[user].DRIVERS_MONTHLY_PAYMENT_2  Where [month] = '" & month.SelectedValue & "' ")
        End Using
    End Sub
End Class