Advertisement
Guest User

Untitled

a guest
Jul 28th, 2017
68
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VB.NET 28.09 KB | None | 0 0
  1.     Private Sub NewSalesReports()
  2.         Dim queryString As String = ""
  3.         Dim sqlConn As New MySqlConnection
  4.         Try
  5.             If sqlConn.State = ConnectionState.Closed Then
  6.                 sqlConn.ConnectionString = madsqlStr
  7.                 sqlConn.Open()
  8.             End If
  9.  
  10.         Catch ex As Exception
  11.             Debug.Print("Connection to MySQL failed")
  12.             Debug.Print(ex.Message.ToString)
  13.         End Try
  14.  
  15.         Try
  16.             queryString = "REPLACE INTO sales_history.install_rates SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, " & _
  17.             "CASE WSTAT " & _
  18.             "WHEN '1' THEN 'Pending' " & _
  19.             "WHEN '5' THEN 'Completed' " & _
  20.             "WHEN 'A' THEN 'Cancelled' " & _
  21.             "END WSTAT, " & _
  22.             "IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, GROUP_CONCAT('\'',RATECD,'\'' SEPARATOR ',') rates, WORDATE, WFINDATE " & _
  23.             "FROM infoddp.WIP w " & _
  24.             "JOIN infoddp.TEMP_WIP_CUST_RATE wcr " & _
  25.             "ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  26.             "WHERE WSTAT IN ('1','5','A') " & _
  27.             "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY) " & _
  28.             "AND wcr.ACCTCORP = '15552' " & _
  29.             "GROUP BY w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT; " & _
  30.             "REPLACE INTO sales_history.disconnect_rates SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, " & _
  31.             "CASE WSTAT " & _
  32.             "WHEN '4' THEN 'Pending' " & _
  33.             "WHEN '8' THEN 'Completed' " & _
  34.             "WHEN 'D' THEN 'Cancelled' " & _
  35.             "END WSTAT, " & _
  36.             "IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, GROUP_CONCAT('\'',RATECD,'\'' SEPARATOR ',') rates, WORDATE, WFINDATE " & _
  37.             "FROM infoddp.WIP w " & _
  38.             "JOIN infoddp.TEMP_WIP_CUST_RATE wcr " & _
  39.             "ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  40.             "WHERE WSTAT IN ('4','8','D') " & _
  41.             "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY) " & _
  42.             "AND wcr.ACCTCORP = '15552' " & _
  43.             "GROUP BY w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT; " & _
  44.             "REPLACE INTO sales_history.upgrade_rates SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, " & _
  45.             "CASE WSTAT " & _
  46.             "WHEN '3' THEN 'Pending' " & _
  47.             "WHEN '7' THEN 'Completed' " & _
  48.             "WHEN 'C' THEN 'Cancelled' " & _
  49.             "END WSTAT, " & _
  50.             "IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, GROUP_CONCAT('\'',RATECD,'\'' SEPARATOR ',') rates, WORDATE, WFINDATE " & _
  51.             "FROM infoddp.WIP w " & _
  52.             "JOIN infoddp.TEMP_WIP_CUST_RATE wcr " & _
  53.             "ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  54.             "WHERE WSTAT IN ('3','7','C') " & _
  55.             "AND RATESIGN = '+' " & _
  56.             "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY) " & _
  57.             "AND wcr.ACCTCORP = '15552' " & _
  58.             "GROUP BY w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT; " & _
  59.             "REPLACE INTO sales_history.downgrade_rates SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, " & _
  60.             "CASE WSTAT " & _
  61.             "WHEN '3' THEN 'Pending' " & _
  62.             "WHEN '7' THEN 'Completed' " & _
  63.             "WHEN 'C' THEN 'Cancelled' " & _
  64.             "END WSTAT, " & _
  65.             "IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, GROUP_CONCAT('\'',RATECD,'\'' SEPARATOR ',') rates, WORDATE, WFINDATE " & _
  66.             "FROM infoddp.WIP w " & _
  67.             "JOIN infoddp.TEMP_WIP_CUST_RATE wcr " & _
  68.             "ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  69.             "WHERE WSTAT IN ('3','7','C') " & _
  70.             "AND RATESIGN = '-' " & _
  71.             "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY) " & _
  72.             "AND wcr.ACCTCORP = '15552' " & _
  73.             "GROUP BY w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT; " & _
  74. "CREATE TABLE infoddp.temp_install_reporting_centers " & _
  75. "SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, WSTAT, IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, RATECD, WORDATE, WFINDATE, RCTR01, RCTR02, RCTR03, RCTR04, RCTR05, RCTR06, RCTR07, RCTR08, RCTR09, RCTR10, RCTR11, RCTR12, RCTR13, RCTR14, RCTR15, RCTR16, RCTR17, RCTR18, RCTR19, RCTR20 " & _
  76. "FROM infoddp.WIP w " & _
  77. "JOIN infoddp.TEMP_WIP_CUST_RATE wcr ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  78. "JOIN infoddp.RATE_REPORTING_CENTER rrc on w.ACCTCORP = rrc.ACCTCORP and RATECD = DRATECODE " & _
  79. "WHERE w.ACCTCORP = '15552' " & _
  80. "AND WSTAT IN ('1','5','A') " & _
  81. "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY); " & _
  82. "REPLACE INTO sales_history.install_rpt_centers SELECT ACCTCORP, HOUSE, CUST, WPCNT, " & _
  83. "CASE WSTAT " & _
  84. "WHEN '1' THEN 'Pending' " & _
  85. "WHEN '5' THEN 'Completed' " & _
  86. "WHEN 'A' THEN 'Cancelled' " & _
  87. "END WSTAT, SLSREP, WORDATE, WFINDATE, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 " & _
  88. "FROM infoddp.temp_install_reporting_centers; " & _
  89. "CREATE TABLE infoddp.temp_upgrade_reporting_centers " & _
  90. "SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, WSTAT, IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, RATECD, WORDATE, WFINDATE, RCTR01, RCTR02, RCTR03, RCTR04, RCTR05, RCTR06, RCTR07, RCTR08, RCTR09, RCTR10, RCTR11, RCTR12, RCTR13, RCTR14, RCTR15, RCTR16, RCTR17, RCTR18, RCTR19, RCTR20 " & _
  91. "FROM infoddp.WIP w " & _
  92. "JOIN infoddp.TEMP_WIP_CUST_RATE wcr ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  93. "JOIN infoddp.RATE_REPORTING_CENTER rrc on w.ACCTCORP = rrc.ACCTCORP and RATECD = DRATECODE " & _
  94. "WHERE w.ACCTCORP = '15552' " & _
  95. "AND WSTAT IN ('3','7','C') AND RATESIGN = '+' " & _
  96. "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY); " & _
  97. "REPLACE INTO sales_history.upgrade_rpt_centers SELECT ACCTCORP, HOUSE, CUST, WPCNT, " & _
  98. "CASE WSTAT " & _
  99. "WHEN '3' THEN 'Pending' " & _
  100. "WHEN '7' THEN 'Completed' " & _
  101. "WHEN 'C' THEN 'Cancelled' " & _
  102. "END WSTAT, SLSREP, WORDATE, WFINDATE, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 " & _
  103. "FROM infoddp.temp_upgrade_reporting_centers; " & _
  104. "CREATE TABLE infoddp.temp_downgrade_reporting_centers " & _
  105. "SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, WSTAT, IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, RATECD, WORDATE, WFINDATE, RCTR01, RCTR02, RCTR03, RCTR04, RCTR05, RCTR06, RCTR07, RCTR08, RCTR09, RCTR10, RCTR11, RCTR12, RCTR13, RCTR14, RCTR15, RCTR16, RCTR17, RCTR18, RCTR19, RCTR20 " & _
  106. "FROM infoddp.WIP w " & _
  107. "JOIN infoddp.TEMP_WIP_CUST_RATE wcr ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  108. "JOIN infoddp.RATE_REPORTING_CENTER rrc on w.ACCTCORP = rrc.ACCTCORP and RATECD = DRATECODE " & _
  109. "WHERE w.ACCTCORP = '15552' " & _
  110. "AND WSTAT IN ('3','7','c') AND RATESIGN = '-'" & _
  111. "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY); " & _
  112. "REPLACE INTO sales_history.downgrade_rpt_centers SELECT ACCTCORP, HOUSE, CUST, WPCNT, " & _
  113. "CASE WSTAT " & _
  114. "WHEN '3' THEN 'Pending' " & _
  115. "WHEN '7' THEN 'Completed' " & _
  116. "WHEN 'C' THEN 'Cancelled' " & _
  117. "END WSTAT, SLSREP, WORDATE, WFINDATE, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 " & _
  118. "FROM infoddp.temp_downgrade_reporting_centers; " & _
  119. "CREATE TABLE infoddp.temp_disconnect_reporting_centers " & _
  120. "SELECT w.ACCTCORP, w.HOUSE, w.CUST, w.WPCNT, WSTAT, IF(SLSREP IS NULL,WHO,SLSREP) SLSREP, RATECD, WORDATE, WFINDATE, RCTR01, RCTR02, RCTR03, RCTR04, RCTR05, RCTR06, RCTR07, RCTR08, RCTR09, RCTR10, RCTR11, RCTR12, RCTR13, RCTR14, RCTR15, RCTR16, RCTR17, RCTR18, RCTR19, RCTR20 " & _
  121. "FROM infoddp.WIP w " & _
  122. "JOIN infoddp.TEMP_WIP_CUST_RATE wcr ON w.ACCTCORP = wcr.ACCTCORP and w.HOUSE = wcr.HOUSE and w.CUST = wcr.CUST and w.WPCNT = wcr.WPCNT " & _
  123. "JOIN infoddp.RATE_REPORTING_CENTER rrc on w.ACCTCORP = rrc.ACCTCORP and RATECD = DRATECODE " & _
  124. "WHERE w.ACCTCORP = '15552' " & _
  125. "AND WSTAT IN ('4','8','D') " & _
  126. "AND (WORDATE = CURDATE() - INTERVAL 1 DAY OR WFINDATE = CURDATE() - INTERVAL 1 DAY); " & _
  127. "REPLACE INTO sales_history.disconnect_rpt_centers SELECT ACCTCORP, HOUSE, CUST, WPCNT, " & _
  128. "CASE WSTAT " & _
  129. "WHEN '4' THEN 'Pending' " & _
  130. "WHEN '8' THEN 'Completed' " & _
  131. "WHEN 'D' THEN 'Cancelled' " & _
  132. "END WSTAT, SLSREP, WORDATE, WFINDATE, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 " & _
  133. "FROM infoddp.temp_disconnect_reporting_centers; "
  134.             Dim cmd As New MySqlCommand(queryString, sqlConn)
  135.             cmd.CommandTimeout = 0
  136.             cmd.ExecuteNonQuery()
  137.             cmd.Dispose()
  138.  
  139.         Catch ex As Exception
  140.             Debug.Print("Failed attempt to insert MySQL string: " & queryString)
  141.             Debug.Print(ex.Message.ToString)
  142.             Debug.Print("Giving up")
  143.             Exit Try
  144.  
  145.         End Try
  146.  
  147.         Try
  148.             Dim i As Integer = 1
  149.             Dim x As String = ""
  150.             Do Until i = 100
  151.                 queryString = "CREATE TEMPORARY TABLE infoddp.irctr SELECT crc.ACCTCORP, crc.HOUSE, crc.CUST, " & _
  152. "CASE WSTAT " & _
  153. "WHEN '1' THEN 'Pending' " & _
  154. "WHEN '5' THEN 'Completed' " & _
  155. "WHEN 'A' THEN 'Cancelled' " & _
  156. "END WSTAT, crc.WPCNT, SUM(IF(RATECD IS NOT NULL,1,0)) total FROM infoddp.temp_install_reporting_centers crc " & _
  157. "WHERE crc.ACCTCORP = '15552' AND (RCTR01 IN ('" & i & "') OR RCTR02 IN ('" & i & "') OR RCTR03 IN ('" & i & "') OR RCTR04 IN ('" & i & "') OR RCTR05 IN ('" & i & "') OR RCTR06 IN ('" & i & "') OR RCTR07 IN ('" & i & "') OR RCTR08 IN ('" & i & "') OR RCTR09 IN ('" & i & "') OR RCTR10 IN ('" & i & "') OR RCTR11 IN ('" & i & "') OR RCTR12 IN ('" & i & "') OR RCTR13 IN ('" & i & "') OR RCTR14 IN ('" & i & "') OR RCTR15 IN ('" & i & "') OR RCTR16 IN ('" & i & "') OR RCTR17 IN ('" & i & "') OR RCTR18 IN ('" & i & "') OR RCTR19 IN ('" & i & "') OR RCTR20 IN ('" & i & "')) " & _
  158. "GROUP BY crc.ACCTCORP, crc.HOUSE, crc.CUST; " & _
  159. "CREATE TEMPORARY TABLE infoddp.ugrctr SELECT crc.ACCTCORP, crc.HOUSE, crc.CUST, " & _
  160. "CASE WSTAT " & _
  161. "WHEN '4' THEN 'Pending' " & _
  162. "WHEN '8' THEN 'Completed' " & _
  163. "WHEN 'D' THEN 'Cancelled' " & _
  164. "END WSTAT, crc.WPCNT, SUM(IF(RATECD IS NOT NULL,1,0)) total FROM infoddp.temp_upgrade_reporting_centers crc " & _
  165. "WHERE crc.ACCTCORP = '15552' AND (RCTR01 IN ('" & i & "') OR RCTR02 IN ('" & i & "') OR RCTR03 IN ('" & i & "') OR RCTR04 IN ('" & i & "') OR RCTR05 IN ('" & i & "') OR RCTR06 IN ('" & i & "') OR RCTR07 IN ('" & i & "') OR RCTR08 IN ('" & i & "') OR RCTR09 IN ('" & i & "') OR RCTR10 IN ('" & i & "') OR RCTR11 IN ('" & i & "') OR RCTR12 IN ('" & i & "') OR RCTR13 IN ('" & i & "') OR RCTR14 IN ('" & i & "') OR RCTR15 IN ('" & i & "') OR RCTR16 IN ('" & i & "') OR RCTR17 IN ('" & i & "') OR RCTR18 IN ('" & i & "') OR RCTR19 IN ('" & i & "') OR RCTR20 IN ('" & i & "')) " & _
  166. "GROUP BY crc.ACCTCORP, crc.HOUSE, crc.CUST; " & _
  167. "CREATE TEMPORARY TABLE infoddp.drctr SELECT crc.ACCTCORP, crc.HOUSE, crc.CUST, " & _
  168. "CASE WSTAT " & _
  169. "WHEN '3' THEN 'Pending' " & _
  170. "WHEN '7' THEN 'Completed' " & _
  171. "WHEN 'C' THEN 'Cancelled' " & _
  172. "END WSTAT, crc.WPCNT, SUM(IF(RATECD IS NOT NULL,1,0)) total FROM infoddp.temp_disconnect_reporting_centers crc " & _
  173. "WHERE crc.ACCTCORP = '15552' AND (RCTR01 IN ('" & i & "') OR RCTR02 IN ('" & i & "') OR RCTR03 IN ('" & i & "') OR RCTR04 IN ('" & i & "') OR RCTR05 IN ('" & i & "') OR RCTR06 IN ('" & i & "') OR RCTR07 IN ('" & i & "') OR RCTR08 IN ('" & i & "') OR RCTR09 IN ('" & i & "') OR RCTR10 IN ('" & i & "') OR RCTR11 IN ('" & i & "') OR RCTR12 IN ('" & i & "') OR RCTR13 IN ('" & i & "') OR RCTR14 IN ('" & i & "') OR RCTR15 IN ('" & i & "') OR RCTR16 IN ('" & i & "') OR RCTR17 IN ('" & i & "') OR RCTR18 IN ('" & i & "') OR RCTR19 IN ('" & i & "') OR RCTR20 IN ('" & i & "')) " & _
  174. "GROUP BY crc.ACCTCORP, crc.HOUSE, crc.CUST; " & _
  175. "CREATE TEMPORARY TABLE infoddp.dgctr SELECT crc.ACCTCORP, crc.HOUSE, crc.CUST, " & _
  176. "CASE WSTAT " & _
  177. "WHEN '3' THEN 'Pending' " & _
  178. "WHEN '7' THEN 'Completed' " & _
  179. "WHEN 'C' THEN 'Cancelled' " & _
  180. "END WSTAT, crc.WPCNT, SUM(IF(RATECD IS NOT NULL,1,0)) total FROM infoddp.temp_downgrade_reporting_centers crc " & _
  181. "WHERE crc.ACCTCORP = '15552' AND (RCTR01 IN ('" & i & "') OR RCTR02 IN ('" & i & "') OR RCTR03 IN ('" & i & "') OR RCTR04 IN ('" & i & "') OR RCTR05 IN ('" & i & "') OR RCTR06 IN ('" & i & "') OR RCTR07 IN ('" & i & "') OR RCTR08 IN ('" & i & "') OR RCTR09 IN ('" & i & "') OR RCTR10 IN ('" & i & "') OR RCTR11 IN ('" & i & "') OR RCTR12 IN ('" & i & "') OR RCTR13 IN ('" & i & "') OR RCTR14 IN ('" & i & "') OR RCTR15 IN ('" & i & "') OR RCTR16 IN ('" & i & "') OR RCTR17 IN ('" & i & "') OR RCTR18 IN ('" & i & "') OR RCTR19 IN ('" & i & "') OR RCTR20 IN ('" & i & "')) " & _
  182. "GROUP BY crc.ACCTCORP, crc.HOUSE, crc.CUST; " & _
  183. "UPDATE sales_history.install_rpt_centers irc " & _
  184. "JOIN infoddp.irctr trc ON irc.CORP = trc.ACCTCORP and irc.HOUSE = trc.HOUSE and irc.CUST = trc.CUST and irc.Wipcount = trc.wpcnt " & _
  185. "SET irc.ctr" & i & " = trc.total; " & _
  186. "UPDATE sales_history.upgrade_rpt_centers irc " & _
  187. "JOIN infoddp.ugrctr trc ON irc.CORP = trc.ACCTCORP and irc.HOUSE = trc.HOUSE and irc.CUST = trc.CUST and irc.Wipcount = trc.wpcnt " & _
  188. "SET irc.ctr" & i & " = trc.total; " & _
  189. "UPDATE sales_history.downgrade_rpt_centers irc " & _
  190. "JOIN infoddp.dgctr trc ON irc.CORP = trc.ACCTCORP and irc.HOUSE = trc.HOUSE and irc.CUST = trc.CUST and irc.Wipcount = trc.wpcnt " & _
  191. "SET irc.ctr" & i & " = trc.total; " & _
  192. "UPDATE sales_history.disconnect_rpt_centers irc " & _
  193. "JOIN infoddp.drctr trc ON irc.CORP = trc.ACCTCORP and irc.HOUSE = trc.HOUSE and irc.CUST = trc.CUST and irc.Wipcount = trc.wpcnt " & _
  194. "SET irc.ctr" & i & " = trc.total; " & _
  195. "DROP TABLE infoddp.irctr;" & _
  196. "DROP TABLE infoddp.ugrctr;" & _
  197. "DROP TABLE infoddp.dgctr;" & _
  198. "DROP TABLE infoddp.drctr;"
  199.  
  200.                 Debug.Print(queryString)
  201.                 Dim cmd As New MySqlCommand(queryString, sqlConn)
  202.                 cmd.CommandTimeout = 0
  203.                 cmd.ExecuteNonQuery()
  204.                 cmd.Dispose()
  205.                 i = i + 1
  206.             Loop
  207.  
  208.         Catch ex As Exception
  209.             Debug.Print("Failed attempt to insert MySQL string: " & queryString)
  210.             Debug.Print(ex.Message.ToString)
  211.             Debug.Print("Giving up")
  212.             Exit Try
  213.  
  214.         End Try
  215.  
  216.         Try
  217.             queryString = "TRUNCATE infoddp.current_rpt_centers; REPLACE INTO infoddp.current_rpt_centers " & _
  218.             "SELECT ACCTCORP, HOUSE, CUST, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM infoddp.CUSTOMER " & _
  219.             "WHERE ACCTCORP = '15552' AND STAT IN ('4','5');" & _
  220. "DROP TABLE infoddp.temp_install_reporting_centers;" & _
  221. "DROP TABLE infoddp.temp_upgrade_reporting_centers;" & _
  222. "DROP TABLE infoddp.temp_downgrade_reporting_centers;" & _
  223. "DROP TABLE infoddp.temp_disconnect_reporting_centers;"
  224.             Dim cmd As New MySqlCommand(queryString, sqlConn)
  225.             cmd.CommandTimeout = 0
  226.             cmd.ExecuteNonQuery()
  227.             cmd.Dispose()
  228.  
  229.         Catch ex As Exception
  230.             Debug.Print("Failed attempt to insert MySQL string: " & queryString)
  231.             Debug.Print(ex.Message.ToString)
  232.             Debug.Print("Giving up")
  233.             Exit Try
  234.  
  235.         End Try
  236.  
  237.         Try
  238.             Dim i As Integer = 1
  239.             Dim x As String = ""
  240.             Do Until i = 100
  241.                 queryString = "CREATE TEMPORARY TABLE infoddp.rctr SELECT crc.CORP, crc.HOUSE, crc.CUST, SUM(IF(SERV IS NOT NULL,1,0)) total FROM infoddp.current_rpt_centers crc " & _
  242.                 "JOIN infoddp.CUST_RATE cr on crc.CORP = cr.ACCTCORP and crc.HOUSE = cr.HOUSE and crc.CUST = cr.CUST " & _
  243.                 "JOIN infoddp.RATE_REPORTING_CENTER rrc on cr.ACCTCORP = rrc.ACCTCORP and cr.SERV = rrc.DRATECODE " & _
  244.                 "WHERE cr.ACCTCORP = '15552' AND (RCTR01 IN ('" & i & "') OR RCTR02 IN ('" & i & "') OR RCTR03 IN ('" & i & "') OR RCTR04 IN ('" & i & "') OR RCTR05 IN ('" & i & "') OR RCTR06 IN ('" & i & "') OR RCTR07 IN ('" & i & "') OR RCTR08 IN ('" & i & "') OR RCTR09 IN ('" & i & "') OR RCTR10 IN ('" & i & "') OR RCTR11 IN ('" & i & "') OR RCTR12 IN ('" & i & "') OR RCTR13 IN ('" & i & "') OR RCTR14 IN ('" & i & "') OR RCTR15 IN ('" & i & "') OR RCTR16 IN ('" & i & "') OR RCTR17 IN ('" & i & "') OR RCTR18 IN ('" & i & "') OR RCTR19 IN ('" & i & "') OR RCTR20 IN ('" & i & "')) " & _
  245.                 "GROUP BY crc.CORP, crc.HOUSE, crc.CUST; " & _
  246.                 "UPDATE infoddp.current_rpt_centers irc " & _
  247. "JOIN infoddp.rctr rctr ON irc.CORP = rctr.CORP and irc.HOUSE = rctr.HOUSE and irc.CUST = rctr.CUST " & _
  248. "SET irc.ctr" & i & " = rctr.total; " & _
  249. "DROP TABLE infoddp.rctr;"
  250.  
  251.                 Debug.Print(queryString)
  252.                 Dim cmd As New MySqlCommand(queryString, sqlConn)
  253.                 cmd.CommandTimeout = 0
  254.                 cmd.ExecuteNonQuery()
  255.                 cmd.Dispose()
  256.                 i = i + 1
  257.             Loop
  258.  
  259.         Catch ex As Exception
  260.             Debug.Print("Failed attempt to insert MySQL string: " & queryString)
  261.             Debug.Print(ex.Message.ToString)
  262.             Debug.Print("Giving up")
  263.             Exit Try
  264.  
  265.         End Try
  266.  
  267.  
  268.         Try
  269.             queryString = "TRUNCATE infoddp.temp_rpt_centers; REPLACE INTO infoddp.temp_rpt_centers " & _
  270.             "SELECT ACCTCORP, HOUSE, CUST, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM infoddp.TEMP_CUST_RATE " & _
  271.             "WHERE ACCTCORP = '15552';"
  272.             Dim cmd As New MySqlCommand(queryString, sqlConn)
  273.             cmd.CommandTimeout = 0
  274.             cmd.ExecuteNonQuery()
  275.             cmd.Dispose()
  276.  
  277.         Catch ex As Exception
  278.             Debug.Print("Failed attempt to insert MySQL string: " & queryString)
  279.             Debug.Print(ex.Message.ToString)
  280.             Debug.Print("Giving up")
  281.             Exit Try
  282.  
  283.         End Try
  284.  
  285.         Try
  286.             Dim i As Integer = 1
  287.             Dim x As String = ""
  288.             Do Until i = 100
  289.                 queryString = "CREATE TEMPORARY TABLE infoddp.rctr SELECT crc.CORP, crc.HOUSE, crc.CUST, SUM(IF(SERV IS NOT NULL,1,0)) total FROM infoddp.temp_rpt_centers crc " & _
  290.                 "JOIN infoddp.TEMP_CUST_RATE cr on crc.CORP = cr.ACCTCORP and crc.HOUSE = cr.HOUSE and crc.CUST = cr.CUST " & _
  291.                 "JOIN infoddp.RATE_REPORTING_CENTER rrc on cr.ACCTCORP = rrc.ACCTCORP and cr.SERV = rrc.DRATECODE " & _
  292.                 "WHERE cr.ACCTCORP = '15552' AND (RCTR01 IN ('" & i & "') OR RCTR02 IN ('" & i & "') OR RCTR03 IN ('" & i & "') OR RCTR04 IN ('" & i & "') OR RCTR05 IN ('" & i & "') OR RCTR06 IN ('" & i & "') OR RCTR07 IN ('" & i & "') OR RCTR08 IN ('" & i & "') OR RCTR09 IN ('" & i & "') OR RCTR10 IN ('" & i & "') OR RCTR11 IN ('" & i & "') OR RCTR12 IN ('" & i & "') OR RCTR13 IN ('" & i & "') OR RCTR14 IN ('" & i & "') OR RCTR15 IN ('" & i & "') OR RCTR16 IN ('" & i & "') OR RCTR17 IN ('" & i & "') OR RCTR18 IN ('" & i & "') OR RCTR19 IN ('" & i & "') OR RCTR20 IN ('" & i & "')) " & _
  293.                 "GROUP BY crc.CORP, crc.HOUSE, crc.CUST; " & _
  294.                 "UPDATE infoddp.temp_rpt_centers irc " & _
  295. "JOIN infoddp.rctr rctr ON irc.CORP = rctr.CORP and irc.HOUSE = rctr.HOUSE and irc.CUST = rctr.CUST " & _
  296. "SET irc.ctr" & i & " = rctr.total; " & _
  297. "DROP TABLE infoddp.rctr;"
  298.  
  299.                 Debug.Print(queryString)
  300.                 Dim cmd As New MySqlCommand(queryString, sqlConn)
  301.                 cmd.CommandTimeout = 0
  302.                 cmd.ExecuteNonQuery()
  303.                 cmd.Dispose()
  304.                 i = i + 1
  305.             Loop
  306.  
  307.         Catch ex As Exception
  308.             Debug.Print("Failed attempt to insert MySQL string: " & queryString)
  309.             Debug.Print(ex.Message.ToString)
  310.             Debug.Print("Giving up")
  311.             Exit Try
  312.  
  313.         End Try
  314.  
  315.  
  316.         Try
  317.             queryString = "REPLACE INTO sales_history.install_rgu SELECT corp, house, cust, wipcount, order_status, salesrep, " & _
  318. "IF(SUM(ctr1 + ctr84 + ctr76) > 0,1,0) BasicRGU, " & _
  319. "IF(SUM(ctr43 + ctr65) > 0,1,0) DigitalRGU, " & _
  320. "IF(SUM(ctr5 + ctr70 + ctr47 + ctr44 + ctr55) > 0,1,0) HSDRGU, " & _
  321. "IF(SUM(ctr92 + ctr40 + ctr88) > 0,1,0) CDVRGU, " & _
  322. "order_date, finalize_date " & _
  323. "FROM(sales_history.install_rpt_centers) " & _
  324. "WHERE corp = '15552' " & _
  325. "group by house,cust,wipcount; " & _
  326. "REPLACE INTO sales_history.disconnect_rgu SELECT corp, house, cust, wipcount, order_status, salesrep, " & _
  327. "IF(SUM(ctr1 + ctr84 + ctr76) > 0,1,0) BasicRGU, " & _
  328. "IF(SUM(ctr43 + ctr65) > 0,1,0) DigitalRGU, " & _
  329. "IF(SUM(ctr5 + ctr70 + ctr47 + ctr44 + ctr55) > 0,1,0) HSDRGU, " & _
  330. "IF(SUM(ctr92 + ctr40 + ctr88) > 0,1,0) CDVRGU, " & _
  331. "order_date, finalize_date " & _
  332. "FROM(sales_history.disconnect_rpt_centers) " & _
  333. "WHERE corp = '15552' " & _
  334. "group by house,cust,wipcount; " & _
  335. "REPLACE INTO sales_history.upgrade_rgu SELECT corp, house, cust, wipcount, order_status, salesrep, 0, 0, 0, 0, order_date, finalize_date " & _
  336. "from(upgrade_rpt_centers) " & _
  337. "WHERE finalize_date = CURDATE() - INTERVAL 1 DAY; " & _
  338. "CREATE TEMPORARY TABLE UP_CHSI SELECT a.corp, a.house, a.cust, b.wipcount, b.order_status, b.salesrep, SUM(b.ctr70 + b.ctr47 + b.ctr5) as CHSIUpRGU " & _
  339. "FROM infoddp.current_rpt_centers a " & _
  340. "JOIN sales_history.upgrade_rpt_centers b on a.corp = b.corp and a.house = b.house and a.cust = b.cust " & _
  341. "JOIN infoddp.temp_rpt_centers d on d.corp = a.corp and d.house = a.house and d.cust = a.cust " & _
  342. "WHERE b.finalize_Date = CURDATE() - INTERVAL 1 DAY " & _
  343. "AND (a.ctr70 = 0 and a.ctr47 = 0 and a.ctr5 = 0) AND (b.ctr70 = 1 or b.ctr47 = 1 or b.ctr5 = 1) and (d.ctr70 = 1 or d.ctr47 = 1 or d.ctr5 = 1) " & _
  344. "GROUP BY house,cust; " & _
  345. "UPDATE sales_history.upgrade_rgu a JOIN UP_CHSI b on a.corp = b.corp and a.house = b.house and a.cust = b.cust and a.wipcount = b.wipcount and a.order_status = b.order_status " & _
  346. "SET chsi = chsiuprgu; " & _
  347. "CREATE TEMPORARY TABLE UP_BASIC SELECT a.corp, a.house, a.cust, b.wipcount, b.order_status, b.salesrep, SUM(b.ctr1 + b.ctr84 + b.ctr76) as BASICUpRGU " & _
  348. "FROM infoddp.current_rpt_centers a " & _
  349. "JOIN sales_history.upgrade_rpt_centers b on a.corp = b.corp and a.house = b.house and a.cust = b.cust " & _
  350. "JOIN infoddp.temp_rpt_centers d on d.corp = a.corp and d.house = a.house and d.cust = a.cust " & _
  351. "WHERE b.finalize_Date = CURDATE() - INTERVAL 1 DAY " & _
  352. "AND (a.ctr1 = 0 and a.ctr84 = 0 and a.ctr76 = 0) AND (b.ctr1 = 1 or b.ctr84 = 1 or b.ctr76 = 1) and (d.ctr1 = 1 or d.ctr84 = 1 or d.ctr76 = 1) " & _
  353. "GROUP BY house,cust; " & _
  354. "UPDATE sales_history.upgrade_rgu a JOIN UP_BASIC b on a.corp = b.corp and a.house = b.house and a.cust = b.cust and a.wipcount = b.wipcount and a.order_status = b.order_status " & _
  355. "SET basic = basicuprgu; " & _
  356. "CREATE TEMPORARY TABLE UP_DIGITAL SELECT a.corp, a.house, a.cust, b.wipcount, b.order_status, b.salesrep, SUM(b.ctr43 + b.ctr65) as DigitalUpRGU " & _
  357. "FROM infoddp.current_rpt_centers a " & _
  358. "JOIN sales_history.upgrade_rpt_centers b on a.corp = b.corp and a.house = b.house and a.cust = b.cust " & _
  359. "JOIN infoddp.temp_rpt_centers d on d.corp = a.corp and d.house = a.house and d.cust = a.cust " & _
  360. "WHERE b.finalize_Date = CURDATE() - INTERVAL 1 DAY " & _
  361. "AND (a.ctr43 = 0 and a.ctr65 = 0) AND (b.ctr43 = 1 or b.ctr65 = 1) and (d.ctr43 = 1 or d.ctr65 = 1) " & _
  362. "GROUP BY house,cust; " & _
  363. "UPDATE sales_history.upgrade_rgu a JOIN UP_DIGITAL b on a.corp = b.corp and a.house = b.house and a.cust = b.cust and a.wipcount = b.wipcount and a.order_status = b.order_status " & _
  364. "SET digital = digitaluprgu; " & _
  365. "CREATE TEMPORARY TABLE UP_CDV SELECT a.corp, a.house, a.cust, b.wipcount, b.order_status, b.salesrep, SUM(b.ctr92 + b.ctr40 + b.ctr88) as CDVUpRGU " & _
  366. "FROM infoddp.current_rpt_centers a " & _
  367. "JOIN sales_history.upgrade_rpt_centers b on a.corp = b.corp and a.house = b.house and a.cust = b.cust " & _
  368. "JOIN infoddp.temp_rpt_centers d on d.corp = a.corp and d.house = a.house and d.cust = a.cust " & _
  369. "WHERE b.finalize_Date = CURDATE() - INTERVAL 1 DAY " & _
  370. "AND (a.ctr92 = 0 and a.ctr40 = 0 and a.ctr88 = 0) AND (b.ctr92 = 1 or b.ctr40 = 1 or b.ctr88 = 1) and (d.ctr92 = 1 or d.ctr40 = 1 or d.ctr88 = 1) " & _
  371. "GROUP BY house,cust; " & _
  372. "UPDATE sales_history.upgrade_rgu a JOIN UP_CDV b on a.corp = b.corp and a.house = b.house and a.cust = b.cust and a.wipcount = b.wipcount and a.order_status = b.order_status " & _
  373. "SET cdv = cdvuprgu; " & _
  374. "TRUNCATE DAILY_TSR; " & _
  375. "REPLACE INTO DAILY_TSR SELECT DST_ID, CURDATE() - INTERVAL 1 DAY, 0 ,0,0,0 " & _
  376. "FROM employee_data.main WHERE EMP_DEPT = 'Customer Service' AND EMP_ROLE NOT IN('Cable Store','Other') ; " & _
  377. "CREATE TEMPORARY TABLE tot_up_rgu SELECT salesrep, finalize_date, SUM(IF(basic + digital >= 1,1,0) + chsi + cdv) as TURGU " & _
  378. "FROM(upgrade_rgu) " & _
  379. "WHERE(finalize_date Is Not null) " & _
  380. "GROUP BY salesrep; " & _
  381. "CREATE TEMPORARY TABLE tot_in_rgu SELECT salesrep, finalize_date, SUM(IF(basic + digital >= 1,1,0) + chsi + cdv) as TIRGU " & _
  382. "FROM(install_rgu) " & _
  383. "WHERE(finalize_date Is Not null) " & _
  384. "GROUP BY salesrep; " & _
  385. "CREATE TEMPORARY TABLE sum_calls " & _
  386. "SELECT DST_ID, rowdate, SUM(ACDCalls) Calls " & _
  387. "FROM employee_data.main " & _
  388. "JOIN oadb_oauser.dcmsagent " & _
  389. "WHERE EMP_EXT = LOGID AND EMP_DEPT = 'Customer Service' AND EMP_ROLE NOT IN ('Cable Store','Other') " & _
  390. "AND ROWDATE = CURDATE() - INTERVAL 1 DAY " & _
  391. "AND SPLIT BETWEEN 590 and 602 " & _
  392. "GROUP BY DST_ID; " & _
  393. "UPDATE daily_tsr " & _
  394. "JOIN sum_calls on dst_id = salesrep and rowdate = finalize_date " & _
  395. "SET acdcalls = calls; " & _
  396. "UPDATE daily_tsr t " & _
  397. "JOIN tot_in_rgu r on t.salesrep = r.salesrep and t.finalize_date = r.finalize_date " & _
  398. "SET InstallRGUs = TIRGU; " & _
  399. "UPDATE daily_tsr t " & _
  400. "JOIN tot_up_rgu r on t.salesrep = r.salesrep and t.finalize_date = r.finalize_date " & _
  401. "SET UpgradeRGUs = TURGU; " & _
  402. "update daily_tsr " & _
  403. "set tsr = (installrgus + upgradergus) / acdcalls; " & _
  404. "REPLACE INTO infoddp.CUST_RATE SELECT * FROM infoddp.TEMP_CUST_RATE; " & _
  405. "TRUNCATE infoddp.TEMP_CUST_RATE; " & _
  406. "REPLACE INTO infoddp.WIP_CUST_RATE SELECT * FROM infoddp.TEMP_WIP_CUST_RATE; " & _
  407. "TRUNCATE infoddp.TEMP_WIP_CUST_RATE"
  408.             Dim cmd As New MySqlCommand(queryString, sqlConn)
  409.             cmd.CommandTimeout = 0
  410.             cmd.ExecuteNonQuery()
  411.             cmd.Dispose()
  412.  
  413.         Catch ex As Exception
  414.             Debug.Print("Failed attempt to insert MySQL string: " & queryString)
  415.             Debug.Print(ex.Message.ToString)
  416.             Debug.Print("Giving up")
  417.             Exit Try
  418.  
  419.         End Try
  420.  
  421.  
  422.         Me.BeginInvoke(callFinishNewSalesReport)
  423.  
  424.         sqlConn.Close()
  425.         sqlConn.Dispose()
  426.  
  427.     End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement