static void createActivetedPINsReportFile(DateTime pActivatedOn, string pActivePinsFilePath, string pFtpFolder) { var _reportFileName = "Cingular_Active_PINS_" + pActivatedOn.ToString("yyyy_MM_dd") + ".pins"; var _reportFilePath = Path.Combine(Path.GetDirectoryName(pActivePinsFilePath), _reportFileName); var _tdate = (pActivatedOn.Year * 1000) + pActivatedOn.DayOfYear; using (var _sr = new StreamReader(pActivePinsFilePath)) { using (var _sw = new StreamWriter(_reportFilePath, false)) { string _line; while ((_line = _sr.ReadLine()) != null) { var _phoneCardRecord = PhoneCardRecord.Parse(_line); if (_phoneCardRecord.TimokActivatedOn == _tdate) { _sw.WriteLine(_phoneCardRecord.ToReportFileString()); } } } } if (File.Exists(Path.Combine(pFtpFolder, _reportFileName))) { //backup existing file var _activePinsFolder = Path.GetDirectoryName(pActivePinsFilePath); File.Copy(Path.Combine(pFtpFolder, _reportFileName), Path.Combine(_activePinsFolder, _reportFileName) + ".existed_backup_" + DateTime.Now.ToString("yyyyMMdd_HHmmss")); } File.Copy(_reportFilePath, Path.Combine(pFtpFolder, _reportFileName), true); }
SortedList getActiveted(DateTime pActivatedOn, short pCustomerAcctId, string pActivePinsFilePath, string pConnectionString) { /* * SET @sql = N' * CREATE TABLE #ActivePINs (active_pin bigint PRIMARY KEY, start_balance decimal(9,2), ani bigint, timok_day_active int); * BULK INSERT #ActivePINs * FROM ''' + @ActivePINsFilePath + N'''; * * SELECT PH.pin, RA.start_balance, CDR.ANI * FROM RbrDb_266.dbo.PhoneCard AS PH * INNER JOIN * RbrDb_266.dbo.RetailAccount AS RA ON * PH.retail_acct_id = RA.retail_acct_id * INNER JOIN * ' + @CdrDbName + N'.dbo.CDR AS CDR ON PH.serial_number = CDR.serial_number * * WHERE PH.serial_number IN * ( * SELECT serial_number FROM ' + @CdrDbName + N'.dbo.CDR * GROUP BY serial_number, customer_acct_id * HAVING MIN(timok_date / 100) = ' + @tday + N' AND customer_acct_id = ' + @customer_acct_id_char + N' * ) * AND PH.pin NOT IN * ( * SELECT active_pin FROM #ActivePINs * ) * ORDER BY PH.pin '; */ var _tday = (pActivatedOn.Year * 1000) + pActivatedOn.DayOfYear; var _cdrDbName = "CDRDb_" + Core.Config.Configuration.Instance.Db.CdrDbVersion + "_" + pActivatedOn.Year + pActivatedOn.Month.ToString("00"); var _sql = " CREATE TABLE #ActivePINs (active_pin bigint PRIMARY KEY, start_balance decimal(9,2), ani bigint, timok_day_active int); " + "BULK INSERT #ActivePINs FROM '" + pActivePinsFilePath + "'; SELECT PH.pin, RA.start_balance, CDR.ANI " + " FROM RbrDb_" + Core.Config.Configuration.Instance.Db.RbrDbVersion + ".dbo.PhoneCard AS PH INNER JOIN " + " RbrDb_" + Core.Config.Configuration.Instance.Db.RbrDbVersion + ".dbo.RetailAccount AS RA ON PH.retail_acct_id = RA.retail_acct_id INNER JOIN " + " " + _cdrDbName + ".dbo.CDR AS CDR ON PH.serial_number = CDR.serial_number WHERE PH.pin NOT IN ( SELECT active_pin FROM #ActivePINs ) AND PH.serial_number IN ( SELECT serial_number FROM " + _cdrDbName + ".dbo.CDR " + " GROUP BY serial_number, customer_acct_id HAVING MIN(timok_date / 100) = " + _tday + " AND customer_acct_id = " + pCustomerAcctId + " ) ORDER BY PH.pin, CDR.start; "; using (var _conn = new SqlConnection(pConnectionString)) { _conn.Open(); var _cmd = new SqlCommand { Connection = _conn, CommandType = CommandType.Text, CommandText = _sql }; //IDbDataParameter _activatedOnParam = _cmd.CreateParameter(); //_activatedOnParam.ParameterName = "@ActivatedOn";//@CustomerAcctId @ActivePINsFilePath //_activatedOnParam.DbType = DbType.Date; //_activatedOnParam.Value = pActivatedOn; //_cmd.Parameters.Add(_activatedOnParam); //IDbDataParameter _customerAcctIdParam = _cmd.CreateParameter(); //_customerAcctIdParam.ParameterName = "@CustomerAcctId"; //_customerAcctIdParam.DbType = DbType.Int32; //_customerAcctIdParam.Value = pCustomerAcctId; //_cmd.Parameters.Add(_customerAcctIdParam); //IDbDataParameter _activePINsFilePathParam = _cmd.CreateParameter(); //_activePINsFilePathParam.ParameterName = "@ActivePINsFilePath"; //_activePINsFilePathParam.DbType = DbType.AnsiString; //_activePINsFilePathParam.Value = pActivatedOn; //_cmd.Parameters.Add(_activePINsFilePathParam); var _list = new SortedList(); using (var _reader = _cmd.ExecuteReader()) { if (_reader != null) { while (_reader.Read()) { var _phoneCardRecord = new PhoneCardRecord(_reader.GetInt64(0), //pin _reader.GetDecimal(1), //start amnt _reader.GetInt64(2), //ani pActivatedOn); if (!_list.Contains(_phoneCardRecord.Pin)) { _list.Add(_phoneCardRecord.Pin, _phoneCardRecord); } } } } return(_list); } }