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);
}
}
