コード例 #1
0
        static void transferData()
        {
            DataClasses1DataContext d1 = new DataClasses1DataContext();
            DataClasses2DataContext d2 = new DataClasses2DataContext();

            Console.WriteLine("Attempting to load data from mi database....");
            List <DATAENTRY> dataEntries = d2.ExecuteQuery <DATAENTRY>(@"
                SELECT [AWBNO]
      ,[BKDATE]
      ,[DEST]
      ,[CLCODE]
      ,[CONSIGNEE]
      ,[CNEADDRESS]
      ,d.[TYPE]
      ,[BMODE]
      ,[MMODE]
      ,[PKGS]
      ,[BWT]
      ,[MWT]
      ,[PMODE]
      ,[AMOUNT]
      ,[POD_DATE]
      ,[POD_TIME]
      ,[STATUS]
      ,[REMARKS]
      ,[USERNAME]
      ,[BILLNO]
      ,[network]
      ,d.[destination]
      ,[clname]
      ,[tdesc]
      ,[tamt]
      ,[ntdesc]
      ,[ntamt]
      ,[fname]
      ,[famt]
      ,[fcode]
      ,[adsns]
      ,[state]
      ,[NETNO]
      ,[dlvboy]
      ,[mainbilno]
      ,[BRANCH]
      ,[MFNO]
      ,[MFDATE]
      ,[MFWD]
      ,[Actualdate]
      ,[upload]
      ,[Shpchrg]
      ,[dtdamt]
      ,[zone]
  FROM [mi].[dbo].[DATAENTRY] d 
  join [BillingDatabase].dbo.[Transaction] t 
  on t.ConnsignmentNo COLLATE DATABASE_DEFAULT = d.AWBNO COLLATE DATABASE_DEFAULT

            ").ToList();

            dataEntries = dataEntries.Where(x => x.clname.Contains("GLAXO")).ToList();
            Console.WriteLine("Total records loaded : {0}", dataEntries.Count);
            Console.WriteLine("Attempting to load data from BillingDatabase...");
            List <Transaction> transactions = d1.ExecuteQuery <Transaction>(@"
    SELECT [ID]
      ,[AmountPayed]
      ,[AmountCharged]
      ,[ConnsignmentNo]
      ,[Weight]
      ,[WeightByFranchize]
      ,t.[Destination]
      ,[DestinationPin]
      ,[UserId]
      ,[BookingDate]
      ,[AddDate]
      ,[LastModified]
      ,t.[Type]
      ,[Mode]
      ,[DOX]
      ,[ServiceTax]
      ,[SplDisc]
      ,[InvoiceNo]
      ,[InvoiceDate]
      ,[CustCode]
      ,[TransMF_No]
      ,[BilledWeight]
      ,[ConsigneeName]
      ,[ConsigneeAddress]
      ,[ConsignerName]
      ,[ConsignerAddress]
      ,[SubClient]
      ,[DeliveryStatus]
  FROM [BillingDatabase].[dbo].[Transaction] t
  join mi.dbo.DATAENTRY d
  on
  d.AWBNO COLLATE DATABASE_DEFAULT = t.ConnsignmentNo COLLATE DATABASE_DEFAULT
").ToList();

            Console.WriteLine("Total records loaded from BillingDatabase: {0} ", transactions.Count);
            List <String> clients = d1.Clients.Select(x => x.CLCODE).ToList();

            Console.WriteLine("Attempting to modify data..... ");
            int i = 0;

            foreach (Transaction transaction in transactions)
            {
                DATAENTRY dataEntry = dataEntries.SingleOrDefault(x => x.AWBNO == transaction.ConnsignmentNo);
                if (dataEntry != null)
                {
                    transaction.AmountCharged = (decimal)(dataEntry.AMOUNT ?? 0);
                    transaction.CustCode      = "KIM";
                    transaction.SubClient     = dataEntry.CLCODE;
                }
                i++;
                if (i % 250 == 0)
                {
                    Console.WriteLine(i.ToString() + " records processed...");
                }
            }
            Console.WriteLine(d1.GetChangeSet().Updates.Count.ToString() + " changes to be done... ");
            d1.SubmitChanges();
            Console.WriteLine("Changed successfully....");
            Console.ReadLine();
        }
コード例 #2
0
        static void funcToHandleAllRecords()
        {
            DataClasses1DataContext d1 = new DataClasses1DataContext();
            DataClasses2DataContext d2 = new DataClasses2DataContext();

            Console.WriteLine("Attempting to load data from mi database....");
            List <DATAENTRY> dataEntries = d2.ExecuteQuery <DATAENTRY>(@"
                SELECT [AWBNO]
      ,[BKDATE]
      ,[DEST]
      ,[CLCODE]
      ,[CONSIGNEE]
      ,[CNEADDRESS]
      ,d.[TYPE]
      ,[BMODE]
      ,[MMODE]
      ,[PKGS]
      ,[BWT]
      ,[MWT]
      ,[PMODE]
      ,[AMOUNT]
      ,[POD_DATE]
      ,[POD_TIME]
      ,[STATUS]
      ,[REMARKS]
      ,[USERNAME]
      ,[BILLNO]
      ,[network]
      ,d.[destination]
      ,[clname]
      ,[tdesc]
      ,[tamt]
      ,[ntdesc]
      ,[ntamt]
      ,[fname]
      ,[famt]
      ,[fcode]
      ,[adsns]
      ,[state]
      ,[NETNO]
      ,[dlvboy]
      ,[mainbilno]
      ,[BRANCH]
      ,[MFNO]
      ,[MFDATE]
      ,[MFWD]
      ,[Actualdate]
      ,[upload]
      ,[Shpchrg]
      ,[dtdamt]
      ,[zone]
  FROM [mi].[dbo].[DATAENTRY] d 
  join [BillingDatabase].dbo.[Transaction] t 
  on t.ConnsignmentNo COLLATE DATABASE_DEFAULT = d.AWBNO COLLATE DATABASE_DEFAULT

            ").ToList();

            Console.WriteLine("Total records loaded : {0}", dataEntries.Count);
            Console.WriteLine("Attempting to load data from BillingDatabase...");
            List <Transaction> transactions = d1.ExecuteQuery <Transaction>(@"
    SELECT [ID]
      ,[AmountPayed]
      ,[AmountCharged]
      ,[ConnsignmentNo]
      ,[Weight]
      ,[WeightByFranchize]
      ,t.[Destination]
      ,[DestinationPin]
      ,[UserId]
      ,[BookingDate]
      ,[AddDate]
      ,[LastModified]
      ,t.[Type]
      ,[Mode]
      ,[DOX]
      ,[ServiceTax]
      ,[SplDisc]
      ,[InvoiceNo]
      ,[InvoiceDate]
      ,[CustCode]
      ,[TransMF_No]
      ,[BilledWeight]
      ,[ConsigneeName]
      ,[ConsigneeAddress]
      ,[ConsignerName]
      ,[ConsignerAddress]
      ,[SubClient]
      ,[DeliveryStatus]
  FROM [BillingDatabase].[dbo].[Transaction] t
  join mi.dbo.DATAENTRY d
  on
  d.AWBNO COLLATE DATABASE_DEFAULT = t.ConnsignmentNo COLLATE DATABASE_DEFAULT
").ToList();

            Console.WriteLine("Total records loaded from BillingDatabase: {0} ", transactions.Count);
            List <String> clients = d1.Clients.Select(x => x.CLCODE).ToList();

            Console.WriteLine("Attempting to modify data..... ");
            int i = 0;

            foreach (Transaction transaction in transactions)
            {
                DATAENTRY dataentry = dataEntries.SingleOrDefault(x => x.AWBNO == transaction.ConnsignmentNo);
                if (dataentry != null)
                {
                    if (transaction.Destination != null && transaction.Destination != "")
                    {
                        try
                        {
                            switch (dataentry.CLCODE.Trim())
                            {
                            case "REKR":
                                transaction.CustCode = "MVHS";
                                break;

                            case "SUNY":
                                transaction.CustCode = "suny";
                                break;

                            case "DLFC":
                                transaction.CustCode = "DISHL";
                                break;

                            case "TVLS":
                                transaction.CustCode = "TRVL";
                                break;

                            case "OFCE":
                                transaction.CustCode = "<NONE>";
                                break;

                            case "HYMI":
                                transaction.CustCode = "HONY";
                                break;

                            case "TREF":
                                transaction.CustCode = "TREF ";
                                break;

                            case "MANJ":
                                transaction.CustCode = "mnoj";
                                break;

                            default:
                                if (clients.Contains(dataentry.CLCODE))
                                {
                                    transaction.CustCode = dataentry.CLCODE;
                                }
                                else
                                {
                                    throw new Exception("No client found.. " + dataentry.CLCODE);
                                }
                                break;
                            }

                            transaction.BilledWeight  = dataentry.BWT;
                            transaction.AmountCharged = (decimal)UtilityClass.getCost(transaction.CustCode, transaction.BilledWeight ?? 0.0, transaction.Destination, transaction.Type, transaction.DOX);
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(transaction.ConnsignmentNo + " : " + e.Message);
                        }
                    }
                    else
                    {
                        Console.WriteLine(transaction.ConnsignmentNo + " : " + "Cannot find destination ");
                    }
                }

                else
                {
                    Console.WriteLine(transaction.ConnsignmentNo + " : " + "Cannot find this transaction in database");
                }
                i++;
                if (i % 250 == 0)
                {
                    Console.WriteLine(">>" + i.ToString() + "Records done....");
                }
            }
            Console.WriteLine("Total records to be affected: " + d1.GetChangeSet().Updates.Count);
            Console.WriteLine("Attempting to save records.... ");
            d1.SubmitChanges();
            Console.WriteLine("Changes done.....");
        }